0

I'm a database developer (not a DBA) responsible for about 20 different non-transactional 1TB SQL Server 2008 - 2012 databases each on its own server. In order to be able to query across databases, the business would like me to co-locate these databases on one query server; however for data loading purposes (periodic large bulk loads) it is convenient to maintain the 1 database/server model. For some databases the tables are truncated and reloaded on a monthly basis; for others, new data is populated on top of existing data via daily jobs. How do you recommend that I transfer data from the source databases to the query server? I want to maintain control over when the data is transferred. Upgrading to a newer version of SQL Server is possible.

0
0

How do you recommend that I transfer data from the source databases to the query server?

I would suggest to read The Data Loading Performance Guide. This whitepaper will give you efficient loading techniques that you can use to load your data.

Below are your constraints that you have listed :

I want to maintain control over when the data is transferred.

For some databases the tables are truncated and reloaded on a monthly basis; for others, new data is populated on top of existing data via daily jobs.

Keeping in mind above constraints, I would suggest Logshipping - you can delay the restoration of log files (control when you want to restore the log backups) to the query server and have query server database in standby mode allowing read access for user queries.

You should be aware that any adhoc logbackups will break the LSNs, so its better to DENY BACKUP LOG to [user|group]

As a side note: AlwaysON might be a tempting solution, but it does not allow you to have control on the data transfer (you can pause the data movement, but that will bloat the T-log on the primary server). It requires enterprise edition and a windows failover cluster.

Replication is another option, but its difficult to maintain and network latency will impact its performance.

1
  • Very helpful! I hadn't known that you can delay the restoration of log files when using log shipping. Thanks also for the link to the white paper.
    – blue-belt
    Aug 12 '16 at 12:59
0

You could use Availability Groups for this, which I've done in the past. You would have 20 AGs, and each would have the query server as a replica, probably an async replica. You could also use replication for this, especially since it's unlikely that the users need all tables in every database. I don't like supporting replication though. Maybe the query server would be a Data Warehouse.

2
  • AlwaysON is good and tempting option, but the OP says I want to maintain control over when the data is transferred. - You cannot do this in AOAG and the OP has not mentioned if Enterprise edition is being used or not.
    – Kin Shah
    Aug 10 '16 at 19:19
  • @Kin, you can suspend data movement on AGs, though you'll need to make sure you have enough space on your transaction log drive on the primary to do this. I wouldn't recommend this, but it can be done.
    – Tara Kizer
    Aug 10 '16 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.