SQL Azure Announcements Welcome Back Phoenix Microsoft Shutting

SQL Azure

Announcements • Welcome Back Phoenix! • Microsoft Shutting Down Northwest Data Center – Reason given: changes in local tax laws – Apps hosted there must move to Southwest – Expect they are relocating the data center elsewhere

Who I am • • • Work for Neudesic as a senior consultant Worked in the industry for about 10 years From the East coast (lived in AZ for 9 years) I like books (bribes are accepted) Pina Coladas, Dancing in the Rain, Long walks on the beach

SQL Azure - Overview Database in the Cloud First preview CTP came out in late August Very similar to SQL Server Database functionality only at present (no SSRS, SSAS, SSB) • Free to use between now and release (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 token will come in the mail

SQL Azure – Manage from Azure. com

SQL Azure – Manage from Azure. com

SQL Azure Server Administration Server Name Admin Show Connection String Data Center Databases 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. net Database=database User ID=user Password=password Trusted. Connection=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 toolbar 3. enter Server name: full servername Login: just the username (no @. . . ) and password 4. Under Options, Connect to Database, enter db name 5. Click Connect, ignore the error that is displayed.

Step 1: Cancel Connect Dialog Cancel Dialog

Steps 2 -3: Click New Query, Enter Info Click New Query Server Name Admin user id and password

Step 4: Enter Database Name

Step 5: Click Connect, Ignore Error

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 – – – • High Availability – – – • 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 – – – • 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 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. 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 Supported 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: – 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.

Multi-tenancy Issues • Might not always connect • Long running queries are likely going to be throttled • Idle times will be short to free up connections for others • Limits on database size

Privacy / Security Issues • Consider encrypting data before it goes to database • Make sure you encrypt your connection to azure via SSMS • Big players are already hosting private data on this - Exchange Hosted Archive(EHA) • Kind of a question mark right now.

DEMO: MIGRATING A SQL SERVER DATABASE AND ASP. NET WEB APPLICATION TO USE SQL AZURE

SQL Azure Resources • Documentation http: //msdn. microsoft. com/enus/library/ee 336279. aspx • Sign up for SQL Azure http: //www. Azure. com • SQL Azure Developer Center http: //msdn. microsoft. com/enus/sqlserver/dataservices/default. aspx • Azure User Group http: //www. Azure. User. Group. com