Making a copy of a replicated database
-
We have a production database in one location and the development team in a different physical location. We have a not-so-fast (T1) connection between the two locations. I need a way to periodically make snapshots of production for the developers to test with. The problem is that I don't want to bring the WHOLE production database across the WAN every time this happens - this takes hours and hogs bandwidth.
I originally figured that I could setup a secondary database at the development location and make it a log shipping target of the production database and then just take snapshots of this database whenever we need them. I have the secondary database successfully setup and in-sync via Log Shipping and it works like a champ ...BUT... (there is always a "but") I can't figure-out any way to make snapshots of it. I get an Error 3036 if I try to back it up. Obviously I don't want to recover the database because that breaks the replication. I have tried right-clicking on the DB in Management Studio and selecting Tasks --> Copy Database but using the "Detach and Attach" menthod breaks mirroring. Choosing the "Use the SQL management Object method" option runs for a few minutes and then fails with some ugly error application/0x00000000 in the event log.
Surely with all the millions of installations of SQL Server 2005 out there someone has come-up with a way of doing this successfully.
-
Instead of setting up Log shipping and restoring DB and have problems with taking snapshots, why not set up a replication between these two servers? Transactional would work fine.
From the replicated server you can take snapshot or Backups.
Another option is to install SQL 2008 and use its Compressed Back ups. It compresses pretty good. I was able to compress a 300 Gig DB to a 40 Gig Back up file.
Another option is to install a third party Back software like Lite speed. That also compresses pretty good.
-Roy
-
Roy Ernest (5/11/2009)
Another option is to install SQL 2008 and use its Compressed Back ups. It compresses pretty good. I was able to compress a 300 Gig DB to a 40 Gig Back up file.Another option is to install a third party Back software like Lite speed. That also compresses pretty good.
You can also use winrar to compress SQL Server 2000 and 2005 backups, they should shrink to about 10% of their original size
The advantage of winrar is that its relatively cheap, works on files of any size (unlike winzip) and has a command line interface so can be integrated with SSIS
-
Samuel Vella (5/11/2009)
Roy Ernest (5/11/2009)
Another option is to install SQL 2008 and use its Compressed Back ups. It compresses pretty good. I was able to compress a 300 Gig DB to a 40 Gig Back up file.Another option is to install a third party Back software like Lite speed. That also compresses pretty good.
You can also use winrar to compress SQL Server 2000 and 2005 backups, they should shrink to about 10% of their original size
The advantage of winrar is that its relatively cheap, works on files of any size (unlike winzip) and has a command line interface so can be integrated with SSIS
O)nly issue with WinRar si the additional time taken. Just imagine that the back up size is around 300 Gig. It will take an additional 1 hr atleast to Zip that up. But if we use a product like Litespeed, the time taken to Back up the file itself is reduced by half and it will be pretty compressed.
-Roy
-
I've not tried to setup transactional replication but that sounds like it could be a winner. Before I spend much time on it, I just want to verify:
1) With transactional replication, the production database will not wait-on the replicated database to commit before committing it's own transaction - correct?
2) The replicated database can be copied or backed-up with no trouble?
3) We can temporarily take the replicated database offline or reboot the second server without negatively impacting the production server or breaking the mirroring process?
If all are "yes" then that sounds like my winner.
-
bbeswick (5/11/2009)
I've not tried to setup transactional replication but that sounds like it could be a winner. Before I spend much time on it, I just want to verify:1) With transactional replication, the production database will not wait-on the replicated database to commit before committing it's own transaction - correct?
2) The replicated database can be copied or backed-up with no trouble?
3) We can temporarily take the replicated database offline or reboot the second server without negatively impacting the production server or breaking the mirroring process?
If all are "yes" then that sounds like my winner.
1. Production Database will have to work a bit more harder since it has to notify the Distributor of the changes. But this cost is very low. make sure that your Distributor is on a separate server.
2. Replicated DB can be Backed up without any issues. Just make sure you dont do any data changes on the replicated DB since this can cause the replication to fail.
3. Rebooting the Replicated server wont be of an issue. The Distributor will try to replicate the changes when it is back online. But taking it offline for a long time can make the replication to fail. You will have to reinitialize the data again.
-Roy
-
Excellent. Hopefully one last question: We only have two SQL Servers in operation - one Production server and one for Development / Testing.
Assuming that I don't have it in the budget to purchase another copy of SQL Server (at least in 2009), how/where would you recommend configuring the distributor?
-
bbeswick (5/11/2009)
Assuming that I don't have it in the budget to purchase another copy of SQL Server (at least in 2009), how/where would you recommend configuring the distributor?As far as I know, you don't need an extra license to install another instance of SQL Server on a server which already hosts SQL Server
This means that the distributor could sit on the development server (keeps it away from production)
I could well be wrong so feel free to shoot me down if that is the case
-
Thats a tricky situation. The reason behind it is
1. If you set up distributor on the production, then you have more load on the production Server (But that depends on the number of transactions you have)
2. If you set it up on the Development, you said something about not a great connectivity.
So I cant answer that question. You do not have to have the Distributor to be a very powerful system.
-Roy
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply