Using Sequences in SQL Server "Denali"

Over the years, SQL Server developers have become more and more inventive at generating sequential ranges of numbers. The identity column works fine for a single table or view, however when you want a similar effect across multiple tables, issues abound. Hence, a collective sigh of relief was heard with the introduction of the SEQUENCE keyword in SQL Server "Denali". It's also useful when you want to generate a key value before actually adding the row to a table or to use the same key value for records spread across multiple tables.

So let's start by looking at the simplest use of sequences. In the following example, I create a sequence and use it to assign customer numbers in an online shopping application.

USE MyShop;
GO

CREATE SEQUENCE Shopping.OrderNumberSequence
INCREMENT BY 10;
GO

When you create a sequence, you can specify the start value, the increment, and the minimum and maximum values. The increment can be a positive value for increasing sequences or a negative value for decreasing sequences. The minimum and maximum values define the start and end points of the sequence, with the default settings being the minimum and maximum values for the data type of the sequence.

You can use the sequence as a default value for columns in your table by using the NEXT VALUE statement. Here, I'll create a table and specify to use the sequence to generate order numbers.

CREATE TABLE [Shopping].[Orders](
[OrderNumber] int PRIMARY KEY DEFAULT (NEXT VALUE FOR Shopping.OrderNumberSequence)
[CustomerNumber] int NOT NULL,
[OrderDate] date NOT NULL,
[OrderStatus] int NOT NULL,
[TotalCost] money NOT NULL)
ON PRIMARY;
GO

When I run this code and then insert a row into the table, the OrderNumber created is -214783648 which is the minimum value for an int. It's not exactly what I'd envisaged though, so I can use ALTER SEQUENCE ... RESTART WITH to reset the initial value for the sequence.

ALTER SEQUENCE Shopping.OrderNumberSequence
RESTART WITH 10;
GO

Altering the sequence has no effect on the existing values used from the sequence, it just reinitializes the sequence to begin at 10. If I'd planned ahead though, I could have simply used the START WITH argument when creating the sequence.

Another key property of a sequence is cycling. You can configure the sequence to restart at the minimum value if the maximum value is reached. Obviously, this removes uniqueness from the sequence, however you can control uniqueness in a table by assigning a trigger to the column using the sequence.

So, what's actually happening to create these values and where are they being stored? If you query sys.sequences, you'll see all the information about the sequences in your database. This catalog view shows you definition of the sequence as well as the current value and some caching information. When you create the sequence, you can define whether to cache values and how many items to cache. If enabled, caching can increase the performance of your applications by decreasing the file access operations they use.

If you create a sequence and define a cache size of 20, when the first value is requested SQL Server will put values for items 1 through 20 into memory and the last value cached is written as the current value on disk. This means that no more disk operations are needed until the 21st value is requested. The trade off of this performance improvement is the risk that should your SQL Server stop unexpectedly, when it restarts it will allocate again from the current value stored on disk, which could result in a gap in the sequence. If that's not a concern to you, consider caching your sequence values. If you need to avoid gaps in the sequence, use the NO CACHE option, but be aware that if you request a number and then do not use it or do not commit the transaction that used it, gaps can still occur.

Finally, to remove a sequence from a database, you use the DROP SEQUENCE statement. As soon as a value from a sequence is used, it loses any links to the sequence, so dropping the sequence has no effect on the tables that used it. However, if you are referencing it from a table definition as I did for the OrderNumber column, you won't be able to drop it until the dependency is removed.

Read More >>

SQL Server “Denali” Integration Services – Projects and Parameters

Some previous posts in this blog have discussed new features in the SQL Server “Denali” database engine. For this post however, I want to focus on some of the key enhancements in SQL Server “Denali” Integration Services (SSIS). SSIS first appeared in SQL Server 2005 as an evolution of the Data Transformation Services (DTS) component in previous releases, and has steadily become a core element of Extract, Transform, and Load (ETL) operations for many data warehousing implementations.

The big news in the “Denali” release of SQL Server Integration Services, is a whole new deployment model for SSIS solutions. In previous releases, the only available unit of deployment is the package (a .dtsx file), and this could be deployed either to the file system or to the MSDB database in a SQL Server instance. This single-package deployment model is at-odds with the development model for SSIS solutions, in which a developer can create a single project that contains multiple packages. Prior to “Denali”, each package must be deployed and any variables that need to be set at runtime must be managed through a package configuration for each individual package. SSIS in “Denali” still supports this “legacy” deployment model, but now also supports project-level deployment to a the new Integration Services Catalog, and project-level parameters that can be used to set variables across multiple packages within a project.

