Another large set of Azure enhancements: including some for SQL folk

I'm really impressed by the rate that the Azure team is making and releasing enhancements. Scott Guthrie posted about these again recently.

The following items in the announcement were ones that I think can be useful to those with an interest in SQL Server:

* General availability of Azure VMs. While we've had SQL Database and SQL Reporting available as services in Azure, we don't have the other parts of the database platform. With Azure VMs, you can spin up a VM to run SQL Server.

* SQL Server templates. You don't need to install SQL Server when creating a VM. There are templates that have this already installed. You can also save your own templates. They've also provided BizTalk and SharePoint templates.

* Hourly billing for the templates above. This means that if you just need a SQL Server system for a few hours, you can easily do that.

* Virtual Networks. You can create a virtual network, including the ability to stretch that out to your on-premises systems.

* Larger memory VMs. If you really want to try out tabular analytic models, etc. you can now get up to 8 cores and 56GB of memory in a VM.

* Durable data disks from storage can now be up to 1TB in size each.

* Significant reduction in the price of the VMs.

You'll find Scott's announcement here: http://weblogs.asp.net/scottgu/archive/2013/04/16/windows-azure-general-availability-of-infrastructure-as-a-service-iaas.aspx

Warning: Lost my stored Azure credential details in SSMS after applying CU3 + Hotfix

I posted the other day about a hotfix that's needed after you apply SQL Server 2012 SP1 CU3, to still be able to open or edit SSIS projects or database maintenance plans in SSDT.

However, I found that after I applied CU3 and the hotfix, that all my stored credential details for SQL Servers was gone. That was particularly nasty for all my Azure-related logons. I had to set them up again.

No idea why that happened but figured I should give you all a heads-up about it!

New Features added to Azure today – AD, Backup & Website Monitoring

It's great to see the constant roll-out of capabilities across the Azure platform.

One in particular that many have been waiting for is Active Directory. Scott Guthrie posted today that it's now available for production use. This is particularly significant and also enables options for on-premises directory sync and federation. If you have been using Office365, you have already been using Azure AD.

Scott also mentioned that they are about to open a preview of a link from OAuth 2 (or OpenID) to Azure AD. That will enable you to use alternate credentials (such as Twitter credentials) to authenticate against Azure AD.

They have also added an Azure Backup option and added a variety of monitoring options for Azure Websites, including the ability to retrieve website logs via FTP.

Scott's post about this is here: http://weblogs.asp.net/scottgu/archive/2013/04/08/windows-azure-active-directory-general-availability-new-backup-service-web-site-monitoring-and-diagnostic-improvements.aspx

Another government certifying Azure: UK

One of the common objections that comes up when talking to people about storing their data in Azure, is that "we are not allowed to do it". In so many cases, that simply isn't true, or it's true for a small part of the overall data. In other cases, it's a case of no-one wanting to be seen to be the first in their industry to do so, for some perceived liability reasons. Again, most of these are unfounded.

The thing that can change these opinions though is certification by governments. Some have already done so and it's great to see the announcement that Windows Azure received G-Cloud Impact Level 2 Accreditation from Cabinet Office for use across the UK Public Sector. You can read more about it here: http://blogs.msdn.com/b/ukgovernment/archive/2013/04/04/windows-azure-receives-g-cloud-impact-level-2-accreditation-from-cabinet-office-for-use-across-the-uk-public-sector.aspx

Using Existing Windows Azure SQL Databases with Azure Mobile Services

I was one of the speakers today at the 6 Weeks of Azure bootcamp event in Sydney today. During the day, as well as speaking, we have an "unconference". There's a lot of discussion on whatever the attendees want to discuss.

