I have a SQL Server 2008r2 database ( Let's call it "BIG_DB") at 200GB. This server gets hit hard. It is the back end for an application, BIG_DB is a source of a lot of reporting.
There are about 10 tables that are being queried for reports constantly and I would like to offset that load to another server( Let's call it "OFFSET"). The Boss wants a maximum 2 minute latency between the BIG_DB AND OFFSET on these 10 tables. This is my proposed solution:
-
When an insert or update happens on any one of those 10 tables on BIG_DB, I write that to a corresponding table ("UPDATE_TABLE") with a similar name and same column list + default GETDATE() column on BIG_DB.
-
Then have OFFSET grab the info on BIG_DB.UPDATE_TABLE and place it into the OFFSET database with the same table setup as BIG_DB.
-
I then have OFFSET serve the reports.
I have only one geographic location, servers are only in house. BIG_DB is OLTP. Is there a better solution? Do I go with replication, SSIS, or straight T-SQL with a linked server? or other?
Best Answer
One issue with your trigger implementation is that if the reporting DB or DB Server goes down for any reason or stops accepting requests, your prod systems will stop as well. Also if you make schema changes note that you'll need to do that on the reporting table as well. Your solution will work, it just isn't ideal. SSIS won't help with the schema changes but at least if the reporting server can't accept requests prod will still keep working.
Replication is nice too if you have a primary key on the table and meet all the replication requirements. Just note that replication (IMO at least) requires more experience to fully understand than a pure AlwaysOn or SSIS solution. Replication requires agents and extra security.
SSIS could work at the frequency you're using it for, maybe it just isn't ideal. If you're OK with losing in flight transactions when the server reboots you can look at Service Broker which will be much more near real time but Service Broker has a even higher learning curve than AlwaysOn.
Personally I would stick with Replication or your current SSIS solution. Both require some tuning and testing but both have their advantages as well. SSIS at least won't deal with all the security and agents that replication requires.
Are you open to using 2014 standard edition read only replica in an AG? It gives you a lot more options, let's you query the entire database, gives you a nice near real time complete copy, and isn't too difficult to setup but absolutely much more difficult to setup than a trigger. Edit: Sorry I just saw that you tagged SQL 2008 R2.