Is Replication the answer
-
I currently have a test server and a production server both running SQL Server 2000. The data on the test server is not being kept current. We are getting ready to be forced to use the test server before our code is put into production. I was wondering if replication was the best solution to keep the test server current with the production server? All of our staff are mostly programmers and have minimal experience administering SQL Server.
Thank you in advance for any help
-
Let's assume all you need is a test area, with current database structures and a reasonably fresh data snapshot. If this assumption is wrong -- if you need up to the minute data concurrency between test and prod -- the rest of this discussion doesn't apply. You'll probably need to use one of the higher end forms of replication -- merge or transactional. And good luck implementing this without an experienced DBA to devote to the issue.
'Snapshot' replication sounds to me like it would be a workable solution for you. Ideally you'd schedule this at a non-peak time, probably once a day at most (depending on how much data you're moving, maybe only once a week), and you'd have to be content with a data image that gets funkier and funkier until the next scheduled replication event.
If you use replication, one thing to be careful about is setting up the communication layer between servers. If you think at some future time you'll need to define a 'linked server' relationship between test and prod, you should do this *before* you install replication. If you don't, the replication wizards will give you a default 'remote server' connection. Once a remote server connection exists, you'll play hell trying to attach using a linked server. But if the linked server already exists, it be cool.
Even so, I do not think replication is a route that ought to be lightly taken by those with minimal experience in SQL Server. Some DBAs do nothing *but* replication. It's a subject area with more than its share of hidden "gotchas!". Personally, I hate replication, and consider using it only when beaten with a garden hose until I'm a whimpering, quivering pile of bloody protoplasm.
The alternatives suggested below would be in lieu of using 'snapshot replication'.
It sounds like all you really need to do is, on a somewhat regular basis, to restore a database on your test server from a production database backup. Yes, there are gotchas here, too, but more manageable ones, in my view. For one thing, restoring a database will overwrite your procedures and functions, but you can simply solve that by using Source Safe and making your programmers responsible for versioning their own code. Another is that you'll need to re-attach database users in your restored test database to the logins defined on the test server -- restoring a database from another server will put these completely out of synch. Usually, the 'sp_change_users_login' procedure is sufficient for this task.
Depending on the amount of data and the number of tables, you could also write a script that, one table at a time, transfers data contents from production to test using the BCP command. The biggest "gotcha" here is that, if you have FOREIGN KEY constraints on your test database, you'll find it efficacious to drop them before you start, and re-create them when you're done. In fact, it will run faster if you drop all the indexes and primary keys and restore them afterwards, as well.
On your test server, you could also set up the prod server as a "linked server" and schedule a series of TRUNCATE TABLE followed by INSERT commands. Again, foreign keys or the sheer volume of data being transferred could work against you.
If you use the Data Transfer wizard, good luck. I hate it. It's evil.
-
Lee has a great summary although I'd avoid the linked server item. If you're a public corp I'm sure it would violate Sarbanes Oxley standards.
I've always done backup/restore, but the snapshot item sounds interesting. Might need to do some testing there
I've had horrible luck with the DTS tasks, so I've avoided them.
-
> If you're a public corp I'm sure it would violate Sarbanes Oxley standards.
I don't even know what Sore Ben's Oxnard is, if it isn't a town on the California coast. Is this a security thing?
> I've always done backup/restore, but the snapshot item sounds interesting.
Backup/restore pretty much does it for me. We do use snapshot replication here, but mainly because I couldn't talk them out of it. Our other DBA isn't as ADD as I am, and seems to have better luck making it sit up and beg. It is good to be the master and not the dog.
> Might need to do some testing there
Why would you want to do that!??
> I've had horrible luck with the DTS tasks, so I've avoided them.
I hold it highly plausible that those good folks who coded up those wizards may have been bereft of any insight into such things as: IDENTITY columns; FOREIGN KEY constraints; object dependencies. All that, and the highly informative message box, "The data transfer has failed." What less could you want?
-
Thank you very much for the prompt reply. I think you guys have made up my mind as far as replication goes. The backup/restore solution sounds safer to me, especially considering the SQL background of our IT group.
This was my first post(definitely will not be the last), and I am very satisfied.
Thanks for you help.
-
I posted a script that does this whole task for my Developers, plus it uses the backup date to uniquely name the database, so it will not overwrite older versions on the Dev server.
http://www.sqlservercentral.com/scripts/contributions/764.asp
Andy
-
When using a script to restore the backup file to a Development server, how do you handle the problem of newly added tables or fields in the development environment.
Robby
-
If you're restoring from backups, you will be overwriting all database objects in the development database. You'll need to keep any dev table definition changes, procedures, functions, etc. that you want to keep separately (e.g. in Source Safe), and then re-create them after the restore.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply