SQL Azure

Agenda

• Announcements

• SQL Azure: First Look

• Demo

Announcements

• LA Cloud Camp Wed 9/30http://www.cloudcamp.com/losangeles/

• Moving to an every-other-month meeting schedule (no October meeting)

• Topic for November: Rich Internet Applications (RIAs)

SQL Azure - Overview

• Database in the Cloud

• Very similar to SQL Server

• Database functionality only at present(no SSRS, SSAS, SSB)

• First preview CTP came out in late August

• First time most of us are seeing it is tonight

• Free to use between now and release

• Release is November 2009 PDC

SQL Azure - Database Only

• SQL Azure does not support all of the features and data types found in SQL Server. Analysis Services, Replication, Reporting Services, and Service Broker are not currently provided as services on the Azure Services Platform.

SQL Azure – How to Sign Up

• Just visit Azure.com and follow the sign-up link

• Your invitation tokenwill come in the mail

SQL Azure – Manage from Azure.com

SQL Azure – Manage from Azure.com

SQL Azure Server Administration

Server Name

Admin

Data Center

Databases

Show Connection

String

Admin Password

Reset

Create New Database

Connection Strings

• It’s intended that the main difference between SQL Server and SQL Azure is the connection string

• Connection String Format:Server=tcp:server.ctp.database.windows.netDatabase=databaseUser ID=userPassword=passwordTrustedConnection=False

Create Database

DEMO: SQL AZURE PORTAL

Can I use SQL Server Mgmt Studio?

Yes, but it’s a little rocky right now. You need to follow these steps precisely to get it working:

1. Cancel out of the first Connect dialog that pops up.2. click on the New Query button from the toolbar3. enter

Server name: full servernameLogin: just the username (no @...) and password

4. Under Options, Connect to Database, enter db name5. Click Connect, ignore the ANSI NULLS error that is displayed.

Step 1: Cancel Connect Dialog

CancelDialog

Steps 2-3: Click New Query, Enter Info

Click New Query

Server Name

Admin user id and

password

Step 4: Enter Database Name

Enter Database

Name

Step 5: Click Connect, Ignore Error

SQL Server Mgmt Studio Gotchas

• No Object Browseruse select * from sys.objects instead

• Connections time out after 5 minutesThis is to protect cloud users from adversely affecting each other

Create DB Locally

Script Table Creation

Create Table in SQL Azure

Create Table, Insert, Select in SQL Azure

DEMO: USING SQL SERVER MANAGEMENT

STUDIO WITH SQL AZURE

SQL Azure – Key Benefits

• Manageability– Scale and functionality of an enterprise data center w/o the admin overhead

– Self-managing capability allows organizations to provision data services without adding to their support burden

– Reduce the initial costs of data services by provisioning only what you need. Provision your data storage in minutes.

• High Availability– Built on Windows Server and SQL Server technologies, flexible enough to cope with any variations in usage and load.

– Replicates redundant copies of your data to multiple physical servers to maintain availability and business continuity.

– In the case of a hardware failure, SQL Azure provides automatic failover to optimize availability for your application.

• Scalability– Scale your solution with ease. After partitioning your data, the service scales as your data grows.

– You only pay for the storage that you use. Scale down the service when you do not need it.

• Familiar Development Model and Relational Data Model– Use the same tools and libraries to build solutions that you do for SQL Server.

– Data is stored in SQL Azure just like it is stored in SQL Server, by using Transact-SQL.

– Familiar database objects - multiple databases with tables, views, stored procedures, indices

• No Physical Considerations– SQL Azure servers and databases are virtual objects that do not correspond to physical servers and databases. By

insulating you from the physical implementation, SQL Azure enables you to spend time on your database design.

Administration

• Logical Administration

– DBA manages schema creation, statistics management, index tuning, query optimization, and security administration (logins, users, roles…)

• Physical Administration

– SQL Azure service automatically replicates all data to provide high availably

– SQL Azure service manages load balancing and, in case of a server failure, transparent fail-over.

Backup

• No backup and restore commands

• You can still use SQL Server Integration Services and the SQLCMD utility to bulk copy data.

Provisioning Model

• Each Azure account can have multiple SQL Azure servers• Each SQL Azure server can be associated with one or

more databases.• A SQL Azure server is a logical group of databases and

acts as a central administrative point for multiple databases.

• Each SQL Azure server includes logins similar to those in instances of SQL Server on your premises.

• Each SQL Azure server has a fully qualified unique domain name, which is produced during the SQL Azure provisioning process. servername.ctp.database.windows.net

Create a Database

• Interactively using the SQL Azure portal, or

• Programmatically with a CREATE DATABASE statement.

Managing Users and Logins

• Server-level login administration– Server-level administration for logins and databases in SQL Azure differs from an

on-premise instance of SQL Server. – During provisioning, SQL Azure creates a server-level principal login equivalent to

the sa login in SQL Server– Additional SQL Azure databases and logins can then be created via T-SQL

• SQL Azure provides the same security principals as SQL Server, such as: – SQL Server logins: Authenticate access to SQL Azure at the server level.– Database users: Grant access to SQL Azure at the database level.– Database roles: Group users + grant access to SQL Azure at the database level.

• Database-level login administration– Database-level administration for users and roles in SQL Azure is the same as for an

on-premise instance of SQL Server.

T-SQL SupportSupported Fully or Partially• Constants

• Constraints

• Cursors

• Index management and rebuilding indexes

• Local temporary tables

• Reserved keywords

• Stored procedures

• Statistics management

• Transactions

• Triggers

• Tables, joins, and table variables

• Transact-SQL language elements such as

– Create/drop databases

– Create/alter/drop tables

– Create/alter/drop users and logins

• User-defined functions

• Views

Not Available• Common Language Runtime (CLR)

• Database file placement

• Database mirroring

• Distributed queries

• Distributed transactions

• Filegroup management

• Global temporary tables

• Spatial data and indexes

• SQL Server configuration options

• SQL Server Service Broker

• System tables

• Trace Flags

Programming Model

• Use one of two programming models when using SQL Azure, depending on the size of your database application.

• For smaller data sets, use a single database, similar to how you use an on-premise instance of SQL Server.

• For larger data sets, we recommend that you partition your data across multiple databases and write parallel "fan-out" queries to fetch the data. Doing this optimizes the performance and availability of SQL Azure.

Migration Tips

• Try the SQL Azure Migration Wizardhttp://sqlazuremw.codeplex.com/

• Upgrade to SQL Server 2008 first

• Analyze errors into 2 categories:

– Different but equivalent in the cloud - minorExample: creating logins can’t be done in a batch

– No equivalent in the cloud – majorExample: no support for user-defined types

DEMO: MIGRATING A SQL SERVER

DATABASE AND ASP.NET WEB APPLICATION TO USE SQL AZURE

SQL Azure Resources

• Documentationhttp://msdn.microsoft.com/en-us/library/ee336279.aspx

• Sign up for SQL Azurehttp://www.Azure.com

• SQL Azure Developer Centerhttp://msdn.microsoft.com/en-us/sqlserver/dataservices/default.aspx

• Azure User Grouphttp://www.AzureUserGroup.com