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.
Author Archives: sirichamarthi
Taking database offline SQL DB
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.
Full-text search in SQL Azure
SQL Azure Full-text search is now in preview. Here is the link to the functionality available and limitations.
Alter database – SQL Azure V12
SQL Azure V12 provides altering more database options and here is the link to the best practices of using alter database in Azure.
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
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.