[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

MSSQL Data sync Stage to dev

I have two separate physical MS SQL 2008 servers that I would like to sync in a nightly job. The table structure will always be the same but I would like the data from the stage server  to overwrite,update, ect the data on the dev server.

What is the best way to accomplish this?
0
jgoodale
Asked:
jgoodale
  • 3
  • 2
  • 2
2 Solutions
 
lcohanDatabase AnalystCommented:
The best way in my oppinion would be to use log shipping and this way both structure and data changes on one server.database would be carried forward to the other server.database.

http://msdn.microsoft.com/en-us/library/ms190640.aspx
0
 
jgoodaleAuthor Commented:
Unfortunately the servers are not on the same network so a local share is not possible. The development server is behind a firewall on a dynamic IP where as the stage server has a static IP behind a different firewall. Exceptions can be created to the static IP but it would be much harder to go the other way.
0
 
Ryan McCauleyData and Analytics ManagerCommented:
If the databases aren't so large that a complete refresh isn't out of the question, I've written a job like this for some servers I use and it's totally automated:

http://trycatchfinally.net/2009/09/moving-a-sql-server-database-to-another-server-on-a-schedule-without-using-replication/

Basically, a scheduled SQL Agent job on the source server takes a backup, compressed it (with 7zip), and ships it to the standby server via FTP. The standby server has a job that's constantly checking for new backups, and then it finds one (once the FTP upload is done), it unzips it, restores it, run some indexing scripts, and then emails me that a backup is restored.

If you have any questions, please let me know.

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lcohanDatabase AnalystCommented:
If the servers are behind different firewals and networks can they talk to each other currently?
If they are not then indeed you can't do log shipping and you can't use Replication or SSIS to transfer all tables from one db to another but you could still do T-log backups on the staging server then FTP or rather SFTP them to your SQLDEV box and restore them over there. For that all you need is both DB's to be in FULL recovery mode and an initial full backup/restore from staging to dev.
0
 
jgoodaleAuthor Commented:
Lets say firewalls aside, the easiest way for scheduled data copy? Ryan's method looks interesting but seems a bit over kill for copy a couple tables with just a couple hundred rows of varchar(max) data. If no other way makes sense that is the direction I will go.
0
 
lcohanDatabase AnalystCommented:
If you need to only "copy a couple tables with just a couple hundred rows of varchar(max) data."
AND the servers can talk to each other then why go that way and not create use a SSIS to do that by simply right click one server.database select Tasks - > export(or import) depending where you run it and go from there. You can use that wizzard to create the SSIS package for you - just don't hit Finish but Save it as well as a file for later use...
OR
Simpli add a Linked Server on either one of them and use distributed queries to copy data from one server.database.table to another!

http://msdn.microsoft.com/en-us/library/ms188279.aspx
0
 
Ryan McCauleyData and Analytics ManagerCommented:
Ah - my method is way overkill for a few hundred rows in a few tables, though it only needs FTP to be open and will do everything else automatically. If there's other data that you don't want to replicate, though, then it won't work well because it takes everything.

You could set up log shipping via FTP - that only requires the single port as well and could move the data over on whatever schedule you want (daily, hourly, constantly, etc). It might be a better option for you. I believe you can do an FTP destination with SQL Server replication, but if you can't, you can just ship the logs locally and have something like SyncBackSE (http://www.2brightsparks.com/syncback/sbse-features.html) ship the logs to your remote site to be applied. I've used that app (no affiliation to them) in a number of cases where I need to FTP files as the appear and it's done a great job.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now