Basit's SQL Server Tips

This article walks the user through installation of SQL Server 2012 on a Windows Server 2008 system using the SQL Server setup installation wizard. The installation process is simple, straight forward and is very similar to SQL Server 2008 / 2008 R2 setup. The procedure described is for a non-clustered server, and can be applied either to a default instance or a named instance. It is intended to be read by Database Administrators and anybody interested in the technical aspects of carrying out this task.

View original post 1,221 more words

Getting Started with Partially Contained Databases

Before SQL Server 2012, when we move or restore databases to a different SQL Server instance, then any logins associated with the database users do not automatically relocates to the new instance. As a result of this, we end up having orphaned users inside databases and these databases do not work straightaway after migration until we create and map logins associated with the database user to the target instance. Obviously, this makes the database less portable, because they depend on the SQL Server instance objects.

Fortunately, SQL Server 2012 comes with the new and robust security feature called partially contained databases, which addresses this vulnerability and makes databases much more portable compared to the earlier versions of SQL Servers. The great advantage of the partially contained databases is that it deals with orphaned users by storing all server-level security metadata and settings, including login details inside contained databases.

Check out my article (ie. Getting Started with Contained Databases) in which discussed this cool security feature of SQL Server 2012.

This article is published on SSWUG.org.

Using SQL Server 2012 FileTables

Basit's SQL Server Tips

SQL Server 2012 allows you to store file/directories in a special table called FileTable that builds on top of SQL Server FILESTREAM technology. As per Microsoft BOL, “FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.”

FileTable has a fixed schema and each row of this table represents a file or a directory. The main advantage of FileTable is that it supports Win32APIs for file or directory management which mean we can access file and directory hierarchy through a Windows Share and database storage is transparent to Win32 application. Files can be bulk loaded, updated as well as managed in T-SQL like any other column. SQL Server also supports backup and restore job for this feature.

In this tip we will take a look at how to use FileTable…

View original post 628 more words

Transact-SQL analytic functions in SQL Server can help solve problems quickly

Like other mainstream commercial database systems, SQL Server supports analytic functions in Transact-SQL to depict complex analytical tasks. With the help of these analytic functions, we can perform common analyses, such as ranking, percentiles, moving averages and cumulative sums that can be expressed concisely in a single SQL statement.

The first batch of Transact-SQL analytic functions came with the release of SQL Server 2005, which included a variety of ranking functions, such as ROW_NUMBER, RANK, DENSE_RANK and NTILE. SQL Server 2012 introduced eight more Transact-SQL analytic functions: PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC, LEAD, LAG, FIRST_VALUE and LAST_VALUE.

Checkout my article here, in which I explained and demonstrated the use of Transact-SQL analytic functions. With the help of these analytic functions, we can quickly solve complex analytical tasks and also eliminate the use of programming self-joins, correlated subqueries and/or use fewer temporary tables compared to the counterparts without such functions.

This article is published on SearchSQLServer.techtarget.com.

Database Recovery Advisor feature of SQL Server 2012

SQL Server 2012 introduces Database Recovery Advisor that provides significant user experience improvements to the ways DBAs can restore databases with SQL Server Management Studio. As we know, SQL Server provides a variety of backup types, so creating the proper recovery sequence for each point in time can be tricky at times. This is where Database Recovery Advisor is useful, because it makes the database restoration process more agile by helping the customers to create more predictable and optimal recovery sequence.

The Database Recovery Advisor provides a visual timeline from the backup history of the database and then presents the available points in time at which the user can restore the database, algorithms to streamline the identification of the appropriate sets of backup media to restore the database back to a specific point in time. By default, the Database Recovery Advisor tries to restore the database from the last backup taken, but as I said earlier, we can use visual timeline feature to restore the database to an earlier point than last full backup.

The Database Recovery Advisor is available via Restore Database dialog box, which you can launch as follow:

1) Right-click database which you want to restore and then navigate to “Databases…” and then click on “Databases…” to launch Restore Database dialog box.

2) In Restore Database dialog box, click “TimeLine…” button to launch Database Recovery Advisor visual timeline.

