sql azure n.
Download
Skip this Video
Download Presentation
SQL Azure

Loading in 2 Seconds...

play fullscreen
1 / 32

SQL Azure - PowerPoint PPT Presentation


  • 79 Views
  • Uploaded on

SQL Azure. Best Practices. Tony Petrossian Microsoft Corporation Tony.Petrossian@microsoft.com. Agenda. Service Recap “Server” Management Connectivity and Tracing Using multiple databases Migrating data and schema in and out of SQL Azure. SQL Azure Recap.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Azure' - rhoda-odonnell


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql azure
SQL Azure

Best Practices

Tony Petrossian

Microsoft Corporation

Tony.Petrossian@microsoft.com

agenda
Agenda
  • Service Recap
  • “Server” Management
  • Connectivity and Tracing
  • Using multiple databases
  • Migrating data and schema in and out of SQL Azure
sql azure recap
SQL Azure Recap
  • Relational database service
    • Built on SQL Server technology foundation
    • Highly scaled
    • Highly secure
    • Database “as a Service” – beyond hosting
  • Customer Value Props
    • Self-provisioning and capacity on demand
    • Symmetry w/ on-premises database platform
    • Automatic high-availability and fault-tolerance
    • Automated DB maintenance (infrastructure)
    • Simple, flexible pricing – “pay as you grow”
sql azure connection
SQL Azure Connection

TDS Gateway

1

8

Front-end Node

Front-end Node

Applications connect directly to a database

  • “Initial Catalog = <db>” in connection string
  • No support for context switching (no USE <db>)

SQL Azure connection strings follow SQL syntax

  • Except for an unusual username format

Format of username for authentication:

  • ADO.Net:Data Source=server.database.windows.net;User ID=user@server;Password=password;...
  • ODBC:Driver={SQL Server Native Client 10.0}; Server=server.database.windows.net; Uid=user@server;Pwd=password;...

Protocol Parser

Protocol Parser

7

2

TDS

Session 1

Gateway Logic

Gateway Logic

3

9

Master Cluster

Master Node

Master Node

5

4

6

Partition Manager

Partition Manager

Data Node Components

Data Node Components

Machine 4

Machine 5

Machine 6

Machine 7

SQL Instance

SQL Instance

SQL Instance

SQL Instance

SQL DB

SQL DB

SQL DB

SQL DB

Master1

DB2

DB3

DB4

DB5

DB2

DB3

Master1

Meta 1

Master1

DB2

DB3

DB4

DB5

DB1

DB7

Scalability and Availability: Fabric, Failover, Replication, and Load balancing

Scalability and Availability: Fabric, Failover, Replication, and Load balancing

server management
Server Management
  • Where should I create my server?
    • Co-locate server with Windows Azure app to reduce latency
  • When should I create a new server?
    • Trade off between geo/admin/billing
  • Managing Server
    • Through the Portal
      • Add/Drop server
      • Establish admin credentials
      • View usage reports
      • Network access configuration
    • Through the Master Database
      • Fine-tune firewall settings through code
      • User logins
      • Usage and metrics reporting (billing)
      • Create/Drop databases
    • Admin roles permissions
      • CREATE/DROP database
      • CREATE/DROP/ALTER login
      • GRANT/REVOKE rights
      • Modifying server firewall settings

HTTP

Portal

User DB

Master DB

TDS

User DB

User DB

SQL Azure Server

server network access control

Each server defines a set of firewall rules

Determines access policy based on client IP

By default, there is NO ACCESS to server

Controlled using

TSQL API against Master DB: sys.firewall_rules, sys.sp_set_firewall_rule, sys.sp_delete_firewall_rule

Portal UX

Server: Network Access Control
server billing and reporting

sys.bandwidth_usage: usage in KB

sys.database_usage: instance count by type per day

Server: Billing and Reporting
application design topics
Application Design Topics
  • Most-applicable SQL Best Practices
    • Connection Pooling
    • Query Parameterization
    • Batching
  • Scaling with data and load
    • Sharding
    • Building copies
  • Deploying and uploading data
    • Bulk copy, BCP.EXE
    • SSIS
    • Microsoft Sync Framework Power Pack for SQL Azure
