SQL Server Replication
Replication is a good option to move large (raw) data sets, SQL Objects (Tables, Functions and Stored Procedures) from one instance of SQL Server to another. This can be achieved through Local and Wide Area Networks, wireless, VPN and the internet.
Publisher - hosts the source database.
Distributor - is the "pass-through" database.
Subscriber - is the final destination of the data.
There are 4 basic types of Replication:
Snapshot Replication - selected data is captured from the Publisher SQL Server and sent to the Subscriber server. This is a good scenario if your database doesn't change much or is smaller in size. It's also the easiest type to setup and maintain.
Transactional Replication - this is for Server to Server scenarios that require high throughput. Transactional consistency is guaranteed within a publication as the data is transferred in the order the data was saved or updated. Data is read from the Transaction Log files and each touch of the data point is captured, including Inserts, Updates and Deletes. DML and DDL schema changes are captured and replicated. There can be multiple Subscribers to a single Publisher.
Merge Replication - process of distributing data from Publisher to Subscriber and is typically used for Mobile applications or distributed server applications like a POS system or combining data from multiple sites. It's the most difficult to setup and maintain.
Peer to Peer Replication, is built on the foundation of Transactional Replication, basically used for 'real time' synchronization. Enterprise Edition will allow bi directional replication.
To create SQL Server Replication, you can use the built in wizards to assist with the installation. You define which database will become the Publishing Database, Distribution Database and Subscription Database(s). Optimally, they should all reside on separate servers, however, this is not mandatory. Deployment of Replication is a combination of SQL Server Management Studio (SSMS) Wizards and scripts for automating purposes.
There are SQL Agent jobs that do certain tasks, including the Snapshot Agent, Log Reader Agent and Distribution Agent. You can set your Agents to run all the time or at specific intervals. And you will need to create Users to run the SQL Agent Jobs.
- Multiple Subscriber databases can point to a single Publishing database.
- Transactional consistency.
- Potentially less coding required to setup and install Replication, including wizards and T-SQL Scripts.
- Replication has built in solutions for Synchronization, Monitoring and Recovery.
- Ability to adjust the frequency of the Replication by adjusting when and how often the SQL Agents run.
- Increased administration time depending on the number of objects moved and subscribers.
- There's no automatic fail over for Disaster Recovery.
- There is with no easy way to Transform (ETL) the data during transit.
- You can apply filters to limit which data gets copied, although there could be a performance hit in high traffic servers.
Service Broker is suitable when you are not just trying to maintain identical copies of the data, but rather each application maintain its own data and the apps engage in an exchange of logical messages over a business conversation. When and how these messages are sent or received is completely driven by the app, rather than the database. Service Broker solves receiver management issues by activating queue readers as required when messages arrive.
To install and configure Service Broker, one must have a good understanding of the hierarchical objects which comprise the architecture. This can become quite complex as there are no built in wizards or front end utilities.
- All messages in a dialog are received in the order sent, with no gaps in the message sequence, even if the server goes down in the middle of a transaction.
- Database integration enhances application performance and simplifies administration.
- Message ordering and coordination for simplified application development.
- Loose application coupling provides workload flexibility.
- Related message locking allows more than once instance of an application to process messages from the same queue without explicit synchronization.
- Automatic activation allows applications to scale with the message volume.
- Asynchronous Queues enable the flexible scheduling of work, which can translate to big improvements in both performance and scalability.
- Service Broker allows the ability to transform and manipulate the data as its moved between databases.
- Service Broker has the ability to initiate transfer of data on Triggers.
- Data is encrypted between servers to protect the connection.
- You can use certificates to secure the machine endpoints with SSL encryption.
- Servers do not have to reside within same network.
- Service Broker allows custom Audit tables to track the flow of data.
- Service Broker allows immediate data transfers with asynchronous transactions.
- Service Broker allows each database side to own its own schema so they do not have to match.
- Service Broker allows multiple servers talking to a single back end service as well as forwarding.
- Service Broker has efficient transactions by leveraging SQL Server's internal transactions.
- Service Broker Queues are database structures, so they are backup via standard SQL Server backup procedures. They gain all the benefits of incremental and differential backup and restore processes provided by SQL Server.
- Service Broker provides a way to send messages when the receiving queue is not available.
- Service Broker allows for Prioritization of Queues.
- Service Broker has the ability to Multicast messages (SQL Server 2012) which enables a single initiator service to send messages to multiple target services.
- Service Broker requires you to do a fair amount of coding.
- Although Service Broker can be much faster, it is not tuned for large data transfers.
- Service Broker could experience performance issues, if the data transfer rate is high.
- Service Broker has no built in mechanism for monitoring and debugging.
- Service Broker does required additional configuration in order to work with the Always On Availability Groups.
- Adding more data centers or servers require lots of new code on both servers preventing easy scale out.