3) Choose “Specific date and time” option and then use the arrow to specify the restore point. After choosing the appropriate restore restore point, click OK button to return to Database Restore dialog box (see below):

4) Click OK button in Database Restore dialog box to start the database restore (see below):

Conclusion

The Database Recovery Advisor is a great feature of SQL Server 2012, allowing users with a visual timeline of database restore points from backup history. As we have seen in this post, this feature is easy to use and with the help of this feature we can easily perform point-in-time database restore.

Recovering from out-of-disk space conditions for tempdb

Another potential problem that you need to tackle as a DBA is running out of disk space on the hard disk that contains tempdb.This is because SQL Server makes extensive use of tempdb when:

  • Tracking versions for row-versioning concurrency.
  • Performing bulk load operations on tables with triggers enabled.
  • Running DBCC CHECKDB.
  • Rebuilding an index with SORT_IN_TEMPDB option.
  • Variables of LOB data types.
  • Storing intermediate query results, for example, during joins, aggregates, or sorts.
  • Service broker dialog information.
  • Caching temporary objects and tables.
  • Storing inserted and deleted tables in triggers.
  • Running sp_xml_preparedocument.

Continue reading

Performing Unattended Installs of SQL Server 2012

In my post here, I’ve discussed the procedure to perform attended installation of SQL Server 2012 on a Windows Server 2008.

Now imagine, you have multiple SQL Server 2012 instances to install with the same configuration on the multiple servers and you want this task to be done as quickly and consistently as possible. When this is the case, you may choose the unattended installation option. SQL Server unattended installation option lets administrators install SQL Server on multiple machines with little or no user interactions. SQL Server 2012 unattended installations can be done using command line parameters or configuration files. All SQL Server step-up screen entries and the dialog responses are made automatically by using information stored in the configuration file or by command-line parameters.

This article shows you the steps to perform unattended installation of SQL Server 2012 on a Windows Server 2008 system by using command line and configuration file.

Continue reading

How to move master and resource system databases?

I received an phone call from a friend today asking how to move master and resource system databases in Microsoft SQL Server 2012.

Well, the process is very simple and is explained in this blog post:

Moving “master” database

The following are the steps to move master database:

  1. Launch SQL Server Configuration Manager and display the Properties for the SQL Server service.

  2. Activate Startup Parameters and modify the datafile (-d) and log file (-l) startup parameters to reference the new location (see below):

  3. Click Apply and then stop the SQL Server service (see below):

  4. Move the master database files to the new location and then start the SQL Server service (see below):

Moving “resource” database (only applies to SQL Server 2005)

The following are the steps to move “resource” database:

  1. Start in master-only recovery mode by running:

    NET START MSSQLSERVER /f /T3608

  2. Launch a command prompt and run sqlcmd.

  3. Use the ALTER DATABASE statement with the MODIFY FILE option to specify the new location for the resource database data and log files.

  4. Use the ALTER DATABASE statement to make the Resource database read-only.

  5. Stop the SQL Server service.

  6. Move the database files for the “resource” database to new location.

  7. Start the SQL Server service.

I hope you will find this information useful 😀

Retrieving password policy settings for SQL login accounts

Today, I wrote the following query for our internal audit report for SAS70. This query provides all the necessary details about SQL Logins policy settings.

This query is using LOGINPROPERTY function to retrieve the sql login policy settings information:

USE [master]
GO

DECLARE @PwdExpirationAge [int]

SET @PwdExpirationAge = 28

SELECT [name] AS [SQL_User]
	,LOGINPROPERTY([name], 'PasswordLastSetTime') AS [PasswordLastResetDT]
	,@PwdExpirationAge - DATEDIFF(DAY, CONVERT([datetime], LOGINPROPERTY([name], 'PasswordLastSetTime')), GETDATE()) AS [DaysUntilExpiration]
	,LOGINPROPERTY([name], 'BadPasswordCount') AS [BadPasswordCount]
	,LOGINPROPERTY([name], 'BadPasswordTime') AS [BadPasswordDT]
	,LOGINPROPERTY([name], 'HistoryLength') AS [HistoryLength]
	,LOGINPROPERTY([name], 'IsExpired') AS [IsExpired]
	,LOGINPROPERTY([name], 'IsLocked') AS [IsLocked]
	,LOGINPROPERTY([name], 'IsMustChange') AS [IsMustChange]
	,LOGINPROPERTY([name], 'LockoutTime') AS [LockoutTime]