The first thing you need to do to take advantage of this new deployment model, is to create an Integration Services catalog on an instance of SQL Server. The Integration Services catalog is a central database in which SSIS projects can be stored and managed, and you can have one catalog per SQL Server instance. The Integration Services catalog uses the SQLCLR (the .NET common language runtime hosted within SQL Server), so you need to enable this first by using the following Transact-SQL:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

Now you’re ready to create an Integration Services catalog, which you can in SQL Server Management Studio as shown here.

When you create the Integration Services catalog, you’re prompted for a password that can be used to protect the databases master key used to encrypt the data.

 

After clicking OK, refreshing the Object Explorer view reveals two new items as shown here. The first is a database named SSISDB, and the second is an SSISDB node beneath the Integration Services folder. The database is a regular SQL Server database that contains a number of tables, views, and stored procedures that you can use to manage and run SSIS projects and packages stored in the catalog. It is also where the projects and  packages in your catalog are physically stored. The SSISDB node under the Integration Services folder provides a management interface for the catalog and enables you to define a logical folder structure for your catalog.

To create a folder in your catalog, simply right-click the SSISDB node under the Integration Services folder, and click Create Folder.  Here I’ve created a folder with the imaginative name My Folder. Note that subfolders named Projects and Environments have automatically been created – we’ll return to these later.

OK, so now we have an Integration Services catalog that contains a folder to which we can deploy an SSIS project; so I guess it’s time we went ahead and created a project to deploy. For our purposes, we’ll create a simple SSIS project that includes a data flow task that extracts the list of database names from the sysdatabases system view in the master database and copies it to a table in another database. I’m going to copy the database list to a table in a database called CmSampleDB, and to make matters a little more interesting, I’m going to create two tables that can act as the destination for the list – one to be used when testing the SSIS solution, and another to be used in production. We’ll design the SSIS project to support a project-level parameter so you can specify which table to use at runtime. Here’s my Transact-SQL code to create the destination tables:

USE CmSampleDB

GO

CREATE TABLE TestDBList

(name nvarchar(250))

GO

CREATE TABLE DBList

(name nvarchar(250))

GO

Now we can go ahead and create the SSIS project using SQL Server Business Intelligence Development Studio (BIDS). Creating an SSIS project in “Denali” is exactly the same as in previous versions, just select the Integration Services Project template as shown here:

When the new project is created, it will contains a single package named Package.dtsx, which you can rename to suit your own requirements – I’m going to name my My Package.dtsx. You can add more packages to the project as required, so for example, I’ll add a second package which I’ll name, um, My Other Package.dtsx. In Solution Explorer, my project now looks like this.

So far, nothing is very different from how you would create an SSIS project in previous releases of SQL Server, but here’s where we’re going to use a new feature – Project Parameters. Project parameters are, as the name suggests, parameters that can be used to pass variable values to the project at runtime. Because these parameters are scoped at the project level, they can be used by any package in the project. To add a project parameter, right-click the project in Solution Explorer and click Project Parameters, or click Project Parameters on the Project menu. Either of these actions displays the Parameters pane as shown here:

As you can see, I’ve used this pane to create a project-level parameter named TableName with a default value of TestDBList. This default value is more correctly known as the Design default value, since it’s used when I run the project within BIDS. When I deploy the project, I can set a Server default value that will override this one when packages in this project are run on the server.

Now I need to create the data flow task that copies the database names from sysdatabases in the master database to the table indicated by the TableName parameter in the CmSampleDB database. To do this I just need to drag a Data Flow task to the design surface of My Package.dtsx as shown here:

Next, I’ll double-click the data flow task to view the data flow design surface, and use the Source Assistant item on the SSIS Toolbox to create a new connection to the master database on my SQL Server instance. Then I can configure the OLE DB source that gets created to extract the name column from the sysdatabases system view by using the following SQL command:

SELECT name FROM sysdatabases

The data flow surface now looks like this:

Next I’ll use the Destination Assistant to add a connection to the CmSampleDB database on my SQL Server instance, and connect the output from the source to the destination as shown here:

To complete the data flow, I need to configure the destination to insert the output from the source into the table specified in the project-level TableName parameter, as shown here:

