how to keep the snapshot in SQL replication forever?

Question

After a new column was added to the one of the articles in the replication. the replication failed. from the Distributor to Subscriber History tab in the SQL replication monitor, The replication failed due to "The initial snapshot for publication 'myDatabase' is not yet available". The snapshot was taken 3 years ago. This is a production server and some tables are huge. How often do we need to take snapshot to avoid this error from happening? Is possible to keep the snapshot for ever?

Answer 1

You need the snapshot to be fairly up to date all the time. Otherwise, it would be a problem if a subscriber needs to be initialized. The aim is to have a point in time starting point. Replication then just needs to make sure it has enough transaction history to bring the subscriber up to date from the time the snapshot was taken.
So for most systems, that's taken once per day. And that's usually not an issue at all.
What it looks like this is saying is that the snapshot isn't available. Given it was taken 3 years ago, it's no surprise that it's too old.
What you need to do is look through the list of jobs on your SQL Server Agent and find jobs that have job steps like REPL*. One of those will be the snapshot agent. You need to make sure that it is scheduled to run regularly (likely daily), and that it does run successfully. You can run it manually to find out.
Generally when it doesn't run, it's either the identity that it runs as doesn't have access to the replicated tables, or it doesn't have permission or access to write to the snapshot folder location. Make sure that it exists and is shared. Either way, in the log, you'll be able to see why it didn't run if it fails.