[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • 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 McCauleyCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 McCauleyCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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