|
|
Browse by Tags
All Tags » SQLGuy (RSS)
-
 Working at Microsoft provides many opportunities to receive pearls of wisdom from individuals at other parts of the organization. Saleem Hakani sent along this little gem a few days ago and I wanted to share it with you. It outlines how to recover an SA password if you forgot it – though you do need to shutdown your SQL Server instance and start it in maintenance mode to accomplish this.
What I have done in the past as well is recommend that customers write the SA password down, put it in an envelope and put that envelope in a safe that requires two people to get authorized access to. This way no one person can get the SA password (two are required) and you don’t need to shut down the server to get it, if needed. Enjoy!
Here’s the scenario: You are a proud and a trusted DBA of your organization who is responsible for some important services running on SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access: þ You have removed any and all built-in administrators account from SQL Server logins. þ You have removed all users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins) þ You have set the password of SA account to something extremely complex which would be hard for anyone to guess or remember. þ For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on user databases but doesn’t have SYSADMIN privileges on the system. þ You have not documented the SA password anywhere to prevent others from knowing the SA password. (Also, it’s not a good practice to document the password) Since you have set the SA password to be complex and you have been using your domain account and not the SA account for all your daily database related activity on SQL Server, the unthinkable has happened “you forgot your SQL Server’s SA password”. You are the only person who knew the SA password in your team and now you don’t remember what it was and you need to make some server level configuration changes to your production SQL Server boxes. What would you do now? Here are some of the options I think you may do: 1. You will try logging in as SA with all possible passwords you have in your mind. 2. You will look for the SA password on your computer hard-drive or in your emails (If you had stored it in some file which is a bad practice) 3. Try to restore MASTER database from database backup. However, this will not help because you will run in to the same issue as you don’t remember the SA password. 4. Rebuild Master database. This may not help as you will lose all system/server level configurations and settings including logins, permissions and any server level objects. 5. Re-install SQL Server 2012 and attach all user databases. This may not work as you may experience same issues that you would experience with #4. All your attempts to login in to the system using SA password have failed and now it is time for you to call up the world’s best product support services team “Microsoft PSS” Here’s what you can do: Many folks have asked me about this and today, I’d like to share with you a backdoor to SQL Server 2012 which would help you gain SYSADMIN access to your production SQL Servers. However, that would mean your Windows account will need to be a member of the local administrators group on Windows Servers where SQL Server 2012 Services are running. SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges. Steps to take control of your SQL Server 2012 as an SA: 1. Start the SQL Server 2012 instance using single user mode from command prompt by launching the command prompt as an administrator. (Note: You can also start SQL Server 2012 using minimal configuration which will also put SQL Server in single user mode) 2. From the command prompt (Run as Administrator) type: SQLServr.Exe –m (or SQLServr.exe –f) and let the SQL Server 2012 database engine start. Make sure you do not close this command prompt window. You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have SQL Server 2012 Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server 2012 machine. Usually the Binn folder is located @ C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn> 3. Once SQL Server 2012 service has been started in single user mode or with minimal configuration, you can now open up another command line window as an administrator and use SQLCMD command from command prompt to connect to SQL Server 2012 instance: SQLCMD –S <Server_Name\Instance_Name> Example: SQLCMD –S “SALEEMHAKANI” You will now be logged in to SQL Server. Keep in mind that you are now logged in as an Admin on SALEEMHAKANI SQL Server 2012 instance. 4. Once you are logged into the SQL Server 2012 using SQLCMD from the command prompt, you have the option of creating a new account and granting it any server level permission. Let’s create a new login in SQL Server 2012 called “Saleem_SQL” and then add this account to SysAdmin server role. To create a new login from the command prompt after performing Step 3: 1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’ 2> GO Example: 1> CREATE LOGIN SQL_SALEEM WITH PASSWORD=’$@L649$@m’ 2> GO To Once the new login “SQL_SALEEM” has been created, now let’s add this login to System Admin server role on SQL Server 2012 instance. From the same command prompt window, execute the following statement: 1> SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN' 2>go
Example: 1> SP_ADDSRVROLEMEMBER SQL_SALEEM,’SYSADMIN’ 2> GO The above operation will take care of granting SYSADMIN privileges to “SQL_SALEEM” login. 5. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m) 6. Log in to the SQL Server 2012 management studio or from the command prompt using “SQL_SALEEM” account and it’s respective password, you now have system admin access to your SQL Server 2012 instance. You may now reset the SA password and take control of your production SQL Server boxes. 
|
-
Before joining Microsoft I spent many years working as an Oracle DBA, SQL Server DBA, consultant, trainer, and so on. I got to see the differences in features and functionality between a wide variety of products. I also saw that there was a different approach that Oracle and SQL Server used for incremental numbers in databases.
There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we’ve been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. You’ve tried IDENTITY() and now try the new feature added in SQL Server 2012 and experience the difference. SQL Server 2012 introduces a brand new schema bound object called SEQUENCE. Sequence generates numeric values based on the specification of a SEQUENCE object. You can generate numeric values in either ascending or descending order and they can be independent of tables unlike IDENTITY columns. Here’s the syntax on how you can create a SEQUENCE using various options. | CREATE SEQUENCE SYNTAX | | CREATE SEQUENCE [schema_name . ] sequence_name [ AS [ built_in_integer_type | user-defined_integer_type ] ] [ START WITH <constant> ] [ INCREMENT BY <constant> ] [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ] [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] [ { CACHE [ <constant> ] } | { NO CACHE } ] [ ; ] | Let’s now take a look at an example on how to use SEQUENCE in SQL Server 2012. In the below example, we will look at: § Creating a sequence § Create a sample Item table § Use SEQUENCE when inserting data in a table § Query Item table § Alter or delete SEQUENCE from SQL Server In this step, we will create a new object called MYSEQUENCE1 that will start with a value of 1 and increment it’s value by 1. | STEP 1 - CREATING A SEQUENCE CALLED MYSEQUENCE1 | | CREATE SEQUENCE ITEMIDSEQUENCE START WITH 1 INCREMENT BY 1; | In this step, we will create a sample ITEM table with just two columns. | STEP 2 - CREATING ITEM TABLE | | CREATE TABLE ITEM ( ITEMNO INT, ITEMNAME VARCHAR(50) ); GO | In this step, we will insert data in the ITEM table by leveraging the newly create MYSEQUENCE1 which will automatically insert and increment the values of MYSEQUENCE1 by 1. | STEP 3 – USING SEQUENCE FOR INSERTING DATA IN A TABLE | | INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'MANGO'); INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'APPLE'); INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'BANANA'); INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'ORANGE'); INSERT INTO ITEM VALUES (NEXT VALUE FOR ITEMIDSEQUENCE, 'GRAPES'); GO | In this step, we will query the ITEM table to review the data in the ITEM table. Notice that the ITEMNO column in the ITEM table has sequential numbers that were auto-inserted using ITEMIDSEQUENCE sequence. | STEP 4 – QUERYING DATA FROM A TABLE USING SEQUENCE | | SELECT * FROM ITEM YOU’LL SEEE THE FOLLOWING RESULT SET. | ITEMNO | ITEMNAME | | 1 | MANGO | | 2 | APPLE | | 3 | BANANA | | 4 | ORANGE | | 5 | GRAPES | | In this step, we will look at Altering, Deleting & Querying Sequences: | ALTER, DELETE, AND QUERY SEQUENCES | | To delete a sequence: DROP SEQUENCE ITEMIDSEQUENCE To alter a sequence: ALTER SEQUENCE ITEMIDSEQUENCE RESTART WITH 900 INCREMENT BY 1 GO To query the next value of Sequence for a particular SEQUENCE object: SELECT NEXT VALUE FOR ITEMIDSEQUENCE To query all sequences available in a database, you can use the below query: SELECT * FROM SYS.SEQUENCES | | THINGS TO KEEP IN MIND: | | § Sequence values are not automatically protected after insertion in to a table. You should use Update Trigger on the table to prevent sequence values from being changed. § Sequences does not automatically enforce uniqueness for sequence values. You should create unique index on the Sequence column to enforce uniqueness. § If you have created a sequence for example assigns values 1 through 100, and if the rows in the table grows beyond 100 rows, then Sequence would start assigning values 1 through 100 again. § By default, if you do not specify data type for a sequence, BIGINT data type is used. Keep in mind that you can create sequence that are of any integer data type. |
 
