Comparing and updating a DB Clone with the Production DB
TimBee
Posts: 2
Hi. Hope you guys and girls can tell me how to proceed from here:
I have a live production webserver running MS SQL. The Database is +50GB in size. I need to move this webserver to another hosting provider so I've made a clone 2 weeks ago and I'm done now with cleaning up the clone.
Next step is to update the SQL Database on the clone with the changes made on the production server during the last 2 weeks.
Limitations: Upload/download speed between the two boxes: transferring 50GB will take a very long time and I can't afford that amount of downtime on the production box.
What I've tried so far is this:
SQL Compare: make a Snapshot of the cloned DB with "Red Gate Snapper". Transferred the Snapshot to the Production Server and ran SQL Compare on the Production Server using the Snapshot as the Target. The Schema of the Production Database and the Cloned one were identical.
SQL Data Compare: I also started this on the Production Server. I Generated a SQL Script out of the Snapshot I've used earlier for "SQL Compare" and used the Script Folder as the Target. The Data Comparison took a lot of time to complete and generated a massive amount of Temporary Data (about the size of the Database itself I guess).
After completion it looked like the Target Database was missing everything, which makes sense since I compared it against a Database Schema without any actual data in it.
A solution would be (I think) that I would be able to create a SQL Data Compare "Snapshot" of the Cloned Database, which in some way is limited in size so I could
upload it to the Production Server to run a comparison against the Production Database. After that is done I end up with the data of the last two weeks: SQL Data Compare will then create an Export/Script that I can transfer to the Cloned Server to update the DB with the missing data.
Is this possible? And if yes: What was I doing wrong/missing. As you probably already noticed: I'm not a SQL Expert.
My worst enemy is data transfer because of the limited transfer speed and size of the database.
Thanks a lot for any info you may have to help me out.
I have a live production webserver running MS SQL. The Database is +50GB in size. I need to move this webserver to another hosting provider so I've made a clone 2 weeks ago and I'm done now with cleaning up the clone.
Next step is to update the SQL Database on the clone with the changes made on the production server during the last 2 weeks.
Limitations: Upload/download speed between the two boxes: transferring 50GB will take a very long time and I can't afford that amount of downtime on the production box.
What I've tried so far is this:
SQL Compare: make a Snapshot of the cloned DB with "Red Gate Snapper". Transferred the Snapshot to the Production Server and ran SQL Compare on the Production Server using the Snapshot as the Target. The Schema of the Production Database and the Cloned one were identical.
SQL Data Compare: I also started this on the Production Server. I Generated a SQL Script out of the Snapshot I've used earlier for "SQL Compare" and used the Script Folder as the Target. The Data Comparison took a lot of time to complete and generated a massive amount of Temporary Data (about the size of the Database itself I guess).
After completion it looked like the Target Database was missing everything, which makes sense since I compared it against a Database Schema without any actual data in it.
A solution would be (I think) that I would be able to create a SQL Data Compare "Snapshot" of the Cloned Database, which in some way is limited in size so I could
upload it to the Production Server to run a comparison against the Production Database. After that is done I end up with the data of the last two weeks: SQL Data Compare will then create an Export/Script that I can transfer to the Cloned Server to update the DB with the missing data.
Is this possible? And if yes: What was I doing wrong/missing. As you probably already noticed: I'm not a SQL Expert.
My worst enemy is data transfer because of the limited transfer speed and size of the database.
Thanks a lot for any info you may have to help me out.
Comments
SQL Data Compare does not have the ability to take a snapshot of a database.
Its sister product SQL Compare has the ability to create a snapshot which contains just the database schema, no data is included.
If I were in your shoes, I would take a full backup of the current webserver database and restore the backup file on the new server.
Next using SQL Data Compare, compare the current production database with the one you have just restored and then deploy the changes that have taken place since the full backup was taken. Ensure that the option "Show identical values in results" is not enabled. Further you can also configure a 'Where' clause filter for each table, to reduce the amount of data to be compared.
Another alternative is to use SQL Backup. Take a full backup of the current webserver database and restore the backup file on the new server using either WITH NORECOVERY or WITH STANDBY recovering options.
Perform log backups of the source database, restore these to the new server / database sing either WITH NORECOVERY or WITH STANDBY recovering options. until you are happy that they are in sync. Then restore the last log backup WITH RECOVERY to bring the new server / database on line.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com