We would be able to Query Azure SQL Database either using portal or SSMS. SSMS is a standard way to Connect and Query Azure Database. Query Editor in Portal is an Additional feature to Query the database. Let us See them in brief.
This Article will provide details on
- Query Editor
- Database Connection using SSMS
- Explore Azure SQL DB
- Generating Connection String
Using Query Editor:
login the Portal –> Get into the Database –> Select on Query Editor –> Provide the credentials to login the Logical Server. To Login the Logical Server you shall either provide the SQL Server Authentication or the Active Directory Password Authentication. While Configuring the database you should have created the admin account.(Next article I would explain on managing Logins)
You shall view the tables, Views and Stored procedures and query the database.
Using SSMS to Connect Azure Database:
Before trying to Connect the Database from SSMS we need to open firewall for the server from where you are trying to connect the database.
To open firewall rule select Set Firewall Rule
Mention the IP Which needs permission to access the Logical Server\Database.
The IP should be external Ip and not internal ip. To gt the External Ip search for my external Ip in Google search. (what you get with Ipconfig query in CMD is internal IP but what we need is external IP)
Once Firewall rule is set then open SSMS in your machine and try to connect the Azure database using Logical Server name as below.
You can get to know the Logical Server Name from Portal
Exploring Azure SQL DB Database:
Once connected to Logical Server, you could see there is only master Database. There is no Msdb or Model Db. Since MSDB is in there you could not have parameters related to MSDB like SQL Agent, Backups etc.
What about TempDB?. You can’t find the tempdb databas any where in management portal or in SQL Server Management Studio. But is there a tempdb? Yes, there is, but you don’t have direct access to tempdb, only indirect by use of temp tables, order by, group by, version store, etc. And you have no control over the tempdb, it’s part of the cloud concept. Do you need access to the tempdb? No, but you have to be carefull when you design your queries, because heavy use of tempdb (>5Gb) may cause a connection termination.
A common practice in SQL server is to split large and complex queries into smaller blocks and store the results in temp tables, but that practice may have to be reconsidered if you have large recordsets SQL Azure databases have read committed snapshot on and every updated record of a table will be placed in the version store (in tempdb), and large updates may cause the connection to terminate. Update records in small pieces to avoid that.
Still You can find the availability of Tempdb using below Query.
SELECT * FROM tempdb..sysobjects WHERE id = object_id(N’tempdb..#TempAzureTable’) AND xtype = N’U’
To Query Database using SSMS
Connection String:
Generating Connection String has been made easier in Azure portal. Just select the connection string feature that will provide the connection string to be used in the application. Based on the Coding language we use the connection string shall be picked up.
CloudToon 
Leave a comment