–Providing IT resources, as a service, in a dynamic and scalable manner over a network
•What does this mean?
–Access from any device (PC, Phone, Multi-Function Devices, TV…)
–Hosted centrally managed software and data
–Unlimited processing, CPU, storage, memory, data centers
–IT on demand
•Five essential characteristics of the Cloud:
–On-demand self-service
–Broad network access
–Resource pooling
–Rapid elasticity
–Measured service
HighLights:
SQL Azure is the first cloud service completely supporting the relational database model.
SQL Azure is fully committed to supporting T-SQL, SQL query, stored procedures, data views, and so on.
SQL Azure supports the traditional Windows user/password security model.
The first release of SQL Azure is almost fully compatible with all existing relational development tools and IDEs, such as SQL Server Management Studio (SSMS) and Visual Studio.
SQL Azure allows all SQL developers and IT staff to seamlessly migrate from an on-premises environment to a cloud environment with almost no learning curve. All knowledge and skills for SQL developers and IT staff can be applied to SQL Azure. This should remove a big concern from when the Azure framework was announced in October 2008 regarding potential job losses for the IT industry (worries brought on because the infrastructure and data storage hardware for an organization no longer need to exist in the organization).
SQL Azure supports PHP, which makes SQL Azure more friendly to Internet applications.
The most important thing is that SQL Azure is the first platform supporting the rational database model and database as a service running in the cloud. The major changes are not in the physical domain but in the logical virtual domain.
Since SQL Server and T-SQL have been in the market for decades, there is a lot of information that can be found on them. Please bear in mind that SQL Azure does not support object browsing from SQL Server Management Studio. To connect to the SQL Azure database in the cloud using SQL Server Management Studio, launch SQL Server Management Studio.
The authentication type that should be selected is SQL Server Authentication. The login name and password are the same as the ones you used to redeem your invitation code.
The connection to SQL Azure will be automatically deactivated if there is no activity detected by SQL Azure. The error message will be thrown. A custom table can be created in the SQL Azure cloud database by using SQL Server Management Studio in a way that is pretty similar to creating a database on a server running in an on-premises environment. One difference, for example, is that the USE statement is not supported when using SQL Azure since we have used New Query to connect to the specific database already. If you need to access a different database, you can start a new query by clicking the New Query button.
Note: Since SQL Azure does not support object browsing from SQL Server Management Studio. Convert UDDTs to Base Type: This option needs to be set to true since SQL Azure does not support userdefined types. They need to be converted into underlying SQL Azure portable types.
TIPS:
Script extended properties: This option needs to be set to false since SQL Azure does not support extended properties.
Script USE DATABASE: This option needs to be set to false since SQL Azure does not support the USE statement.
Script Data: This option needs to be set to false since we do not care about the data at this moment. If you run the generated script without any modification, then you will get an error message. Two radio buttons can be found to the left of the button Test Connection, which allows users to quickly switch the connection back and forth between the SQL Azure cloud service and on-premises SQL workstation environments. There are two ways to execute a script. One way is to click the Execute button, and the other way is to select and highlight the text and press F5. If there is a script selected, the caption of the Execute button turns to Execute Selected, and the background color of the button changes,The caption of the Execute button turns to Execute Selected, and the background color turns to gold. Azure table storage supports the Language Integrated Query (LINQ) extensions for .NET 3.0 or later versions, ADO.NET Data Services, and representational state transfer (REST), which allows applications developed using non-.NET languages to access table storage via the Internet. There is no limit on the number of tables and entities or on the table size. There is no need for table storage developers to handle the data access transactions, optimistic concurrency for updates, and deletes. Also, there is no need for developers to worry about performance, since cloud storage is highly scalable. Especially for long queries or queries that encounter a time-out, partial results will be returned and the unfinished queries can be continued with a return continuation token.
Challenges Facing Enterprise IT:
Provisioning, deploying and managing servers at scale
Enabling faster, more efficient development of applications with existing knowledge and toolsets
Reducing IT hardware and infrastructure costs
SQL Azure Database: (The first and only true relational database as a service)
Self-Managed:
Easy provisioning and deployment
Auto high-availability and fault tolerance
Self-maintaining infrastructure; self-healing
No need for server or VM administration
Elastic Scale:
Database utility; pay as you grow
Flexible load balancing
Business-ready SLAs
Enable multi-tenant solutions
World-wide presence
Developer Agility:
Build cloud-based database solutions on consistent relational model
Leverage existing skills through existing ecosystem of developer and management tools
Explore new data application patterns
Challenges Today:
Promotions, events, ticket selling businesses are “bursts – bound” by nature
Capacity constraints limit business agility
High costs of entry into new business
Difficult to roll out extra capacity quickly
Idle capacity “off-bursts” is cost prohibitive
Solution (SQL AZURE):
Elastic scale – database as a service
Pay as you grow and shrink
Easy to provision and manage database
No hardware, no manual database administration required
Best Integration – Office 2010 & Other Tools.
Focus on combining the best features of SQL Server running at scale with low friction.
Highly scaled out relational database as a service
Relational database service
SQL Server technology foundation
Highly symmetrical
Highly scaled
Database “as a Service” – beyond hosting
Customer Value Props
Self-provisioning and capacity on demand
Symmetry on-premises database platform
Automatic high-availability and fault-tolerance
Automated DB maintenance (infrastructure)
Simple, flexible pricing – “pay as you grow”
Application Topologies:
SQL Azure access from within MS Datacenter (Azure compute) -- Also Know as "Code Near"
SQL Azure Access from outside MS Datacenter (On-premises) -- Also Know as "Code Far"
SQL Azure Access from within and outside MS Datacenter (On-premises & Azure Compute) -- Also Know as "Hybrid"
Partitioning, when do I need it?: (Concept of X (Storage), Y (Transactions) and Z axis from Low to high)
Single Database, No Partitioning
Partitioned Data. Partitioning Based on Application Requirements (Storage)
Partitioned Data, Partitioning based on Application Requirements (IOPS)
Partitioned Data, Partitioning based on Application Requirements (IOPS, Storage or both)
Developing on a local SQL Express instance has some advantages
Easy to get started, you already know how to do it!
Full fidelity with the designer and debugging tools
Reduces latency when using local Azure development tools
Reduces bandwidth and databases costs for development
Some caveats
Remember to alter your VS build settings to switch the connection string when deploying
Use tools (like SQLAzureMW) to keep you within the supported SQL Azure features
Always test in SQL Azure before deploying to production
Connecting to SQL Azure:
SQL Azure connection strings follow normal SQL syntax
Applications connect directly to a database
“Initial Catalog =
No support for context switching (no USE
Some commands must be in their own batch
Create/Alter/Drop Database & Create/Alter/Drop Login, & Create/Alter USER with FOR/FROM LOGIN
Encryption security
Set Encrypt = True, only SSL connections are supported
TrustServerCertificate = False, avoid Man-In-The-Middle-Attack!
Format of username for authentication:
ADO.Net:
Data Source=server.database.windows.net;
User ID=user@server;Password=password;
Setup your firewall rules first!
Resilient Connection Management:
Connections can drop for variety of reasons
Idleness (greater than 30 minutes)
Throttling
Long running transactions > 5 minutes
Resource Management
Database failover
Hardware failure
Load Balancing
Upgrade
What to do on connection failure?
Wait, then retry if it is a transient failure
Change your workload if throttled, i.e. break up your transaction
SELECT * INTO temp tables: (DOES NOT SUPPORT)
SELECT *INTO #Destination FROM Source WHERE [Color] LIKE 'Red‘
To work around this you need to create your destination table then call INSERT INTO. Here is an example:
CREATE TABLE #Destination (Id int NOT NULL, [Name] nvarchar(max), [Color] nvarchar(10))
INSERT INTO #Destination(Id, [Name], [Color])
SELECT Id, [Name], [Color] FROM Source WHERE [Color] LIKE 'Red';
SQL Azure Firewall:
What is it?
A way to restrict access to your database
How does it work?
UI
Programmatic access
Common scenarios
I need my development machine to be able to access it
I need access from my laptop when I am not in the office
I want to restrict access to only my application running in Windows Azure
Data Access APIs:
Supported APIs:
ADO.Net .Net 3.5 SP1 and 4.0
ODBC - SNAC 10
Entity Framework .Net 3.5 SP1 and 4.0
SQL Server 2008 Driver for PHP v1.1
Unsupported:
OleDB
Connection String:
ADO.Net
Encrypt=True and add @servername to User ID
ODBC
Encrypt=yes and add @servername to Uid
Protocols:
Supported:
TCP/IP over port 1433
UnSupported:
Dynamic ports
Named pipes
Shared memory
Authentication Mode:
Supported:
SQL Auth
UnSupported:
Windows Auth
Feature Parity:
Administration Surface:
Physical Server Properties does not apply in SQL Azure
You have a master database but no access to server level constructs such as
sp_configure, endpoints, DBCC commands, server level DMVs and System Views.
Programmability Surface:
Certain Features are partially available today:
USE, XML processing, deprecated T-SQL etc.
List is available here; http://msdn.microsoft.com/en-us/library/ee336267.aspx
Some features are not available today:
Full-text Search, Remote Data Access and Linked Servers, Distributed Transactions,
Change Tracking, Service Broker etc.
List is available here; http://msdn.microsoft.com/en-us/library/ee336253.asp
Migration Assistant for MySql and Access:
Scenario
Auto porting of schema, database code and data from MySql and Access to SQL Azure
SQL Server Migration Assistant for MySql and Access
Supports MySQL 4.1 and up
Support Access v 97 and up
SQL Server versions supported (all editions)
SQL Azure, SQL Server 2005, SQL Server 2008 and 2008 R2
Transferring Large Data:
Best Practices for efficient Data movement to SQL Azure
Use the right tools is key
BCP
SSIS
Network performance considerations
Latency vs Bandwidth
Use the right tools (BCP & Bulk Copy APIs):
Scenario
High speed programmable data import and export
Best Practices
Optimize Databases for Data Import
Disable/Delay creation of none Clustered Indexes
Order Data on the Clustered Index
Disable Triggers and Constraint checking
-N Native mode so no data type conversion needed.
-c Character mode for interoperability
-b batchsize will let you control transaction size
-h”TABLOCK, ORDER(…)” optimize writes
Use the right tools (SSIS – Best of Breed Data Transformation Utility):
SSIS Design Surface - Data Flow Task
Diverse Source and destinations
To/From Flat Files, ADO.Net, OleDB
Fully programmable flow
Loops, Sorts, Conditional operators, XML/WebServices Processing etc
VS Debugging support with data viewers, watches and conditional breakpoints
Best Practices – Data Flow Task
Remember; Optimize Databases for Data Import
Disable/Delay creation of none Clustered Indexes
Order Data on the Clustered Index
Disable Triggers and Constraint checking
Batch Size: Adjust the transaction size
Buffer and Blob temp storage area; spread over to multiple drives for large data transfers
Parallelization – based on execution trees, task will auto parallelize
Use the right tools (Import and Export Wizard):
Scenario
Simplified wizard for migrating schema and data through SSIS
Pros
Great performance out of the box
Allows ‘save as package’ for full control
To know more details please visit and watch the videos: http://www.microsoft.com/en-us/sqlazure/videos/default.aspx
