SQLAzure- implementing connectivity features, performance tuning and security gotchas


As a DBA we can relate ”SQLAzure” as Database As A Service, nothing but offering database hosting and in Microsoft terms its relational database in the cloud!

If you know about SQL Data Services (SQL2008 days) then SQLAzure is the one which was built on architecture with full scalability, fault tolerance, and high availability features, has been enhanced to offer the relational database services on top of existing SDS architecture, named SQL Azure. Microsoft data centers provide these services with load balancing, failover, and replication capabilities.

To talk about Architectecture side SQLAzure comprise three layers: infrastructure, platform, and services that run inside the Microsoft data center and the client layer from the user side. At any point in time, SQL Azure maintains three replicas of user application databases. In case one replica fails, the infrastructure and platform create a new database to maintain the three replicas, making them available at any point in time.

As the service is provided NEED basis, not all the features are associated with SQL Azure such as Service Broker or CLR. That’s most of it about the introduction on SQLAzure and now we need to know on the connectivity features which has two-pattern method, they are code-near and code-far.

 

  • The code-near connectivity needs data access application deployed on-premises and the same data center as the SQL Azure database. The applications web interface connectivity is exposed through Windows Azure web role that is hosted from an ASP.NET application.
  • The code-far connectivity needs the application deployed on-premises and in different data centers than SQL Azure. The application executes the SQL queries using the TDS protocol to SQL Azure database.

 

On top of that you have to ensure that performance issues are addressed and in order to do that the queries that are executed from application need to optimized enough. So the best part of optimization is to ensure the simple (known) terms stated below :

  • For frequently executed queries, use stored procedures and batching within the code. 
  • Always limit the number of round-trips to the server and the number of columns to be returned.
  • The default connection timeout is 30 seconds and before reporting a connection failure,it retries connections. 
  • For error handling always use the TRY…CATCH block statements to catch transactions on database operations. 
  • If there is a database there needs to be ETL as well. In order to achieve efficient data transfer methods we can take help of Bulk copy utility (bcp.exe) or the SSIS package. 
  • Further on the best results part on data synchronization use SQLAzure DataSync Application
  • By default SQLAzure rolls back the transaction automatically to free up the resources held by that transaction.
  • If, for any reason, a huge volume of data is transmitted, it is ideal to build the re-try logic in the application. See this SQLAzure Re-Try Logic blog for more information.
  • As you may be aware that  the connectivity is GEO location and dependent on WAN, you need to keep follow the current status of database using Service Dashboard.
  • If necessary you can also use the SQL Azure web page to report any live-site issue if the connectivity errors are persistent.

 

For the assessment side and how your data platform can be ready for SQL Azure in line with SQL Server 2012 you can visit SQLAZure Compatibility Assessment page. This assessment page will helps you to evaluate the current database and provides whether you can move that DB services to SQL Azure.

In addition to this SQLAzure team released a new lab in relation to the above subject, as per the source: (SQL Server blog)

A new lab went live: Microsoft Codename “SQL Azure Compatibility Assessment”. This lab is an experimental cloud service targeted at database developers and admins who are considering migrating existing SQL Server databases into SQL Azure databases and want to know how easy or hard this process is going to be.

There are two steps involved in this lab:

  1. You first need to generate a .dacpac file from the database you’d like to check on with SQL Server Data Tools (SSDT) CTP4. SQL Server 2005, 2008, 2008 R2, 2012 (CTP or RC0) are supported.       
  2. Next, you upload your .dacpac to the lab cloud service, which returns an assessment report, listing the schema objects that need to change before you can move that database to SQL Azure.

You find more information on the lab page for this project and in the online documentation. A step-by-step video tutorial will walk you through the process. Of course, we would love to hear feedback from you!

Finally you need to ensure to following golden-rule within database design & build principles. To be able to provide database application services using SQL Azure make sure ou build the on-premise database using SQL Express edition, then generate a script that will be executed on the cloud DB side (SQL Azure) either using SQL Server Management Studio or SQLCMD. More details about how network topology and Client API network works refer to this SQLAzure Technet WIKI article.

In addition to this you can take community provided tools (from CodePlex) and in specific to SQL Azure see SQL Database Migration wizard that is specifically targeted to help you to migrate 2000/2005/2008/2008R2/2012 databases.