Did you know that you can connect to your SQL Azure database using Microsoft JDBC…
First of all, you have to open the firewall for your IP addresses in Windows Azure Plattform portal to be able to connect to a SQL Azure database. Do you want to know whats annoying with that? Well, I write my blog posts on a laptop and I do it at home, at my office, at a cafe and I use Lan access, mobile internet and wifi spots. Do you think my IP address is the same where ever I try to connect to SQL Azure? No, I think I have to open the Firewall to all IP addresses in the world. But in normal case, you would only need to open the firewall for the Corporate network, my access to SQL Azure is not the normal case. If you use SQL server management studio to work with your databases, you are familiar with the possibility to switch between databases in the management studio. BUT in SQL Azure, you can’t switch database in a connection. Once the connection to a DB is done, you have to stick with that database until you do another connection.
Try connect to a database in SQL Azure, type: USE another_database_name GO This will give you an error! But what about selecting data from a table in a different database with three part name? No, you can’t access a table in another database either. SELECT * FROManother_database_name.dbo.table1
This will also fail! In SQL Azure, it is not possible to
– use the SQL Server Management Studio GUI to switch databas
– switch database with USE databasename statement
– reference another database from current database by SELECT * FROM database.schemaname.tablename
– reference another database from current database by using OPENDATASOURCE
This is important to take into consideration when you design your database. If you need to split your data into two or more databases, you need to handle them by different connections in your application (Sharding), or use Federation, but thats another blog post.
Another important thing to remeber is that your connections will terminate for a number of reasons:
– Lock consumption: Sessions consuming greater than one million locks are terminated.
–Uncommitted transactions: Uncommitted transactions can block the truncation of log files. To prevent this, the distance from the first or oldest active transaction log sequence number (LSN) to the tail of the log (current LSN) cannot exceed 20% of the size of the log file.
–Transactions blocking system calls: If a transaction locks a resource for more than 20 seconds, it is terminated.
–Log file size: Transactions consuming excessive log resources are terminated. The maximum permitted log size for a single transaction is 1-gigabyte (GB).
–TempDB usage and size: When a session uses more than 5 GB of tempdb space (= 655,360 pages), the session is terminated.
– Excessive memory usage: When there is memory contention, sessions consuming greater than 16-megabyte (MB) for more than 20 seconds are terminated in the descending order of time the resource has been held.
–Maximum database size: Transactions attempting to updates or inserts will be terminated when the database reaches its max size.
– Idle connections: Connections to your SQL Azure database that are idle for 30 minutes or longer will be terminated.
– Transaction termination: SQL Azure kills all transactions after they run for 24 hours.
– Denial of Service attacks
– Network problems
– Failover problems
To prevent your application from failure you need to prevent and handle these situations. To prevent the connection from termination you need to:
-Tune you application
– Keep transactions as short as possible
– Close idle transactions manually
– Avoid uncommitted transactions
– Use sort and grouping only when you need to
– Divide large insert/update/deletes into smaller batches
– Only open the required IP adresses for your application to work
Well, this is no difference from what you are already used to, is it? No, this is “the old school”, a common best practice applied in the cloud. The best practice is to use connection pooling and close the connections as soon as possible. You can read more about SQL Azure connections at :http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management.aspx
Stay tuned for more blog posts about SQL Azure and feel free to contact any of our SQL server consultants and we will help you.