Cool Features in SQL Server Denali HADR

If you haven’t been following the slowly oozing information spigot from the next SQL Server version, code name project “Denali”, then you may not be familiar with some really cool, new features implemented around high-availability or what is sometimes currently being referred to as HADR (High availability disaster recovery).

SQL Server has had log-shipping since I don’t remember when, database mirroring since SQL Server 2005 and Windows Clustering has been available to cluster SQL Server for many releases, too.

But these have existed as somewhat independent technologies which an administrator needed to learn about separately and an architect needed to design a SQL Server solution understand the risks, benefits and costs of each. Also, DBAs don’t always have the skills, experience, or network & system access to set-up and configure clustering software.

A lot of this has been consolidated and improved in Denali and I’ve been going through the latest features and roadmap leading us to the Always On HADR feature set in the next SQL Server. Below is my own compiled list of what I perceive to be the greatest benefits that a SQL Server DBA will receive from SQL Server Denali around high availability:

  1. You can have multiple mirrored databases from a single primary DB, known as failover replicas.
  2. You can now report from the mirrored database by connecting directly to the replica. Not need to take snapshots any longer.
  3. Geo-clustered SQL Server now supports multiple subnets
  4. Once you have a configured cluster, SQL Server can interop with the cluster software from T-SQL using the cluster API, keeping your hands clean from touching the Windows Cluster

 

For more details on the latest of the Denali high availability features, click here. BTW, straight-up mirroring, log shipping & Clustering will still exist by themselves, too, in SQL Server post R2.

Top SQL Server Trends in 2011

What are the top trends for Microsoft SQL Server in 2011? Check out my answers to SearchSQLServer’s questions here.

Something for Oracle PL/SQL Developers in Denali

Getting back to our common theme on this blog regarding Oracle & SQL Server coexistence, migration and integration, I want to touch today briefly on a new feature in SQL Server v-next / Denali / SQL 11 / SQL 2011, whatever you wish to call it at this time. Let’s call it by code name Denali. CTP 1 is avaliable for download here. For as long as SQL Server has been around, developers have had not-so-nice things to say about the limitations on the T-SQL Identity column property, used for a similar numeric sequences in tables to PL/SQL sequences. It should be noted that sequences are standard SQL objects and SQL Server has used identity to implement similar functionality. Interestingly, Oracle-to-SQL Server migration tools such as SQL Server Migration Assistance for Oracle, a free download from Microsoft.com, utilizes its own sequence generator when converting to T-SQL. This way, you can use the Oracle CURRVAL and NEXTVAL.

With SQL Server identity, you do not get flexibility in that it is tied to a single table while sequence objects are not. They are separate manageable objects. You also do not set min, max and steps cannot be modified in an identity column.

Here is sample code for creating a sequence:

SET NOCOUNT ON;
USE AdventureWorks2008R2;

— Specific INT in most cases
CREATE SEQUENCE dbo.MySequence AS INT
  START WITH 1
  INCREMENT BY 1;

— Generate next value for sequence
SELECT NEXT VALUE FOR dbo.MySequence;

— In your table, you can store that sequence as an INT column
CREATE TABLE dbo.MyTable
(
  MyID INT NOT NULL,
  MyName VARCHAR(50) NOT NULL,
  CONSTRAINT PK_ID PRIMARY KEY(MyID)
);

INSERT INTO dbo.MyTable(MyID, MyName) VALUES(NEXT VALUE FOR dbo.MySequence, ‘Mark’);

Here are my favorite 2 postings that I’ve read so far to give you good depth of knowledge on using the Sequence object in CTP 1 of Denali from T-SQL guru master Itzik Ben-Gan here and here.

SQL Server 11 Denali and 2010 Year-End

I won’t repeat my most recent SQL Server Magazine BI Blog posting here so instead just scoot on over there and read it here. It focuses on SQL BI but I did spend some going into a little bit of detail on the direction of the BI Blog for 2011 in terms of SQL Server data warehouse products, Cloud Computing with Azure and a few Denali features to keep your eyese on. Peace in 2011!