Microsoft SQL Server Q&A
Configuring Transactional Replication in SQL 2008 standard ed.
Last Post 14 Feb 2013 08:46 AM by Marc. 3 Replies.
13 Feb 2013 01:47 PM
One SQL 2008 server is used for Production and the other SQL 2008 Server is used for Testing purposes, these are on different servers in the same subnet.
I would like to set up Transactional Replication between these two machines Production & Test but I am unsure if we can actually do this.
What I need to know is if you can have the replication from the Publisher (Production) to the Subsciber (Test) but have the names of the databases be different once in Test so they dont interfere with testing of new code in the Test database server.
In Production we have 12 databases and the sames ones exist on our test server.
Database Az on Production and Database Az on Test.
Can replication be set up so that database Az on Production will be either a different name like Az1 on the Test server (Subscriber) or can I have this replication going to another instance on the Test server so that we dont interfere with our test code.
Hopefully this convoluted question makes sense to someone :)
I am new to SQL and learning as I go and I keep getting tasked with more things to configure. geuss it is the best way to learn.
thanks for your assistance.
14 Feb 2013 05:51 AM
Don't need same db name for replication, but table names have to be same.
14 Feb 2013 08:24 AM
As rm stated yes you can do that but you also might want to consider using backup & restore instead of replication. Usually the test instance doesn't need to be 100% up to date with prod so often times you can get away with restoring a backup to test once a week or so. Replication to test can cause certian unwanted side effects. For isntance if the test instance is down then the replication gets queued up on prod. And if you have an exteneded down time it can affect your logs on prod. Also if you are doing any changes on the test instance you may break the replication depending on what you are changing. Just something to consider.
14 Feb 2013 08:46 AM
I appreciate the assistance. I need to spend more time thinking and reading about this. I tend to agree with you though as right now we are doing backups and copying over current prod database into another server and appending the names. Might be that it is better to stay with this setup and keep it simple, especially seeing how we do not have a DBA. And I am relunctantly being tasked with this, but I believe it is good for my career :)
thanks again gentlemen!
Microsoft SQL Server Q&A
Acceptable Use Policy