windows azure sql database & your data

@brisebois www.alexandrebrisebois.com

Windows Azure SQL Database& Your Data

Alexandre BriseboisMVP Windows Azure

Boom!? What Went Wrong?

http://bit.ly/1gG3SLT

Windows Azure SQL Database VS

SQL Server on Windows Azure Virtual Machines

http://bit.ly/1cReNGH

Using SQL Server in Windows Azure Virtual Machines

• It’s a full SQL server• It’s resources aren’t

shared• You must maintain and

patch the server• No High Availability• You will probably need

an I.T professional to help you manage the Virtual Machine

SQL Server Enterprise Edition on a Medium VM (2 CPU, 3.5GB RAM) Costs $20,364/ Year

Using SQL Database• It’s highly available• It’s data is redundant (3

Copies)• It’s response times are

unpredictable• It doesn’t support a high

volume of concurrent queries

• It can be federated (scaled out)

• It’s constantly updated by Microsoft

• It’s maintained by Microsofthttp://bit.ly/1cReNGH

SQL Database 150GBCost $2,709.36 / Year

Each SQL Database computer is equipped with 32 GB RAM, 8 CPU cores and 12 hard drives.

To ensure flexibility, each SQL Database computer can host multiple subscribers at a time. (Very High Density)

http://bit.ly/NoF0jZ

Windows Azure SQL Database Cannot be Set to Simple Recovery Model

SQL Databases are set to

FULL Recovery Model &

READ_COMMITTED_SNAPSHOT

http://bit.ly/1eM05MB

Steps Towards Optimized Windows Azure SQL Databases

http://bit.ly/1iWxmb3

http://bit.ly/1hsz7eU

Take Control! Use the Microsoft SQL Server Data Tools

http://bit.ly/1iwpdvw

Building Clustered Indexes on Non-Primary Key Columns in Windows Azure SQL Database

http://bit.ly/1nyFgOd

Use IDs for lookup data and use GUIDs for data that is susceptible to synchronization and to public use.

In a blog post “Uniqueidentifier and Clustered Indexes” the author points out that the easiest and best approach to creating a Clustered Index is to use a datetime2 column.

datetime2 has a resolution of 1/1000000 of a second

http://bit.ly/Nopd4t

Creating NONCLUSTERED INDEXES

• If a CLUSTERED INDEX is present on the table, then NONCLUSTERED INDEXES will use its key instead of the table ROW ID.

• To reduce the size consumed by the NONCLUSTERED INDEXES it’s imperative that the CLUSTERED INDEX KEY is kept as narrow as possible.

• SQL Database can JOIN and INTERSECT INDEXES in order to satisfy a query without having to read data directly from the table.

• Favor many narrow NONCLUSTERED INDEXES

• Create Filtered INDEXES to create highly selectivity

• Use Covering INDEXEs to reduce the number of bookmark lookups required to gather data that is not present in the other INDEXES.

• Covering INDEXES can be used to physically maintain the data in the same order as is required by the queries’ result sets reducing the need for SORT operations.

• Covering INDEXES have an increased maintenance cost, therefore you must see if performance gain justifies the extra maintenance cost.

• NONCLUSTERED INDEXES can reduce blocking by having SQL Database read from NONCLUSTERED INDEX data pages instead of the actual tables.

http://bit.ly/1idgltT

Identify Missing Indexes in Your Windows Azure SQL Database

http://bit.ly/1qMrE0N

Don’t Forget About Index Maintenance on Windows Azure SQL Database

• On SQL Server, when we identify 5% to 30% of average fragmentation we should issue a REORGANIZE statement

• On SQL Database we can only use the REBUILD WITH (ONLINE = ON) statement

http://bit.ly/1qMrE0N

Entity Framework and TransactionScope transactions are limited to 10 minutes

http://bit.ly/1fTckv2

Identifying the Top 20 Slowest Queries Being Executed Against a Windows Azure SQL Database

http://bit.ly/1kD3eDp

Does Your Data Really Belong in Your SQL Database?

http://bit.ly/1fTdHdg

Put Your SQL Database on a Diet!

Using SQL Database & Windows Azure Storage

• It’s highly availability• It’s data is redundant (3

copies)• It’s constantly updated by

Microsoft• It’s maintained by Microsoft• Can use services like a CDN• The load is dispersed over

many services

SQL Database 10GB + 100GB Blob+ 100GB Table = Cost $803.52 /Year

