Managing Azure SQL Database using SQL Server Management Studio
You can use SQL Server Management Studio (SSMS) to administer Azure SQL Database servers and databases. This topic walks you through common tasks with SSMS. You should already have a server and database created in Azure SQL Database before you begin. See Create your first Azure SQL Database and Connect and Query using SSMS for more information.
For a tutorial showing you how to create a server, create a server-based firewall, view server properties, connect using SQL Server Management Studio, query the master database, create a sample database and a blank database, query database properties, connect using SQL Server Management Studio, and query the sample database, see Get Started Tutorial.
It's recommended that you use the latest version of SSMS whenever you work with Azure SQL Database.
Always use the latest version of SSMS because it is continually improved to work with the latest updates to Azure and SQL Database. To get the latest version, see Download SQL Server Management Studio.
Create and manage Azure SQL databases
While connected to the master database, you can create databases on the server and modify or drop existing databases. The following steps describe how to accomplish several common database management tasks through Management Studio. To perform these tasks, make sure you are connected to the master database with the server-level principal login that you created when you set up your server.
To open a query window in Management Studio, open the Databases folder, expand the System Databases folder, right-click on master, and then click New Query.
Use the CREATE DATABASE statement to create a database. For more information, see CREATE DATABASE (SQL Database). The following statement creates a database named myTestDB and specifies that it is a Standard S0 Edition database with a default maximum size of 250 GB.
CREATE DATABASE myTestDB (EDITION='Standard', SERVICE_OBJECTIVE='S0');
Click Execute to run the query.
Use the ALTER DATABASE statement to modify an existing database, for example if you want to change the name and edition of the database. For more information, see ALTER DATABASE (SQL Database). The following statement modifies the database you created in the previous step to change edition to Standard S1.
ALTER DATABASE myTestDB MODIFY (SERVICE_OBJECTIVE='S1');
Use the DROP DATABASE Statement to delete an existing database. For more information, see DROP DATABASE (SQL Database). The following statement deletes the myTestDB database, but don't drop it now because you will use it to create logins in the next step.
DROP DATABASE myTestBase;
The master database has the sys.databases view that you can use to view details about all databases. To view all existing databases, execute the following statement:
SELECT * FROM sys.databases;
- In SQL Database, the USE statement is not supported for switching between databases. Instead, you need to establish a connection directly to the target database.
Many of the Transact-SQL statements that create or modify a database must be run within their own batch and cannot be grouped with other Transact-SQL statements. For more information, see the statement-specific information.
Create and manage logins
The master database contains logins and which logins have permission to create databases or other logins. Manage logins by connecting to the master database with the server-level principal login that you created when you set up your server. You can use the CREATE LOGIN, ALTER LOGIN, or DROP LOGIN statements to execute queries against the master database that manages logins across the entire server. For more information, see Managing Databases and Logins in SQL Database.
Use the CREATE LOGIN statement to create a server-level login. For more information, see CREATE LOGIN (SQL Database). The following statement creates a login called login1. Replace password1 with the password of your choice.
CREATE LOGIN login1 WITH password='password1';
- Use the CREATE USER statement to grant database-level permissions. All logins must be created in the master database. For a login to connect to a different database, you must grant it database-level permissions using the CREATE USER statement on that database. For more information, see CREATE USER (SQL Database).
To give login1 permissions to a database called myTestDB, complete the following steps:
To refresh Object Explorer to view the myTestDB database that you created, right-click the server name in Object Explorer and then click Refresh.
If you closed the connection, you can reconnect by selecting Connect Object Explorer on the File menu.
- Right-click myTestDB database and select New Query.
Execute the following statement against the myTestDB database to create a database user named login1User that corresponds to the server-level login login1.
CREATE USER login1User FROM LOGIN login1;
Use the sp_addrolemember stored procedure to give the user account the appropriate level of permissions on the database. For more information, see sp_addrolemember (Transact-SQL). The following statement gives login1User read-only permissions to the database by adding login1User to the db_datareader role.
exec sp_addrolemember 'db_datareader', 'login1User';
Use the ALTER LOGIN statement to modify an existing login, for example if you want to change the password for the login. For more information, see ALTER LOGIN (SQL Database). The ALTER LOGIN statement should be run against the master database. Switch back to the query window that is connected to that database. The following statement modifies the login1 login to reset the password. Replace newPassword with the password of your choice, and oldPassword with the current password for the login.
ALTER LOGIN login1 WITH PASSWORD = 'newPassword' OLD_PASSWORD = 'oldPassword';
Use the DROP LOGIN statement to delete an existing login. Deleting a login at the server level also deletes any associated database user accounts. For more information, see DROP DATABASE (SQL Database). The DROP LOGIN statement should be run against the master database. The statement deletes the login1 login.
DROP LOGIN login1;
The master database has the sys.sql_logins view that you can use to view logins. To view all existing logins, execute the following statement:
SELECT * FROM sys.sql_logins;
Monitor SQL Database using Dynamic Management Views
SQL Database supports several dynamic management views that you can use to monitor an individual database. A few examples of the type of monitor data you can retrieve through these views are following. For complete details and more usage examples, see Monitoring SQL Database using Dynamic Management Views.
Querying a dynamic management view requires VIEW DATABASE STATE permissions. To grant the VIEW DATABASE STATE permission to a specific database user, connect to the database and execute the following statement against the database:
GRANT VIEW DATABASE STATE TO login1User;
Calculate database size using the sys.dm_db_partition_stats view. The sys.dm_db_partition_stats view returns page and row-count information for every partition in the database, which you can use to calculate the database size. The following query returns the size of your database in megabytes:
SELECT SUM(reserved_page_count)*8.0/1024 FROM sys.dm_db_partition_stats;
Use the sys.dm_exec_connections and sys.dm_exec_sessions views to retrieve information about current user connections and internal tasks associated with the database. The following query returns information about the current connection:
SELECT e.connection_id, s.session_id, s.login_name, s.last_request_end_time, s.cpu_time FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections e ON s.session_id = e.session_id;
Use the sys.dm_exec_query_stats view to retrieve aggregate performance statistics for cached query plans. The following query returns information about the top five queries ranked by average CPU time.
SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time), SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC;