top 7 things you want to do in windows azure sql database, but can't

11
Top 7 Things You Want To Do In Windows Azure SQL Database, But Can’t Thomas LaRock Technical Evangelist and Senior DBA Confio Software Wednesday, May 29, 2013 1 http://www.confio.com

Upload: thomas-larock

Post on 14-Jul-2015

6.928 views

Category:

Technology


0 download

TRANSCRIPT

Page 1: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

Top 7 Things You Want To Do In Windows Azure SQL Database, But Can’t

Thomas LaRock

Technical Evangelist and Senior DBA

Confio Software

Wednesday, May 29, 2013 1http://www.confio.com

Page 2: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

Introduction

Windows Azure SQL Database (WASD) should be seen as a different version of SQL Server.

Not as something that is “less” than the on-premiseversion, just something that is different and meant to help solve a different set of problems.

The following is my list of the top 7 features that WASD does not currently support.

Wednesday, May 29, 2013 2http://www.confio.com

Page 3: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

Database File Placement

Placement of your data and database files has long been one of the standard tasks that a DBA takes care of when creating databases.

That changes with Windows Azure SQL Database, as you no longer have the option for data file placement, or for filegroup management either.

If you cannot control the placement of data on files, or filegroup management, then you can say goodbye to partition functions and schemes as well.

Read more at: http://msdn.microsoft.com/en-us/library/windowsazure/jj156171.aspx

Wednesday, May 29, 2013 3http://www.confio.com

Page 4: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

Database Mirroring

Introduced with SQL 2005 SP1, database mirroring has been a popular feature to help user meet high availability requirements.

With WASD, your data is replicated automatically to three different copies. There is no longer a need for you to manually configure your own solution to achieve high availability.

Wednesday, May 29, 2013 4http://www.confio.com

Page 5: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

Trace Flags

Trace flags are a way for a DBA to force the SQL Server engine to perform non-default actions and behavior. They have become an essential tool especially for debugging things like deadlocks.

With WASD the ability to configure a trace flag is not an option. In fact, you aren’t allowed to change any of the configuration options for your WASD instance.

The lack of configuration options and trace flags always reminds me what I tell my kids when I feed them dinner:

“You get what you get and you don’t get upset.”

Wednesday, May 29, 2013 5http://www.confio.com

Page 6: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

BACKUP and RESTORE

WASD does not support either the BACKUP DATABASE or RESTORE DATABASE commands.

If you want to get a transactionallyconsistent backup of your database you must create a new database using the

CREATE DATABASE [dbname]

AS COPY OF [source_dbname]

syntax. You will want to consider this when building your disaster recovery plans, as the traditional concept of “running backups” changes in WASD.

Read more at: http://msdn.microsoft.com/en-us/library/windowsazure/jj156170.aspx

Wednesday, May 29, 2013 6http://www.confio.com

Page 7: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

CLR Objects

Common Language Runtime (CLR) objects are not supported by WASD.

If you have spent the last few years building and deploying your own CLR objects in order to help meet business requirements then you are going to find this lack of support to be a showstopper for migration to WASD.

Read more about CLR at: http://msdn.microsoft.com/en-us/library/ddk909ch(v=vs.100).aspx

Wednesday, May 29, 2013 7http://www.confio.com

Page 8: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

Distributed Queries

Most of us have grown accustomed to the use of linked servers in order to link disparate database servers together. Linked servers allows for a user to write simple queries against many data sources.

With WASD the use of linked servers and therefore distributed queries is no longer an option. You’ll need to rethink how you architect your queries and your data sources.

Wednesday, May 29, 2013 8http://www.confio.com

Page 9: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

Service Broker

For the dozens of faithful service broker users out there this is also a showstopper.

Imagine having spent the last six (or more) years building out solutions that rely on the use of Service Broker. How likely are you to scrap all that work in order to migrate to WASD?

Not very.

I would imagine that many shops that have invested in building solutions on technologies that are not supported in WASD are going to be quite slow to adapt.

Wednesday, May 29, 2013 9http://www.confio.com

Page 10: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

Summary

WASD does not support each and every option found in an on-premise version of SQL Server.

But here’s the catch: Fewer options will mean less overhead.

Everything is a trade off. Sometimes not having an option makes your decisions easier.

Wednesday, May 29, 2013 10http://www.confio.com

Page 11: Top 7 Things You Want To Do In Windows Azure SQL Database, But Can't

For More Information

• For a list of limitations in WASD go to: http://msdn.microsoft.com/en-us/library/windowsazure/ff394115.aspx

• For help with database performance go to http://www.confio.com

• For more information on WASD you can check my blog at http://thomaslarock.com/category/sql-azure/

Wednesday, May 29, 2013 http://www.confio.com 11