Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replicate databases for querying only Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 2:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:09 AM
Points: 119, Visits: 344
Hi,
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.


Possible/impossible solutions?:
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.

TIA!

Post #1565800
Posted Wednesday, April 30, 2014 5:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:32 AM
Points: 299, Visits: 559
gilalig (4/29/2014)
Hi,
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.


Possible/impossible solutions?:
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.

TIA!



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
Post #1566313
Posted Wednesday, April 30, 2014 5:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 1,306, Visits: 2,472
RTaylor2208 (4/30/2014)


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.


+1

Transactional replication with Pull Subscrition.



Sujeet Singh
Post #1566318
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse