Would appreciate any help in implementing a solution for the following:
Source server: sql server 2008 r2 enterprise
Target server: sql server 2012 on DMZ
We need to replicate 7 databases from each of 3 sql servers (2008) to the sql server 2012 on the dmz. The data is needed solely for engineers to query the databases on the dmz (they have no connection to the source db server). There will be no need to failover to the target databases.
The data needs to be available to the users- readonly- with a max of 1-5 minutes delay (there is no issue with network latency).
The source databases -
recovery model is "simple"
Some of the source databases have many inserts (~every minute) . There are very very few updates. Deletes occur on each insert.
The schema on the source databases will undergo changes over time.
Since we won't have continual direct access to the source db server we would rather opt for a solution that deals with the least amount of maintenance on the source server.
Special attention needs to be to the affect on performance on the source (production server). The requirement is that the replication not affect performance nor the source's database design.
1. sql replication - if I understand correctly replication is used per object and requires a lot of support and maintenance on the source (publisher). We are interested in all the tables and views on all the databases and very little maintenance on the source.
2. mirroring - probably irrelavant since the target databases need to be available for querying and again, we really don't want to affect the principal source server.
3. log shipping - also probably not appropriate since we are using simple recovery model.
4. ssis - preparing a package that pulls or pushes the delta of the data between the server. How would we manage schema changes then?
5. Backup and restore? Some of the databases are large. There is only one filegroup on each databases.
Given you requirement for minimal latency and no change to the recovery model I would say that your only option is transactional replication.
>Mirroring requires full recovery
>log shipping requires at least bulk logged recovery and it will require the DB to be in norecovery during the restores making the destination unaccessible.
> SSIS - You could use CDC but again this process may take one than 1 to 2 minutes to do the ETL and requires CDC enabled on the tables in your source DB you want to replicate.
>backup restore again will require the destination to be unavailable during your restore.
MCITP SQL 2005, MCSA SQL 2012