FROM [sys].[sql_logins]
GROUP BY [name];
GO

I hope you will find this query useful.

Useful new DMV’s in SQL Server 2008 R2 SP1 and SQL Server 2012

Introduction

Microsoft SQL Server 2008 R2 and SP1 and SQL Server 2012 has a new set of DMVs that includes sys.dm_server_memory_dumps, sys.dm_server_registry, sys.dm_server_services, sys.dm_os_windows_info, sys.dm_os_volume_stats and sys.dm_exec_query_stats. These DMVs can be used to return the information about SQL Server configuration and installation, memory dumps related information and information that can be useful to diagnose problems and tune query performance.

In this article, I’ll cover the purpose and use of these new DMVs of SQL Server 2008 R2 SP1 and SQL Server 2012 (DENALI):

1) sys.dm_server_memory_dumps – It contains the information about memory dumps files generated by the SQL Server Database Engine. It returns 3 columns listed as below:

Column Name Data type Description Attribute
filename nvarchar(256) Memory dump file name and path. NOT NULL
creation_time datetimeoffset(7) Date and time the file was created. NOT NULL
size_in_bytes bigint Size of the memory dump file in bytes. NULL

Usage:

USE [master]
GO

SELECT * FROM sys.dm_server_memory_dumps;
GO

Example:

Currently there are no memory dump files are available but if you see any on your SQL Server then alert your SQL Server DBA, because memory dumps can be serious and could lead to data corrouption and other problems.

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://technet.microsoft.com/en-us/library/hh204543.aspx

2) sys.dm_server_registry – This DMV contains the configuration and installation information for the current instance of SQL Server that is stored in Windows registry. This DMV returns one row per registry key. It returns 3 columns listed as below:

Column Name Data type Description Attribute
registry_key nvarchar(256) Stores name of registry key. NULL
value_name nvarchar(256) Stores the name of key value. In Windows Registry Editor this item is shown under Name column. NULL
value_data sql_variant Stores value of the key data. In Windows Registry Editor this item is shown under Data column. NULL

In old version of SQL Server you can access the Windows registry information via xp..regread. This DMV is use full if you want to audit the configuration of your SQL Server.

USE [master]
GO

SELECT * FROM sys.dm_server_registry;
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/hh204561.aspx

3) sys.dm_server_services – The initial version of this DMV that was released in SQL Server 2008 R2 SP1 only returns information about SQL Server and SQL Agent service. In SQL Server 2012 it also returns information about Full-text service.

This DMV returns one row per service. It returns 11 columns listed as below:

Column Name Data type Description Attribute
servicename nvarchar(256) Stores name of the SQL Server, Full-text or SQL Server Agent service. NOT NULL
startup_type int Indicates start mode of the SQL Server, Full-text or SQL Server Agent service.

Value Description
0 Other (boot start)
1 Other (system start)
2 Automatic
3 Manual
4 Disabled
NULL
startup_desc nvarchar(256) Describe the start mode of the SQL Server, Full-text or SQL Server Agent service.

Value Description
Other Other (boot start)
Other Other (system start)
Automatic Auto start
Manual Demand start
Disabled Disabled
NOT NULL
status int Indicates the existing status of the SQL Server, Full-text and SQL Agent service.

Value Description
1 Stopped
2 Other (start pending)
3 Other (stop pending)
4 Running
5 Other (continue pending)
6 Other (pause pending)
7 Paused

 

NULL
status_desc nvarchar(256) Describe the existing status of the SQL Server, Full-text and SQL Agent service.

Value Description
Stopped Service is currently stopped.
Other (start pending) Service is in the process of starting.
Other (stop pending) Service is in the process of stopping.
Running Service is currently running.
Other (continue pending) Service is in pending state.
Other (pause pending) Service is in the process of pausing.
Paused Service is currently paused.

 

