With PDC around the corner the SQL Azure team has delivered an updated CTP that’s feature complete, running on production clusters and containing cool new features like firewall, table value parameters, support for BULK INSERT and new data types. Checkout the product team blog for details.
So, what does this new release mean to developers?
Move Forward with Confidence
Now that you can get your hands on a feature complete version of SQL Azure you can have confidence that when SQL Azure Database becomes generally available, all your databases and data will be automatically converted into an active subscription to the service based on the subscription offer you choose at go-live.
The October CTP is running in a new, production-ready cluster. You can access the new developer portal for the October CTP via the same URL as before https://sql.azure.com/ which you will notice has an updated skin and a few new features.
Another thing you may notice is that your existing projects are no longer showing up! You can access your August CTP project through the developer portal available at this URL https://ctpportal.database.windows.net.
New Server URI
With the release of the October CTP you will also need to create a new server for your account. This is the same process for creating a server as you may have followed for the August CTP. Guidance on this is available in the Introduction to SQL Azure hands on lab in the Window Azure Platform Training Kit.
One interesting thing to note is that the Server Location is South Central US which is right alongside Windows Azure so you should see even less latency between the services.
The other big thing to know is that there is a different URI for the Server Name. Before the syntax was
To access servers created in the production cluster you remove the “ctp.” portion of the Server Name. A connection to the new server will be
I have a feeling that this is going to trip up a bunch of developers so hopefully this helps. Another area I believe will catch a number of people off guard is the firewall feature.
New Firewall Feature
For years we’ve been telling people to lock down access to their databases machines. With the August CTP of SQL Azure there was no way to limit access to what machines had access to your server. With the October CTP you can now specify which IP addresses have access to your server which is awesome!
By default no IP’s are allowed to access your server. If you try to connect you will get the following error.
TITLE: Connect to Server
Cannot connect to m9vhbwlttp.database.windows.net.
Cannot open server ‘<MY_SERVER_NAME>’ requested by the login. Client with IP address ‘<MY_IP_ADDRESS>’ is not allowed to access the server.
Login failed for user ‘<MY_USERNAME>’. (Microsoft SQL Server, Error: 40615)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=40615&LinkId=20476
You can modify the firewall rules via the portal, T-SQL stored procs or web service API’s which you can view MSDN docs for details on. Using the portal just click on the Firewall Settings tab. From there you can either check the box to Allow Microsoft Services access to this server which will enable all Windows Azure Web and Worker Roles to access your server.
Additionally, you can click the Add Record button and add your current IP address which is shown in the dialog box.
These updated rules may take up to 5 minutes for the change to take affect so please be patient.
Moving Your Data and Databases
First, you will need to move databases from the current CTP cluster over to the new cluster. As mentioned above, these are separate, distinct machine clusters and actually reside in different data centers. More importantly, the data and databases you create in the October CTP will automatically roll over into the production service at go-live – at which point we will start to charge for those databases based on the service offering you’ve sign up for. Rather than risk inadvertently charging people for a bunch of test databases, the team got feedback that it was best to allow people to ‘opt-in’ to the new cluster. That way, people will only get charged for what they need and use.
Creating databases in the October CTP will be even easier and faster than on the August CTP. I’d recommend checking out the Migrating Databases to SQL Azure lab in the Windows Azure Platform Training Kit for guidance on moving data with BCP and SSIS. With the BULK INSERT support in the October CTP developers can deploy BCP.EXE into a Windows Azure Worker Role eliminating the need to copy the data down locally.
As I mentioned, SSIS is also an option, although we know that customers have experienced performance issues with the SQL Server 2008 Integration Services and SQL Azure. The next CTP of SQL Server 2008 R2 will have an update to SSIS which dramatically increases performance when loading data in SQL Azure.
Specify Database Edition
With this CTP you can also use a MAXSIZE clause when executing CREATE TABLE to select Web or Business Edition.
CREATE DATABASE foo (MAXSIZE = 1GB)
CREATE DATABASE foo (MAXSIZE = 10GB)
Everyone is driving hard towards PDC and we are all very excited for Commercial Availability. Keep your eyes pealed for updates to SQL Server Management Studio and SQL Server Integration Services as part of the SQL Server 2008 R2 wave. With the updates to SSMS you will be able to use the Object Browser and have an option for exporting SQL Azure compatible scripts. With the updates to SSIS you will see dramatic performance improvements when loading data in a SQL Azure database.