SQL Server Tacklebox- P18: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 3 The migratory data the data in them needs to be regularly resynched with the source. The added benefit of using a tool such as log shipping is that you can segregate reporting processes from transactional processes which can offer performance gains in some scenarios. Log shipping vs. mirroring vs. replication There are three main tools that you can choose from when implementing a HA solution for data migration Log Shipping I have used log shipping for a variety of data migration tasks and it is probably the most reliable mechanism that I have found. However it does have its downsides and a big one is that refreshing the data requires everyone to be out of the target database and data is only as fresh as the last restore on the target. Log shipping can be setup natively or using third party tools like RedGate SQL Backup which sets up all of the automated schedules for you and also compresses the backed up logs making for much faster data loads. In terms of financial cost the only significant consideration is the secondary server instance though you can also log ship to the same server as I will show. The space required to store log backups is much less than the space required to implement a solution that performs a full backup and restore of a database and the time to synch two databases via log shipping is drastically lowered. Native SQL Server Replication This is one of the few solutions that provide near-real time data on a target. However to be quite honest I have avoided native SQL replication for a long time. It is not so much the overhead of maintenance or administration that has prevented me from deploying it to production but the learning curve of the technology and need for compliant schemas . In order to use native replication the database schema has to fit into a normalized structure with unique primary keys defined for each replicated table. For whatever reason many third party vendors do not always adhere to this requirement. Database Mirroring SQL .
