The Challenge

As developers, we are up against odds that push us to make trade-offs in order to go into production on time. More often than not, it’s a race where security becomes an afterthought.

Securing Azure SQL Databases

Security mechanisms come in many flavors. It is a requirement that needs to be defined and implemented on day 1. These rituals (policies and practices), must become natural in your application life cycle management. Consider these as a starting point from which you can develop your own security practices.

• Do not use the default user for development, testing or for deployments
  • Create a user specifically for deployments (can perform schema alterations)
  • Create a user on a per application basis (cannot alter schema and has limited write access)
  • Create a user for support investigations (this should be read-only)
  • Create individual accounts for members of DevOps who will need to act upon the database. (these accounts should have limited write access)
• Reference data should be read-only (immutable versions) and should only be updated through deployments. This type of data can be stored in NoSQL data services to augment the overall scalability of your application.
• Enable Auditing for Azure SQL Database, this feature will give you deep insight in how the database is manipulated and about how it is used.
• Use SQL Database Projects to design, build, version and deploy
• Use schemas to segregate tenants, reference data, activity data and resouce (shared) data.
• Use schemas to keep track of ownership chaining
• Encrypt connection string passwords at rest
• Use strong passwords
• Set Trusted_Connection=False in the connection string. This forces server certificate validation
• Set Encrypt=True in the connection string to force the client to use SSL
• Ensure that you are covered against SQL Injection
• SQL Database Firewall rules should block everything except the consuming applications
  • SQL Database Firewall rules should be set on both the Server and Database

Working with Microsoft Azure Resources

On September 22 2014, I had the pleasure of speaking to the MSDEVMTL community about working with Microsoft Azure Resources.

Microsoft Azure Resources include Blob Storage, Table Storage, Queue Storage, Service Bus, Virtual Machines, Cloud Services and SQL Database. During my talk, I introduced a couple of tools that allow us to work with these resources. Some tools are built by Microsoft others are built by companies like Cerebrata, Cloud Berry and Zudio.

Rebuilding SQL Database Indexes

A few months ago I wrote a blog post titled “Don’t Forget About Index Maintenance on Azure SQL Database“. Since then, Microsoft Azure SQL Database has changed a lot. We aren’t as concerned about the size of the database anymore, because databases can reach 500 GB in size. Take a moment to think about that number. 500GB is a lot of data! Before you get excited and move on to more important things, ask yourself this question, does all that data really belong in my SQL Database? Put some thought into it, you may be surprised by the answers you come up with.

Demystifying Windows Azure SQL Database Billing

SQL Database Web and Business editions are charged based on the actual volume of the database, measured in GB.

Continue Reading...

Which Transact-SQL Statements are Supported on Microsoft Azure SQL Database?

Last night I spoke at the Communauté .NET Montréal about Azure SQL Database and Your Data. Throughout the evening many great questions surfaced and helps us gain a better understanding of what the differences were between SQL Server and SQL Database.

One question stood out and it was “what statements are supported on SQL Database?”

Following our discussions I was fortunate to come across the following MSDN pages (Thanks to Mike Martin for pointing these out).

A list of unsupported Transact-SQL Statements for Azure SQL Database

Microsoft Azure SQL Database does not support all of the Transact-SQL statements that are delivered in SQL Server 2008. The following table provides a list of the Transact-SQL statements that Azure SQL Database does not support.

• $PARTITION
• CHECKPOINT
• BULK INSERT
• BACKUP
• MORE…

A list of partially supported Transact-SQL Statements for Azure SQL Database

In Microsoft Azure SQL Database, some Transact-SQL statements do not support some of the arguments and options that exist in their corresponding Transact-SQL statements in SQL Server 2008.

A list of supported Transact-SQL Statements for Azure SQL Database

These Transact-SQL statements provide the same functionality as their corresponding Transact-SQL statements in SQL Server 2008.

An additional page also caught my eye and it lays out the Azure SQL Database support for programmability enhancements in SQL Server 2012. Luckily it also points out the programmability enhancements that are not supported at this time.

Indexes are Crucial on Azure SQL Database!

One of the issues that seem to come up time and time again, is the lack of a basic index strategy. Many Windows Azure SQL Databases fail under load because they lack indexes.

Continue Reading...

GUIDs VS. IDs on Azure SQL Database

This is an age old war and this is my take on it.

GUIDs are awesome, especially when you need to synchronize data between data sources. IDs are great for speed. To be honest, it’s a memory thing and you need all the speed you can get when you build stuff on Azure.

Since SQL Database like SQL Server uses 8 Kilobyte pages to store data, using IDs will allow you to store more data and optimize for operations like JOINs and MERGEs.

That being said, when I am asked whether to use a GUID or an ID as a primary key for tables stored in Azure SQL Database, I usually give the following advice. Use IDs for lookup data and use GUIDs for data that is susceptible to synchronization and to public use.

Consequently, if you are using GUIDs as primary keys, I strongly recommend creating clustered indexes on non-primary key columns. This will allow you to further optimize data retrieval.

Configuring Entity Framework 6.0.X for Services That Use Windows Azure SQL Database

Since Entity Framework 6 has achieved General Availability I have been waiting for version 6.0.2 (available on NuGet). This version of Entity Framework packs a ton of new features, including async queries and built-in retries to deal with the inevitable transient faults. On Windows Azure, there are to be expected. Having the retry logic built-in is music to any seasoned Windows Azure developer who has had to implement this logic using the Transient Fault Handling Application Block.

Don’t get me wrong, the Transient Fault Handling Application Block is still very useful to implement your own error handling strategies. A good example can be found in my post about defining an HTTP Transient Error Detection Strategy for REST calls.

Since Entity Framework has changed a lot since version 5.0, it may come as a surprise that the transient fault retry logic isn’t activated by default. If you want to take advantage of this feature, you will need to activate it. The following piece of code, will show you what you need to know about configuring Entity Framework 6.0.X for services that use Windows Azure SQL Database.

The Truth About Windows Azure PowerShell

The truth about Windows Azure PowerShell is that I should have paid more attention to it. As new features make their way to General Availability (GA) on Windows Azure, they are accompanied by cmdlets (pronounced command-lets) that empower Developers and IT Pros to automate repetitive tasks. In turn reducing human error and the amount of time required to accomplish complex multi-step tasks.

What is it?

PowerShell is Microsoft’s task automation and configuration management framework, consisting of a command-line shell and associated scripting language built on .NET Framework. Windows Azure PowerShell is a module that provides cdlets to manage Windows Azure through Windows PowerShell. You can use the cmdlets (pronounced command-lets) to create, test, deploy, and manage solutions and services.

Steps Towards Optimized Azure SQL Databases

Azure SQL Database is a high density multi-tenant database service, whose performance is not guaranteed due to its nature. Thus, it’s imperative that we pay attention to details. This post is all about reviewing the structure and performance of existing Azure SQL Databases by giving you insights into what you should be looking for.

Prior to making any changes to your application, you should look into taking control of your database’s life cycle management by employing tools like the Microsoft SQL Server Data Tools. These tools allow you to target specific platforms like Azure SQL Database, which is very handy because it allows you to identify unsupported features before you deploy. Furthermore, they provide IntelliSense and make it possible to manage database versions by storing scripts in source control solutions like Microsoft Team Foundation Server.