Scaling Windows Azure SQL Database

http://bit.ly/1d7hhkP

Using Sharding in Windows Azure SQL Database

• It’s highly availability• It’s data is redundant (3

copies)• It can handle + concurrent

queries that a 1 database• It’s quite complex to backup• It’s response time is

unpredictable• It’s constantly updated by

Microsoft• It’s maintained by Microsofthttp://bit.ly/1cReNGH

SQL Database 5 * 10GBCost $2,757.24 /Year

Using Premium for SQL Database

• It has dedicated resources• It’s response time is

predictable• It’s highly available• It’s data is redundant (3

Copies)• It’s maintained by Microsoft• It can be sharded (scaled

out)• It’s constantly updated by

Microsoft http://bit.ly/1cReNGH

SQL Database Premium P2 150GBCost $11,331 /Year (preview pricing)

Disaster Recovery

http://bit.ly/1nyrLOi

Disaster Recovery

http://bit.ly/PJm6GH

What Does it Cost to Backup a Windows Azure SQL Database?

Disaster Recovery

http://bit.ly/1id79FY

TIPS & Tricks

Create Database Diagrams of Your Windows Azure SQL Databases

http://bit.ly/1dhxu19

Friends Don’t Let Friends use Lazy Loading on Windows Azure!

http://bit.ly/1iW2XxL

Creating a Staging Database Instance From Production on Windows Azure SQL Database

http://bit.ly/1gzhfB5

• Replace NewDatabaseName by the name of the new Database

• Replace ServerName by the database server name of the source Database

• Replace SourceDatabase by the name of the source Database

Using Common Table Expressions (CTE) to Remove Duplicates

http://bit.ly/1lIDJAr

40552 : The session has been terminated because of excessive transaction log space usage.

http://bit.ly/1hsOmEx

40552 : The session has been terminated because of excessive transaction log space usage.

http://bit.ly/1hsOmEx

• 40501 – Retry to connect to SQL Database in intervals of 10 seconds until the resources are available and your connection is established again.

• 40550 – The session has been terminated because it has acquired too many locks. Try reading or modifying fewer rows in a single transaction.

• 40549 – Session is terminated because you have a long running transaction. Try shortening your transaction.

• 40551 – The session has been terminated because of excessive TEMPDB usage. Try modifying your query to reduce temporary table space usage.

• 40552 – The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

• 40553 – The session has been terminated because of excessive memory usage. Try modifying your query to process fewer rows.

• 40545- The service is experiencing a problem that is currently under investigation.• 40544 – The database has reached its size quota. Partition or delete data, drop indexes,

or consult the documentation for possible resolutions.• 40174 – The partition is in transition and transactions are being terminated.

ADO.Net DataTable why did I ever stop using you?• It has been around since the early days of .Net and has

been tested thoroughly • Can be used to repopulate the database • Has change tracking through the DataRow RowState

property. • Is a 2 dimensional array with individually typed

columns • It can be consumed using Linq

http://bit.ly/1iwF8Ko

Reaching Performance Targets on Windows Azure by Preparing and Pre-Calculating Data

http://bit.ly/1cRWP6U

Use SqlBulkCopy and Views to Gain Speed!

http://bit.ly/1gzilg5

Entity Framework 6 SQL Database

http://bit.ly/1cRWP6U

Windows Azure SQL Database& Your Data

Alexandre BriseboisMVP Windows Azure

windows azure sql database & your data

brisebois http

microsoft http

services http

lynopd4t sql database

windows azure sql database

database server

virtual machine http

source database

Technology

sql azure federations - wou homepage - western … · web...

introducing azure sql database

sql azure database: present and future

sql server konferenz 2017 - register...

sql azure database whitepaper r01

windows azure sql database overview

windows azure sql database federations

azure sql managed instance...azure sql the family of sql...

eduardo piairo - deployment pipeline for databases (azure...

azure paas (webapp & sql database) workshop solution

scalable relational database with sql azure

building cloud apps using azure sql database

tablas temporales en sql server 2016 y azure sql database...

sql server and azure sql database gdpr guidance... · sql...

introduction to windows azure and windows azure sql database

performance tuning azure sql database

windows azure sql database tutorials

azure sql database managed instance - microsoft...

sql server and azure sql database gdpr guidance... ·...

monitoring microsoft azure sql server vms and sql database