Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my SQL Server 2012 database I have two tables:

  • [tbl_Customers] - about 30 rows
  • [tbl_Users] - about 10,000 rows

I need to schedule the two tables above to be copied to a new instance, say every 12 hours. I am considering setting up a SQL Azure account to act as the second instance.

[tbl_Users] is a popularly-accessed table, with CRUD operations being performed quite a lot, therefore the data changes often. It doesn't matter too much about those changes being replicated on the second instance immediately (but it would be nice if possible if the option allows).

Can anyone recommend which approach they might take to achieve this? I'm new to this type of thing so please don't hold back with the simplicity of your answers...!

I have SQL2012 Standard on my dev machine (but no SSIS at the moment although I could install it), but don't know if my existing SQL host and/or Microsoft have SSIS on their offerings.

share|improve this question
add comment

2 Answers 2

As long as all the changes happen only on your first instance, transactional replication is what you are looking for. It was designed for this purpose and usually operates close to realtime.

You can find an introduction to replication here: http://www.sqlservercentral.com/stairway/72401/

If you need to be able to apply changes on both machines, merge replication might be the better option, but is was not designed for realtime operations. The above link has an introduction to merge replication too.

share|improve this answer
    
Great articles, I'm just working through them now. Thanks. –  EvilDr Sep 19 '13 at 14:42
    
Every so often I do a full backup, then log backup, then shrink the log file. I assume that anything outstanding for replication at this point would be forced to the subscribing server? –  EvilDr Sep 19 '13 at 14:43
1  
You can't delete data that replication still needs by shrinking the log file. It is maintained independent of the log backups. So, to shrink successfully you need to wait for a log backup and for replication to get through the data. - Why don't you just schedule regular log backups. Then you don't have to worry about shrinking the log-file at all. –  Sebastian Meine Sep 20 '13 at 18:24
add comment

I am considering setting up a SQL Azure account to act as the second instance.

SQL Azure database does not support replication. See SQL Server Feature Limitations (Windows Azure SQL Database)

I need to schedule the two tables above to be copied to a new instance, say every 12 hours. Can anyone recommend which approach they might take to achieve this?

If you want to use SQL Azure database, then you have to go about either using any one of the approach as below :

  1. Use SQL Data Sync which is still in preview state. You can set the frequency on how often you want to sync the data to Azure.
  2. Use SSIS to migrate your data. You can even schedule your SSIS package to run as per your needs, e.g. every 12 hrs.
  3. Possibly use SQL Azure Migration Wizard which essentially uses BCP (Bulk Copy) to get data out and BULK INSERT to push data back in Azure.

My personal choice and what I have implemented at my company is using SSIS. Flexible and you can easily troubleshoot if something goes wrong.

share|improve this answer
    
Thanks. No replication support is a painful thing to hear...! Is there a release date on SQL Data Sync? The last update was December 2012! I'm going to have a look at SSIS over the next few days. I thought that with replication I could always have a perfect mirror on the second instance, but I might have to settle for scheduling SSIS packages instead which isn't as attractive sadly... –  EvilDr Sep 20 '13 at 9:11
    
It is correct that Windows Azure SQL Database does not support replication. However, there are a lot of other restrictions to it too that makes me usually recommend to get an Azure hosted SQL Server instance instead. If you go that route, you can use replication. –  Sebastian Meine Sep 20 '13 at 18:21
    
Do you mean a SQL instance on a Virtual Machine? I looked at that but costs were pretty steep for an always-on solution... –  EvilDr Sep 21 '13 at 11:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.