Skip to content

Latest commit

 

History

History
213 lines (176 loc) · 12.4 KB

sql-azure-management.md

File metadata and controls

213 lines (176 loc) · 12.4 KB

Managing Azure SQL Database using SQL Server Management Studio

You can use SQL Server Management Studio (SSMS) to administer Azure SQL Database logical servers and databases. This topic walks you through common tasks with SSMS. You should already have a logical server and database created in Azure SQL Database before you begin. To get started, read Create your first Azure SQL Database and then come back.

It's recommended that you use the latest version of SSMS whenever you work with Azure SQL Database. Visit Download SQL Server Management Studio to get it.

Connect to a SQL Database logical server

Connecting to SQL Database requires that you know the server name on Azure. You might need to sign in to the portal to get this information.

  1. Sign in to the Azure Management Portal.

  2. In the left pane, click SQL Databases.

  3. On the SQL Databases home page, click SERVERS at the top of the page to list all of the servers associated with your subscription. Find the name of the server to which you want to connect and copy it to the clipboard.

    Next, configure your SQL Database firewall to allow connections from your local machine. You do this by adding your local machines IP address to the firewall exception list.

  4. On SQL Databases home page, click SERVERS and then click the server to which you want to connect.

  5. Click Configure at the top of the page.

  6. Copy the IP address in CURRENT CLIENT IP ADDRESS.

  7. In the Configure page, Allowed IP Addresses includes three boxes where you can specify a rule name and a range of IP addresses as starting and ending values. For a rule name, you might enter the name of your computer. For the start and end range, paste in the IP address of your computer into both boxes, and then click the checkbox that appears.

    The rule name must be unique. If this is your development computer, you can enter the IP address in both the IP range start box and the IP range end box. Otherwise, you might need to enter a broader range of IP addresses to accommodate connections from additional computers in your organization.

  8. Click SAVE at the bottom of the page.

    Note: There can be up as much as a five-minute delay for changes to the firewall settings to take effect.

    You are now ready to connect to SQL Database using Management Studio.

  9. On the taskbar, click Start, point to All Programs, point to Microsoft SQL Server 2014, and then click SQL Server Management Studio.

  10. In Connect to Server, specify the fully-qualified server name as serverName.database.windows.net. On Azure, the server name is an autogenerated string composed of alphanumeric characters.

  11. Select SQL Server Authentication.

  12. In the Login box, enter the SQL Server administrator login that you specified in the portal when you created your server.

  13. In the Password box, enter the password that you specified in the portal when you created your server.

  14. Click Connect to establish the connection.

SQL Server 2014 SSMS with the latest updates offers expanded support for tasks like creating and modifying Azure SQL databases. In addition, you can also use Transact-SQL statements to accomplish these tasks. The steps below provide examples of these statements. For more information about using Transact-SQL with SQL Database, including details about which commands are supported, see Transact-SQL Reference (SQL Database).

Create and manage Azure SQL databases

While connected to the master database, you can create new databases on the server and modify or drop existing databases. The steps below 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 new database. For more information, see CREATE DATABASE (SQL Database). The statement below creates a new database named myTestDB and specifies that it is a Standard S0 Edition database with a default maximum size of 250GB.

    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 statement below 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 statement below deletes the myTestDB database, but don't drop it now because you will use it 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.

Note

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 available from the links listed above.

Create and manage logins

The master database keeps track of 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 will manage logins across the entire server. For more information, see Managing Databases and Logins in SQL Database.

  • Use the CREATE LOGIN statement to create a new server-level login. For more information, see CREATE LOGIN (SQL Database). The statement below creates a new 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, but 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:

    1. To refresh Object Explorer to view the myTestDB database that you just 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.

    2. Right-click myTestDB database and select New Query.

    3. 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 statement below 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 statement below 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 below 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. Below are a few examples of the type of monitor data you can retrieve through these views. 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 you want to manage with your server-level principle login 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;