5

We have a 2012 standard edition instance that is doing log-shipping of 2 related databases every 15 minutes to a secondary 2012 standard edition instance for DR purposes. Although it provides read-only access, users are automatically disconnected every 15 minutes when the restore job runs (it takes up to 2 minutes to complete the restores).

Without interfering with that process, we would like to set up a secondary instance for reporting and developer troubleshooting purposes that would also be kept up to date at least every 15 minutes (the reports need near real-time data), but would not involve periodic disconnections, enable different security than the primary (so that devs can read from the secondary but not the primary), and preferably allow indexes to be created that do not exist on the primary (to improve reporting performance).

1) How can we achieve this? 2) How can we achieve this without having to upgrade to Enterprise Edition?

1

Transactional Replication is typically used to off-load reporting to another server/instance and can be near real-time in a best case scenario. The benefit of Transactional Replication is that you can place different indexes on the subscriber(s) to optimize reporting. You can also choose to replicate only a portion of the data if only a subset is needed for reporting.

With Transactional Replication you will need Standard Edition or higher for the Publisher and Subscribers can be Express Edition or higher.

Have a look at Transactional Replication to get started.

If you have any questions, let me know. I hope this helps.

  • My understanding is that TR requires PKs for all tables. We're stuck with a database for a third-party app that doesn't meet that criteria. – Mark Freeman May 9 '14 at 18:56
  • I'm always surprised when I hear this. How are the rows uniquely identified when reporting? – Brandon Williams May 9 '14 at 19:50
  • I'm not saying that there isn't a good candidate for PKs on each table, just that the company that created this mess didn't create them on some tables. They don't use many FKs either. – Mark Freeman May 9 '14 at 21:21
0

If Always On isn't available you could combine a custom ETL process with Change Tracking to ensure it does the minimum of work. This allows your secondary to have a different schema, not just different indexing. The ETL package can be scheduled as often as necessary.

Using triggers to fire off Service Broker messages to the secondary can work. I've heard reports of it not scaling well at the very highest transaction loads, however. I implemented Service Broker-like functionality once by have a CLR SP fire a WCF message to an external C# program. It worked well for my use pattern.

  • We have a very fluid environment (weekly releases to production), so I'm hesitant to build something that would require tweaking when we add a table or even a column. One of the databases is for a third-party app, so I can't add triggers to it. I probably can't add Change Tracking to it either, as that modifies the schema. But that might be a good choice for our other databases. – Mark Freeman May 13 '14 at 17:09

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.