1

We have an older vendor supplied application that is earmarked for platform upgrades in 2019 but is currently running SQL Server 2008 (SP4). It's about 1.2TB of data. Our internal IT unit has come to the point that we want to create a readable secondary for some reports, but mostly ad-hoc reporting. Usage is about 1500 active sessions and about 25,000 Be/S peak.

Now onto the actual question. The option I forsee are transactional replication, mirroring, and log shipping with a read only standby. One of the developers also put Service Broker with CDC ... any landmines or curveballs with CDC and SB?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dan White
  • 11
  • 3
  • I would try asking this on dba.stackexchange.com. – Chris Albert Apr 24 '17 at 18:18
  • 1
    This really doesn't have anything to do with **programming** (which *this site* is **all about**), but with DB administration - so it's off-topic here and belongs on [dba.stackexchange.com](http://dba.stackexchange.com) - voting to move. – marc_s Apr 24 '17 at 18:19
  • thanks ... been a rough monday and I posted it on the wrong site. – Dan White Apr 24 '17 at 18:32

1 Answers1

1

Service Broker is a very powerful tool to create and manage ques. CDC reads the log asynchronously to pick up changes to designated tables. They don't interact with each other and are designed to have low impact on an active database. They both work very well even in high volume situations. Like many features in SQL Server they can be used with a minimal learning curve but if you want to really take advantage of these tools some study is required.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22