Now I’m ready to build and deploy the project to the Integration Services catalog I created earlier. Building the project in BIDS creates a .ispac file, which you can then import into the catalog using SQL Server Management Studio, or deploy directly to the catalog from BIDS by clicking Deploy on the Project menu (or by right-clicking the project in Solution Explorer and clicking Deploy). Whichever approach you use, deployment to the catalog is accomplished via the Integration Services Deployment Wizard. After the Welcome screen, the wizard prompts you to select the project you want to deploy – in this case, the .ispac file I just built.

Next, the wizard loads and validates the project before prompting you for the destination. This consists of the server where the Integration Services catalog is hosted, and the path to the folder where you want to deploy the project. Here, I’ve select the My folder folder i created earlier.

Finally, the wizard prompts you to set Server default values for any project parameters. You can use the Design default value, specify a new value, or use an environment variable. We’ll look at environment variables shortly, but for now I’ve set the Server default value for the TableName parameter to DBList.

 

Completing the wizard deploys the project to the catalog, which you can verify in SQL Server Management Studio. note that the project is actually saved to the Projects sub-folder of the path specified in the wizard, and that all packages within the project are deployed as a single unit.

The final thing I want to do is to define a test environment and a production environment that can be used to control the execution context for the project. To do this, I’ll right-click the Environments folder and click Create Environment. Using this approach I’ve created two environments called Test and Production.

You can edit the properties of each environment to create environment variables, which in turn can be used to set project parameters when project packages are run in the context of the environment. For example, here I’m creating an environment variable named tName in the Test environment with a value of TestDBList. I’ve also created an environment variable with the same name in the Production environment and assigned the value DBList.

Finally, I can hook the environments up to the project by editing the properties of the project in the Integration Services catalog and adding environment references, as shown here…

… and setting parameters to get their values from environment variables as shown here (note that in the CTP release, you must click OK after adding the environment references on the References page before re-opening the Properties window and changing the parameter value on the Parameters page):

So now we have a project deployed to our Integration Services catalog. The project contains two packages - one of which doesn’t actually do anything, and another that copies the list of database names from the sysdatabase system view in the master database to a table in the CmSampleDB database.  There is a project-level parameter that is used to indicate which table the database names should be copied to, and this is set to TestDBList or DBList depending on the environment that the package is executed in. To test this, I can right-click My Package.dtsx in Object Explorer and click Run, which produces the following dialog box:

Note that I can select the environment reference I want to use, which will determine the value of the tName environment variable, which will in turn set the value for the TableName project parameter and ultimately determine which table the data is copied to. For this example, I’ll select the Test environment and run the package, and the data is coped to the TestDBList table as shown below:

To review past operations in the catalog, you can right-click SSISDB under the Integration Services folder in Object Explorer and click Operations. This shows a list of all operations that have been performed in the catalog in reverse order of occurrence, so in this example you can see that a project was deployed and then a package was executed.

Double-clicking an entry in the list reveals more information about the operation. For example, here’s the  details for the package execution:

Note that clicking the Parameters tab shows the parameter values that were used for this particular execution:

I’ve used this article to give you a quick tour of the new deployment model for SSIS and how you can use project parameters and environment variables to create a more flexible but manageable ETL solution with SSIS in SQL Server “Denali”. For more information about what’s new in SSIS in “Denali”, see SQL Server Books Online.

Read More >>

SQL Server Denali - Query Acceleration for Data Warehouses with ColumnStore Indexes