One of the offline questions I got today though was about how to hook up Azure Mobile Services to an existing Windows Azure SQL Database rather than using the one that Mobile Services can auto-create. While getting that to work, we learned a few things:

  • Tables need to be created in a schema that has the same name as the Mobile Service. (ie: if I want a table called CurrentTasks, and my service is called TaskManager, I need to have my table called TaskManager.CurrentTasks.
  • Tables need to have a clustered primary key that is an integer and is called "id" (it appears case-sensitive in the app for some reason)
  • After you modify the mobile service to point to your database (or after you create a mobile service referencing the existing database), you need to add each table to the service by using the mobile services portal. (If you reference a table that doesn't exist, that's when it would also create the table)

The attendee had already created his tables in the dbo schema. We moved his tables by using:

ALTER SCHEMA servicename TRANSFER dbo.OldTableName;

It's also worth noting that Mobile Services isn't yet available in all datacenters. The attendee's database was in a different datacenter and it's important to try to avoid that, or the latency can be a big issue. He and another attendee were asking about the best ways to move the database to another datacentre. One option for this is to export a BACPAC of the database to Azure Storage, and to then import the database to the alternate server.

Unfortunately, even though CREATE DATABASE AS COPY OF works across servers within the same datacenter, it doesn't work for servers in different datacenters.

 

Windows Azure SQL Reporting – now configurable in the new portal

Scott Guthrie blogged yesterday about a raft of new changes to the Azure platform.

Of interest to SQL folk are really three main changes:

* SQL Reporting has made its way into the new portal (ie: you no longer have to flip back to the old portal to work with it. Hopefully Data Sync will do the same soon).

* Better options now exist for downloading blobs from Azure Storage. (This could include, for example, SQL Server backups made using BACKUP TO URL).

* New tools for monitoring the usage and availability of VMs.

For others, it's good to see that you can now upload .cer certificate files, not just .pfx certificate files when working with cloud services, and also interesting to see Android support added to mobile services.

You'll find Scott's post here: http://weblogs.asp.net/scottgu/archive/2013/03/04/windows-azure-updates-android-support-sql-reporting-services-active-directory-more.aspx

SQL Down Under Show 56 – Thomas LaRock – DBAs moving to architect, constant learning, Azure, big data

I recorded another podcast today with Thomas LaRock, aka SQL Rock Star.

In this show, Tom and I discussed the migration of many DBAs into architect roles, the challenges in constant learning (and useful resources for doing so), the hype around big data, the role of Windows Azure in our future, and upcoming PASS events.

It's online now: https://www.sqldownunder.com/Resources/Podcast.aspx

Enjoy!

Sysprep improvements in SQL Server 2012 SP1 CU2

Sysprep has been a useful tool to allow the preconfiguration of SQL Server environments, along with the rest of the operating system. This can be very useful for templated Azure VMs as well as for on-premises systems and private clouds.

One of the limitations has been that not all SQL Server components were "sysprep-able".

Another important change in CU2 for SQL Server 2012 SP1 is that more SQL Server components are now able to be "sysprepped". In particular, SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS) can now be part of this.

This is good news and you'll find more info in the VM Engineering team's blog post by Robert Hutchison here: http://blogs.technet.com/b/scvmm/archive/2013/01/25/expanded-sysprep-support-in-sql-server-2012-sp1-cu2.aspx

Backup TO DISK, TAPE and now URL – Backup On-Premises SQL Server Databases to Azure Storage

There has been a lot of discussion over the years about whether or not it's a good idea to introduce new functionality in service packs (for SQL Server, the OS, and others). The concern is that it can invalidate testing, invalidate documentation, books, courseware, etc. However, it can also introduce much-needed features without having to wait for another version of the product. It's been even-less likely that new functionality would be introduced in a cumulative update (or CU), which are basically a set of rolled-up hotfixes.

However, interesting new functionality has appeared in CU2 for SQL Server 2012 SP1.

The BACKUP command now supports TO URL when specifying a backup device. That allows you to specify the location of an Azure storage account as the backup destination. This avoids the need to create a backup locally, and to then have a process to copy it to Azure storage. Similarly, RESTORE now also supports a FROM URL option.

This is very interesting news for a number of reasons.

