Our main production database A is a 300 GB SQL Server 2005 Enterprise database. We are setting up another server for Database B [SQl Server 2005 Standard ] for reporting purposes . Database B will need to be synchronized with A every hour or so .
What would you suggest the best method to do the synchronization ?? I have read online that mirroring wont work nicely from Ent to Std edition . Regarding Log Shipping , Clients are slightly skeptikal about databse B not being available while tran logs are being restored . Do you suggest transactional Replication ?? Any experiences or insight will be great !!
You could flip flop the reporting databases. Have two of them apply the logs to the off line one, swap the databases then apply the logs to the other. Would need to arrange it so that the client has a configurable connection - could be a proxy or via views in another database depending on your setup.
Depends on how much time you want to spend over it and how much expertise is available.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
In your case Transactional Replication(needs perfect monitoring Scripts in handy) OR Logshipping which is like standalone type with DR residing on Server 2 and may be every 1 - 2 Minute Logs might ship to this DR.
Alternatively you can even think of Cluster oriented might another option.
Replication OR Log Shipping??
Posted - 06/07/2012 : 13:39:33
