T-SQL Tuesday #77: Favorite SQL Server Feature
Let the weeping and gnashing of teeth begin, fore I have chosen...REPliCAtion! as my favorite SQL Server feature.
Seriously though, replication has been around since the beginning and it's not going anywhere. I can't think of any other feature more prolific than replication. Name another SQL Server HA/DR technology that is as extensible as replication. Replication has gotten a bad rap over the years mostly on anecdotal comments that it "breaks all the time" or "it takes too much time to manage". I've worked in many environments and have setup dozens and dozens of instances of log shipping, mirroring, clusters, availability groups, and replication. From my anecdotal experience, I can tell you I've had more trouble with availability groups than I have with replication. If you have a good DBA that understands replication, uses it correctly, and is provided the correct tools (read $ for hardware/infrastructure) replication works just fine. I have setup replication in a global environment in which multiple databases, publications, subscriptions, and agents ran around the clock and without issue.
For starters you have three kinds:
- Snapshot - scheduled point in time refresh
- Transactional - low latency active sync
- Merge - consolidation of multiple sources (more or less writable syncing subscribers).
Then you can pile on a whole list of features not available in the other SQL technologies:
- Replicate data to any ODBC or OLE DB accessible database including Oracle, AKA heterogeneous replication.
- Subscribers are readable
- Updateable subscribers
- Peer-to-peer
- Ability to create different indexes on the subscribers
- Ability to selectively choose which pieces of data (articles) to replicate (i.e. not all-or-nothing)
- Atricle Filtering (vertical and horizontal)
- Stored procedure executions as articles
- Flexible enough to work over slow connections
REF:
Chapter 43 of Microsoft SQL Server 2014 Unleashed is excellent!
No comments:
Post a Comment