For customers using SQL Server within Azure Virtual Machines (VMs), it provides a way to specify the use of a storage account as the backup destination. While you could do that before by mapping a drive letter to a storage account (and that's what you do when setting up a VM in the first place), the number of drive letters available is limited and this avoids using them up.

For customers using SQL Server on-premises, this provides a direct cloud-based backup option with low-cost, large storage available. The biggest limitation for these customers will no doubt be network bandwidth but for some customers, that isn't an issue. It's also an interesting new DR option for customers that have relatively smaller databases that don't take too long to send over the network but which are none-the-less critical to the organisations.

Another group of customers that would benefit from this would be any customer that needs to distribute a single copy of a database to multiple locations. A backup can be done once, and each secondary location can restore directly from the cloud. I can imagine this being an interesting way to push out new versions of reference data, etc.

A notable group of customers that would benefit from this would be those using Windows Azure SQL Databases. However, the syntax is not yet supported in WASD. I have no advanced knowledge on this but I'm guessing that adding this looks pretty likely, as it would be so beneficial.

When you connect to a storage account, you need to provide both the URL and a storage key. There are two types of keys. One type of key allows for unlimited access. The other type of key can be time-bombed, handed out for short-term purposes and can easily be revoked. Both these types of keys should work with this new option. The BACKUP and RESTORE commands both support a WITH CREDENTIAL option for providing the link to this key.

Credentials were introduced in SQL Server 2005. They are basically a way of giving a name to a set of authentication details (name and secret/password). Previously we have used them with SQL Server Agent Proxy accounts, to allow us to fine tune the permissions that are granted to steps in SQL Server Agent jobs. Now they have an interesting new use.

You'll find details of the changes, with samples of the syntax in this article: http://blogs.msdn.com/b/windowsazure/archive/2013/01/24/sql-server-backup-and-restore-to-cloud-simplified.aspx

Working with Aliases for Windows Azure SQL Databases in SQL Server Management Studio

One of the issues that is often raised with Windows Azure SQL Database is that you don't get to pick the name of your server, so you end up with a bizarre name such as:

yy2l95dk1k.database.windows.net.

I can understand why the team did this. Apparently when they first set it up, they allowed you to pick your own server name, so everyone started registering Coke, Pepsi, etc. Not wanting to have yet another place for people to argue about name ownership, they quickly removed that ability. I'm glad they did.

However, when working with a databases, I've been finding that I'm constantly looking at lists of Azure servers and having no idea which one is which. When I open SQL Server Management Studio (SSMS), and ask to connect, I'm greeted with a list of servers that looks like:

  • yy2l95dk1k.database.windows.net
  • ky4296dk1k.database.windows.net
  • xy9914dk2j.database.windows.net

and so on. Now I'm sure there are people that can remember which one is which, but as the number of servers increases (particularly when I'm dealing with client servers as well as my own), I'm not one of those people that can.

Normally when I'm working with a bunch of servers and I only have IP addresses, I configure SQL Server Client Aliases for each address. However, when I first tried to configure an alias like AzureDemo for a server called yy2l95dk1k.database.windows.net, I found I couldn't connect to it. I received an error that said "Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). That led me to believe that using an alias wouldn't work.

However, in later versions of this error message, more information is provided. "Some libraries do not end the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match. (Microsoft SQL Server, Error: 40531). Once again, when I first saw this, I presumed that it would still stop me from using an alias but that is not the case.

To use a meaningful alias, what you need to do is:

  • Using SQL Server Configuration Manager, in the SQL Native Client 11.0 Configuration\Aliases node, create an alias. Pick a meaningful name (ie; HRServer) for the Alias Name, leave the port at the default (ie: 1433), leave the protocol at the default (ie: TCP/IP) and provide your real server name (ie yy2195dk1k.database.windows.net) in the Server field.
  • Create the same alias in the SQL Native Client 11.0 Configuration (32bit)\Aliases node.
  • When connecting to the server in SSMS, enter your new alias (ie: HRServer) for the Server name, SQL Server Authentication for the Authentication method, and for the login, specify your login name followed by an @ symbol, followed by the first segment of the real server name (ie: mylogin@yy2195dk1k), then enter your password.
  • If the user isn't an admin user that has the ability to connect to the master database, you'll also need to manually set the database name using the Options button. (Note that you won't be able to browse for database names).

Once you have done that with all your servers, finding and connecting to the right server should be easy.

In a separate post, I'll describe how to push out aliases to other users in your domain.