March 7, 2012 at 12:21 pm
I've got replication set up between 3 separate physical servers:
1) Publisher (production OLTP system)
2) Distributor (this is all this machine is used for)
3) Subscriber (Used for reporting)
When I had set this up a few days ago, I took the snapshots and got everything up and running. Now I'd like to add in a 2nd and 3rd Subscriber (so an additional 2 servers) to the mix. However, when I created the subscription on the new server it immediately began creating a new snapshot! I stopped that - as it is not acceptable since the publisher is an OLTP machine and downtime needs to be scheduled.
Surely there's a way around having to create new snapshots every time you create a subscription to the publisher???
In a publications properties there's a "Snapshot always available". By default, it seems to always be set to "False". Does anyone know what/how you can set it to "True"? I assume that having that value set to true would allow me to create multiple subscriptions?
Am I doing something wrong in the process?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 7, 2012 at 12:47 pm
When you create the subscription, it wall ask you if you want to initialize after you create the subscription. Make sure to say no here.
Jared
CE - Microsoft
March 7, 2012 at 2:50 pm
This route will allow it to be created but immediately results in errors stating that the rows are not found and the subscriber...
Seems like there should be a setting somewhere to make this setting = True? Also have ths sinking that this will end up in me having to take the snapshots all over again...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 7, 2012 at 2:58 pm
Maybe I am misunderstanding... If you don't initialize with a snapshot, the tables have to be manually synced for all data before replication began. Otherwise you must push a snapshot. We don't see any issues with pushing a snapshot to reporting as long as the agent takes anywhere from 1-1.5 minutes. It doesn't affect our production databases. Of course, everyones situation is different. Why are you concerned with the snapshot running?
Jared
CE - Microsoft
March 8, 2012 at 2:51 am
SQLKnowItAll (3/7/2012)
When you create the subscription, it wall ask you if you want to initialize after you create the subscription. Make sure to say no here.
Unless you do it through TSQL in which you have full control. In a production environment its generally too risky to be setting up replication through the wizard as it does a lot of things which you likely wont want or need without asking.
March 8, 2012 at 5:46 am
MysteryJimbo (3/8/2012)
SQLKnowItAll (3/7/2012)
When you create the subscription, it wall ask you if you want to initialize after you create the subscription. Make sure to say no here.Unless you do it through TSQL in which you have full control. In a production environment its generally too risky to be setting up replication through the wizard as it does a lot of things which you likely wont want or need without asking.
Like what? We use transactional replication and transactional replication with updateable subscriptions on almost every single one of our databases. We administer them all using the wizard from set up to recreating if something "stuffs up." I'm not sure what things you would need to set that you cannot control through the wizard.
If the database is small and small amount of transactions/minute (totally subjective of course) we use the snapshot agent to initialize. If the database is too large and/or there are way too many transactions/minute we do not use snapshot, but sync manually, start replication, then sync again for info that did not get replicated.
Jared
CE - Microsoft
March 8, 2012 at 6:28 am
I've added new articles in the past which reinitalise subscribers when it isnt required is one example.
Unpredictable and multiple schema locks when dropping subscriptions is another.
Managing something as potentially damaging through TSQL is more reliable and you have greater control should problems arise, i.e. the wizard hangs.
March 8, 2012 at 9:40 am
This is a highly active OLTP production server where we can easily take a 100 transactions/minute so creating snapshots (which place table locks) is not an option unless we carefully plan for it.
Shouldn't I be able to create a new snapshot on Publisher via the GUI, choose "No" to initialize, and then create the subscriptions elsewhere? Doesn't t the subscriber DB use the snapshot that's created to sync before it starts applying replicated commands from the distributor?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 8, 2012 at 9:41 am
If the database is too large and/or there are way too many transactions/minute we do not use snapshot, but sync manually, start replication, then sync again for info that did not get replicated.
What are the steps involved to syncing manually?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 8, 2012 at 9:57 am
MyDoggieJessie (3/8/2012)
If the database is too large and/or there are way too many transactions/minute we do not use snapshot, but sync manually, start replication, then sync again for info that did not get replicated.
What are the steps involved to syncing manually?
When we create the subscription, we uncheck the "initialize" box. That let's SQL Server know that it can go ahead and begin replicating without having to initialize. Then we manually sync by a filtered merge or SQL Data Compare by Redgate.
It was my understanding that the snapshot agen creates a concurrent snapshot that does not lock tables. I will have to research that more, but we have never seen any issues.
Jared
CE - Microsoft
March 8, 2012 at 11:09 am
If you run a snapshot via the GUI (which is what I always use) it immediately places table/object locks on the tables being bcp'd. This works okay for tables that are transaction-related but creates chaos on the tables which are being constantly written/read to/from.
Pretend I'm a complete idiot (not too far from the truth :hehe: I'm afraid), how do you go about the sync in your approach? What specifically are the steps involved?
Then we manually sync by a filtered merge or SQL Data Compare by Redgate.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 8, 2012 at 11:29 am
MyDoggieJessie (3/8/2012)
If you run a snapshot via the GUI (which is what I always use) it immediately places table/object locks on the tables being bcp'd. This works okay for tables that are transaction-related but creates chaos on the tables which are being constantly written/read to/from.Pretend I'm a complete idiot (not too far from the truth :hehe: I'm afraid), how do you go about the sync in your approach? What specifically are the steps involved?
Then we manually sync by a filtered merge or SQL Data Compare by Redgate.
We really just look for missing rows in the subscriber. In the large database, we have 4 publishers all converging on 1 subscriber. The easiest, of course, is to use RedGate's SQL Data Compare because it will generate all of the needed insert scripts. However, we may also just do a INSERT INTO subscriberTable SELECT * FROm publisherTable LEFT JOIN ON primaryKeyColumn(s) WHERE subscriberTable.someColumn IS NULL.
Full set up:
1. Create publication
2. Backup publisher database
3. Restore publisher database on subscriber
4. Create subscription WITHOUT initialization
5. Verify replication is working
6. Use 1 of the above methods to insert missing rows into subscriber
Hopefully this helps! 🙂
Jared
CE - Microsoft
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply