SQL Server Code-Named “Denali”–Getting Started with Availability Groups and Keyboard Customization

Bill Ramos
Principal Program Manager, SQL Server Manageability

If you missed SQL PASS or you didn’t get your copy of SQL Server code named “Denali”, you’re in luck. You can now download it from: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

For the full experience, you will want to run the 64 bit version on Windows Server 2008 R2. You can find the TechNet 180 day evaluation copy of Windows Server 2008 R2 at: http://technet.microsoft.com/en-us/evalcenter/dd459137.aspx

Availability Groups Setup

To test our the new Availability Group feature set, you will need at least on good sized Hyper-V box that can run two VMs each with Windows Server 2008 R2 and then install the feature “Failover Clustering” on each node.  Then, on each node, install an instance of Denali on each of the nodes. Use a Windows account for the database engine instance because the two instances need to talk to each other along with a shared location for the initial backup/restore seeding process.

You are not installing SQL Server Failover Cluster instances, so you don’t need a shared storage device. Think of Availability Groups as Database Mirroring on steroids.

I typically test with 1GB Hyper-V machines and then set the SQL Server memory limit to 500MB (thanks to BrentO for this tip at SQL PASS).

On each instance, you need to “Enable SQL HADR service” using SQL Server Configuration Manager.

Denali CTP1 Availability Groups and Keyboard Shortcuts

Restore some test databases from one of your existing systems onto the database engine instance that you designate as the Primary. make sure the recovery model is set to Full. You may need to take a full backup and a transaction backup to get things to work in the wizard.

While connected to the Primary instance in the Object Explorer (OE), make a new connection to the Secondary instance in OE. This is to make sure both instances can talk to each other. To be extra paranoid, go to the Secondary computer; connect to the instance on that computer; and then connect to the Primary instance. This is to make sure you don’t run into any Kerberos issues.

From there, you’ll restart the instance on both nodes; launch SSMS; expand the Manageability node; right click on the Availability Group folder; and choose the New Availability Group… command.

Rather than go through the specifics of the wizard, it should be intuitively obvious what you need to do. When you add the Secondary replica instance in the wizard, and for the Primary replica as well, change the Read Mode in Secondary setting for both to “Allow all Connections”. This enables all applications like SSMS and Excel to access the databases that you selected for the availability group as read-only databases.

Check out the HADR books-on-line for the topic at http://msdn.microsoft.com/en-us/library/ff878484(SQL.110).aspx. BrentO also has a great write up titled “SQL Server Denali: HADRON ROCKS.

Customizing the Keyboard

Before you file the connect item that Ctrl+E no longer executes a query – we know about this already – and the team is apologizes for the temporary inconvenience. F5 and the often forgotten Alt+X keys still work.

The Denali SQL Server Management Studio now uses the Visual Studio 2010 shell. As part of the conversion to the new shell, the team left in the ability for you to customize the keyboard just like the full Visual Studio product! At this point you should be saying w00t Smile

To add Ctrl+E to Execute a query, do the following:

  • Go to the Tools menu and select Customize
  • Click on the Commands tab in the Customize dialog and then click on the Keyboard… button at the bottom of the dialog.
  • In the Press shortcut keys field, press Ctrl+E

You’ll see where we goofed up by assigning Ctrl+E as a global shortcut key to Query Execute (Ctrl+E(DMX Script Editor))

To fix this snafu, to the following:

  • In the “Show Commands Containing” control, type query.execute and press the tab key.
  • In the drop down the list of available values in the “Use new shortcut in:” control and select SQL Query Editor.
  • Press Ctrl+E in the “Press shortcut keys:” control and click on the Assign button.
  • Then click OK to dismiss the keyboard Options and Close to dismiss the Customize dialog

Ctrl+E now works. In the next public release of Denali, this will be fixed.

For more information regarding keyboard customization in Denali, see the topic “Manageability Enhancements (Database Engine)”

Don’t forget to follow https://twitter.com/ms_sql_server for breaking news and use the #denali hash tag. If you are having problems, check out the MDSN forums and as always, you can use the “Community” menu and “Report a Bug” command to file a Connect bug or suggestion. Don’t forget to use search to see if someone as filed a Connect bug on the same problem already like “SSMS Short Cut key "CTRL + E" is not working to execute any querySmile