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.
Best Answer
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.