Let's spend a few minutes talking about some
things you should consider when developing applications for the cloud.
You spend a large portion of this article discussing how to connect to
SQL Azure, but even before you start coding the very first thing you
should consider is your connection. First and foremost, secure your
connection string from injection attacks and man-in-the-middle attacks.
The .NET Framework provides a simple class in which to create and manage
the contents of connection strings used by the SqlConnection class.
This class is called the SqlConnectionStringBuilder class.
The following example
illustrates how to use this class. I first define four static variables
to hold the username, password, database name and server:
private static string userName = "SQLScott@server";
private static string userPassword = password;
private static string dataSource = "tcp:server.database.windows.net";
private static string dbName = "TechBio";
I then modify my GetConString method to use the SqlConnectionStringBuilder class to dynamically build my connection string:
string GetConString(int connType)
{
if (connType == 1)
SqlConnectionStringBuilder connstr = new SqlConnectionStringBuilder();
connstr.DataSource = dataSource;
connstr.InitialCatalog = dbName;
connstr.Encrypt = true;
connstr.TrustServerCertificate = false;
connstr.UserID = userName;
connstr.Password = userPassword;
return connstr.ToString();
...
}
Thus, consider the following when connecting to a SQL Azure database.
Use the SqlConnectionStringBuilder class to avoid injection attacks.
Encrypt
your connection. Set the Encrypt parameter to True and the
TrustServerCertificate to False to ensure a properly encrypted
connection to avoid any man-in-the-middle attacks.
Use MARS (Multiple Active Results Sets) whenever possible to lessen the trips to the database.
Lastly, let's discuss
some connection constraints. You discussed these previously briefly but
in bears repeating them because you're discussing SQL Azure connections.
The idea is that Azure is handling the connections, and because
multiple resources will more than likely using the same server as you,
the last thing Microsoft want is for you to hog all the resources and
bring the server to its knees. Thus, your connection can, and probably
will, be closed automatically if your connection meets any of the
following criteria:
Long running
queries and Long running single transactions - If your query takes a
long time to execute (right now the time is set at 30 seconds) then
Azure will kill your connection. Equally, if you have a single
transaction
Idle Connections - Play nice with others and close your connections. Don't leave them hanging open.
Excessive
resource usage - This should go without saying. Because you share the
resources with others, don't hog all the resources. Again, play nice.
Failover
because of server failures - This one is obvious. If the server fails,
so will your connection. But when your application reconnects, you'll
automatically connect to one of the failover databases.
You have control over the
first three items, you don't have control over the last item. Items one
and three you referenced at the beginning of the article. The bottom
line is to test locally before deploying remotely. This is a must, an
absolute must. Spend some time looking at your queries and their
execution plans to ensure they will execute efficiently and timely.
Don't deploy to the cloud until you're satisfied that your database will
play nice with the others.
