Author Archives: sirichamarthi

Inconsistent database ids in SQL Azure V12

In SQL Server there a couple simple ways to obtain the database id. They are db_id() built in and database_id coulmn from sys.databases view. However there is no guarantee that these two matches in SQL DB. While the database_id filed in sys.databases is consistent and won’t change in the life time of the database, db_id() built in can potentially change after every failover depending on the SQL instance the database ends up on.
Dbid also can be inconsistent between the master and user database. We should not take sys.databases view into account if we want to query other views like sys.dm_exec_Requests, sys.dm_tran_locks or sys.sysprocesses as the dbid used in them is the one represented in db_id() built in.

Minimize login time in SQL DB V12

SQL DB server master and user database can sit in two different machines and SQL server instances. Under the load (connecting to multiple databases) master will be bottleneck. This page talks about improving the login experience using contained users and database firewall rules.

SQL Azure Connection Strings

Wondering how to connect to SQL Azure databases? here are the connection string formats in ADO.Net , ODBC, PHP and JDBC to connect to the SQL Azure databases:

ADO.NET


Server=tcp:“{YourServer}”.database.windows.net,1433;Database=“{DatabaseName}”;User ID=“{UserNamer}”@”{YourServer}”;Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

ODBC


Driver={SQL Server Native Client 11.0};Server=tcp:“{YourServer}”.database.windows.net,1433;Database=“{DatabaseName}”;Uid=“{UserNamer}”@”{YourServer}”;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

PHP


Server: “{YourServer}“.database.windows.net,1433 \r\nSQL Database: “{DatabaseName}”\r\nUser Name: “{UserNamer}“\r\n\r\nPHP Data Objects(PDO) Sample Code:\r\n\r\ntry {\r\n $conn = new PDO ( \”sqlsrv:server = tcp:”{YourServer}”.database.windows.net,1433; Database = “{DatabaseName}”\”, \””{UserNamer}“\”, \“{your_password_here}\“);\r\n $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );\r\n}\r\ncatch ( PDOException $e ) {\r\n print( \”Error connecting to SQL Server.\” );\r\n die(print_r($e));\r\n}\r\n\rSQL Server Extension Sample Code:\r\n\r\n$connectionInfo = array(\”UID\” => \””{UserNamer}”@”{YourServer}”\”, \”pwd\” => \”{your_password_here}\”, \”Database\” => \””{DatabaseName}”\”, \”LoginTimeout\” => 30, \”Encrypt\” => 1, \”TrustServerCertificate\” => 0);\r\n$serverName = \”tcp:”{YourServer}”.database.windows.net,1433\”;\r\n$conn = sqlsrv_connect($serverName, $connectionInfo);

JDBC


jdbc:sqlserver://”{YourServer}”.database.windows.net:1433;database=”{DatabaseName}”;user=”{UserNamer}”@”{YourServer}”;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

Diagnostic connections to SQL DB V12 Databases

SQL DB V12 supports diagnostic connections (DAC) to the SQL DB V12 databases similar to the SQL Standalone. Using this an admin can connect to the database to troubleshoot the issues when he can’t connect to the database (for example concurrent session limit reached).
Connection String:
Sqlcmd –S .database.windows.net,1434 –U -P -d DatabaseName
The port to connect is 1434. However it appears one can’t connect to the master database.
Usage:
If one offending session took a lock on an object where rest all the concurrent sessions waiting on that session to finish we end up in a situation where admin can’t connect to the database to recover it. This is when one can open DAC connection to the database and kill the offending session.

Row versioning in SQL Database V12

SQL server supports optimistic concurrency control using row versioning (snapshot isolation and read committed snapshot isolation). These two isolation levels help reducing the query lock contention and works great for certain application types. However one may need to review the usage of these and the application pattern based on the resource consumption in the SQL DB offering for the better database performance.

Please refer to the following page: http://www.sqlindepth.com/row-versioning-in-sql-database-version-v12/

SQL Database: Query database edition and service objective through t-sql

SQL database v12 allows querying the database edition and service objective through t-sql using databasepropertyex. One has to connect to the database to get the edition / serviceobjective of that database. This query won’t return the results if run on the master database. Here are the results for a standard, S0 database

Syntax:

select DATABASEPROPERTYEX(db_name(),’serviceobjective’)
select DATABASEPROPERTYEX(db_name(),’serviceobjectiveid’)
select DATABASEPROPERTYEX(db_name(),’edition’)

Returns:

S0
f1173c43-91bd-4aaa-973c-54e79e15235b
Standard

 

Why SQL Azure?

This blog is about the unique offerings of Azure SQL database and the advantages of it over other cloud offerings and on premise SQL server.