Webcast: SQL Service Broker Advanced Performance Tips and Tricks Tomorrow
We will be looking at some of the advanced features of SQL Service Broker as well as some of the advanced techniques which can be used to maximize the performance of the SQL Service Broker.
> Register Now
> Live date: 11/3/2010 at 12:00 Pacific
Featured Article(s)
SQL Server Log Shipping (Part 2 of 2)
In this session, were going to go a little bit deep in log shipping, well talk about what log shipping really is, kind of explain from a high-level perspective of log shipping and then just discuss the internals in terms of how the system works, what steps are required to configure log shipping, what roles happen behind the scenes in terms of whats going on when log shipping is working, the jobs that are initiated from a SQL agent perspective, talk a little bit about the architecture.
Featured White Paper(s)
VIDEO WHITEPAPER – How to Avoid Downtime – VIDEO WHITEPAPER
In this Experts & Insights video, we take a close-up look at the data protection challenges and solutions surrounding downtim… (read more)
Featured Script
Change owner to DBO on all objects
This script changes the object ownership to dbo for all objects not owned by dbo (and INFORMATION_SCHEMA). It builds a d… (read more)
Failover Techniques
It’s been a while since we talked about database failover techniques. I thought I’d take time today to review some of the options.
Microsoft provides some out of the box failover tools.
Clustered services allows you to have a single IP address supported by multiple servers working as a cluster. Normally one server serves as the database service while a second server waits in the background should things fail with the first server. Both servers point to a common disk area on a SAN. Only one server manipulates the disk at a single time.
Mirroring is a method where the client application commands are sent to two servers hosting separate concurrently. This method has two completely separate disk systems, therefore the cost is higher than clustering if SAN disk technology is used.
Replication can be used for failover. There are many kinds of replication. At the core, transactions occur on one database, and those transactions are replicated onto another database. Replication is more granular than Clustering or Mirroring in that the entire database does not have to be replicated. It also allows for duplication in more than one direction, and to more than one other data store.
Log Shipping is a method whereby transaction backups are taken from the production database, copied to a backup server and restored, leaving the backup database in a state to allow additional transaction logs to be restored. In the event of a failure, the last transaction log is restored with finalization, and the backup database is now production ready. High end versions of SQL Server support log shipping out of the box. You can also roll your own.
Backup and Restore is an old stand by where database backups are made to disk, tape, etc. Those backups may be restored to another server or disk when required. This method has the longest up front time for recovery.
Third Party Methods
There are a number of third party methods that provide failover using different technologies.
SAN Replication is a method whereby modifications to the disk are replicated to another device. It is very fast because the replication is performed at a binary level.
Network Layer Mirroring is a method some third party tools do by inserting a broker in between the communication of the client with the database. This proxie then routes the request to one or more databases so that the Create/Retrieve and Updates are performed at all sites. Essentially this is the same thing as mirroring except it has the ability to go to more than two databases.
Another vendor utilizes multiple database services and a SAN. The hosting of a database can be moved from one server (virtual or physical) in a rapid fashion by simply exchanging which machine is hosting the database. It’s quite powerful. This is not pure failover in that loss of disk is dependent on the SAN. Neither is Clustered Services a pure failover. If the SAN should fail, it doesn’t matter how many servers you have running.
There are other third party tools that also do a good job. Most of them work in a similar fashion using some of the techniques represented above.
So there is an overview for your Tuesday Morning. Which is your favorite and what business objectives caused you to make your choice. Send your experience to share with our readers to btaylor@sswug.org.
Cheers,
Ben