[Windows Azure] Managing SQL Database using SQL Server Management Studio
发布日期:2021-08-26 18:18:22 浏览次数:41 分类:技术文章

本文共 12593 字,大约阅读时间需要 41 分钟。

Managing Windows Azure SQL Database using SQL Server Management Studio

You can use Windows Azure SQL Database Management Portal or the SQL Server Management Studio (SSMS) client application to administer your SQL Database subscriptions and create and manage associated logical servers and databases. The guidance below describes how to use Management Studio to manage SQL Database logical servers and databases. For information on how to use SQL Database connections in application code, see .

Note Click to collapse

You can use either SQL Server 2012 or the SQL Server 2008 R2 version of Management Studio. Earlier versions are not supported.

This task includes the following steps:

Step 1: Get Management Studio

Management Studio is an integrated environment for managing SQL databases. When managing databases on Windows Azure, you can use the Management Studio application installed with SQL Server or download the free SQL Server 2012 Management Studio Express (SSMSE) version. The steps below describe how to install SSMSE.

  1. On the page, select the x86 version of Management Studio if you are running a 32-bit operating system, or x64 if you are running a 64-bit operating system. Click Download, and when prompted, run Setup.

  2. Click New SQL Server stand-alone installation or add features to an existing installation and click OK.

  3. Accept the license terms and click OK.

  4. Click Install to install files required by SQL Server Setup.

  5. On the Feature Selection screen, Management Tools - Basic is preselected. This is because you are running the installer for Management Studio. If you are running Setup for all of SQL Server Express, choose the Management Tools - Basic option, and click Next.

  6. On the Error Reporting screen, you can optionally choose to send error reports to Microsoft. This is not required to use SSMSE. Click Next to start the installation.

  7. When the installation is complete, you will see the Complete page. Click Close.

Step 2: Connect to SQL Database

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

  1. Sign in to the .

  2. In the left pane, click on 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.

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

  2. Click Configure at the top of the page.

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

  4. 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.

  5. 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.

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

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

  3. Select SQL Server Authentication.

  4. In the Login box, enter the SQL Server administrator login that you specified in the portal when creating your server in the format login@yourServerName.

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

  6. Click Connect to establish the connection.

On Windows Azure, each SQL Database logical server is an abstraction that defines a grouping of databases. The physical location of each database might be on any computer in the data center.

In previous versions, you had to connect directly to master when setting up the connection in Management Studio. This step is no longer necessary. Connections will now succeed based on the server name, authentication type, and administrator credentials.

Many of the SSMS wizards you can use for tasks like creating and modifying logins and databases on a SQL Server database are not available for SQL databases on Windows Azure, so you'll need to utilize 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 .

Step 3: Create and Manage 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, right-click on master, and then click New Query.

Click Execute to run the query.

  • Use the CREATE DATABASE statement to create a new database. For more information, see . The statement below creates a new database named myTestDB and specifies that it is a Web Edition database with a maximum size of 1 GB.

    CREATE DATABASE myTestDB(MAXSIZE=1GB, EDITION='web');
  • Use the ALTER DATABASE statement to modify an existing database, for example if you want to change the name, maximum size, or edition (business or web) of the database. For more information, see . The statement below modifies the database you created in the previous step to change the maximum size to 5 GB.

    ALTER DATABASE myTestDBMODIFY(MAXSIZE=5GB, EDITION='web');
  • Use the DROP DATABASE Statement to delete an existing database. For more information, see . 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 Click to collapse

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.

Step 4: 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 .

  • Use the CREATE LOGIN statement to create a new server-level login. For more information, see . 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 .

  • To give login1 permissions to a database called myTestDB, complete the following steps:

    1. Refresh Object Explorer to view the myTestDB database that you just created. It should appear below the System Databases folder that contains master.

      If you closed the connection, you can reconnect by selecting Connect Object Explorer on the File menu. Repeat the instructions in to connect to the database.

    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 . 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 . 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 login1WITH 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 . 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;

Step 5: 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 .

  • 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/1024FROM 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_timeFROM    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_statsGROUP BY query_stats.query_hashORDER BY 2 DESC;

Additional Resources

转载于:https://www.cnblogs.com/licheng/p/3266392.html

转载地址:https://blog.csdn.net/weixin_33894640/article/details/92633433 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:C# https客户端获取证书的工具方法
下一篇:LDAP和单点登录

发表评论

最新留言

哈哈,博客排版真的漂亮呢~
[***.90.31.176]2024年03月25日 16时47分26秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

慕课python第五周测试答案_中国大学MOOC(慕课)_python+_满分章节测试答案 2019-04-21
lsof查看占用高_lsof解决磁盘占用过高,查询却无大文件处理一例! 2019-04-21
python调用oracle过程 权限不足_oracle-存储过程提示 ORA-01031: 权限不足 2019-04-21
java ee6教程_Java EE 极简教程(六):框架的选择 2019-04-21
java io流过滤流_IO流分类详细介绍和各种字节流类介绍与使用 过滤流 字节流 2019-04-21
java预处理指令_Java程序员学C++_1_C++中的预处理命令 | 学步园 2019-04-21
java分词支持拼音_java 支持分词的高性能拼音转换工具,速度是 pinyin4j 的两倍... 2019-04-21
java中的%不对 如何处理_Java心得--异常及其处理 2019-04-21
java 上传速度计算_java常见3种文件上传速度对比和文件上传方法详细代码 2019-04-21
java 中区分月份_输入一年当中的月份,判断是哪个季节.(用java编写并且用到了import java.io.*;)... 2019-04-21
java 试图模版_图解Java设计模式之模板模式 2019-04-21
java.exe占用cpu_Windows服务器java.exe占用CPU过高问题分析及解决 2019-04-21
支付宝 java 乱码_支付宝即时到账接口中文乱码问题 2019-04-21
java中的handler理解_handler 与message的一些理解 2019-04-21
JAVA礼物题_这些Java面试题,你一定要记住! 2019-04-21
java 隐藏email_java Email 2019-04-21
linux下qt浏览word文件内容,Qt获取office文件内容 2019-04-21
amd锐龙笔记本cpu怎么样_不知不觉已经15款 AMD Ryzen锐龙笔记本处理器盘点 2019-04-21
syslog打印不带等级_(转)syslog日志等级 2019-04-21
librosa能量_librosa语音信号处理 2019-04-21