use pooled connections
Use Pooled Connections
  • Increases efficiency by removing re-login
  • // When pooling, use connection and return immediately
  • // Do not hold for a long time – pool ensures fast turnaround
  • // on second use
  • using (SqlConnection conn = new SqlConnection(…))
  • {
  • conn.Open();
  • using (SqlCommandcmd = conn.CreateCommand())
  • {
  • cmd.CommandText = …;
  • }
  • }
  • using (SqlConnection conn = new SqlConnection(…))
  • {
  • conn.Open(); …
connections retry on failure

Connections can drop for variety of reasons

Idleness for more than 30-minutes

Transient (network) errors

Intentional throttling

First step: reconnect immediately

Handles idleness- and transient-disconnects

The SQL Azure Gateway handles connection retry for app

Connections attempted for ~30s before failure

What to do on connection failure?

Wait (10 seconds), then retry

Check the error messages and follow recommendation

Server health can be checked via Portal

TSQL APIs will come in later releases

Connections: Retry on failure
connection pattern
Connection Pattern

Keep it inside

the main loop

  • while(true)
  • {
  • using(SqlConnection connection = newSqlConnection(connStr))
  • {
  • try
  • {
  • connection.Open();
  • using (SqlCommandcmd = connection.CreateCommand())
  • {
  • cmd.CommandText = @"SetBCPJobStartTime";
  • cmd.CommandType = CommandType.StoredProcedure;
  • cmd.Parameters.Add(newSqlParameter(@"@BCPJobId", BCPJobId));
  • cmd.ExecuteNonQuery();
  • }
  • }
  • catch (SQLExceptionSqexc)
  • {
  • // deal with error
  • }
  • catch (Exceptionexc)
  • {
  • // deal with error
  • }
  • }
  • // more stuff
  • // …..
  • }
tracing connectivity problems

Each session assigned a unique ‘sessionId’

Tracks session state and service errors

Retrievable from CONTEXT_INFO()

Save this with each new connection

If you need support, support personnel will greatly appreciate that value

Tracing Connectivity Problems
tracing helper pattern
Tracing Helper Pattern
  • Guid? sessionId = null;
  • using (SqlConnection conn = new SqlConnection(…))
  • {
  • // Grab sessionId from new connection
  • using (SqlCommandcmd = conn.CreateCommand())
  • {
  • conn.Open();
  • cmd.CommandText =
  • "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())";
  • sessionId = new Guid(cmd.ExecuteScalar().ToString());
  • }
  • // continue processing
  • ...
  • }
maximize your performance

Batching: push logic to Server

Use stored procedures and batching

Limit number of round trips to server

Example: batch 10 statements vs. 10 round-trips

Parameterized queries

Parameterize queries (limits compiles)

Declare all parameters, type and length

Lack of parameter size leads to cache bloat

Maximize your Performance
declare parameter lengths
Declare Parameter Lengths!

// Length inferred: leads to cache bloat 

cmd.CommandText = "SELECT c1 FROM dbo.tbl WHERE c2 = @1";

cmd.Parameters.Add("@1", SqlDbType.NVarChar).Value = "1";

cmd.Parameters.Add("@1", SqlDbType.NVarChar).Value = "22";

(@1 nvarchar(1)) SELECT c1 FROM dbo.tbl WHERE c2 = @1(@1 nvarchar(2)) SELECT c1 FROM dbo.tbl WHERE c2 = @1

// Length supplied: no cache bloat 

cmd.CommandText = "SELECT c1 FROM dbo.tbl WHERE c2 = @1";

cmd.Parameters.Add("@1", SqlDbType.NVarChar, 128).Value = "1";

(@1 nvarchar(128)) SELECT c1 FROM dbo.tbl WHERE c2 = @1

maximize elasticity benefits

SQL Azure balances databases across machines

Divide your data into smaller chunks

Makes for better load-balancing

Ensures we can place your data on the most-appropriate servers

Using shorter transactions

Ensures we can respond to issues faster

Avoid impacting others sharing the same box

Thus avoiding being throttled by SQL Azure

Maximize elasticity benefits
multi database case study
Multi-Database Case Study

Kelly Blue Book http://www.kbb.com/

  • Provider of vehicle valuation data
  • > 13 million visitors to their site per month
  • 2.5 GB Database Size
  • Data refreshed every week
  • Replicate data across 6 databases for increased perf
  • Increase/Decrease database count based on demand
copies managing lots of read access
Copies: Managing Lots of Read Access

App

LB

Server

Node 76

Node 2

Node 14

Node 19

Node 21

Node 33

Node 99

Master DB

DBC1

DBC2

DBC3

DBC4

DBC5

DBC6

SQL Azure Cluster

sharding example ticketdirect architecture
Sharding Example: TicketDirect Architecture

Client Applications

Azure Roles

.Net Service Bus

Castellan.Azure

  • Box Office sales
  • Ticket Printing
  • System Administration
  • Venue/Event Management
  • Partitioning

WCF

Distributed Cache Worker

MemCache

http:// TicketDirect .*

Dynamic Worker

(tasks uploaded as blobs)

PartitionerWorker

On PremiseSQL Server

-- - --- - -

Azure Storage

-- - --- - -

Tables to record server & partition information

Blobs to store web and worker role resources

Queues for communication between clients and roles

Castellan

Venue

Castellan.old (VB6)

SQL Azure

Castellan Venue DB

Castellan Venue DB

Castellan Venue DB

Castellan Venue DB’s

Castellan Venue DB’s

Castellan Venue DB’s

Castellan

Venue 1

Partition(s)

Venue 2 Partition(s)

Venue N Partition(s)

...

One application DB, many venue DB’s – each partitioned in to many parts (40+)

shards managing lots of transactions
Shards: Managing Lots of Transactions

App

Server

Node 76

Node 2

Node 14

Node 19

Node 21

Node 33

Node 55

Node 99

Master DB

DB1

DB2

DB3

DB3

DB4

DB5

DB6

SQL Azure Cluster

managing the schema
Managing the Schema
  • Moving between SQL Server & SQL Azure
    • Use SSMS script generation tool
    • SQL Azure Migration Wizard
      • http://sqlazuremw.codeplex.com
    • Use Data-Tier Application
      • VS2010, SQL Server 2008 R2, SQL Azure SU2 (April 2010)
    • Build your own scripts
      • Watch out for unsupported T-SQL
      • Don’t depend on instance or server level objects
getting data in and out

SQL Azure supports standard SQL data import and export patterns

Use bulk loading patterns where possible

BCP – console .EXE bulk load/export tool

SSIS – SQL integration server

Bulk APIs in ODBC and ADO.Net

SQL Azure supports data synchronization

With on-premises DBs and client stores

Always good advice:

Break batches up into smaller, consumable chunks

Add retry and tracing logic to ensure robust resume in face of failures

Getting Data In and Out
data import ado net bulk copy api
Data Import: ADO.Net Bulk Copy API

// Bulk data import

using (SqlBulkCopy bulk = newSqlBulkCopy(newSqlConnection(conn))

{

DestinationTableName = "dbo.data",

BatchSize = 2000, // Transaction size (length)

BulkCopyTimeout = 10000, // Transaction timeout

NotifyAfter = 1000, // Progress callback

})

{

bulk.SqlRowsCopied += newSqlRowsCopiedEventHandler(

myProgressCallback);

bulk.WriteToServer(sourceDataReader);

}

data export import bcp exe
Data Export/Import: BCP.EXE

// BCP example

SET SRV=somesrv.database.windows.net

SET LOGIN=mylohin@somesrv

SET PW=something

SET S_DR=C:\flats

SET DB=TPCH

bcp %DB%.dbo.supplier in %S_DR%\supplier.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

bcp %DB%.dbo.nationin %S_DR%\nation.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

bcp %DB%.dbo.regionin %S_DR%\region.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

bcp %DB%.dbo.customerin %S_DR%\customer.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

bcp %DB%.dbo.partin %S_DR%\part.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|“

bcp %DB%.dbo.supplier out %S_DR%\supplier.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

bcp %DB%.dbo.nationout %S_DR%\nation.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

bcp %DB%.dbo.region out %S_DR%\region.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

bcp %DB%.dbo.customer out %S_DR%\customer.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

bcp %DB%.dbo.part out %S_DR%\part.tbl -c -U %LOGIN% -P %PW% -S %SRV% -t "|"

summary

Many SQL Server patterns apply to SQL Azure

Use SQL best practices wherever possible

Patterns discussed:

Connectivity (to database, not server)

Tracing and support

Batching, Pooling and Parameterization

Getting data in and out

Summary
additional resources
Additional Resources
  • Azure Prices and Packages:
    • http://www.microsoft.com/WindowsAzure/offers/
  • SQL Azure Information:
    • http://www.microsoft.com/windowsazure/sqlazure/

MSDN Resources

    • http://www.microsoft.com/windowsazure/sqlazure/
thank you for your attention
Thank you for your Attention!
  • For more Information please contact
  • Tony Petrossian
    • Principal Program Manager
    • SQL Azure Development
    • Microsoft Corporation
    • tonypet@microsoft.com