SQL
Server 2012 Always On Technology
SQL Server
2012 now has Always On technology. This is where the database can keep a mirror
copy/copies of the database that are constantly being refreshed. In the
event of a failure of the Primary, it will seamlessly fail over to another
copy. What really different is that the unlike previous versions of SQL
Server is you can use the copies to do real work. For example you could backup
your production database off a copy. You could use one of the copies for a
reporting server. This is huge.
SQL
Server 2012 Always On Versus SQL Server Mirroring
This has
significant advantages over traditional SQL Server Mirroring. In traditional
mirroring
- You cannot manage groups of databases,
so that when a single database failed they would all failover.
- There is only one Mirror of the
database allowed
- The Mirror is not useable
unless you are prepared to work with Snapshots.
SQL Server
2012 Always On Versus SQL Server Log Shipping.
Log
Shipping can be very complex to set up. The biggest downfall is you cannot use
the receiving database since it is in a recovery state as the transaction logs
are being applied.
SQL
Server Always On Versus SQL Server Clustering.
With SQL
Server clustering, you are dealing with a shared storage system that adds cost
and complexity to the equation. With a 2 node SQL Server Active/Active Cluster
unlike an Oracle RAC database, only one node can access a database at a
particular time.
So, when
you compare SQL Server 2012 Always On technology to other current High
Availability SQL Server options you can see why I think it rocks. This is a
huge advancement for SQL Server.
SQL
Server 2012 - Other Advancements.
Databases
can now be grouped together.
When a failure happens, the entire group of database is treated as one. When
one fails over they all fail over. This is very powerful when you have an
application that uses more than one database in an instance.
File Streams took a huge step forward. The
database and the O/S can now communicate with each other, making it much easier
and more transparent to keep both sides in sync. Additionally, the 2 Gig file
limit is no longer a limitation.
You now
have the ability to connect directly to a database and be
given permission that only work within that database without going through the
over all SQL Server. If for any reason you need to a database, the
permission move with it from one server to another. The Server accounts are no
longer needed or required. This takes security to a whole new level.
There is a
new Column Store Index. This is really huge from a data warehouse
perspective. By using the column store index, you eliminate the need to do Page
I/O Latching. In other words avoid the need to go out to the disk. You are only
doing I/O latching in memory. This provides a huge performance enhancement 10X
plus!
SQL Server
2012 now support server level security roles. No longer do we need
to create the same role in multiple databases.
This
release of SQL Server is the most compelling release to come out in 7 years.
This is a real winner. The Always On capability and Column Store Index alone
represent significant improvement over previous versions.