|
-
In SQL Server 2012 we have added the IIF() function to the SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions. In earlier versions of SQL Server, we have used IF..ELSE and CASE..ENDCASE to perform logical conditional operations. However, IIF() can be used as a shorthand way of writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE. | In this example, we will evaluate the values of two variables and return the result of a variable which is bigger in value. | | DECLARE @A INT=40 DECLARE @B INT=30 SELECT IIF(@A > @B, 'A IS GREATER THAN B', 'B IS GREATER THAN A') GO; Executing the above T-SQL will return the following result: ------------------- A IS GREATER THAN B (1 row(s) affected) | | In this example, we will evaluate the age of John and Julie and identify who is older between them. Please observe the use of sub functions within IIF. | | DECLARE @JOHN_AGE INT=35 DECLARE @JULIE_AGE INT=29 SELECT IIF(@JOHN_AGE > @JULIE_AGE, 'JOHN IS OLDER THAN JULIE BY '+LTRIM(STR(@JOHN_AGE-@JULIE_AGE))+' YEARS', 'JULIE IS OLDER THAN JOHN BY '+LTRIM(STR(@JULIE_AGE-@JOHN_AGE))+' YEARS') GO; Executing the above statement will return the following result: -------------------------------------------- JOHN IS OLDER THAN JULIE BY 6 YEARS (1 row(s) affected) | Note: Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels. 
|
-
This week we have something for you to share with the SQL Server developers, as well as use in your DBA T-SQL scripts to make life easier when you re-use common code. Saleem Hakani provides some guidance on new functionality in SQL Server 2012 to help make you more productive when creating T-SQL code.
SQL Server 2012 introduces a new feature called T-SQL Snippets. T-SQL Snippets allow you to quickly build T-SQL statements without having to remember the commands or its syntax. This feature can potentially help with reduced development time and in increased productivity for developers and DBA’s. Snippet templates in SQL Server 2012 are based on XML with predefined fields and values. When you use T-SQL snippet, these fields are highlighted and the user can tab through each fields and change the values as required. Imagine a series of commands that you always use when creating a Trigger or a Table or a Stored Procedure or even a SELECT statement. You can now reduce the amount of time and code you would write by implementing T-SQL Snippets in SQL Server 2012. Snippets are categorized for ease of use so that the user can see and use various snippets based on the category. SQL Server 2012 introduces three types of snippets: | DEFAULT SNIPPETS (aka: Expansion Snippets) | SURROUND SNIPPETS | CUSTOM SNPPETS | | These are code templates for various T-SQL commands that you can quickly insert into your T-SQL code when creating tables, stored procedures, triggers, etc. | These are code templates that allow you to implement code construct like Begin End, If, While, etc. | Custom Snippets allow you to create your own Snippet that can appear with the Snippet menu when invoked. | INVOKING OR INSERTING T-SQL SNIPPET: There are multiple ways with which you can invoke T-SQL Snippets in SQL Server 2012. The easiest way to invoke T-SQL Snippet is to right click on context menu in the query editor and you will be presented with the Snippet options. Another quick way of inserting a snippet when writing T-SQL code is to use “CTRL + K X” shortcut key combination by placing the cursor where you would like to insert the snippet. CREATING A CUSTOM SNIPPET Let’s now look at how to create a custom Snippet and add it to the Snippet menu. Creating and using a Snippet is a 3 steps process: 1. Create a Snippet using XML 2. Register the Snippet in SSMS 3. Invoke the Snippet when using Query Editor NOTE: By default, all T-SQL Snippets are stored under the following folder and are saved as .snippet files: “C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033” | | | STEP1: CREATE A T-SQL SNIPPET FILE USING XML: In the example below, we will create a snippet that can be used to write Select statement for any table and would allow the use of CASE statement for an equality check on a column. | CASE_END.SNIPPET File | | <?xml version="1.0" encoding="utf-8" ?> <CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0"> <Header> <Title>Case-End</Title> <Description> Insert Case...End Construct. </Description> <Author> Saleem Hakani (Microsoft Corporation) </Author> <SnippetTypes> <SnippetType>Expansion</SnippetType> </SnippetTypes> </Header> <Snippet> <Code Language="SQL"> <![CDATA[ Select <Column_Name1>, <Column_Name2>, <Column_Name3>, <Column_Name4>= CASE <Column_Name4> WHEN '<value>' THEN '<Result>' WHEN '<value>' THEN '<Result>' WHEN '<value>' THEN '<Result>' WHEN '<value>' THEN '<Result>' ELSE 'Value not found' END, <Column_Name5>, <Column_Name6> From <Table_Name> Go ]]> </Code> </Snippet> </CodeSnippet> </CodeSnippets> | | | | | STEP2: REGISTER THE SNPPET WTH SQL SERVER MANAGEMENT STUDIO Once the above file is created, you can now use the Code Snippets Manager to register the snippet with SQL Server Management Studio. You can either add a new folder based on the snippet category or import individual snippets to the My Code Snippets folder. To Add a Snippet Folder: 1. Launch SQL Server Management Studio 2. Select “Tools” from the menu items and Click on “Code Snippets Manager” (This will launch the Snippet Manager) 3. Click on the “Add” button 4. Browse to the folder containing CASE_END.Snippet file, and click on the Select Folder button. Next step is to import the Snippet in to SQL Server Management Studio: To Import a Snippet: 1. Launch SQL Server Management Studio 2. Select “Tools” from the menu items and click on “Code Snippets Manager” 3. Click on the “Import” button at the bottom 4. Browse to the folder containing CASE_END.snippet file and select CASE_End.snippet file by clicking on it and then click on “Open” button. | | | | STEP3: INVOKE THE SNIPPET FROM QUERY EDITOR You now have a snippet created called CASE_END that can be invoked by using the shortcut key from the query editor by pressing “CTRL + K X” and then by selecting the category folder in which you created the snippet. Alternatively, you can right click on the context menu in query editor and select “Insert Snippet…” | Using the above steps, you can create T-SQL code snippet and register it with SQL Server Management Studio. You can also create complex snippets of various regular tasks and make your SQL Server life easy. 
|
-
SQL Server 2012 introduces brand new query hints that allow you to implement query paging solution. In the past, we have used TOP operator to return the top number of rows from a table. However, OFFSET & FETCH options of the ORDER BY clause can give you more benefits than just the TOP operator. Assume you have 50,000 records in a table and you want to query 1200 rows starting from 25000. Traditionally you would use a cursor to implement this solution however with the introduction of OFFSET and FETCH, you can now prevent using cursors for such queries and it’s also much easier to write as well. So let’s take a look at what OFFSET and FETCH means: OFFSET: Allows you to offset the record pointer to a specific row in a table FETCH: Allows you to fetch/return number of rows you request in Fetch. OFFSET and FETCH when used together can return the exact/precise set of data you are looking for. Let’s take an example of querying 5 rows from a table that has 10 records. To make this an interesting example, lets query 5 records from a 10 records table starting from the 4TH record: In a real-life scenario, you’ll have millions of records and you may want to query rows from a specific offset. So back to the above example: Please pay attention to the OFFSET and FETCH clauses below to understand how and where to place them in a query. | LETS CREATE AN ITEMS TABLE | | CREATE TABLE ITEMS ( ITEM_ID INT IDENTITY(1,1), ITEM_NAME VARCHAR(50) ); GO; | | INSERT ROWS IN ITEMS TABLE | | INSERT INTO ITEMS VALUES ('MANGO'); INSERT INTO ITEMS VALUES ('APPLE'); INSERT INTO ITEMS VALUES ('BANANA'); INSERT INTO ITEMS VALUES ('KIWI'); INSERT INTO ITEMS VALUES ('PLUMS'); INSERT INTO ITEMS VALUES ('GRAPES'); INSERT INTO ITEMS VALUES ('WATERMELON'); INSERT INTO ITEMS VALUES ('HONEYDEW'); INSERT INTO ITEMS VALUES ('CHERRY'); INSERT INTO ITEMS VALUES ('STRAWBERRY'); GO; | | QUERYING THE TABLE USING OFFSET AND FETCH | | --IN THIS QUERY, WE ARE OFFSETTING/SKIPPING THE ROWS BY 3 RECORDS (starting at 4th record) AND RETURNING THE NEXT 5 ROWS. SELECT * FROM ITEMS ORDER BY ITEM_ID OFFSET 3 ROWS FETCH NEXT 5 ROWS ONLY GO; EXECUTING THE ABOVE STATEMENT WILL RETURN THE BELOW RESULT SET AS REQUESTED: (Note: ITEM_ID values are being generated thru Identity column) | ITEM_ID | ITEM_NAME | | 4 | KIWI | | 5 | PLUMS | | 6 | GRAPES | | 7 | WATERMELON | | 8 | HONEYDEW | (5 row(s) affected) NOTE: YOU CAN ALSO USE VARIABLES WITH OFFSET AND FETCH CLAUSES. HERE’S AN EXAMPLE: DECLARE @OFFSET INT=3, @FETCH INT=5 SELECT * FROM ITEMS ORDER BY ITEM_ID OFFSET @OFFSET ROWS FETCH NEXT @FETCH ROWS ONLY GO; EXECUTING THE ABOVE COMMAND WILL RETURN THE SAME RESULTS AS USING CONSTANT VALUE WITH OFFSET AND FETCH. | ITEM_ID | ITEM_NAME | | 4 | KIWI | | 5 | PLUMS | | 6 | GRAPES | | 7 | WATERMELON | | 8 | HONEYDEW | (5 row(s) affected) | 
|
-
SQL Server 2012 introduces a brand new string function called CONCAT(). CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function and takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This function would help eliminate the need of explicit data conversions when concatenating two values. Note: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned. SYNTAX: SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN]) -- Between 2 and 254 values can be passed. | TEST 1 - CONCATENATION WITH CONCAT() | TEST 2 - CONCATENATION WITHOUT CONCAT() | | Declare @a Varchar(100)='Microsoft SQL Server is now ' Declare @b int=22 Declare @c varchar(200)=' years old' Select CONCAT (@a, @b, @c) Go Returns: Microsoft SQL Server is now 22 Years old | Declare @a Varchar(100)='Microsoft SQL Server is now ' Declare @b int=22 Declare @c varchar(200)=' years old' Select @a+Convert(varchar,(@b))+@c Go; Returns: Microsoft SQL Server is now 22 Years old | In the above example, observe the use of CONCAT() function. There’s no data conversion being performed if you are using CONCAT function. However, in the second test, we are having to use data conversion function to convert Integer value to a string. The data returned by CONCAT function depends on the type of values passed. The below table shows the mapping of input and output types:
| Input Value / Type | Output Value / Type | | SQL-CLR (TYPES & UDT) | NVARCHAR(MAX) | | NVARCHAR(MAX) | NVARCHAR(MAX) | | NVARCHAR(<=4000) | NVARCHAR(<=4000) | | VARBINARY(MAX) | NVARCHAR(MAX) | | ALL OTHER INPUT TYPES | VARCHAR(<=8000) *Unless one of the parameters is an NVARCHAR of any length and in that case, the output value would be in NVARCHAR(MAX) | NOTE: CONCAT function only works with SQL Server 2012 and above versions. However, to make this work in older versions of SQL Server, you will need to create your own custom function that can take care of concatenating values and appropriately converting their data types.
|
-
In an ideal world, hard drives and other hardware never fail, software is never defective, users do not make mistakes, and hackers are never successful. However, we live in a less than perfect world and we should plan and prepare to handle adverse events. In today’s topic, we will focus on best practices for backing up large mission critical databases. Performing and maintaining good backups is atop priority for any DBA/Developer/Engineer working with SQL Server. KEEP IN MIND: BACKUP AND RESTORE IS NOT A HIGH AVAILABILITY FEATURE. YOU MUST PERFORM REGULAR BACKUPS OF YOUR DATABASES. RESTORING a database from backup is simply a repair feature and not an availability feature. If you are running a mission-critical system and if your database requires high availability, then please look into various H/A features available with SQL Server. If you are running a large/mission-critical database system than you need your database to be available continuously or for extended periods of time with minimal down-time for maintenance tasks. Therefore, the duration of situations that require databases to be restored must be kept as short as possible. Additionally, if your databases are large, requiring longer periods of time to perform backup and restore than you MUST look into some of the cool new features that SQL Server offers to increase the speed of backup and restore operations to minimize the effect on users during both backup and restore operations. | USE MULTIPLE BACKUP DEVICES SIMULTANEOUSLY | | If you are performing backups/restore on a large database than use multiple backup devices simultaneously to allow backups to be written to all the devices at the same time. Using multiple backup devices in SQL Server, allows database backups to be written to all devices in parallel. One of the potential bottleneck in backup throughput is the backup device speed. Using multiple backup devices can increase throughput in proportion to the number of devices used. Similarly, the backup can be restored from multiple devices in parallel. | | USE MIRRORED MEDIA SET | | Use a mirrored media set. A total of four mirrors is possible per media set. With the mirrored media set, the backup operation writes to multiple groups of backup devices. Each group of backup devices makes up a single mirror in the mirrored media set. Each single mirror set must use the same quantity and type of physical backup devices, and must all have the same properties. | | USE SNAPSHOT BACKUPS (FASTEST BACKUP) | | This is the fastest way to perform backups on databases. A snapshot backup is a specialized backup that is created almost instantaneously by using a split-mirror solution obtained from an independent hardware and software vendor. Snapshot backups minimize or eliminate the use of SQL Server resources to accomplish the backup. This is especially useful for moderate to very large databases in which availability is very important. Snapshot backups and restores can be performed sometimes in seconds with very little or zero effect on the server. | | USE LOW PRIORITY BACKUP COMPRESSION | | Backing up databases using the newly introduced backup compression feature, could increase CPU usage and any additional CPU consumed by the compression process can adversely impact concurrent operations. Therefore, when possible create a low priority compressed backup whose CPU usage is limited by Resource Governor to prevent any CPU contention. | | USE FULL, DIFFERENTIAL AND LOG BACKUPS | | If the database recovery model is set to FULL, than use different combination of backups (FULL, DIFFERENTIAL, LOG). This will help you minimize the number of backups that need to be applied to bring the database to the point of failure. | | USE FILE/FILEGROUP BACKUPS | | Use file and file group backups and T-log backups. These allow for only those files that contain the relevant data, instead of the whole database, to be backed up or restored. | | USE A DIFFERENT DISK FOR BACKUPS | | Do not use the same physical disk that holds database files or Log files for backup purposes. Using the same physical disk not only affects the performance, but also may reduce the recoverability of the plan. | 
|
-
There are times when you need to track all the changes happening to a table. In the past, developers have used custom code to track changes by calling stored procedures, triggers and until recently even change data capture (also known as CDC).
SQL Server now supports a simplified method of auditing changes using DML statements without having to use Triggers or CDC (Change Data Capture). SQL Server introduces an OUTPUT clause as a part of DML statement that can help you in tracking changes made during any DML operations. The OUTPUT clause can save the result-set in a table or table variable. The functionality is similar to what triggers had with INSERTED and DELETED tables which used to access the rows that have been modified during the DML operation. Example: In this example we will change the ITEMCOMMENT value from the ITEM table to the reverse of the original value and store the original and changed values in a table. | TRACKING CHANGES USING OUTPUT CLAUSE | | --CREATE ITEM TABLE CREATE TABLE ITEM (ITEMNAME VARCHAR(40), ITEMCOMMENT VARCHAR(255)) --INSERT DATA IN TO ITEM TABLE -- PLEASE NOTICE THE MULTIPLE INSERTS WITH ONE INSERT STATEMENT INSERT INTO ITEM VALUES ('MANGO','ELPPA NAHT RETTEB SI OGNAM'), ('PEACH','ETATS HCAEP A SI AIGROEG'), ('GRAPES','TAERG ERA SEPARG') -- QUERY THE ITEM TABLE SELECT * FROM ITEM 
--DECLARE A TABLE VARIABLE TO STORE THE CHANGED VALUE DECLARE @RECORDCHANGES TABLE(CHANGE VARCHAR(255)) --UPDATE THE ITEM TABLE TO FIX ITEM COMMENTS --NOTICE THE USE OF OUTPUT PARAMETER BELOW. --THE CHANGES ARE STORED IN THE TABLE VARIABLE UPDATE ITEM SET ITEMCOMMENT=REVERSE(ITEMCOMMENT) OUTPUT 'ORIGINAL VALUE: [' + DELETED.ITEMCOMMENT+'] HAS BEEN CHANGED TO: ['+ INSERTED.ITEMCOMMENT+']' INTO @RECORDCHANGES --QUERY THE CHANGES FROM THE TABLE VARIABLE SELECT * FROM @RECORDCHANGES -- RESULTSET FROM THE CHANGED TABLE 
--QUERY THE ITEM TABLE SELECT * FROM ITEM --YOU WILL SEE THE BELOW RESULTSET 
| NOTE: OUTPUT CLAUSE IS NOT SUPPORTED FOR THE FOLLOWING STATEMENTS: 1. DML statements that reference local partitioned views, distributed partitioned views, or remote tables. 2. INSERT statements that contain an EXECUTE statement. 3. Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100. 4. The OUTPUT INTO clause cannot be used to insert into a view, or rowset function. 5. A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target. Please refer to SQL Server BOL (Books Online) for detailed information on the Output clause. 
|
-
You may have heard about Transparent Data Encryption (TDE), which was introduced in SQL Server 2008. But what does it do, what are its advantages and disadvantages and how can you leverage this technology in SQL Server? Keep reading this tips and tricks article to learn how. Transparent Data Encryption (also called as TDE) is a technology in SQL Server that offers encryption of data-at-rest. This feature automatically encrypts the entire database (data and log files), as well as database backups, without requiring any programming or code changes to your application. The process is entirely transparent, hence the name Transparent Data Encryption. When TDE is first enabled for a specific database, SQL Server encrypts the database in the background. During this process, the database remains online and responsive to client requests (similarly, when encryption is disabled, SQL Server decrypts the database in the background). Encryption is performed at the page level, and does not increase the size of the database in any way. Once the entire database is encrypted, new data gets encrypted on the fly as it is written to disk, and all data gets decrypted when read back. Figure 1 illustrates the typical key hierarchy used for transparent data encryption. If you are worried about protecting your sensitive data at rest, the solution is to use Transparent Data Encryption in SQL Server. 
Figure 1: Transparent Data Encryption Benefits of using TDE: (1) Ease of implementation and transparency: TDE is essentially a “flip-the-switch” solution that allows you to encrypt your entire database and log files without application modifications. (2) Additional security compared to cell-level encryption: TDE automatically encrypts tempdb and the database log files to prevent data leakage. (3) Any data-type supported: Unlike cell-level encryption that returns only varbinary data, TDE allows you to store data using any native data type. (4) Support for indexes: Because data is decrypted in the buffer pool, TDE allows the SQL Server query processor to use existing indexes on the data. Drawbacks of TDE: (1) Lowest support encryption granularity is the database. (2) The data is not protected from authenticated, authorized database users, including the DBA. | HOW TO ENABLE TDE IN 5 EASY STEPS | | --Step 1: Create a Database Master Key in for the master database if --it does not already exist. USE Master; GO IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1GoodPassw0rd'; --Step 2: Create a server certificate for the TDE. This certificate is --used to protect the Database Encryption Key (DEK). IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'SampleDatabase TDE Certificate') CREATE CERTIFICATE SampleDatabaseTDEcertificate WITH SUBJECT = 'SampleDatabase TDE Certificate'; --Step 3: Create a Database Encryption Key (DEK) in the user database --(SampleDatabase) for use by TDE. USE SampleDatabase; GO IF NOT EXISTS (SELECT 1 FROM sys.dm_database_encryption_keys WHERE database_id = ( SELECT dbid FROM master..sysprocesses WHERE spid=@@SPID )) CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE SampleDatabaseTDEcertificate --Step 4: Turn on TDE encryption ALTER DATABASE SampleDatabase SET ENCRYPTION ON --Step 5: Query DMV to confirm TDE is in effect SELECT DB_NAME(database_id) as DB,encryption_state FROM sys.dm_database_encryption_keys WHERE database_id=DB_ID(); GO | | | | HOW IS DATABASE ENCRYPTION KEY MANAGED? | | Since the symmetric database encryption key (DEK) is used the encrypt the database in TDE, it cannot be left un-encrypted. To learn more on how SQL Server encrypts the DEK refer to this blog post. | | CERTIFICATE EXPIRY – SHOULD I WORRY ABOUT IT? | | TDE encryption does NOT enforce expiration dates for certificates. You can continue to decrypt data with a certificate in TDE even if it is expired. This is different than service brokering and mirroring features where certificate expiry needs to be acted upon by the DBA. However, don’t forget to backup your TDE certificates with the private key. The certificate backup is required while restoring / attaching the encrypted database. | | TDE AND BACKUP COMPRESSION – CAN’T HAVE YOUR CAKE AND EAT IT TOO! | | Encryption introduces randomization in the data to maintain confidentiality. The high entropy of encrypted data makes it very difficult to compress, possibly even growing in size, because there is no statistical tendency to eliminate. It is because of this reason that backup compression is not recommended on a database with TDE enabled. 
Figure 2 : Backup compression with TDE (BUFFERCOUNT = default, MAXTRANSFERSIZE = default, 1 backup device) Observations : (1) On a TDE enabled database, backup compression doesn’t reduce the size of the database. (2) CPU utilization for the compressed backup is higher than the uncompressed backup, even though the backup size is not much different because the CPU resources are wasted in the compressed backup operation since attempts are made to compress the data even though it is not very compressible. (3) On a TDE enabled database, it takes longer to perform a compressed backup that it takes to perform an uncompressed backup. Click here for more information on backup compression. | 
|
-
Did you know that you can now use the TOP operator for Deleting, Inserting and Updating data in SQL Server tables?
Using the TOP operator for DML operation can help you in executing very large data operations by breaking the process into smaller pieces. This can potentially help with increased performance and also helps with improving database concurrency for larger and highly accessed tables. This is considered as one of the best techniques for managing data modifications on large data loads for reporting or data warehouse applications.
When you perform an update on large number of records using single set updates, it can cause the Transaction Log to grow considerably. However, when processing the same operation in chunks or pieces, each chunk is committed after completion allowing SQL Server to potentially re-use the T-Log space. Another classic issue many of us have experienced is when you are performing very large data updates and you cancel the query for some reason, you would have to wait for a long time while the transaction completely rolls back. With this technique you can perform data modifications in smaller chunks and you can continue with your updates more quickly. Also, chunking allows more concurrency against the modified table, allowing user queries to jump in, instead of waiting for several minutes for a large modifications to finish.
Let’s take an example of deleting 1000 rows of records in a chunk. Assume a table called LARGETABLE table that has millions of records and you want delete 1000 records in chunk:
| DELETING LARGE TABLE IN CHUNKS | | --CREATE A DEMO TABLE CALLED LARGETABLE CREATE TABLE LARGETABLE (ID_COL INT IDENTITY(1,1), COL_A VARCHAR(10),COL_B VARCHAR(10)) GO --INSERT THE DATA IN LARGETABLE.. NOTICE THE USE OF ‘GO 10000’ INSERT INTO LARGETABLE VALUES ('A','B') GO 10000 --QUERY THE TABLE SELECT COUNT(*) FROM LARGETABLE; --PERFORM DELETION OF 1000 ROWS FROM LARGETABLE WHILE (SELECT COUNT(*) FROM LARGETABLE) > 0 BEGIN DELETE TOP (1000) FROM LARGETABLE SELECT LTRIM(STR(COUNT(*)))+' RECORDS TO BE DELETED' FROM LARGETABLE --THIS IS JUST A COMMENT. END | The above technique can also be used with INSERT and UPDATE commands.
One thing to remember is that this is ideally suited for data warehouses and not really something to be used if the table you are making changes to is also being modified by other users in an OLTP type of database environment.
|
-
Last week you learned how to setup the encryption key hierarchy in SQL Server. Now, let’s use encryption to encrypt sensitive data in SQL Server. It is quite possible that you might have sensitive data that needs encryption at a finer level of detail than the entire database. Most of the row might need to be visible to users, while certain sensitive information such as employee salary might require encryption. You might also want the ability for certain users to be able to encrypt/decrypt certain cells as shown in figure 1. The solution is to use cell-level encryption in SQL Server. 
Figure 1: Cell-level encryption with 2 different users and keys on a data table With cell-level encryption in SQL Server, it is possible to encrypt data in individual cells within a table. BENEFITS OF USING CELL-LEVEL ENCRYPTION: (1) Granular, user specific control on encrypting individual cells or column values rather than entire databases (compared to using Transparent Data Encryption – TDE). (2) Data retains its encrypted state in memory unless it is actively decrypted. DRAWBACKS OF USING CELL-LEVEL ENCRYPTION: (1) Requires application changes and analysis of tables to locate sensitive data that needs to be encrypted. (2) Encryption of data introduces randomization. This makes it impossible to index data and causes a performance impact since indexes on encrypted columns cannot be used while searching for a value. (3) Cell-level encryption built-in functions only return varbinary type data and the output is limited to up to 8000 bytes. | WHAT IF SOMEONE TAMPERS WITH ENCRYPTED DATA? | | You can now mitigate this risk by using the @add_authenticator and @authenticator arguments of the cell-level encryption built-in’s. Refer to this blog post to learn how. | | HOW TO USE CELL-LEVEL ENCRYPTION? | | -- Show how a column can be encrypted and decrypted and how an authenticator value can be used create database demo; use demo; -- create a simple employee table create table t_employees ( id int primary key, name varchar(300), salary varbinary(300)); -- create a key to protect the employee sensitive data, in this case - the salary create symmetric key sk_employees with algorithm = aes_192 encryption by password = '1Str0ngPassword'; -- open the key so that we can use it open symmetric key sk_employees decryption by password = 1Str0ngPassword'; -- verify key was opened select * from sys.openkeys; -- insert some data -- we will use the id as an authenticator value to tie the salary to the employee id insert into t_employees values (101, 'Alice Smith', encryptbykey(key_guid('sk_employees'), '$200000', 1, '101')); insert into t_employees values (102, 'Bob Jones', encryptbykey(key_guid('sk_employees'), '$100000', 1, '102')); -- see the result; salary is encrypted select * from t_employees; -- create a view to automatically do the decryption -- note that when decrypting we specify that the id should be used as authenticator create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees; -- see the result, the decrypted data is available select * from v_employees; -- demo the authenticator role -- copy salary of Alice and overwrite the value for Bob -- execute next 3 lines as batch declare @salary varbinary(300); select @salary = salary from t_employees where id = 101; update t_employees set salary = @salary where id = 102; -- note that both entries have the same salary blob select * from t_employees; -- see the result, the decrypted data for Bob is no longer available -- because it doesn't match the authenticator, which is his employee id select * from v_employees; -- now close the key close symmetric key sk_employees; -- verify key was closed select * from sys.openkeys; -- see the result, we can no longer decrypt any data because the key is closed -- to access the data again we would need to reopen the key select * from v_employees; -- cleanup drop view v_employees; delete from t_employees; drop table t_employees; drop symmetric key sk_employees; use master; drop database demo; | | OOPS – MORE THAN 8000 BYTES OF CIPHER-TEXT! | | Because there is a hard 8000 byte limit on the output of built-in’s for cell-level encryption, your application will need to slice the input before encrypting it! Refer to this blog post to learn how. | | WORRIED ABOUT INDEXING YOUR ENCRYPTED DATA? | | Because the cell-level encryption built-in functions are nondeterministic, which means that every time a function is called, the output will be different, indexing encrypted data is not possible. However this problem can be solved by using hashes or MAC’s of the plain-text for indexing purposes. Refer to this blog post to learn how. | 
|
-
Ever wonder how encryption keys are protected and managed in SQL Server? Microsoft SQL Server makes use of a key hierarchy, which helps to protect keys that are to be used for encryption. This hierarchy can best be viewed as a series of layers, in which each layer encrypts the layer below it. This hierarchy provides a highly secure infrastructure for sensitive data. Figure 1 illustrates the encryption key hierarchy: 
Figure 1: SQL Server Encryption Key Hierarchy At the core of the encryption hierarchy is data that needs to be encrypted. The arrows in the diagram illustrate the most common encryption configurations used to encrypt data. Data is first either encrypted with a symmetric key or a password. In symmetric key cryptography, the same key is used for encryption and decryption of the data. Symmetric key encryption is faster than asymmetric key cryptography or certificate base encryption. After encrypting data using a symmetric key, the symmetric key itself cannot be left un-encrypted and it is encrypted with another symmetric key or an asymmetric key. An asymmetric key consists of a key-pair – public key and private key. The private key is protected by a password or database master key (which is a symmetric key). There are 2 copies of the database master key in SQL Server, one protected with a password and another protected with the Service Master Key (SMK). The service master key resides at the root of the encryption hierarchy and is protected using Windows Data Protection API (DPAPI). In summary, keep in mind the following concepts: (1) For best performance, always encrypt data using symmetric keys instead of certificates or asymmetric keys. (2) There are 2 copies of the Database Master Key (DMK) – protected with a password and the Service Master Key (SMK). (3) The Service Master Key (SMK) is created by SQL Server setup and is encrypted using Windows Data Protection API. (DPAPI). (4) The Extensible Key Management (EKM) module holds symmetric or asymmetric keys outside SQL Server. (5) SMK and DMK are symmetric keys. (6) Always use strong passwords for encryption. (7) Always use stronger encryption algorithms. | QUERY SYMMETRIC KEYS | | SELECT * FROM SYS.SYMMETRIC_KEYS; GO | | CREATING A SYMMETRIC KEY | | CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = '1Str0ngPassword’; GO | | QUERY CERTIFICATES | | SELECT * FROM SYS. CERTIFICATES; GO | | CREATING A CERTIFICATE ENCRPTED WITH A PASSWORD | | CREATE CERTIFICATE MySelfSignedCert ENCRYPTION BY PASSWORD = ‘1Str0ngPassword' WITH SUBJECT = 'Self Signed Certificate By Damir', EXPIRY_DATE = '07/14/2016'; GO | | CREATE DATABASE MASTER KEY IN TEST DATABASE | | Use Test; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1Str0ngPassword'; GO | | QUERY ENCRYPTIONS OF SYMMETRIC KEYS | | SELECT * FROM SYS. KEY_ENCRYPTIONS; GO | | QUERY ENCRYPTIONS OF SYMMETRIC KEYS | | SELECT * FROM SYS. KEY_ENCRYPTIONS; GO | | QUERY CERTIFICATES | | SELECT * FROM SYS. CERTIFICATES; GO | | CREATING A CERTIFICATE ENCRYPTED WITH A PASSWORD | | CREATE CERTIFICATE MySelfSignedCert ENCRYPTION BY PASSWORD = ‘1Str0ngPassword' WITH SUBJECT = 'Self Signed Certificate By Damir', EXPIRY_DATE = '07/14/2016'; GO | | CREATE DATABASE MASTER KEY IN TEST DATABASE | | Use Test; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1Str0ngPassword'; GO | | QUERY ENCRYPTIONS OF SYMMETRIC KEYS | | SELECT * FROM SYS. KEY_ENCRYPTIONS; GO | 
|
-
Ever felt the need for knowing who is logging on to your SQL Server and at what time? Ever felt like restricting some of the specific users for certain time-period or firing a trace to track down user activity? Ever felt like limiting the number of concurrent connections from specific users? Well, you can do all that now with SQL Server. SQL Server introduced a new feature called Logon Triggers that allows you to fire a T-SQL, or a stored procedure in response to a LOGON event. You may use logon trigger to audit and control users by tracking login activity, restricting logins from accessing SQL Server, or by limiting the number of sessions for specific logins. Logon Triggers are fired only after a login is successfully authenticated but just before the user session is actually established. All messages originating from inside the trigger (ex: messages, errors) from the PRINT statement are sent to the SQL Server error log. NOTE: If the user authentication fails for any reason, then the Logon triggers are not fired. Below example shows you how you can create a Logon trigger and send a message to SQL Server error log as soon as any user logs in: | CREATING A SERVER WIDE LOGON TRIGGER | | CREATE TRIGGER OPS_LOGON ON ALL SERVER AFTER LOGON AS BEGIN PRINT SUSER_SNAME() +' HAS JUST LOGGED IN TO '+UPPER(LTRIM(@@SERVERNAME))+' SQL SERVER AT '+LTRIM(GETDATE()) END GO | | LIMITING A LOGIN TO 5 CONCURRENT SESSIONS | | CREATE TRIGGER OPS_LOGON ON ALL SERVER WITH EXECUTE AS 'TORONTO\DAMIR' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'TORONTO\DAMIR' AND (SELECT COUNT(*) FROM SYS.DM_EXEC_SESSIONS WHERE IS_USER_PROCESS = 1 AND ORIGINAL_LOGIN_NAME = 'TORONTO\DAMIR') > 5 ROLLBACK; END; | | QUERYING SERVER LEVEL TRIGGERS | | SELECT * FROM SYS.SERVER_TRIGGERS GO | | DROPPING OPS_LOGON SERVER LEVEL TRIGGER | | DROP TRIGGER OPS_LOGON ON ALL SERVER GO | 
|
-
Remember those days when the database would go in to suspect mode and you had to perform various steps to recover the database by putting the database in the emergency mode and then extracting the data out of that emergency database? These are the high level steps you had to perform in previous (<SQL2K5) versions of SQL Server 1. Enable modifications to system catalogs. 2. Change the status of the database in SysDatabases system object to 32768 3. Restart SQL Server services (Once restarted database would appear in Emergency mode) 4. You would then transfer the data from your database in to another database This process was not an easy process and involved manually updating system tables. Often this information was not publicly available. This process has changed since the release of SQL2K5 onwards, putting the user database in EMERGENCY mode is now a supported and documented feature in the current release of SQL Server (unlike SQL Server 2000/7.0/6.x where you had to change the status of SYSDATABASES)
With the release of SQL2K5, SQL Server no longer allows making any changes to the system tables even by SA’s. Making even a slightest change to system objects is restricted. However, there may be situations when you would need to put the database into EMERGENCY mode and export/extract the data out of the corrupt database in to another database and in order to do that, SQL Server now provides a new feature as part of the ALTER DATABASE statement that would enable System Administrators to put the database in to EMERGENCY mode.
In the below example, we will see how this can be done using the ALTER DATABASE statement. Note: This is simply an example of how to put the database in emergency mode and how to bring it back to its normal state. In a real life scenario, once the database is in suspect mode and you put it in EMERGENCY mode, you may not be able to put it back in the normal state due to corruption. In this situation, you must export the data to another database. IMPORTANT: It is strongly recommended that you perform regular backups of your database to avoid any data loss. | PUTTING SALES DATABASE IN EMERGENCY MODE | | ALTER DATABASE SALES SET EMERGENCY GO Once the database is in emergency mode, you should now export the data from the SALES database in to some other database. | | PUTTING THE DATABASE BACK TO NORMAL STATE | | ALTER DATABASE SALES SET ONLINE GO |
NOTE: One of the good feature of SQL Server EMERGENCY mode is that when you run DBCC CHECKDB on a user database that doesn’t have a log file (ex: disk on which log file(s) were residing crashed and can’t be recovered), CHECKDB will rebuild the log file automatically for that user database when it is run while the database is in EMERGENCY mode. THINGS TO KEEP IN MIND: When the database is put in the EMERGENCY mode, it is marked as READ_ONLY and logging is disabled. Only members of SYSADMIN role can set and are allowed to access the database when in emergency mode. You can verify if the database is in emergency mode or not by examining the “STATE” and “STATE_DESC” columns in the sys.databases catalog view or from the “STATUS” property of the DATABASEPROPERTYEX function. IMPORTANT: It is strongly recommended that you perform regular backups of your database to avoid any data loss.
|
-
There are times when SQL Server may be so busy processing requests that it can no longer allocate memory or processor resources to even allow an SA to connect. This has been an issue with older versions of SQL Server. SQL Server solves this problem by introducing a feature called "Dedicated Administrator Connection” (DAC). DAC uses a specific TCP endpoint in a SQL Server instance that is always attached to a dedicated UMS (User Mode Scheduler). DAC provides a connection that can be used by a member of the sysadmin role to access a SQL Server instance, thereby guaranteeing that an administrator could not be locked out of SQL Server due to resource allocation issues. DAC connection is similar to a regular SQL Server connection except that it has only one worker thread to serve requests. It cannot run any queries that require parallel work/multiple threads such as BACKUP or parallel query plan. It will use the regular system memory and in case of failure, it will use its reserved memory. Only a single connection of DAC is allowed at a time and if a connection is already being used, any subsequent connections are refused. Due to the limitation in available resources (One thread and limited amount of memory), you must be very careful of the types of queries you run. You MUST use DAC connection exclusively for diagnostic purposes to resolve a condition that cannot normally be resolved through a regular connection. You can create a connection to DAC in two ways: 1) Using SQLCMD from the Command Prompt 2) Using SQL Server Management Studio *You must use -A parameter when using SQLCMD to use Dedicated Administrator Connection. THINGS TO KEEP IN MIND: 1. DO NOT USE DAC FOR EVERYDAY WORK. IT IS EXCLUSIVELY RESERVED FOR EMERGENCY PURPOSES ONLY. 2. ONLY ONE DAC CONNECTION IS ALLOWED PER INSTANCE. IF SOMEONE ELSE IS CONNECTED TO SQL SERVER USING DAC CONNECTION OR HAS LEFT THE CONNECTION OPEN, YOU WILL NOT BE ABLE TO CONNECT TO SQL SERVER USING DAC UNLESS THE OTHER CONNECTION IS CLOSED. 3. BY DEFAULT, DAC CONNECTION IS ALLOWED ONLY ON A LOCAL SERVER CONNECTION. YOU MUST ENABLE IT FOR REMOTE CONNECTION IF YOU WISH TO USE IT REMOTELY. (BY DEFAULT IT IS NOT ON FOR A CLUSTERED INSTANCE) | CONNECTING TO DAC FROM COMMAND PROMPT | | C:\> SQLCMD –S SERVERNAME –E –A * The administrator can now execute queries to diagnose the problem and possibly terminate the unresponsive sessions. | | RECOMMENDED QUERIES AND COMMANDS FOR DAC | | 1. KILL COMMAND 2. DBCC TRACEON/TRACEOFF 3. DMV/BASIC SINGLE TABLE QUERIES 4. SET OPTIONS 5. SP_CONFIGURE/RECONFIGURE (OPTIONS THAT DO NOT REQUIRE RESTARTING SQL SERVER) | | AVOID QUERIES AND COMMANDS FOR DAC | | 1. BACKUP/RESTORE 2. DBCC CHECK COMMANDS 3. AVOID ANY COMMANDS THAT REQUIRE MULTIPLE THREADS 4. MULTIPLE TABLE JOINS 5. AVOID STATEMENTS THAT MAY GENERATE LOTS OF IO 6. AVOID ANY DDL STATEMENT | 
|
|
|
|