ColumnStore indexes are a new feature of SQL Server Denali that boost query performance in data warehouses. Although this feature is not a part of the latest CTP release, it is a significant development given the energy that Microsoft has expended in recent years to compete with its main rivals as a serious platform for enterprise data warehousing and business intelligence (BI). With the SQL Server 2008 and SQL Server 2008 R2 releases, Microsoft introduced features such as data compression, improved performance for partitioned table and indexes (http://cm-bloggers.blogspot.com/2009/04/table-and-index-partitioning-in-sql.html), star join query optimization (http://cm-bloggers.blogspot.com/2009/05/sql-server-2008-star-join-query.html), the Fast Track reference architectures (http://cm-bloggers.blogspot.com/2009/06/fast-track-data-warehouse-reference.html), Resource Governor (http://cm-bloggers.blogspot.com/2009/05/sql-server-2008-resource-governor.html), and the new Parallel Data Warehouse (PDW) offering. These have given SQL Server the manageability, scalability, and fast, predictable performance times required to deliver true enterprise data warehousing. Meantime, PowerPivot technologies, deeper integration with SharePoint and Office, Report Builder (http://cm-bloggers.blogspot.com/2009/01/sql-server-reporting-services-report.html), and a whole host of improvements to Reporting Services and Analysis Services have helped to put Microsoft in the leading pack for BI solutions.

ColumnStore indexes should be seen in the light of these earlier developments as a way of further boosting data warehouse performance, and by extension, BI performance. Early reports suggest this could be by factors of ten or more and even by up to one thousand times in some cases, so it really does have the potential to seriously speed up long-running queries.

ColumnStore indexes use database pages to store their data, just like standard indexes; However, unlike the pages of standard indexes, which store rows of data, the pages in a ColumnStore index store only the contents of individual columns. Pages are grouped into sets, one set for each column in the table. For example, for a table with five columns, a ColumnStore index would have five sets of pages, one set for each column in the table. Storing data in this way improves disk read performance because it enables SQL Server to retrieve and place in memory only the data columns that are specified in the query. Unnecessary columns of data that would also be returned in a standard index are not returned because they are not present in the index data pages.

Compression on ColumnStore indexes also has a big part to play in boosting performance. This is because columns generally tend to contain repetitive data and one way in which SQL Server compression works is by removing exactly this kind of redundant, repeated data. As a result, once it is loaded into memory, the index takes up much less space and is therefore much less likely to be paged out, ensuring that it remains available for subsequent queries that reference the same columns. Many queries that are run against data warehouses centre around a limited set of columns which are contained in fact tables, so a few carefully planned ColumnStore indexes could result in a major performance boost for these types of queries.

I wasn't able to try ColumnStore indexes out for myself, as the CTP doesn't seem to support them, as my attempt to create one shows:

However if Microsoft's performance figures translate into real-world scenarios, then this should add even more strength to an already compelling data warehousing / BI package. For more information, go to: http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf

Read More >>

Step by Step Guide to Setting up HADR in SQL Server Denali

Following on from my previous post, I wanted to try out the HADR functionality in SQL Server “Denali” CTP1.

To test the solution, I wanted to run SQL Server HADR in a virtual machine environment, with all the VMs running on one physical host.

This seemed like a pretty straightforward task at first. There were a number of blogs with step by step instructions, however none of them actually showed HADR running and I was soon to find out why. After following the steps and finding that it did not work, I did some digging around online and, thanks to the help of Justin Erickson at Microsoft, found that Books Online is unclear on the pre-requisites (and soon to be corrected). Essentially, it sounds like you can run HADR on a single node cluster and this is what the other blog posters, and myself initially, had done. This is not the case. HADR must run on a multi-node cluster. So here are the steps to set this up in a virtual machine environment:

 

Pre-requisites

Hardware (physical)

One machine running Windows Server 2008 R2 with enough memory to run three virtual machines (I had 8GB and this was sufficient)

Hardware (virtual)

One domain controller running Windows Server 2008 R2 which I have name DenaliDC.

Two domain joined Windows Server 2008 R2 VMs with SQL Server Denali installed. Note: These cannot be a copy of the same VHD or clustering will fail (and it will take you ages to work out why). I have named these DenaliOne and DenaliTwo.

Note: I had some problems connecting to the default instance in Management Studio and so I have used named instances DenaliOne\DenaliOne and DenaliTwo\DenaliTwo.

Virtual Network

I created five private virtual networks as follows:

This is a bit over the top, but virtual network cards are free, so why not?

Domain Controller Networking

I added two network cards on the DataOne and DataTwo networks. DenaliOne has a fixed IP address of 192.168.10.1 and DenaliTwo has a fixed IP address of 192.168.20.1.

I added DHCP with a scope of 192.168.10.10 – 192.168.10.20.

I added DNS and created a default forward lookup zone.

DenaliOne Networking

I added five network cards as follows:

Name: DataOne

IP Address: Obtain automatically

Name: DataTwo

IP Address: 192.168.20.11

Default gateway and preferred DNS Server: 192.168.10.1.

Name: iSCSIOne

Remove Client for Microsoft Networks, QoS Packet Scheduler, File and Printer Sharing for Microsoft Networks, IP Version 6

IP Address: 192.168.2.1

Register this connection’s address in DNS: Unchecked

Name: iSCSITwo

Remove Client for Microsoft Networks, QoS Packet Scheduler, File and Printer Sharing for Microsoft Networks, IP Version 6

IP Address: 192.168.3.1

Register this connection’s address in DNS: Unchecked

Name: Heartbeat

Remove Client for Microsoft Networks, QoS Packet Scheduler, File and Printer Sharing for Microsoft Networks, IP Version 4

IP Address: Obtain an IPv6 address automatically

Register this connection’s address in DNS: Unchecked

DenaliTwo Networking

I added five network cards as follows:

Name: DataOne

IP Address: Obtain automatically

Name: DataTwo

IP Address: 192.168.20.12

Default gateway and preferred DNS Server: 192.168.10.1.

Name: iSCSIOne

Remove Client for Microsoft Networks, QoS Packet Scheduler, File and Printer Sharing for Microsoft Networks, IP Version 6

IP Address: 192.168.2.2

Register this connection’s address in DNS: Unchecked

Name: iSCSITwo

Remove Client for Microsoft Networks, QoS Packet Scheduler, File and Printer Sharing for Microsoft Networks, IP Version 6

IP Address: 192.168.3.2

Register this connection’s address in DNS: Unchecked

Name: Heartbeat

Remove Client for Microsoft Networks, QoS Packet Scheduler, File and Printer Sharing for Microsoft Networks, IP Version 4

IP Address: Obtain an IPv6 address automatically

Register this connection’s address in DNS: Unchecked

Additional Steps

There is a slight problem with clustering in a virtual machine which is the inability to share a VHD. The way around this is to use a virtual iSCSI device. This involves either using Windows Storage Server or a product called Starwind. I hadn’t used either before and opted for a free trial of Starwind as there was less to download.

Starwind Setup

1. Download and install Starwind on the DC from here.

2. Add a host with an IP address of 127.0.0.1 and Port of 3261.

3. Double click the host and logon with a username of root and password of starwind.

4. Add a target with a suitable name (I used Quorum) and the following settings:

a. Storage type of Hard Disk

b. Device type of Basic Virtual and Image File device

c. Device creation method: Create new virtual disk (place it wherever you want with a .img extension)

d. Image File device parameters: Allow multiple concurrent iSCSI connections

e. Accept defaults for all other parameters.

5. You should end up with something like this:

6. Click on the host and then click Network in Server Settings. You should then see the IP Address and port that the host is using. In this case I will use 192.168.10.1 and 3260. Note down these values.

Install iSCSI Inititor

On both SQL Server VMs:

1. Install the iSCSI Initiator from here. There is also a Starwind version, but I used the Microsoft one.

2. Run iSCSI Initiator and click the Discovery tab.

3. Click Discover Portal and enter the values from the Starwind software that you noted earlier.

4. Now click the Targets tab and click Refresh.

5. Connect to the Starwind target.

6. Run the Disk Management console (Diskmgmt.msc) and you should see a new disk.

7. Bring this disk online in both virtual machines, but only format it from one VM (DenaliOne)

Add Windows Clustering

On both SQL Server boxes run Server Manager and add the Failover Clustering feature. After any reboots check that you can still see the iSCSI disk and if not run iSCSI Initiator and check that you are connected to the target. It is quicker to disconnect and connect again than wait for the automatic reconnection.

Follow these steps to build the cluster:

1. On DenaliOne run Failover Cluster Manager and in the Actions box click Create a Cluster.

2. Skip the introduction screen and add DenaliOne and DenaliTwo. They should appear with their FQDNs.

3. Click Next through all of the warnings until the Cluster Validation runs.

4. Resolve and Cluster Validation errors. Don’t worry about warnings for this example.

Install SQL Server HADR

1. On DenaliOne:

a. Create a folder for transferring logs. I created C:\HADRShare\Logs.

b. Share this folder.

2. On both SQL Server VMs:

a. Star SQL Server Configuration Manager.

b. Right click the SQL Server service and click Properties.

c. Click the SQL HADR tab.

d. Click Enable SQL HADR service and click OK.

e. Stop and Restart SQL Server.

3. On DenaliOne run SQL Server Management Studio.

a. Create a database (I called this SQLHADR). For simplicity with backup and restore, place the files in a directory that exists on both servers. I created C:\Databases\Data on both virtual machines and used this.

b. Create a table in this database and add a row to the table.

c. Backup this database and restore it on DenaliTwo with the RESTORE WITH NORECOVERY option.

d. Expand Management and right click Availability Groups.

e. Click New Availability Group and click Next.

f. Enter a suitable name (I used SQLHADR) and click Next.

g. On the Select Databases page select SQLHADR and click Next.

h. Click Add and connect to DenaliTwo.

i. I want to use the secondary, or mirror, database to offload querying from the primary server. It is no longer necessary to create a snapshot and the querying happens with live data. To configure this change the read mode in Secondary Role to Allow Read Intent Connections and click Next:

j. Click Finish.

Start Data Synchronization

1. Click Start Data Synchronization.

2. Specify the share that you created previously and click Test.

3. Click OK.

4. Click Close.

Querying the Secondary Instance

With SQL HADR you can now query the secondary database. Previously, you could not query the mirror. Now, as long as we allowed connections when we set up the availability group, we can offload queries from the primary database:

Now, with SQL Server HADR, we have a high availability solution providing a failover database, but we can also offload querying to this database and have a reduced workload on our primary server. We’ve come a long way since log-shipping!

Performing a Failover

1. In Management Studio on the DenaliTwo\DenaliTwo instance expand Management, expand Availability Groups, expand SQLHADR, expand Availability Replicas and right click DenaliTwo\DenaliTwo.

2. Click Force Failover.

3. Click OK.

Note: Do not try to failover the primary database. This will fail.

 

Notes

This wasn’t the most straightforward environment to setup, mostly due to inaccurate step by step instructions on other blogs (never trust a solution that doesn’t show it working at the end). The key things that I found were:

• You need a two node cluster. This requires a shared disk, although SQL Server doesn’t use this shared disk.
• For some reason, I had inconsistent problems with default instances. If this is the case, try a named instance.
• When you create the networks in Hyper-V, create one at a time and configure it in the VMs before creating the next network. This ensures that you know which network is which and helps with troubleshooting.
• If anything doesn’t work and you change the configuration, you must disable HADR on all instances, restart the instances, re-enable HADR and restart the instances again.
• I had HADR running on every instance on every server, although it now seems to work with only the mirrored instance having HADR enabled.
• Perform failovers on the secondary database, not the primary. This makes sense when you have multiple secondary databases because you will failover the new primary.

Read More >>

SQL Server “Denali” HADR

Continuing with the SQL Server Denali blogs , I thought I’d take a look at the new high availability disaster recover, or HADR, functionality.

With SQL Server 2005, Microsoft introduced Database Mirroring. Prior to this we had a system called Log Shipping which was essentially an automatic backup and recovery system. Log Shipping did supply a failover instance of SQL Server, but, by default, it replicated every 15 minutes, which could result in the loss of important data. Furthermore, Log Shipping had no automatic failover. With Database Mirroring we had a system which was easier to setup, allowed us to choose the level of availability against performance, and with SQL Native Client, had automatic failover. The only limitation was that you couldn’t query the mirror. This could be remedied by taking periodic snapshots and using these for reporting, however if you wanted a real-time reporting database to take the load off your principal instance, then database mirroring wasn’t the answer. The other limitation was that you only had one mirror for each database.

Five years on and what has changed? Well, we now have High Availability Disaster Recovery (HADR). HADR is mirroring on steroids. You can have multiple mirrors (or secondary databases) to provide improved availability and ,furthermore, we can query these mirrors. We also have availability groups. Each availability group can have multiple databases and we can now failover these databases together in their group rather than on database at time.

In the CTP version of Denali there are some limitations which will not exist in the final product. For now, we can only have one secondary database, but in the release version, we can have four. We also only have asynchronous mode which is the equivalent to high performance mode, but again this is expected to be supplemented in the release version. Failovers can only be forced, which has the potential for data loss and are not currently automatic, but again, this will change.

There are some caveats. Obviously, there is a load on the server to transfer the logs. The CTP release uses an asynchronous mode whereby the primary server does not wait until the secondary server confirms that it has written the log records to disk before the transaction is committed. This reduces load on the primary server, but does have the potential that an update could be lost if the primary server fails just after a transaction is committed, but before it has been written to the secondary server. With the final release there will be a synchronous mode, but you will need to decide between protection and performance.

Currently, there are not many tools beyond setup and Force Failover available in Management Studio, although there are the following PowerShell cmdlets allowing you to also suspend and resume availability group databases:

Add-SqlAvailabilityGroupDatabase
Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
New-SqlAvailabilityReplica
Remove-SqlAvailabilityGroup
Resume-SqlAvailabilityGroupDatabase
Suspend-SqlAvailabilityGroupDatabase
Switch-SqlAvailabilityGroup

Although this is much more complex to setup than database mirroring, it provides a genuine high availability solution that also improves performance by offloading query operations to a mirror server. It will be very interesting to see how this evolves in the final product, but from these beginnings, SQL Server HADR looks to be an excellent availability solution.

Read More >>

Using the New THROW Keyword in SQL Server "Denali"

When SQL Server 2005 introduced the TRY...CATCH blocks, SQL Server developers were finally able to use structured exception handling to their code. However, passing error information back to the application from the CATCH block using a RAISERROR statement is not as simple as it seems. To ensure that the correct information is passed back to the caller, you have to add your error information to the sys.messages table. That's easy enough to do, but if your application moves onto another server, there's a risk that you'll forget to add the error to sys.messages on the new server and your application may not function correctly.

SQL Server "Denali" introduces the THROW keyword, which extends the exception handling functionality of SQL Server 2008. It eliminates the issue of having to define errors in sys.messages and also enables you to handle an error and then rethrow that error back to the calling code.

So let's look at some examples of how you can use THROW in your Transact-SQL code. If you are implementing search functionality, you might want to customize the information returned when a SELECT statement finds no records. In this situation, you can simply use the THROW statement to return your own error message to the calling application.

SELECT * FROM Person.Person
WHERE LastName = 'Smyth'

IF @@ROWCOUNT = 0
THROW 50001, 'No results found.', 1

You can see in this example that I've used an error number greater than 50000. This is a requirement of THROW to avoid replicating any of the system error numbers. I've specified a meaningful message to be returned to the calling application that can be used directly in the calling code and the state parameter, which you can use to locate where the error originated.

The output when you run this code will be:

(0 row(s) affected)
Msg 50001, Level 16, State 1, Line 7
No results found.

You can also use THROW in a TRY ... CATCH block. This enables you to execute Transact-SQL code such as rolling back a transaction or logging information before passing the error back to the calling application.

BEGIN TRY
DECLARE @BusEntityID int;
SET @BusEntityID = (SELECT MAX(BusinessEntityID) FROM Person.BusinessEntity)
INSERT Person.Person(BusinessEntityID, PersonType, NameStyle, FirstName, LastName)
VALUES(@BusEntityID, 'EM', 0, 'Lin', 'Joyner')

END TRY

BEGIN CATCH
-- Handle the error.
-- Log the error in the SQL Server application log.
THROW
END CATCH

By using the THROW statement on its own, you will simply rethrow the existing error that has occurred. This is useful when the SQL Server error message is meaningful to the user or application. If you run this code with a non-unique value for the BusinessEntityID field, you will see the following error:

(0 row(s) affected)
Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK_Person_BusinessEntityID'. Cannot insert duplicate key in object 'Person.Person'. The duplicate key value is (20780).

You can use the parameters of THROW to make the error information more meaningful to the calling application or user as shown in the following example.

BEGIN TRY
DECLARE @BusEntityID int;
SET @BusEntityID = (SELECT MAX(BusinessEntityID) FROM Person.BusinessEntity)
INSERT Person.Person(BusinessEntityID, PersonType, NameStyle, FirstName, LastName)
VALUES(@BusEntityID, 'EM', 0, 'Lin', 'Joyner')

END TRY

BEGIN CATCH
-- Handle the error.
-- Log the error in the SQL Server application log.
THROW 51111, 'Invalid business entity id.', 2
END CATCH

In this case, the output when you violate the primary key will be as follows:

(0 row(s) affected)
Msg 51111, Level 16, State 2, Line 9
Invalid business entity id.

You'll notice that the severity level when you throw your own error is defaulting to 16. You cannot define a severity level for custom errors, however when you rethrow a SQL Server error, the original severity level is retained. You can only rethrow errors from inside a CATCH block, if you are outside of the CATCH block, you must specify the parameters.

So, as you can see, the new THROW statement in SQL Server "Denali" extends the structured exception handling capabilities of earlier versions of SQL Server.

Read More >>