NOT NULL
process_id int Stores the process id of the SQL Server, Full-text and SQL Agent service. NOT NULL
last_startup_time datetimeoffset(7) Stores the date and time when the SQL Server, Full-text and SQL Agent service was last started. NULL
service­_account nvarchar(256) Stores the account name of authorized account to control the service. NOT NULL
filename nvarchar(256) Stores the full OS path of service executable. NOT NULL
is_clustered nvarchar(1) Indicates whether the service is installed as a resource of the cluster server. NOT NULL
cluster_nodename nvarchar(256) Stores the names of the cluster nodes. NULL

In old versions of SQL Server you can access the same information by accessing the Windows registry information for the service via xp..regread. This DMV is useful if you want to audit the what services are installed as part of the SQL installation.

Usage:

USE [master]
GO

SELECT * FROM sys.dm_server_services;
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/hh204542.aspx

4) sys.dm_os_windows_info – The DMV returns detailed information about Windows OS version for example the release number, service pack level and language version of SQL Server Windows OS. This DMV returns only one row with four columns listed as below:

Column Name Data type Description Attribute
windows_release nvarchar(256) Stores the Windows OS release number. NOT NULL
windows_service_pack_level nvarchar(256) Store the service pack level of Windows operating system. NOT NULL
Windows_sku int Stores ID of Windows Socket Keeping Unit (SKU) NULL
os_language_version int Stores Windows locale identifier (LCID) of the operating system. NOT NULL

In old versions of SQL Server you can access the same information by accessing the Windows registry information for the OS via xp..regread.

Usage:

USE [master]
GO

SELECT * FROM sys.dm_os_windows_info;
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/hh204565.aspx

5) sys.dm_os_volume_stats – The DMV returns Windows OS volume information on which the database files resides. It takes two parameters i.e. database_id (ID of the database) and file_id (ID of the database file). This DMV returns 13 columns listed as below:

Column Name Data type Description Attribute
database_id int ID of the database. NOT NULL
file_id int ID of the file. NOT NULL
volume_mount_point nvarchar(512) Mount point at which the volume is rooted. Can return an empty string. NOT NULL
volume_id nvarchar(512) Operating system volume ID. Can return an empty string NOT NULL
logical_volume_name nvarchar(512) Logical volume name. Can return an empty string NOT NULL
file_system_type nvarchar(512) Type of file system volume (for example, NTFS, FAT, RAW). Can return an empty string NOT NULL
total_bytes bigint Total size of the volume in bytes. NOT NULL
available_bytes bigint Available free space on the volume in bytes. NOT NULL
supports_compression bit Indicates if the volume supports operating system compression. NOT NULL
supports_alternate_streams bit Indicates if the volume supports alternate streams. NOT NULL
supports_sparse_files bit Indicates if the volume supports sparse files. NOT NULL
is_read_only bit Indicates if the volume is currently marked as read only. NOT NULL
is_compressed bit Indicates if this volume is currently compressed. NOT NULL

In old versions of SQL Server you can access the same information by writing your own CLR procedure by using one of the .NET framework languages.

Usage:

USE [master]
GO

SELECT * FROM sys.dm_os_volume_stats (database_id, file_id);
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/hh223223.aspx

6) sys.dm_exec_query_stats – This existing DMV has new columns added that return very useful information. These new columns are listed as below:

  • total_rows – Returns the total number of rows returned by the query
  • last_rows – Returns the total number of rows returned by last execution of the query.
  • min_rows – Returns the minimum number of rows query returned over the number of times that the plan has executed since it was last compiled.
  • max_rows – Returns the maximum number of rows query returned over the number of times that the plan has executed since it was last compiled.

Usage:

USE [master]
GO

SELECT * FROM sys.dm_exec_query_stats;
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/ms189741.aspx

Conclusion

In this article, I’ve covered the new useful DMVs of SQL Server 2008 R2 SP1 and SQL Server 2012 (DENALI). These DMVs help us to manage our SQL Servers efficiently by returning some very useful information. It is difficult to retrieve similar kind of information in older versions of SQL Servers and one need to write lengthy bespoke code to retrieve the similar information returned by these DMVs. I’ve already written some SSRS queries by using these DMVs which details the useful information which our companies auditors needs to audit our our SQL Servers.