Strategy to select
-
We have 2 SQL Servers, one is live-primary (we are keeping our company on-line service data) and the other is a backup. I want the backup to be updated every 5 minutes to the status of primary server- what strategy to select (or common for this type of situation)? Thanks
-
If you mean that you want the data replicated to the backup server, two of the most common ways are transactional replication and log shipping.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Thanks, but frankly from BOL I couldn't get what is more beneficial in my case?
-
Transactional replication doesn't require a SQL Server Enterprise license (nor the equivalent on the OS side). Also, the "backup database" can be used live, but this can cause issues if changes happen to the backup database and you aren't merging changes back into the primary. Log shipping takes care of this latter concern in that the backup database must stay in a read-only state as it gets updated. However, it does require the SQL Server Enterprise license on the servers.
K. Brian Kelley
@kbriankelley -
You can script log shipping using Standard Edition. The reason that we are doing log shipping instead of replication in this scenario is that there are no primary keys defined on the tables. It is a third party app with over 900 tables so log shipping was the best option in this case.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Good point. Shameless plug for the magazine... SQL Server Standard's July 2004 issue had an article on how to do this:
http://www.sqlserverstandard.com/issue/200407.aspx
K. Brian Kelley
@kbriankelley -
Thanks guys for your help.Good luck!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply