SQL control constructs. Launching the SQL Server Management Studio program. Policy-based management
01/05/15 28.1KSometimes you really want to put your thoughts in order, sort them out. And even better, in alphabetical and thematic sequence, so that clarity of thinking finally comes. Now imagine what chaos would happen in " electronic brains » any computer without a clear structuring of all data and Microsoft SQL Server :
MS SQL Server
This software product is a database management system (DBMS) relational type, developed by Microsoft Corporation. A specially developed Transact-SQL language is used to manipulate data. Language commands for selecting and modifying a database are built on the basis of structured queries:
Relational databases are built on the interconnection of all structural elements, including due to their nesting. Relational databases have built-in support for the most common data types. Thanks to this, SQL Server integrates support for programmatically structuring data using triggers and stored procedures.
Overview of MS SQL Server Features
The DBMS is part of a long chain of specialized software, which Microsoft created for developers. This means that all links of this chain (applications) are deeply integrated with each other.
That is, their tools easily interact with each other, which greatly simplifies the process of developing and writing program code. An example of such a relationship is the MS Visual Studio programming environment. Its installation package already includes SQL Server Express Edition.
Of course, this is not the only popular DBMS on the world market. But it is precisely it that is more acceptable for computers running Windows, due to its focus on this operating system. And not only because of this.
Advantages of MS SQL Server:
- Has a high degree of performance and fault tolerance;
- It is a multi-user DBMS and operates on the client-server principle;
The client part of the system supports creating user requests and sending them to the server for processing.
- Tight integration with the Windows operating system;
- Support for remote connections;
- Support for popular data types, as well as the ability to create triggers and stored procedures;
- Built-in support for user roles;
- Advanced database backup function;
- High degree of security;
- Each issue includes several specialized editions.
Evolution of SQL Server
The features of this popular DBMS are most easily seen when considering the history of the evolution of all its versions. We will dwell in more detail only on those releases in which the developers made significant and fundamental changes:
- Microsoft SQL Server 1.0 – released back in 1990. Even then, experts noted high speed data processing, demonstrated even with maximum load in multi-user mode;
- SQL Server 6.0 - released in 1995. This version was the first in the world to implement support for cursors and data replication;
- SQL Server 2000 - in this version the server received a completely new engine. Most of the changes affected only the user side of the application;
- SQL Server 2005 – the scalability of the DBMS has increased, and the management and administration process has been simplified in many ways. A new API has been introduced to support the .NET software platform;
- Subsequent releases were aimed at developing the interaction of the DBMS at the level of cloud technologies and business analytics tools.
The basic system kit includes several utilities for SQL settings Server. These include:
Configuration Manager. Allows you to manage all network settings and database server services. Used to configure SQL Server within a network.
- SQL Server Error and Usage Reporting:
The utility is used to configure sending error reports to Microsoft support.
Used to optimize the operation of the database server. That is, you can customize the functioning of SQL Server to suit your needs by enabling or disabling certain features and components of the DBMS.
The set of utilities included in Microsoft SQL Server may vary depending on the version and edition software package. For example, in the 2008 version you will not find SQL Server Surface Area Configuration.
Starting Microsoft SQL Server
For the example, the 2005 version of the database server will be used. The server can be started in several ways:
- Through the utility SQL Server Configuration Manager. In the application window on the left, select “SQL Server 2005 Services”, and on the right - the database server instance we need. We mark it and select “ Start” in the submenu of the right mouse button.
- Using the environment SQL Server Management Studio Express. It is not included in the Express edition installation package. Therefore, it must be downloaded separately from the official Microsoft website.
To start the database server, launch the application. In the dialog box " Connection to the server"In the "Server name" field, select the instance we need. In field " Authentication" leave the value " Windows Authentication" And click on the “Connect” button:
SQL Server Administration Basics
Before you start MS SQL Server, you need to briefly familiarize yourself with the basic capabilities of its configuration and administration. Let's start with a more detailed overview of several utilities included in the DBMS:
- SQL Server Surface Area Configuration– this is where you should go if you need to enable or disable any feature of the database server. At the bottom of the window there are two items: the first is responsible for network parameters, and in the second you can activate a service or function that is disabled by default. For example, enable integration with the .NET platform via T-SQL queries:
Rick Dobson, Ph.D. Selective translation by S. A. Ledenev
This article discusses how to manage SQL server security using Access with the SQL Distributed Management Objects (SQL-DMO) library in Visual Basic for Applications (VBA)
Introduction
This article is mainly aimed at developers of ADP projects. You may be familiar with 2 things regarding SQL server security. First, security is not an option like it was in Jet. Secondly, the security model of SQL Server is different from the security model of Access. Even if you have a rough understanding of SQL Server security, this article will teach you how to configure it to protect your project's resources.
This article may also be useful for SQL server administrators. The solutions offered here can help them manage server security using Access. This article can help administrators learn how to manage server security without using Enterprise Manager's graphical tools or running Transact SQL (T-SQL) scripts from Query Analyzer. By learning how to manage server security using Access, administrators have the option of creating a faster solution.
The article can be roughly divided into two parts about the security of SQL Server 7.0 and SQL Server 2000. First, the concept of SQL Server security is discussed, focusing on how to manage security using Access. This information is sufficient to develop multi-user applications with different groups of users who have clearly defined rights to use server objects. The second part of the article demonstrates software solutions based on managing SQL-DMO (SQL Distributed Management Objects) objects using Microsoft Visual Basic® for Applications (VBA). Because SQL-DMO has a hierarchical object construction model very similar to the Microsoft Office object construction model, all that is needed is to learn more about SQL-DMO objects, properties, methods, and events regarding security. In addition, Access 2002 programmers cannot do without programming the security configuration functions in VBA, since they are not available from the Database Security menu.
SQL Server Security Concept
This section consists of 4 main parts. The introduction begins with a description of authentication - the process of checking a user for the right to access the server database. Using Enterprise Manager, SQL Server provides two types of authentication. The first part of the section shows the differences and similarities between them.
Next, we talk about the concept of an authorized user who serves to connect to the server. Authorized users are called logins. Two types of logins correspond to two SQL server authentication models. Logins are responsible for access rights to the server.
The third part introduces the concept of users. SQL Server reserves the concept of users as a concept that characterizes database users. Typically, there are several logins on a server, each login is associated with several users - one for each database that the login is allowed to access. Logins and users can belong to fixed roles that describe a set of permissions. You can control the behavior of logins and users by managing their membership in fixed server roles and fixed database roles, respectively.
Part 4 talks about user-defined database roles.
Authentication
Authentication is described as the process of authorizing a user on an instance of SQL server and some database associated with the user. Authorization is associated with a login, which describes the user's rights on the server. Authorization works like a key to a castle. If the key fits the lock, the person uses it to gain access to everything in the room whose door was locked with that lock. An SQL server usually has several locks with different keys to them. Each key gives access to a different set of databases located on the server.
SQL Server supports two types of authentication: SQL Server and Microsoft Windows® (or Windows NT® with SQL Server 7.0). These two types determine who performs login verification: SQL server or Windows. With Windows authentication, users have trusted Accounts to access the SQL server. These accounts are verified by Windows, but the SQL server "knows" the account name. Windows authentication allows users to log in to Windows and the SQL server by entering only one password. With SQL Server authentication, the SQL server itself performs the authorization. With any type of authentication, the SQL server must know the authorized logins.
There are pros and cons to both the first and second types, but Windows authentication is preferable. Windows has a more complex authorization procedure, and it frees the SQL server administrator from having to manage accounts. On the other hand, not all operating systems that support SQL server support Windows authentication. For example, Microsoft Data Engine (MSDE) installed with Microsoft Office 2000 and Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) installed with Office XP: Both can run on Windows 98, which does not support Windows authentication. Or SQL Server 7.0 and MSDE running on Windows 95. Some organizations may prefer the ability to control access to database resources at the DBA level rather than more global level Windows administrator.
Any SQL Server instance can have any of two authentication types that can be set in Enterprise Manager (I'll cover the third type later in this article). The SQL Server documentation defines the term mixed mode connection to a SQL Server, which supports both Windows and SQL Server authentication. If the server only supports Windows authentication, then the server has a Windows (or Windows NT) authentication type. By default, MSDE is installed with a mixed authentication type. MSDE 2000, on the other hand, installs with Windows authentication by default. Developers and administrators using Enterprise Manager can change the authentication type using graphical tools. Also, any application can use the SQL-DMO library to change the server authentication type.
Logins and fixed server roles
The ADP project contains a Database window similar to traditional Jet solutions. However, the ADP project connects to the server through an OLE DB connection, unlike a Jet database. In an ADP project, the login type is specified in the Data Link Properties dialog box. To call this dialog: from the database window, select the File menu, select Connection from the menu.
The Connection Options dialog box allows you to select the database server, login, and database to which the project will connect via OLE DB. There are two ways to connect to the server. The first one selects the use internal audit Windows NT security (use Windows NT integrated security). This is called Windows authentication. By selecting this option, you do not need to provide a login and password. This is because Windows authorizes the user when Windows starts. When the option to use internal security is selected, the ADP project sends the Windows login to the SQL server when attempting to create an OLE DB connection to it. By selecting the second option (use a specific user name and password), you must specify the SQL server login. In the user name field, the standard SQL server login name must be entered - the login supported by this SQL server. The password is optional, but it is strongly recommended to use a password, since together with the login, the password provides an additional level of security.
Although entering your login is key when creating OLE DB connections in an ADP project, the login in itself is not sufficient to grant rights to perform any tasks on the server or with the database. There is the concept of login membership in fixed server roles, which allows the ADP project to perform some server functions, such as creating new databases and managing logins. Depending on the version of the SQL server to which the ADP project connects, there are seven or eight fixed server roles. SQL Server 7.0 and MSDE provide seven fixed server roles, while SQL Server 2000 and MSDE 2000 provide eight. SQL Server Books Online (BOL) contains comprehensive documentation about fixed server roles, including T-SQL expressions for assigning and removing logins from role membership. For example, the Roles section of BOL talks about fixed server fields and a corresponding set of permissions for accessing databases, such as viewing and writing to tables.
The following table provides a quick overview of fixed server role names and a brief description of them. You can run the sp_helpsrvrole system stored procedure to obtain a list of fixed server role names. Executing the sp_srvrolepermission system stored procedure displays a detailed list of functions for each fixed server role. There are differences between the fixed server roles in 7.0 and 2000 versions of SQL Server. For example, bulkadmin is new role in SQL Server 2000. Additionally, the DROP DATABASE statement was only available to the sysadmin role in SQL Server 7.0, and SQL Server 2000 allows members of the dbcreator role to also perform this procedure.
|
Fixed server role name |
Description of the fixed server role |
| sysadmin | Execute any server or database expression |
| serveradmin | Server administration, configuration, start, stop. |
| setupadmin | Administration of linked servers and the right to run stored procedures at the server startup stage. |
| securityadmin | Management of logins and passwords. May give the right to create new databases. |
| processadmin | Executing the KILL command. |
| dbcreator | Creating, changing, renaming and deleting databases. |
| diskadmin | Managing files on disk. |
| bulkadmin | Executing BULK INSERT statements. |
The sa login is a special SQL server login. This login is a member of the sysadmin group and provides the right to perform any functions on the server. SQL Server creates this login during installation and cannot be deleted. Immediately after the installation is completed, the sa login does not have a password. It is necessary to set a password for sa to ensure the security of your database server, especially for servers with mixed authentication types. Remember: servers with Windows authentication do not accept or process SQL server logins.
When installing SQL server on Windows 98 or Windows ME, the server is always installed with a mixed authentication type, so it can accept SQL server logins. The default authentication types differ for SQL Server 7.0 and MSDE from SQL Server 2000 and MSDE 2000 installed on Windows 2000 and Windows NT. For SQL Server 7.0 and MSDE, the default installation process installs the server with a mixed authentication type. In contrast, SQL Server 2000 and MSDE 2000 are installed with Windows authentication by default. Additionally, the Version 2000 installation process assigns members of the Windows Administrators group to the sysadmin fixed server role. Therefore, these logins are similar to the sa login, which has full control over the server.
Users and fixed database roles
Typically, users access SQL Server to work with one or more databases. For this purpose, SQL Server has a mechanism for matching logins with databases. Just as a SQL server can have one or more logins, each login can have one or more accounts associated with it. However, one account corresponds to only one login (with two exceptions). The SQL Server documentation defines accounts as database users (or users). Every database user has access to this database.
SQL Server can grant database users ownership of the database and permissions to use database objects. SQL Server provides permission assignment within the database at two independent levels. At the first level, you can assign one or more permission clusters to a user. The SQL Server documentation defines these clusters as fixed database roles. I call them permission clusters because they can combine permissions on many database objects. For example, one fixed role (db_datareader) allows you to read data from any table in the database—not just one or two specific tables. At the second level, you can assign permissions at the level of specific database objects. SQL Server allows you to combine both methods: a user can be a member of one or more fixed database roles and also have specific permissions on specific database objects. I'll cover managing permissions on specific database objects in the next section.
Two special users can be associated with more than one login. These are the dbo and guest users. The dbo user is a member of the sysadmin fixed server role, and can create objects on the server, such as databases or database tables. A user whose login does not belong to the sysadmin fixed server role can also create database objects, such as tables.
The visibility of objects in the ADP Project Database Window depends on whether the project login belongs to the sysadmin fixed server role. If the ADP project login is a member of the sysadmin role, the Database Window displays all objects owned by the dbo user, without parentheses after their names. For dbo users, all objects owned by users other than dbo are shown with the user name enclosed in parentheses after the database object name. If the ADP project login does not belong to the sysadmin role, the names of objects owned by this user are shown in the database window without parentheses. The names of objects whose owner is dbo are shown with the suffix (dbo). Objects whose owners are not ADP project login or dbo user are not shown in the Database Window.
Most logins have users for each database they have access to. However, a login can access a database even without a user in that database if the database has a dedicated user. The name of this user is guest, which is a user for logins without database accounts. The guest user is not associated with any login. You can assign permissions to the guest user just like any other user in the database. This allows you to configure rights for logins that do not have specific users in a specific database.
Members of the sysadmin fixed server role have unrestricted rights to any database on the server. Therefore, members of this role can access the database even if the database has no users.
One of the fastest and easiest ways to grant a user rights to perform functions in a database is to assign the user to a member of fixed database roles. There are nine fixed database roles in SQL Server, and they are the same for SQL Server 7.0 and SQL Server 2000. The names and brief descriptions of the fixed database roles are shown in the following table. You can run the sp_helpdbfixedrole system stored procedure to obtain a list of fixed database role names. Calling the sp_dbfixedrolepermission system stored procedure will return a table set with specific permissions for each fixed database role.
|
Fixed database role name |
Description of the fixed database role |
| db_owner | Unlimited permissions in the database. |
| db_accessadmin | To add and remove database users. |
| db_datareader | For reading from database tables and views. |
| db_datawriter | To add (insert), edit (update) and delete (delete) records of database tables and views. |
| db_ddladmin | To execute any SQL Data Definition Language statement (or perform these functions using a GUI) on a database. |
| db_securityadmin | To manage user role membership, object access permission, and database ownership. |
| db_backupoperator | To create backup copies (backing) and restore from them (restoring) databases. |
| db_denydatareader | To deny (or revoke) permission to make any SELECT from a specific database object. |
| db_denydatawriter | To deny (or revoke) permission to any INSERT, UPDATE, or DELETE statement performed on a specific database object. |
Assigning fixed database roles to users affects the functions that users can perform. It is possible to change the impact of a user's membership in a fixed database role by assigning permissions to the user on specific database objects. For simplicity, this section does not cover this, but the next section covers assigning permissions to specific objects and defined by user(user-defined) database roles. The Database Window does not show any tables unless the user for the ADP project login is a member of the db_datareader fixed database role. A user without membership in the db_backupoperator role cannot execute the Backup or Restore menu commands (Tools menu, Database Utilities submenu). Accordingly, users who are not members of the db_ddladmin fixed database role cannot create tables or other objects in the database. Using the Database Window, a user without membership in the db_ddladmin role can edit server objects such as tables and views, or create new ones; however, edited and created objects will not be saved to the database.
Members of the db_owner fixed database role can perform all the functions provided by membership in any other database role. Because logins in this role can create databases without membership in the sysadmin fixed server role, members of the db_owner group are not required to be dbo users. Subsequently, members of the sysadmin fixed server role can perform any functions in the database that a member of the db_owner group can perform. Additionally, tables and views created by the dbo user have improved visibility compared to tables and views created by members of the db_owner role who are not members of the sysadmin fixed server role.
User-defined database roles and permission assignments
The SQL Server security specification allows six types of permissions for specific database objects. Database administrators can set permission to access specific objects by creating user-defined database roles and adding users to these groups. They can then assign permissions to specific objects in those roles. All members of any of these special roles inherit the permissions of those roles. Database administrators can add and remove users from roles after they are created, and change permissions for roles.
The six object permission names and their descriptions are shown in the following table. You can assign these permissions to specific database objects. If you want to grant permissions to all objects, you will have to assign permission to each database object. However, you are better off using fixed database roles if you need to grant permission to all database objects. For example, if you want to give SELECT permission to all tables and views (including those that have not yet been created), it is easier to assign the user to the db_datareader fixed database role. If your goal is to limit SELECT permission to only one or two existing views, then create a user-defined database role, grant SELECT permission to those views, and include in that role those users who need to be given special permissions.
|
Permission name |
Permission Description |
| SELECT | View records in a table or view. |
| INSERT | Add new records to a table or view. |
| UPDATE | Changing the contents of table or view records. |
| DELETE | Deleting records from tables or views. |
| REFERENCES | Allows you to create foreign keys to a primary key or unique index of a table or user-defined table function(row-returning user-defined function). |
| EXECUTE | Execute a stored procedure or user-defined function. |
Permissions to specific database objects can have three states: granted, denied, and revoked. If you grant access to an object, the permission value will have the status granted. Accordingly, if you deny access, the permission will be denied. If you are revoking a permission, change the permissions from granted or denied to revoked. Revoked permissions do not allow or deny access to an object.
Because it is possible for a user to be included in multiple fixed and user-defined database roles, permission conflicts between roles are possible. You can use these conflicts to fine-tune your security system. For example, a user is included in the db_datareader fixed database role, which gives the user the right to execute SELECT queries on all tables and views in the database. However, you can deny reading from the sales table to a user who has permission to read data from all tables in order to hide sensitive information in the sales table from him. Accordingly, members of the db_denydatareader fixed database role cannot issue SELECT queries against any table in the database, even if users are included in a user-defined database role that grants read permission from some specific tables. The denied permission always overrides the granted permission.
SQL Server makes it easier to manage security by assigning permissions to groups of users through their membership in user-defined and fixed database roles. Remember that each user usually corresponds to a specific login. In turn, logins can correspond to specific Windows users. However, one login can also be part of a group Windows users. Therefore, if you already have a Windows group for which you want to set the same security settings, then create a login for the Windows group. Then create a user for this login. Any permissions you define for this user apply to all members of the corresponding Windows group.
SQL Server Security Programming
SQL-DMO is a container application (Automation application), the purpose of which is to administer SQL Server. Because SQL-DMO is an API for SQL Server Enterprise Manager, you can program everything in SQL-DMO that the Enterprise Manager graphical tools can provide you with, including managing all aspects of SQL Server security. This aspect of SQL-DMO is especially useful for projects using MSDE and MSDE 2000 because they do not include Enterprise Manager. Additionally, Access 2002 programmers will need to learn some software solutions for managing security, since Access 2002 does not have the ability to manage security through menus (as did Access 2000).
In Access, you can program using SQL-DMO the same way you use any other COM object. Your VBA project must use the SQL-DMO library. Microsoft SQLDMO Object Library is the name of this object library. Add a link to the library using the References command (Tools menu). The library DLL file is included with MSDE and MSDE 2000. Other versions of SQL Server include the DLL and a Help file that you can open directly from the Visual Basic Editor (VBE) window. Those using Microsoft Office XP Developer Edition can install a version of SQL Server that includes a help file for SQL-DMO.
The figure shows a fragment of the hierarchical SQL-DMO model with objects for code examples located further in the text. Notice that the SQLServer object is at the top of the hierarchy. Many applications can connect to SQL Server through the SQLServer object. The SQLServer object's descendants are collections of objects for logins and databases, as well as individual objects such as IntegratedSecurity, which provide the ability to manage SQL Server security. The following example uses the SecurityMode property of the IntegratedSecurity object to demonstrate how to set the SQL Server authentication mode. Members of the Databases collection are individual databases, each of which has its own collections and objects. One of these collections is the Users collection. Another collection is used for fixed and user-defined database roles (DatabaseRoles). Individual users' membership in the DatabaseRoles collection determines the users' ability to read and modify objects in the Tables and Views collections.
Drawing. A fragment of the SQL-DMO object model related to SQL Server security
Before we talk about SQL-DMO, it is important to know that the DLL for SQL-DMO is different for SQL Server 7 and SQL Server 2000. The two versions of SQL-DMO differ in at least two aspects. First, the version for SQL Server 2000 includes new objects introduced in SQL Server 2000, as well as improvements to traditional ones. SQL-DMO for SQL Server 2000 also includes traditional SQL Server 7.0 management objects. Object names for 2000 usually end with 2 (for example, SQLServer2 for new objects, instead of SQLServer for traditional ones). Second, programs that use SQL-DMO for SQL Server 2000 cannot be controlled by SQL Server 7.0 (even if the programs use traditional SQL-DMO object names). However, you can manage SQL Server 2000 by using the SQL-DMO library for SQL Server 7.0. This limitation is a result of changes in library file formats when moving to a new version; The SQL-DMO script syntax has not changed.
The difference between versions of SQL-DMO requires careful attention to which version of the library you will use in your project. If you want your SQL-DMO application to work with SQL Server 7.0 and SQL Server 2000, you must write programs using SQL-DMO for SQL Server 7.0. On the other hand, if you need to take advantage of SQL Server 2000, then you need to develop your application using the library for SQL Server 2000. Since the article demonstrates security programming techniques using SQL-DMO, I used the SQL Server 7.0 version of SQL-DMO. Either way, the code looks the same for different versions, unless you are using new objects introduced in SQL Server 2000.
Connecting to SQL server
Just as you can connect to SQL Server in two ways using the Connection Properties dialog in an ADP project, you can connect to SQL Server in two ways through SQL-DMO. One method corresponds to authentication using SQL Server. Using this method, your code must send the server name, login, and password via SQL-DMO to the server. You can use the server name parameter to specify different instances of SQL Server on your local workstation or on another workstation on the network. SQL-DMO also allows you to connect by specifying only the server name. In this case, SQL-DMO sends the ID of the user authorized in Windows to the SQL server instance. To use this method, you must set the server's LoginSecure property to True.
The following listing shows a pair of procedures that demonstrate the syntax for connecting to an instance of SQL Server using a SQL Server login. The first procedure defines three string parameters for the server name (srvname), login (suid) and password (pwd). It then sends them to the second procedure, which begins by initializing the SQLServer object. This object represents a server instance. The second procedure then calls the Connect method of the SQLServer object. This method takes three parameters as input. The syntax for passing the variables server name, login and password is demonstrated.
| Sub CallSQLDMOSQLServerLogin() Dim srvname As String Dim suid As String Dim pwd As String "Defining arguments for SQL server login "Calling the connection procedure using the SQL server login method Sub SQLDMOSQLServerLogin(srvname As String, _ "Calling the Connect method to connect using the SQL server login method "Clearing variables The following code example demonstrates the syntax for connecting to a SQL Server instance using the Windows login. Sub CallSQLDMOWindowsLogin() SQLDMOWindowsLogin srvname Sub SQLDMOWindowsLogin(srvname As String) "Server instance "Set the LoginSecure property before calling "Clearing variables |
Changing the authentication mode
One of the major benefits that SQL-DMO provides to MSDE and MSDE 2000 programmers is that it provides a feature that would not otherwise be available to them. This is because Enterprise Manager is not included in the MSDE or MSDE 2000 installation package. For example, the SQL Server Enterprise Manager client component allows administrators to graphically change the server authentication mode: Windows authentication or mixed mode. The ADP project does not provide this option. However, the following couple of procedures will allow you to change the server's authentication mode without even being able to use Enterprise Manager.
The first procedure determines the value of one parameter and sends it to the second procedure. This parameter specifies one of two authentication modes. The procedure comments show the names of two predefined constants according to the authentication mode. Mixed mode authentication is the default mode. SQL-DMO allows you to set an authentication mode that is not available even in Enterprise Manager. In other words, you can use SQL-DMO to tell the server to accept only SQL Server logins.
The second procedure connects to the server using a Windows user ID. It then sets the SecurityMode property of the IntegratedSecurity object for the server to the value of the parameter passed to it from the first procedure. If the value of this property changes the authentication mode, the mode will not change until you stop and restart the server. However, calling the Stop method of the server object cannot immediately stop the server. You must wait until the server stops. Using the Status property of the server object, your program can monitor for a message that the server has stopped. The procedure uses a loop, waiting for the Status value to change to SQLDMOSvc_Stopped. Next, the procedure executes the Start method of the SQLServer object. Calling this method will set a new authentication mode for the server.
| Sub CallChangeServerAuthenticationMode() Dim constAuth As Byte "Set constAuth to: "Set default value for constAuth "call a procedure to change the authentication mode Sub ChangeSeverAuthenticationMode(constAuth As Byte) "Set the server name; "an instance of the SQLServer object for the connection "Set the SecurityMode property for Windows "We call the command to stop and wait "Restarting the server with a mixed authentication type "Clean up |
Opening of the ADP project
Sometimes it is necessary to programmatically open an ADP project from your SQL-DMO application. ADP project opening technology does not require the use of SQL-DMO, however, your SQL-DMO application can take advantage of this capability. The last code example in this article (right before the final section) demonstrates how to create a new user. The need to open an ADP project is based on the generation of a connection string when installing an application based on an ADP project.
The following code example demonstrates how to open an existing ADP project. In the code below, the ADP project name is msdn_test_security.adp. The sample code allows you to open an ADP project in Windows authentication mode or SQL Server authentication mode. Again two procedures are used. The list of parameters in the first procedure - CallOpenADPWindowsOrSQLServer - is relatively large, which is due to the need to specify the server name, database, path and name of the ADP project file, SQL Server login and password, as well as a logical variable. The Boolean variable determines whether the project is opened with Windows or SQL Server authentication. When specifying Windows authentication, there is no need for a login and password because the ADP project automatically references the Windows user ID. The second procedure uses these parameters to open an ADP project.
Before describing the second procedure, it would be useful to explain some features of the code. First, the procedure that opens the ADP project is already running in the Access session. Therefore, we need to create a new instance of Access for the project that we intend to open. To create an Access session, an OLE class is used.
Secondly, any existing ADP project always opens with the server connection parameters saved during the previous opening. Therefore, the program must open the project using the old connection parameters, and then change these parameters. New connection settings can only be assigned to an open project. After closing the project, the new connection parameters are saved until the user changes them in the Connection Parameters dialog. (However, the Office object model allows you to hide and disable the Connection menu command on the File menu.)
Third, a session with an open ADP project will live as long as the variable representing that session lives. Use the Public description in the session definition for the project being opened. The variable is declared in the module description section. This will allow the session to live as long as the project that opens it, that is, our code, lives. The Access session variable in the example is named appAccess, and the following declaration is in the standard module definitions section. Since the declaration must be at the top of the module, you will not see it in the code for either the first or second procedure.
Public appAccess as Access.Application
The second procedure, OpenADPWindowsOrSQLServer, begins with a message asking whether to leave the session open after the procedure completes. If the user answers "no", the procedure will set the connection parameters for the project and then close the session without a chance to view the changes. Next, the project starts a new Access session using the CreateObject function. The arguments to this function vary depending on the version of Access you are using (the code describes the specifics of using Access 2000 or Access 2002). After creating a session for a project, the procedure calls the OpenAccessProject method to add the ADP project to the session. The argument to this method is a path along with the name of the project file. The .adp extension is optional. After the project is opened, the procedure overrides its connection settings using the OpenConnection method of the CurrentProject object. The procedure redefines connections using two various strings connections depending on the value of the bolWindowsLogin variable. This Boolean variable is set by the first procedure to indicate what type of authorization needs to be set for the project. The last step of the procedure closes the session if the user answered yes to the question in the message box before opening the session.
| Sub CallOpenADPWindowsOrSQLServer() Dim srvname As String Dim dbname As String Dim path As String Dim prname As String Dim suid As String Dim pwd As String "Setting options for opening an ADP project "Calling the procedure for opening the ADP prname project Dim bolLeaveOpen As Boolean "Leave the project open after the procedure is completed? "Access session object definition (use .9 for Access 2000 "Open the project with the login and password of the last launch "Set up a new login for Windows or SQL Server authentication; "We close the session, or we stop |
Adding and removing logins
There are three ways to connect to SQL Server. These three types include one type that is checked by SQL Server and two other types that are checked by Windows. The two types that Windows checks are logins for individual user IDs and logins for groups of Windows users. If you already have a Windows user group, all of whose members need to be given the same rights to SQL server resources, you only need to set the login to the Windows group. This will save you from having to create a login for each group member.
The login creation example demonstrates the syntax for creating a standard SQL Server login and a login based on a Windows user group. The code for creating a SQL Server login for a Windows group is almost the same as the code for creating a login for an individual Windows user. Therefore, the following example of creating a login for a Windows group is exactly the same for creating logins for individual Windows users.
The example of creating logins consists of two procedures. The first procedure defines the parameters for creating a login on SQL Server, with which the second procedure will create new logins. The third procedure is a function that converts the value of an internal constant into a string describing the name of the constant.
The second procedure, after connecting to the server, initializes the login object. This object is a member of the Logins collection shown in the figure. Next, the procedure sets three parameters for the login: its name (login_name), default database (defaulf_db_name) and password (password). Simple assignment operations operate on the first two arguments. To set a password for the SQL server login, the SetPassword method of the login object is called. When setting a password for a new login, use an empty string as the first argument to the method; specify the password for the new login as the second argument. In our example, the password for login_name is defined as "password" (without quotes). Adding the login_name login is completed by calling the Add method of the Logins collection.
You can also use the SetPassword method to override the password of an existing login. The first argument of the SetPassword method in this case is the current password. However, if you do not know the current password, you can specify an empty string as the first argument, just like when creating a password for a new login. Creating or changing a login is possible if your current login (under which you connected to the server) is included in one of the fixed server roles: sysadmin or securityadmin. It doesn’t matter how you change the password: programmatically or by other means. The example uses the sa login, which is part of the sysadmin fixed server role.
The next few lines of the second procedure create a login for the Windows group named msdn_OS_users. In my case this Windows group of three Windows user IDs, in reality the Windows group could be either a local computer user group or a domain user group Windows server. The login creation process begins by initializing a new login object (lgn1). If you try to reuse lgn1 without reinitializing (zeroing), a runtime error will occur reminding you that a new object is required. The procedure uses a two-part Windows group name. The first part is the Windows server name, the second is the Windows server group name. The backslash serves as a delimiter. The default database for the second login is again the your_db_name database. Since the second login is created for Windows authentication, there is no need to set a password. However, you must set the Type property to SQLDMOLogin_NTGroup. This value corresponds to an internal constant corresponding to the Windows group login. When adding a SQL Server login, you do not need to set the Type property because it is the default for SQL Server logins. The creation of the second login ends by calling the Add method of the Logins collection.
After adding two new logins, the procedure prints the names and types of all server logins. Type property corresponds to the value of the internal constants. To make the display of types readable, a function is used that converts constants into corresponding text names.
After printing the members of the Logins collection, the procedure deletes two newly created logins by name: login_name and YOUR_SERVER_NAME\msdn_OS_users. This example demonstrates the use of the Logins collection, its Remove method, and the Name property of the login that you want to remove. To confirm the deletion of two logins, the procedure displays the list of logins again from the Logins collection. Open the Immediate window and compare the two lists of logins: after adding and removing two logins.
| Sub CallLoginDemo() Dim srvname As String Dim suid As String Dim pwd As String "set parameters for login when authenticating with SQL server "Calling the procedure for creating logins Sub LoginDemo(srvname As String, _ suid As String, pwd As String) "Initializing a server object "Calling the Connect method "Initializing the login object "Adding a SQL Server login srv1.Logins.Add lgn1 "Initializing the login for reuse "Add a login for the Windows group "Displaying all logins after adding two new ones "Deleting newly added logins "Re-displaying all logins "Clearing variables Function DecodeLoginType(lgn_type As Byte) As String Select Case lgn_type |
Creating a login with a user who is a member of the db_datareader role
Programmatically adding a new login and assigning it to a default database does not create a user in the database for that login. Therefore, an ADP project based on this login cannot connect to the default database. In order for the project to work with the database, you must create a user in the database for your login.
The final code example of the article demonstrates the procedure for adding a new SQL Server authentication login and also creating a user for that login in the your_db_name database. In this example, the new user is also assigned to the db_datareader fixed database role. Finally, the ADP project is opened based on the new login and user.
After connecting to the server and creating a new login_name, the code creates a new user. Next, the Name and Login properties are assigned to the user. The Login property means the login under which the user connects - login_name in our example. The user is then ready to be added to the Users collection of the database. Note that the syntax for adding a new user follows the syntax for the object hierarchy. The Add method for a new user is applied to the Users collection, which in turn belongs to the database object. Database is a member of the Databases collection owned by the server object (SQLserver). After creating a user, one or more lines are required to add the user to the db_datareader fixed database role. The method for adding a user to a role is called AddMember. In our case, the AddMember method of the DatabaseRoles collection element adds a user to a database role. This method takes as a parameter the string expression of the Name property of the new user object.
Once the security configuration for the new login and user is completed, the OpenADPWindowsOrSQLServer procedure is called. The previous listings provide an example of how to use this procedure. In our case, the msdn_test_security.adp file is opened based on the login_name. To check the functionality of the login, open the your_db_name database tables. Additionally, the functionality of the new login can be checked in the Connection Parameters dialog box by specifying the login your_login.
If you try to run the MakeLoginWithDatareaderUser procedure again, the attempt will fail. This is because the login your_login already exists. An error will occur when you try to add it again. An attempt to run the procedure may fail for another reason: You have not closed the project connected to the database with this login. You can write standard VBA error handling to handle runtime errors. Similar errors may accompany the execution of the previous examples. I chose not to go into a discussion of runtime error detection, focusing on SQL server security issues.
| Sub MakeLoginWithDatareaderUser() Dim srv1 As SQLDMO.SQLServer Dim lgn1 As SQLDMO.Login Dim usr1 As SQLDMO.User Dim srvname As String Dim suid As String Dim pwd As String Dim dbname As String Dim path As String Dim prname As String Dim bolWindowsLogin As Boolean "Defining the arguments "initialize the server "Call the Connect method for login when authenticating with the SQL server "Initialize and add the login object "Initialize and add the user object "Set parameters for opening an ADP project prpath = "Path_to_project_file" "This argument controls the use of the Windows login "Calling the project opening procedure prname "We leave the object open for viewing |
Conclusion
SQL Server security is built on different principles than those used by Jet database programmers. However, SQL Server Books Online carefully documents SQL Server security guidelines. This article translates the basic BOL rules into specific rules whose purpose is to use an Access project with SQL Server. The code examples in this article demonstrate how to manage SQL Server security using Access.
Posted by Mike Weiner
Co-author: Burzin Patel
Editors: Lubor Kollar, Kevin Cox, Bill Emmert, Greg Husemeier, Paul Burpo, Joseph Sack, Denny Lee , Sanjay Mishra, Lindsey Allen, Mark Souza
Microsoft SQL Server 2008 contains a number of improvements and new functionality that extend the functionality of previous versions. Administering and maintaining databases, maintaining manageability, availability, security, and performance are all the responsibilities of a database administrator. This article describes the ten most useful new features in SQL Server 2008 (in alphabetical order) that make your job as a DBA easier. In addition to a brief description, for each of the functions possible situations of its use are given and important recommendations by use.
Activity Monitor
When troubleshooting performance issues or monitoring a server in real time, an administrator will typically run a series of scripts or check relevant information sources to gather overall data about the processes running and identify the cause of the problem. SQL Server 2008 Activity Monitor brings this information together to provide at-a-glance information about running and recently running processes. The database administrator can both view high-level information and analyze any of the processes in more detail and view wait statistics, which makes it easier to identify and resolve problems.
To open Activity Monitor, right-click the registered server name in Object Explorer, then select Activity Monitor or use the standard toolbar icon in SQL Server Management Studio. Activity Monitor offers the administrator an overview section similar in appearance to the Windows Task Manager, as well as components for detailed viewing of individual processes, resource waits, data file I/O, and recent resource-intensive requests, as shown in Figure. 1.
Rice. 1:Activity Monitor ViewSQL Server2008 in WednesdayManagement Studio
Note. Activity Monitor uses a data refresh frequency setting that can be changed by right-clicking. When selecting frequent data updates (every less than 10 seconds), the performance of a highly loaded working system may go down.
Using Activity Monitor, an administrator can also perform the following tasks:
· Pause and resume activity monitor with one right-click. This allows the administrator to "save" state information to a specific point in time and will not be updated or overwritten. But do not forget that when updating data manually, expanding or collapsing a section, the old data will be updated and lost.
· Right-click a row item to display the full query text or a graphical execution plan using the Recent High Queries menu item.
· Trace with Profiler or terminate processes in the Processes view. Profiler application events include events RPC:Completed, SQL:BatchStarting And SQL:BatchCompleted, and AuditLogin And AuditLogout.
Activity Monitor also allows you to monitor the activity of any local or remote instance of SQL Server 2005 that is registered with SQL Server Management Studio.
Audit
The ability to monitor and record events that occur, including who is accessing objects and the timing and content of changes, helps an administrator ensure compliance with regulatory or organizational security compliance standards. In addition, understanding the events occurring in the environment can also help in developing a plan to reduce risks and maintain a safe environment.
In SQL Server 2008 (Enterprise and Developer editions only), SQL Server Audit provides automation that allows administrators and other users to prepare, save, and view audits various components servers and databases. The function provides the ability to audit with detail at the server or database level.
There are server-level audit action groups, such as the following:
· FAILED_LOGIN_GROUP tracks failed login attempts.
· BACKUP_RESTORE_GROUP reports when the database was backed up or restored.
· DATABASE_CHANGE_GROUP audits the time a database was created, modified, or deleted.
Database-level audit action groups include the following:
· DATABASE_OBJECT_ACCESS_GROUP is called whenever a CREATE, ALTER, or DROP statement is executed on a database object.
· DATABASE_OBJECT_PERMISSION_CHANGE_GROUP is called when GRANT, REVOKE, or DENY statements are used on database objects.
There are other audit actions such as SELECT, DELETE and EXECUTE. For more information, including a complete list of all audit groups and actions, see SQL Server Audit Action Groups and Actions.
Audit results can be sent to a file or event log (System Log or Windows Security Event Log) for later review. Audit data is created using Extended Events- another new feature in SQL Server 2008.
SQL Server 2008 audits allow an administrator to answer questions that were previously very difficult to answer after the fact, such as, “Who dropped this index?”, “When was the stored procedure modified?”, “What change was made that might prevent a user from accessing this table?” ? and even "Who executed the SELECT or UPDATE statement on the table [ dbo.Payroll] ?».
For more information about using SQL Server auditing and examples of how to implement it, see the SQL Server 2008 Compliance Guide.
Backup compression
DBAs have been proposing to include this feature in SQL Server for a long time. Now it's done, and just in time! Recently, for a number of reasons, for example, due to the increased duration of data storage and the need to physically store more data, database sizes have begun to grow exponentially. When backing up a large database, you must allocate a significant amount of disk space for the backup files, as well as allocate a significant amount of time for the operation.
When you use SQL Server 2008 backup compression, the backup file is compressed as it is written, which not only requires less disk space, but also reduces I/O operations and takes less time to complete the backup. In lab tests with real user data, a 70-85% reduction in backup file size was observed in many cases. In addition, tests showed that the duration of copy and restore operations was reduced by approximately 45%. It should be noted that additional processing When compressed, it increases the load on processors. To separate the time-intensive copying process from other processes and minimize its impact on their operation, you can use another function described in this document - Resource Governor.
Compression is enabled by adding the WITH COMPRESSION clause to the BACKUP command (see SQL Server Books Online for more information) or by setting this option on page Options dialog box Database backup. To avoid the need to make changes to all existing backup scripts, a global setting has been implemented that enables compression of all backups created on a server instance by default. (This option is available on the page Database Settings dialog box Server properties; it can also be installed by executing the stored procedure sp_ configure with parameter value backupcompressiondefault, equal to 1). The backup command requires an explicit compression option, and the restore command automatically recognizes the compressed backup and decompresses it when restoring.
Backup compression is an extremely useful feature that saves disk space and time. For more information about configuring backup compression, see the technical note Tuning backup compression performance inSQL Server 2008 . Note. Compressed backups are supported only in SQL Server 2008 Enterprise and Developer editions, but all editions of SQL Server 2008 allow you to restore compressed backups.
Centralized management servers
Often a DBA manages many instances of SQL Server at once. The ability to centralize management and administration of many SQL instances into a single location saves significant effort and time. The centralized management server implementation, available in SQL Server Management Studio through the Registered Servers component, allows an administrator to perform various administrative operations on many SQL Servers from a single management console.
Centralized management servers allow the administrator to register a group of servers and perform the following operations on them as a single group, for example:
· Multi-server query execution: You can now run a script on multiple SQL Servers from a single source, and the data will be returned to that source, without having to log into each server separately. This can be especially useful when you need to view or compare data from multiple SQL Servers without running a distributed query. Additionally, as long as previous versions of SQL Server support the query syntax, a query run from the SQL Server 2008 Query Editor can also run on instances of SQL Server 2005 and SQL Server 2000. For more information, see the SQL Server Manageability Working Group blog at Running Multi-Server queries in SQL Server 2008 environment.
· Import and define policies on many servers: within functionality Policy-based management(another new feature in SQL Server 2008, also described in this article), SQL Server 2008 provides the ability to import policy files into individual groups of central management servers and allows you to define policies on all servers registered in a particular group.
· Manage services and access SQL Server Configuration Manager: The Servers from Central Management tool helps you create a control center where the DBA can view and even change (with the appropriate permissions) the state of services.
· Import and export of registered servers: Servers registered with Central Management Servers can be exported and imported when transferred between administrators or different installations of SQL Server Management Studio. This feature provides an alternative to an administrator importing or exporting his or her own local groups in SQL Server Management Studio.
Keep in mind that permissions are enforced using Windows Authentication, so user rights and permissions may vary across different servers that are registered in the Central Management Server group. For more information, see Administering Multiple Servers with Central Management Servers and Kimberly Tripp's blog: Central SQL Management ServersServer2008 - are you familiar with them?
Data Collector and Management Data Store
Performance tuning and diagnostics are time-consuming and may require advanced SQL Server skills as well as an understanding of the internal structure of databases. System Monitor Windows (Perfmon), SQL Server Profiler, and dynamic management views solved some of these problems, but they often had an impact on the server, were labor intensive to use, or involved methods for collecting disparate data that made them difficult to combine and interpret.
To provide clear, actionable information about system performance, SQL Server 2008 provides a fully extensible performance data collection and storage tool, the Data Collector. It contains several out-of-the-box data collection agents, a centralized repository of performance data called a management data warehouse, and several pre-built reports to present the collected data. Data Collector is a scalable tool that collects and aggregates data from multiple sources, such as Dynamic Management Views, Perfmon Performance Monitor, and Transact-SQL queries, at a fully configurable data collection frequency. The data collector can be extended to collect data on any measurable application attribute.
Another useful feature of the management data warehouse is the ability to install it on any SQL Server and then collect data from one or more SQL Server instances. This minimizes the impact on the performance of production systems, and also improves scalability in the context of monitoring and collecting data from many servers. In laboratory tests, the observed loss bandwidth when running agents and running the management data warehouse on a busy server (using an OLTP workload) was approximately 4%. The performance loss can vary depending on the frequency of data collection (the test mentioned was conducted under an extended workload, with data transferred to storage every 15 minutes), and it can also increase sharply during data collection periods. In any case, you should expect some reduction in available resources because the DCExec.exe process uses a certain amount of memory and CPU resources, and writing to the management data store will increase the load on the I/O subsystem and require space allocation in the location of the data and log files. In the diagram (Figure 2) shows a typical data collector report.
Rice. 2:Data Collector Report ViewSQL Server 2008
The report shows SQL Server activity during the data collection period. It collects and displays events such as waits, CPU, I/O and memory usage, as well as statistics on resource-intensive queries. The administrator can also drill down into report items, focusing on a single query or operation to investigate, identify, and resolve performance issues. These data collection, storage, and reporting capabilities enable proactive monitoring of the health of the SQLServers in your environment. When needed, they allow you to look back at historical data to understand and evaluate changes that impacted performance over the period being monitored. The Data Collector and Management Data Store are supported in all editions of SQLServer 2008 except SQLServerExpress.
Data compression
The simplicity of database management greatly simplifies routine administration tasks. As tables, indexes, and files grow in size and very large databases (VLDBs) become more common, managing data and working with large files becomes increasingly complex. In addition, the increasing demand for memory and physical I/O bandwidth with the volume of data requested also increases administrative complexity and costs the organization. As a result, in many cases, administrators and organizations must either expand the memory or I/O bandwidth of their servers, or accept reduced performance.
Data compression, introduced in SQL Server 2008, helps resolve these issues. This feature allows the administrator to selectively compress any table, table partition, or index, thereby reducing disk space, memory footprint, and I/O operations. Compressing and decompressing data loads the processor; however, in many cases the additional CPU load is more than offset by the I/O gains. In configurations where I/O is a bottleneck, data compression can also provide performance gains.
In some lab tests, enabling data compression resulted in 50-80% disk space savings. Space savings varied widely: if the data contained few duplicate values, or the values used all the bytes allocated for the specified data type, the savings were minimal. However, the performance of many workloads did not increase. However, when working with data containing a lot of numeric data and a lot of repeating values, significant disk space savings and performance gains ranging from a few percent to 40-60% were observed for some sample query workloads.
SQLServer 2008 supports two types of compression: row compression, which compresses individual table columns, and page compression, which compresses pages of data using row, prefix, and dictionary compression. The level of compression achieved varies greatly depending on the data types and the contents of the database. In general, using row compression reduces the overhead on application operations, but also reduces the compression ratio, meaning less space is saved. At the same time, page compression results in more application overhead and CPU usage, but it also saves significantly more space. Page compression is a superset of row compression, that is, if an object or section of an object is compressed using page compression, row compression is also applied to it. Additionally, SQLServer 2008 supports the storage format vardecimal from SQL Server 2005 SP2. Please note that since this format is a subset of string compression, it is deprecated and will be removed from future versions of the product.
Both row compression and page compression can be enabled on a table or index online without impacting data availability to applications. At the same time, it is impossible to compress or decompress a separate section of a partitioned table in online mode without disabling it. Tests have shown that the best approach is a combined approach, in which only a few are compressed. largest tables: This achieves an excellent ratio of disk space savings (significant) to performance loss (minimal). Because a compact operation, like index creation or rebuild operations, also has requirements for available disk space, compaction must be performed with these requirements in mind. A minimum of free space will be required during the compression process if the compression starts with the smallest objects.
You can compress data using Transact-SQL statements or the Data Compression Wizard. To determine how an object might change in size when it shrinks, you can use the system stored procedure sp_estimate_data_compression_savings or data compression wizard. Database compression is only supported in SQLServer 2008 Enterprise and Developer editions. It is implemented exclusively in the databases themselves and does not require any changes to the applications.
For more information about using compression, see Creating Compressed Tables and Indexes.
Policy-based management
In many business scenarios, it is necessary to maintain specific configurations or enforce policies, either on a specific SQLServer or multiple times across a group of SQLServers. An administrator or organization may require a specific naming scheme to be applied to all user tables or stored procedures that are created, or may require certain configuration changes to be applied to multiple servers.
Policy-based management (PBM) provides the administrator with a broad range of control over the environment. Policies can be created and checked for compliance. If a scan target (such as a database engine, database, table, or SQLServer index) does not meet the requirements, the administrator can automatically reconfigure it to meet those requirements. There are also a number of policy modes (many of which are automated) that make it easy to verify policy compliance, log policy violations and send notifications, and even roll back changes to ensure policy compliance. For more information about definition modes and how they relate to aspects (the concept of policy-based management (PBM), also discussed in this blog), see the SQL Server Policy-Based Management Blog.
Policies can be exported and imported as XML files to be defined and applied across multiple server instances. Additionally, in SQLServerManagement Studio and the Registered Servers view, you can define policies across multiple servers that are registered in a local server group or a central management server group.
Previous versions of SQL Server may not provide all policy-based management functionality. However, the function making report policies can be used on SQL Server 2005 and SQL Server 2000 servers. For more information about using policy-based management, see Administering Servers Using Policy-Based Management in SQLServer Books Online. For more information about the policy technology itself, with examples, see the SQL Server 2008 Compliance Guide.
Predicted performance and concurrency
Many administrators face significant challenges supporting SQLServers with constantly changing workloads and ensuring predictable levels of performance (or minimizing variations in query plans and performance). Unexpected query performance changes, changes in query plans, and/or general performance issues can be caused by a number of reasons, including increased load from applications running on SQLServer or an upgrade to the version of the database itself. Predictability of queries and operations running on SQLServer makes it much easier to achieve and maintain availability, performance, and/or business continuity goals (meeting service level agreements and operational support level agreements).
In SQLServer 2008, several features have been changed to improve predictable performance. Thus, certain changes have been made to the plan structures of SQLServer 2005 ( consolidation of plans) and added the ability to manage lock escalation at the table level. Both improvements promote more predictable and streamlined interactions between the application and the database.
First, plan structures (Plan Guide):
SQL Server 2005 introduced improvements to query stability and predictability through a new feature at the time called “plan guides,” which provided instructions for running queries that could not be changed directly in the application. For more information, see the whitepaper Enforcing Query Plans. Although the USE PLAN query hint is a very powerful feature, it only supported DML SELECT operations and was often awkward to use due to the sensitivity of plan structures to formatting.
SQL Server 2008 expands the plan guide engine in two ways: first, it expands support for the USE PLAN query hint, which is now compatible with all DML statements (INSERT, UPDATE, DELETE, MERGE); secondly, introduced new feature consolidation of plans, which allows you to directly create a plan outline (pinning) of any query plan that exists in the SQL Server plan cache, as shown in the following example.
sp_create_plan_guide_from_handle
@name = N'MyQueryPlan',
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
A plan guide created in any way has a database area; it is stored in a table sys.plan_guides. Plan structures only affect the query plan selection process of the optimizer, but do not eliminate the need to compile the query. Also added function sys.fn_validate_plan_guide, to review existing SQL Server 2005 plan structures and ensure they are compatible with SQL Server 2008. Plan pinning is available in SQL Server 2008 Standard, Enterprise, and Developer editions.
Secondly, escalation of blocking:
Blocking escalation often caused blocking problems and sometimes even deadlocks. The administrator had to fix these problems. In previous versions of SQLServer, it was possible to control lock escalation (trace flags 1211 and 1224), but this was only possible at instance-level granularity. For some applications this fixed the problem, but for others it caused even more problems. Another flaw in SQL Server 2005's lock escalation algorithm was that locks on partitioned tables were escalated directly to the table level rather than to the partition level.
SQLServer 2008 offers a solution to both problems. It introduces a new setting that allows you to control lock escalation at the table level. Using the ALTERTABLE command, you can choose to disable escalation or escalate to the partition level for partitioned tables. Both of these features improve scalability and performance without unwanted side effects affecting other objects in the instance. Lock escalation is set at the database object level and does not require any application changes. It is supported in all editions of SQLServer 2008.
Resource Governor
Maintaining sustainable service levels by preventing runaway requests and ensuring resources are allocated to critical workloads has previously been challenging. There was no way to guarantee the allocation of a certain amount of resources to a set of requests, and there was no control over access priorities. All requests had equal rights to access all available resources.
New SQL function Server 2008's Resource Governor helps address this issue by providing the ability to differentiate workloads and allocate resources based on user needs. Resource Governor limits are easily reconfigured in real time with minimal impact to running workloads. The distribution of workloads across the resource pool is configured at the connection level, and the process is completely transparent to applications.
The diagram below shows the resource allocation process. In this scenario, three workload pools are configured (Admin, OLTP, and Report workloads) and then the OLTP workload pool is given the highest priority. At the same time, two resource pools are configured (Pool pool and Application pool) with specified limits on memory size and processor (CPU) time. The final step is to assign the Admin workload to the Admin pool, and assign the OLTP and Report workloads to the Application pool.
The following are features to consider when using the resource governor.
- Resource Governor uses the login credential, hostname, or application name as the "resource pool identifier", so using a single login per application for certain numbers of clients per server can make pooling more difficult.
- Object grouping at the database level, where access to resources is controlled based on the database objects being accessed, is not supported.
— You can only configure the use of processor and memory resources. I/O resource management is not implemented.
- Dynamic switching of workloads between resource pools after connection is not possible.
- Resource Governor is only supported in SQL Server 2008 Enterprise and Developer editions and can only be used for the SQL Server Database Engine; management of SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS) is not supported.
Transparent Data Encryption (TDE)
Many organizations pay great attention to security issues. There are many different layers that protect one of an organization's most valuable assets—its data. More often than not, organizations successfully protect the data in use through physical security measures, firewalls, and strict access restriction policies. However, in case of loss physical media With data, such as a disk or tape with a backup copy, all the listed security measures are useless, since an attacker can simply restore the database and gain full access to the data. SQL
Server 2008 offers a solution to this problem through Transparent Data Encryption (TDE). With TDE encryption, data in I/O operations is encrypted and decrypted in real time; Data and log files are encrypted using a Database Encryption Key (DEK). A DEK is a symmetric key protected by a certificate stored in the server's >master database, or an asymmetric key protected by an Extensible Key Management Module (EKM).
The TDE feature protects data at rest so that data is MDF files, NDF, and LDF cannot be viewed using a hex editor or any other method. However, active data, such as the results of a SELECT statement in SQL Server Management Studio, will remain visible to users who have permission to view the table. Additionally, because TDE is implemented at the database level, the database can use indexes and keys to optimize queries. TDE should not be confused with column level encryption - it is separate function, allowing you to encrypt even active data.
Database encryption is a one-time process that can be started with the Transact - SQL command or from SQL Server Management Studio and then runs on a background thread. Encryption or decryption status can be monitored using the dynamic management view sys.dm_database_encryption_keys. In laboratory tests, encrypting a 100 GB database using the AES _128 encryption algorithm took about an hour. Although the overhead of using TDE is determined primarily by the application workload, in some of the tests conducted this overhead was less than 5%. One thing to be aware of that may impact performance is that if TDE is used on any of the databases on the instance, then the system database is also encrypted tempDB. Finally, when using different functions at the same time, the following must be considered:
- When you use backup compression to compress an encrypted database, the size of the compressed backup will be larger than without using encryption because encrypted data is poorly compressed.
- Database encryption does not affect data compression (row or page).
TDE enables an organization to ensure compliance with regulatory standards and overall data protection levels. TDE is supported only in SQL Server 2008 Enterprise and Developer editions; its activation does not require changes to existing applications. For more information, see Data Encryption in SQL Server 2008 Enterprise Edition or the discussion in Transparent data encryption.
To summarize, SQL Server 2008 offers features, enhancements, and capabilities that make your job as a database administrator easier. The 10 most popular ones are described here, but SQL Server 2008 has many other features that make life easier for administrators and other users. For lists of "Top 10 Features" for other SQL Server verticals, see the other "Top 10... in SQL Server 2008" articles on this site. Full list of functions and their detailed description See SQL Server Books Online and the SQL Server 2008 overview website.
SQL Server Management Studio has two primary uses: administering database servers and managing database objects. These functions are discussed below.
Database server administration
Administrative tasks that you can perform using SQL Server Management Studio include, but are not limited to:
server registration;
connection to the server;
creating new server groups;
managing multiple servers;
starting and stopping servers.
These administration tasks are described in the following subsections.
Server registration
SQL Server Management Studio separates the activity of registering servers from the activity of exploring databases and their objects. (Both types of actions can be performed through the Object Explorer.) Before you can use the databases and objects of any server, whether local or remote, you must register it.
You can register the server when you first start SQL Server Management Studio or later. To register a database server, right-click the desired server in Object Explorer and select Register from the context menu. If the Object Explorer panel is hidden, open it by executing the menu command View --> Object Explorer. The New Server Registration dialog box will open as shown in the figure below:
Select the name of the server you want to register and the authentication type for that server (that is, Windows Authentication or SQL Server Authentication), and then click Save.
Connecting to the server
SQL Server Management Studio also separates the tasks of registering a server and connecting to a server. This means that when a server is registered, that server does not automatically connect. To connect to a registered server, you need to right-click the desired server in the Object Inspector window and select Connect from the context menu that appears.
Creating a new server group
To create a new server group in the Registered Servers panel, right-click the Local Server Groups node local servers) in the Registered Server window and in the context menu, select New Server Group. In the New Server Group Properties dialog box that opens, enter a unique name for the group and, optionally, its description.
Managing multiple servers
Using Object Explorer, SQL Server Management Studio allows you to administer multiple database servers (called instances) on a single computer. Each instance of the Database Server component has its own set of database objects (system and user databases) that are not shared between instances.
To manage the server and its configuration, right-click the server name in Object Explorer and select Properties from the context menu that appears. The Server Properties dialog box will open, containing several pages such as General, Security, Permissions, etc.
The General page displays general server properties:
The Security page contains information about the server's authentication mode and login audit method. The Permissions page displays all accounts and roles that have access to the server. The bottom of the page displays all the permissions that can be granted to these accounts and roles.
You can change the name of the server by giving it a new name. To do this, right-click the required server in the Object Explorer window and select Register from the context menu. You can now give the server a new name and change its description. Servers should not be renamed unless necessary, as this may affect other servers that link to them.
Starting and stopping servers
By default, the Database Engine server starts automatically when the Windows operating system starts. To start a server using SQL Server Management Studio, right-click the desired server in the Object Inspector and select Start from the context menu. This menu also contains Stop and Pause options for performing appropriate actions on the server.
Managing Databases with Object Explorer
Administrative tasks that you can perform using SQL Server Management Studio include:
creating databases without using Transact-SQL language;
modifying databases without using the Transact-SQL language;
manage tables without using Transact-SQL language;
creation and execution SQL statements(described in the next article).
Creating databases without using T-SQL
A new database can be created using the Object Browser Object Explorer. As its name suggests, Object Explorer can also be used to explore server objects. From the panel of this tool you can view all server objects and manage the server and databases. The server object hierarchy tree contains, among other folders, a Databases folder. This folder, in turn, contains several subfolders, including a folder for system databases, and a folder for each user-created database.
To create a database using Object Explorer, right-click the Databases node and select the New Database menu item. In the New Database dialog box that opens, in the Database name field, enter the name of the new database, and then click OK.
Each database has several properties, such as file type, initial size, etc. A list of database property pages is located in the left pane of the New Database dialog box. The General page of the Database Properties dialog box contains information such as the name, owner, and collation of the database, among other things:
The properties of a specific database's data files are listed on the Files page and contain information such as the name and initial size of the file, the location of the database, and the file type (for example, primary). The database can be stored in several files. SQL Server uses dynamic disk space management. This means that the database size can be configured to automatically grow and shrink as needed.
in order to change Autogrowth property(Autogrowth) On the Files page, in the Autogrowth column, click the ellipsis icon (...) and make the appropriate changes in the Change Autogrowth dialog box. To allow automatic growth of the database size, you need to set Enable Autogrowth checkbox. Whenever the existing file size is insufficient to store the added data, the server automatically requests the system to allocate additional disk space to the file. The amount of additional disk space (percentage or megabytes) is indicated in File Growth field in the same dialog box. And in the section Maximum File Size You can either limit the maximum file size by selecting the Limited to (MB) switch, or you can remove the size restrictions by selecting the Unlimited switch (this is the default setting). If the file size is limited, you must specify its allowed maximum size.
The Filegroups page of the Database Properties dialog box displays the names of the filegroups to which the database file belongs, the filegroup partition (default or explicit), and the operations allowed to be performed on the filegroup (read-write or read-only). reading).
On the Options page of the Database Properties dialog box, you can view and modify all database-level options. The parameter groups are Automatic, Containment, Cursor, Miscellaneous, Recovery, Service Broker, and State. The State group contains, for example, the following four parameters:
Database Read-OnlyAllows you to set access to the database as full access or read-only access. In the latter case, users cannot modify the data. The default value of this parameter is False.
Restrict AccessSets the number of users who can simultaneously use the database. The default value is MULTI_USER.
Database StateDescribes the state of the database. The default value for this parameter is Normal.
Encryption EnabledSpecifies the database encryption mode. The default value for this parameter is False.
The Extended Properties page displays additional properties for the current database. On this page you can delete existing properties and add new ones.
The Permissions page displays all users, roles, and associated permissions.
Other pages Change Tracking, Mirroring ( Mirror reflection) and Transaction Log Shipping describe capabilities related to data availability.
Database modification
Using Object Explorer, you can modify existing databases by changing database files and filegroups. To add new files to a database, right-click the desired database and select Properties from the context menu. In the Database Properties dialog box that opens, select the Files page and click the Add button located at the bottom of the Database files section. A new line will be added to the section, in the Logical Name field of which you should enter the name of the database file to be added, and in other fields set the necessary properties of this file. You can also add a secondary filegroup for the database by selecting the Filegroups page and clicking the Add button.
The previously mentioned database properties can only be modified by System Administrator or the owner of the database.
To delete databases using Object Explorer, right-click the name of the desired database and select Delete from the context menu that opens.
Table management
After creating the database, the next task is to create all the necessary tables. Similar to creating a database, tables in it can also be created using either Transact-SQL or Object Explorer. As with creating a database, here we'll look at creating tables using Object Explorer only.
To practice creating tables, let's create a Department table in the SampleDb database. To create a database table using Object Explorer, expand the Databases node, and then the desired database node, right-click the Tables folder and select New Table from the context menu that opens. A window will open at the top right of the Management Studio window to create columns for the new table. Enter the table column names, their data types, and the resolution of null values for each column, as shown in the top right pane in the figure below:
To select one of the system-supported data types for a column, in the Data Type column, select and then click the downward-facing triangle at the right edge of the box (this triangle appears after you select a cell). As a result, in the drop-down list that opens, select the required data type for the column.
You can change the data type of an existing column on the Column Properties tab (bottom panel in the figure). Some data types, such as char, require the length to be specified in the Length line, while others, such as decimal, require the scale and precision to be specified in the corresponding Scale and Precision lines on the Column Properties tab. Some others, such as int, do not require any of these properties to be specified. (Invalid values for a particular data type are highlighted in shaded font in the list of all possible column properties.)
To allow null values for a given column, you must set the appropriate field flag for it. Also, if a column requires a default value, it should be entered in the Default Value or Binding line of the Column Properties panel. A default value is assigned to a column cell automatically unless a value is explicitly entered for it.
The Number column is the primary key of the Department table. To make a column the primary key of a table, right-click it and select Set Primary Key from the context menu.
Once you have completed all the table creation work, click the cross on the Table Designer tab. A dialog box will open asking you whether to save your changes. Click Yes, which will open the Choose Name dialog box, asking you to enter a name for the table. Enter the desired table name and click OK. The table will be saved under the specified name. To display the new table in the database hierarchy, in the Object Explorer toolbar, click the Renew icon.
To view and change the properties of an existing table, expand the database that contains the desired table, expand the Tables node in that database, right-click the desired table, and then select Properties from the context menu. This will open the Table Properties dialog box for the table. As an example, the figure below shows the Table Properties dialog box on the General tab for the Employee table in the SampleDb database.
To rename a table, in the Tables folder, right-click it in the list of tables and select Rename from the context menu. And to delete a table, right-click it and select Delete.
Having created all four tables of the SampleDb database (Employee, Department, Project and Works_on - you can find the detailed table structure in the source code), you can use another feature of SQL Server Management Studio to display an entity-relationship diagram - diagram (ER) (entity-relationship) this database. (The process of converting database tables into an entity-relationship (ER) diagram is called reverse engineering.)
To create an ER diagram for the SampleDb database, right-click its Database Diagrams subfolder and select New Database Diagram from the context menu. If a dialog box appears asking whether to create helper objects, select Yes.
This will open the Add Table dialog box, allowing you to select tables to add to the chart. Once you've added all the necessary tables (in this case, all four), click Close and the wizard will create a chart like the one shown below:
The figure shows only the intermediate ER diagram of the SampleDb database, not the final ER diagram, because although it shows all four tables with their columns (and their corresponding primary keys), it still does not show the relationships between the tables. The relationship between two tables is based on the primary key of one of the tables and the possible corresponding column (or columns) of the other table.
There are three relationships between the tables in the SampleDb database. The Department table has a 1:N relationship with the Employee table because each value in the Department table's primary key (Number column) corresponds to one or more values in the DepartmentNumber column in the Employee table (a department can have multiple employees).
Similarly, there is a relationship between the Employee and Works_on tables because only the values that are present in the primary key column of the Employee table (Id) are also present in the EmpId column of the Works_on table. The third relationship exists between the Project and Works_on tables, because only values that are present in the primary key of the Project table (Number) are also present in the ProjectNumber column of the Works_on table.
To create these three relationships, the ER diagram must be reconstructed by specifying for each table the columns that correspond to the key columns of the other tables. This column is called foreign key. To see how this is done, let's define the DepartmentNumber column of the Employee table as a foreign key to the Department table. To do this, perform the following steps:
The other two relationships are created in a similar way. The figure below shows an ER diagram showing all three relationships between the SampleDb database tables:
First Microsoft version Servers SQL was introduced by the company back in 1988. The DBMS was immediately positioned as a relational one, which, according to the manufacturer, had three advantages:
- stored procedures, thanks to which data retrieval was accelerated and its integrity was maintained in multi-user mode;
- constant access for administration without disconnecting users;
- open server platform that allows you to create third party applications using SQL Server.
2005, codenamed Yukon with advanced scaling capabilities, was the first version to fully support .NET technology. Support for distributed data has improved, and the first reporting and information analysis tools have appeared.
Integration with the Internet made it possible to use SQL Servers 2005 as the basis for creating e-commerce systems with simple and secure access to data through popular browsers using the built-in Firewall. The Enterprise version supported parallel computing on an unlimited number of processors.
Version 2005 was replaced by Microsoft SQL Server 2008, which is still one of the most popular database servers, and a little later the next version appeared - SQL Servers 2012, with support for compatibility with the .NET Framework and other advanced information processing technologies and Visual Studio development environment. A special SQL Azure module was created to access it.
Transact-SQL
Since 1992, SQL has been the standard for accessing databases. Almost all programming languages use it to access the database, even if it seems to the user that he is working with the information directly. The basic syntax of the language remains the same to ensure compatibility, but every database management system vendor has tried to add to SQL additional functions. It was not possible to find a compromise, and after the “war of standards” two leaders remained: PL/SQL from Oracle and Transact-SQL from Microsoft Server sSQL.
T-SQL procedurally extends SQL to access Microsoft Servers SQL. But this does not exclude the development of applications on “standard” operators.
Automate your business with SQL Server 2008 R2
Reliable operation of business applications is extremely important for modern businesses. The slightest downtime of the database can lead to huge losses. The Microsoft SQL Server 2008 R2 database server allows you to reliably and securely store information of almost unlimited volume, using management tools familiar to all administrators. Supported vertical scaling up to 256 processors.
Hyper-V technology makes the most of the power of modern multi-core systems. Support on one processor for multiple virtual systems reduces costs and improves scalability.
Analyze your data
For fast real-time analysis of data streams, the SQL Server StreamInsight component is used, which is optimized for this type of task. It is possible to develop your own applications based on .NET.
Uninterrupted operation and data security
Support for optimal performance at any time is ensured by the resource regulator built into the server. The administrator can manage the load and system tools, set limits for applications on the use of processor and memory resources. Encryption functions provide flexible and transparent protection of information and maintain a log of access to it.
Unlimited database size
Data storage can be scaled quickly and securely. Users can use ready-made Fast Track Date Warehouse templates to support disk arrays up to 48 TB. The basic configuration supports equipment from leading companies such as HP, EMC and IBM. Information compression functions according to the UCS 2 standard allow you to use disk space more economically.
Improving the efficiency of developers and administrators
New software wizards allow you to quickly eliminate underutilized servers, improve control and optimize performance without the need for external third-party specialists. Monitor application and database performance, find improvements in dashboards, and speed up updates and installations.
Tools for personal business analysis
There has never been a consensus among companies as to who should do analytics—IT departments or users directly. The personal reporting system solves this problem through modern tools for secure and effective construction, analysis and modeling of business processes. Direct access to databases in Microsoft Office and SharePoint Server is supported. Corporate information can be integrated with other types of content, such as maps, graphics, and videos.
Convenient collaboration environment
Give your employees access to information, collaboration, and data analysis with the PowerPivot e-commerce app. Excel tables. The program allows you to analyze information and model business processes and publish reports for public access on the web or SharePoint.
For visual creation of internal reports, the Report Builder 3.0 system is offered, which supports many formats and a wide range of predefined templates.
Work with databases for free
The company provides small projects and novice developers with a special free version of Microsoft SQL Server Express. This includes the same database technologies as “full” SQL versions Server.
Visual Studio and Web Developer development environments are supported. Create complex tables and queries, develop Internet applications with database support, and gain direct access to information from PHP.
Take advantage of the full power of Transact-SQL and the most advanced data access technologies of ADO.NET and LINQ. Stored procedures, triggers and functions are supported.
Concentrate on the elements of business logic, and the system will optimize the database structure on its own.
Create rich reports of any complexity. Use search capabilities, integrate reports with Microsoft Office applications, and add geographic location information to documents.
Applications being developed can work without a connection to the database server. Synchronization is performed automatically using proprietary transactional replication technology Sync Framework.
Administer your infrastructure using management policies for all databases and applications. Common operational scenarios reduce the time spent optimizing queries and creating and restoring enterprise-scale backups.
SQL Server 2008 R2 Express Edition is ideal for quickly deploying websites and online stores, programs for personal use, and small businesses. This is a great option to get started and learn.
Manage your databases using SQL Server Management Studio
Microsoft SQL Server Management is a specialized environment for creating, accessing, and managing databases and all elements of SQL Server, including reporting services.
The system combines in one interface all the capabilities of administration programs from earlier versions, such as Query Analyzer and Enterprise Manager. Administrators receive software with a large set of graphical development and management objects, as well as an expanded language for creating scripts for working with the database.
The Microsoft Server Management Studio code editor deserves special attention. It allows you to develop scripted scripts in Transact-SQL, program multidimensional data access queries and analyze them with support for saving results in XML. Creation of queries and scripts is possible without connecting to a network or server, with subsequent execution and synchronization. There is a wide selection of pre-installed templates and version control system.
The Object Browser module allows you to view and manage any built-in Microsoft Servers SQL objects across all servers and database instances. Easy access to the information you need is critical for rapid application development and version control.
The system is built on the Visual Studio Isolated Shell system, which supports extensible settings and extensions third party developers. There are many communities on the Internet where you can find everything necessary information and code examples for developing your own data management and processing tools.
According to the research company Forrester Research, the Microsoft SQL Server 2012 database server was among the top three leaders in the corporate information storage market at the end of 2013. Experts note that the rapid growth of Microsoft's market share is due to an integrated approach corporations to automate business processes. Microsoft SQL Server is a modern platform for managing and storing data of any type, complemented by analytics and development tools. Separately, it is worth noting the ease of integration with other company products, such as Office and SharePoint.
