December 15, 2009 at 2:23 pm
I have been assigned a project to replicate several hundred databases from a half dozen SQL servers to a single QA SQL server. My initial thought was that snapshot replication would be good for this but the more I look into to it that doesn't seem to be the case.
As far as I can tell when setting up a publication you are not able to select multiple databases to be included in that publication. This is a problem for me as I have over 300 databases on single server. I have also noticed that the database has to exist on the subscriber side in order for the original initialization to occur which is also a problem with the amount of databases that I need to replicate to the subscriber.
Does anyone have any suggestions on how I can work around these issues? Or of their is something I'm missing in this setup please let me know.
December 15, 2009 at 3:08 pm
Unfortunately snapshot replication is per database so, you would have to set it up 300 times if that is the number of databases you have to copy.
Honestly your best bet is probably going to be pulling a set of your production backups and start restoring on your QA server.
Of course, if this has to be repeated at set intervals than snapshot would be the best thing to do or plan on configuring snapshot or other replication methods to ensure that it is automated in the future.
Hopefully someone else can think of something easier.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 15, 2009 at 3:41 pm
My suggestion would be going with SSIS Packages.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 16, 2009 at 3:44 am
If you're just needing to get a copy of the databases in their current state onto another server, and are not too concerned with applying new transactions etc, then why don't you just take a copy of the latest backup for each each db and script a restore on the QA server
December 17, 2009 at 8:52 am
If I had to do this, "replicate several hundred databases from a half dozen SQL servers", I'd just create a script to write out the replication TSQL commands and execute them. In other words, have a query that loops through the databases on each server instance and dynamically create and execute the relevant TSQL replication commands to enable publishing, create the publication, designate the subscriber, etc.
That is pretty much what we do internally now, although we drive the process with tables listing all the publishers, publications, articles, and subscribers.
December 29, 2009 at 1:04 pm
Don't forget the old standby, bcp, which can be easily scripted (and is what snapshot replication is using behind the scenes). In fact there are at least a couple of generic scripts for this right here in the SSC repository.
December 30, 2009 at 9:28 am
we can use Attach&Detach methods
December 30, 2009 at 10:23 am
nvkumar12 (12/30/2009)
we can use Attach&Detach methods
Detach / Attach requires the primary database to be offline. Which may not be a good way of doing this. :w00t:
Moreover the OP has not replied at least once, which indicates either the solution was found or the issue was dropped.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply