September 12, 2018 at 4:58 pm
How often is the good practice for replication snapshot schedule in sql server transactional replication? My understanding is that the snapshot is used for initial load only right? Later all the changes are pushed through agent? Then what is the purpose of schedule to run daily nightly. Please advise?
September 12, 2018 at 6:18 pm
Admingod - Wednesday, September 12, 2018 4:58 PMHow often is the good practice for replication snapshot schedule in sql server transactional replication? My understanding is that the snapshot is used for initial load only right? Later all the changes are pushed through agent? Then what is the purpose of schedule to run daily nightly. Please advise?
If you initialize everything with a snapshot (instead of initializing with a backup), it will only schedule the snapshot agent to run once for that initialization. And you should leave it at that. You are right that it's not needed that often with transactional replication. But you may need to reinitialize for things like adding a new subscriber or an existing subscriber becomes badly out of date or has been offline. You also may need to generate a snapshot with changes to the publication like adding articles, changes to published tables, etc
You could end up never using the snapshot agent - initialize from a backup and then it's always in sync, no changes, etc. But in reality it doesn't happen too often.
If you are still testing replication, go to Replication Monitor and generate a new snapshot and reinitialize one or more publications. Then go back in Replication monitor, go to the Agents tab, select the snapshot agent and view details. You'll see what it was doing when. The files it generates go to the replication share you set up - the default is repldata in your instance directory (instance\MSSQL\repldata). It all makes more sense if you can take some time to play around with those and see what's going on.
Sue
September 17, 2018 at 11:14 am
Thanks Sue! Another question, I am trying to script out the existing replication. When we upgrade, I have to setup the replication after the upgrade.
I can see when I right click on publication it gives option to create replication publication script. However, I am not seeing any option to create script for subscription and distribution? Please advise?
When I do the upgrade I will bring the databases from another server. However, the replication Jobs are existing on the server. So when I re setup the replication do I need to remove the Jobs so the replication scripts will create the Jobs right?
September 17, 2018 at 11:31 am
Admingod - Monday, September 17, 2018 11:14 AMThanks Sue! Another question, I am trying to script out the existing replication. When we upgrade, I have to setup the replication after the upgrade.
I can see when I right click on publication it gives option to create replication publication script. However, I am not seeing any option to create script for subscription and distribution? Please advise?When I do the upgrade I will bring the databases from another server. However, the replication Jobs are existing on the server. So when I re setup the replication do I need to remove the Jobs so the replication scripts will create the Jobs right?
Go up one more level where you are on the specific publication to script. So right click on Local Publications and select Generate script. From there you have the option for the jobs and the distributor properties. The script will tell you towards the bottom of the script what to run it at the subscriber.
Generate the script from the Local Publications folder and take a look at what it's doing. It covers what you are looking for. Otherwise you are generating the script for just the publication, nothing else.
Sue
September 18, 2018 at 10:40 am
Thanks Sue! Another question,
We are configuring transaction replication on the Test server which is going to be production(it has already replication configured from Server A to Server B). When I restore the database from production to this test server (i.e. Server A)the replication will be gone(which is valid since the replication is not exists on the current production server). Since I already configured the replication on Test server(Server A) it has distribution database and replication Jobs still exists from previous replication configuration to Server B). However, I have Generated the script before restoring the database from production backup. Do you think I should be good running the script with creating publisher only or I should delete the previous replication Jobs on Server A and subscriber on Server B and run the script which will create publisher, articles and subscriber and Jobs? Please advise?
September 18, 2018 at 11:37 am
Admingod - Tuesday, September 18, 2018 10:40 AMThanks Sue! Another question,
We are configuring transaction replication on the Test server which is going to be production(it has already replication configured from Server A to Server B). When I restore the database from production to this test server (i.e. Server A)the replication will be gone(which is valid since the replication is not exists on the current production server). Since I already configured the replication on Test server(Server A) it has distribution database and replication Jobs still exists from previous replication configuration to Server B). However, I have Generated the script before restoring the database from production backup. Do you think I should be good running the script with creating publisher only or I should delete the previous replication Jobs on Server A and subscriber on Server B and run the script which will create publisher, articles and subscriber and Jobs? Please advise?
You might be okay but often that doesn't really work too well just because remnants of replication often will exist in other areas. Since your still in test, it would be a really good idea to work on removing it all. Not just for getting rid of any of the old pieces but just so you get comfortable with wiping it all out - which can be a pain sometimes.
You can start by following the documentation on it:
Disable Publishing and Distribution
Keep in mind, you could still run into issues after following all of that. So when you run the script to recreate it all, don't execute it all at once but run it piece by piece which should help if you hit any errors in the script.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply