Advertisements
Feeds:
Posts
Comments

Archive for the ‘T-SQL’ Category

Read-only routing refers to the ability of SQL Server to route incoming read-intent connection requests, which are directed to an availability group listener, to an available readable secondary replica. I find this little script very useful when I want to automate and and same time while limiting the human input errors during Read-Only Routing configuration.

 

Make sure to update the parameters’ value as per your AG and server details

USE [master]
GO
 
SET NOCOUNT ON
GO
 
DECLARE   @AGName               VARCHAR(40) = 'Your AG Name'      
        , @PrimaryNodeName      VARCHAR(40) = 'Your Primary Replica'     
        , @SecondaryNodeName    VARCHAR(40) = 'Your Secondary Replica'     
        , @PrimaryNodeIP        VARCHAR(40) = '10.5.6.10'  -- Not required unless marked 1 for @RouteUsingIP below
        , @SecondaryNodeIP      VARCHAR(40) = '10.6.6.11'  -- Not required unless marker 1 for @RouteUsingIP below
        , @Domain		VARCHAR(40) = '.Domain.com'
        , @RouteUsingIP         TINYINT     = 0            -- 1 for True, 0 for False (1 is recommended)
        , @PrimaryRoutingURL    VARCHAR(40) = ''
        , @SecondaryRoutingURL  VARCHAR(40) = ''
        , @SQLCommand           VARCHAR(2000)
 
 
IF @RouteUsingIP > 0
BEGIN
    SET @PrimaryRoutingURL = @PrimaryNodeIP
    SET @SecondaryRoutingURL = @SecondaryNodeIP
END
ELSE
BEGIN
    SET @PrimaryRoutingURL = @PrimaryNodeName + @Domain
    SET @SecondaryRoutingURL = @SecondaryNodeName + @Domain
END
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N''TCP://'+@PrimaryRoutingURL+':1433''));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N''TCP://'+@SecondaryRoutingURL+':1433''));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@PrimaryNodeName+''' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('''+@SecondaryNodeName+''','''+@PrimaryNodeName+''')));'
PRINT @SQLCommand
 
SET @SQLCommand = N'
ALTER AVAILABILITY GROUP ['+@AGName+'] MODIFY REPLICA ON
N'''+@SecondaryNodeName+''' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('''+@PrimaryNodeName+''','''+@SecondaryNodeName+''')));'
PRINT @SQLCommand
Advertisements

Read Full Post »

If you are familiar with the new feature Read-Only Routing introduced in SQL Server 2012 with AlwaysOn then you should keep this script handy to generate URLs to be used in Read-Only Routing configuration instead of typing down yourself to limit any errors.

Connect to each replica in your AlwaysOn Availability Group and run the below script to get the read_only_routing_url for that replica.
Then copy the URL from the result set and use it when setting up read_only_routing_url. Find more details on that here

PRINT 'Read-only-routing url script v.2012.1.24.1'
 
PRINT 'This SQL Server instance version is [' + cast(serverproperty('ProductVersion') as varchar(256)) + ']'
 
IF (ServerProperty('IsClustered') = 1) 
BEGIN
    PRINT 'This SQL Server instance is a clustered SQL Server instance.'
END
ELSE
BEGIN
    PRINT 'This SQL Server instance is a stANDard (not clustered) SQL Server instance.'   
END
 
IF (ServerProperty('IsHadrEnabled') = 1) 
BEGIN
    PRINT 'This SQL Server instance is enabled for AlwaysOn.'
END
ELSE
BEGIN
    PRINT 'This SQL Server instance is NOT enabled for AlwaysOn.'
END
 
-- Detect SQL Azure instance. 
DECLARE @is_sql_azure bit
SET @is_sql_azure = 0
 
BEGIN try 
    SET @is_sql_azure = 1 
    EXEC('DECLARE @i int SET @i = sql_connection_mode()') 
    PRINT 'This SQL Server instance is a Sql Azure instance.'
END try 
BEGIN catch 
    SET @is_sql_azure = 0 
    PRINT 'This SQL Server instance is NOT a Sql Azure instance.'
END catch
 
-- Check that this is SQL 11 or later, otherwise fail fast. 
IF (@@microsoftversion / 0x01000000 < 11 or @is_sql_azure > 0) 
BEGIN
    PRINT 'This SQL Server instance does not support read-only routing, exiting script.'
END
ELSE
BEGIN -- IF server supports read-only routing
 
    -- Fetch the dedicated admin connection (dac) port. 
    -- Normally it's always port 1434, but to be safe here we fetch it FROM the instance. 
    -- We use this later to exclude the admin port FROM read_only_routing_url. 
    DECLARE @dac_port int
    DECLARE @reg_value varchar(255) 
    EXEC xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp', 
        N'TcpDynamicPorts', 
        @reg_value output
 
    SET @dac_port = cast(@reg_value as int)
 
    PRINT 'This SQL Server instance DAC (dedicated admin) port is ' + cast(@dac_port as varchar(255)) 
    IF (@dac_port = 0) 
    BEGIN 
        PRINT 'Note a DAC port of zero means the dedicated admin port is not enabled.' 
    END
 
    -- Fetch ListenOnAllIPs value. 
    -- IF SET to 1, this means the instance is listening to all IP addresses. 
    -- IF SET to 0, this means the instance is listening to specIFic IP addresses. 
    DECLARE @listen_all int 
    EXEC xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp', 
        N'ListenOnAllIPs', 
        @listen_all output
 
    IF (@listen_all = 1) 
    BEGIN 
        PRINT 'This SQL Server instance is listening to all IP addresses (default mode).' 
    END 
    ELSE 
    BEGIN 
        PRINT 'This SQL Server instance is listening to specIFic IP addresses (ListenOnAllIPs is disabled).' 
    END
 
    -- Check for dynamic port configuration, not recommended with read-only routing. 
    DECLARE @tcp_dynamic_ports varchar(255) 
    EXEC xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', 
        N'TcpDynamicPorts', 
        @tcp_dynamic_ports output
 
    IF (@tcp_dynamic_ports = '0') 
    BEGIN 
        PRINT 'This SQL Server instance is listening on a dynamic tcp port, this is NOT A RECOMMENDED CONFIGURATION when using read-only routing, because the instance port can change each time the instance is restarted.' 
    END 
 ELSE 
    BEGIN 
        PRINT 'This SQL Server instance is listening on fixed tcp port(s) (it is not configured for dynamic ports), this is a recommended configuration when using read-only routing.' 
    END
 
    -- Calculate the server domain AND instance FQDN. 
    -- We use @server_domain later to build the FQDN to the clustered instance. 
    DECLARE @instance_fqdn varchar(255) 
    DECLARE @server_domain varchar(255)
 
    -- Get the instance FQDN using the xp_getnetname API 
    -- Note all cluster nodes must be in same domain, so this works for calculating cluster FQDN. 
    SET @instance_fqdn = '' 
		EXEC xp_getnetname @instance_fqdn output, 1 
 
    -- Remove embedded null character at END IF found. 
    DECLARE @terminator int 
		SET @terminator = charindex(char(0), @instance_fqdn) - 1 
			 IF (@terminator > 0) 
		BEGIN 
			SET @instance_fqdn = substring(@instance_fqdn, 1, @terminator) 
		END
     -- Build @server_domain using @instance_fqdn. 
		 SET @server_domain = @instance_fqdn
     -- Remove trailing portion to extract domain name. 
		 SET @terminator = charindex('.', @server_domain) 
    IF (@terminator > 0) 
		 BEGIN 
			SET @server_domain = substring(@server_domain, @terminator+1, datalength(@server_domain)) 
		 END 
    PRINT 'This SQL Server instance resides in domain ''' +  @server_domain + ''''
 
    IF (ServerProperty('IsClustered') = 1) 
    BEGIN 
        -- Fetch machine name, which for a clustered SQL instance returns the network name of the virtual server. 
        -- AppEND @server_domain to build the FQDN. 
        SET @instance_fqdn = cast(serverproperty('MachineName') as varchar(255)) + '.' + @server_domain 
    END
 
    DECLARE @ror_url varchar(255) 
    DECLARE @instance_port int
 
    SET @ror_url = '' 
    -- Get first available port for instance. 
    SELECT TOP 1    -- SELECT first matching port 
    @instance_port = port 
    FROM sys.dm_tcp_listener_states 
    WHERE type=0 -- Type 0 = TSQL (to avoid mirroring ENDpoint) 
    AND state=0    --  State 0 is online    
    AND port <> @dac_port -- Avoid DAC port (admin port) 
    AND 
    -- Avoid availability GROUP listeners 
    ip_address not in (SELECT ip_address FROM sys.availability_GROUP_listener_ip_addresses agls) 
    GROUP by port        
    ORDER BY port asc  -- Pick first port in ascENDing ORDER
 
    -- Check IF there are multiple ports AND warn IF this is the case. 
    DECLARE @list_of_ports varchar(max) 
    SET @list_of_ports = ''
 
    SELECT 
    @list_of_ports = @list_of_ports + 
        case datalength(@list_of_ports) 
        when 0 then cast(port as varchar(max)) 
        ELSE ',' +  cast(port as varchar(max)) 
        END 
    FROM sys.dm_tcp_listener_states 
    WHERE type=0    --     Type 0 = TSQL (to avoid mirroring ENDpoint) 
    AND  state=0    --  State 0 is online    
    AND  port <> @dac_port -- Avoid DAC port (admin port) 
    AND 
    -- Avoid availability GROUP listeners 
    ip_address not in (SELECT ip_address FROM sys.availability_GROUP_listener_ip_addresses agls) 
    GROUP BY port        
    ORDER BY port asc
 
    PRINT 'This SQL Server instance FQDN (Fully QualIFied Domain Name) is ''' + @instance_fqdn + '''' 
    PRINT 'This SQL Server instance port is ' + cast(@instance_port as varchar(10))
 
    SET @ror_url = 'tcp://' + @instance_fqdn + ':' + cast(@instance_port as varchar(10))
 
    PRINT '****************************************************************************************************************' 
    PRINT 'The read_only_routing_url for this SQL Server instance is ''' + @ror_url + '''' 
    PRINT '****************************************************************************************************************'
 
    -- IF there is more than one instance port (unusual) list them out just in case. 
    IF (charindex(',', @list_of_ports) > 0) 
    BEGIN 
        PRINT 'Note there is more than one instance port, the list of available instance ports for read_only_routing_url is (' + @list_of_ports + ')' 
        PRINT 'The above URL just uses the first port in the list, but you can use any of these available ports.' 
    END
	END

Read Full Post »

DBAs most often face scenarios where they need to capture graphical execution plan of a query currently running on a live production instance because of multiple reasons like:

  • why a particular SPID is causing blocking
  • why is my query running slow
  • why isn’t the index getting used
  • which operator is costing more and why

While there are multiple ways to retrieve the execution plan, below is the query I always keep handy as I can run this safely on a live production server with minimal effort.

SELECT CONVERT(XML, c.query_plan) AS ExecutionPlan
FROM sys.dm_exec_requests a with (nolock)
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
OUTER APPLY sys.dm_exec_text_query_plan (a.plan_handle, a.statement_start_offset, a.statement_end_offset) c
LEFT JOIN sys.dm_exec_query_memory_grants m (nolock)
ON m.session_id = a.session_id
AND m.request_id = a.request_id
JOIN sys.databases d
ON d.database_id = a.database_id
WHERE  a.session_id = @@SPID --replace @@SPID with the SPID number for which you want to capture query plan
ORDER BY a.Start_Time
 

Hope this will be a good addition to your query bank.

Read Full Post »

Recently I came across a situation where queries are loading extremely slow from a table. After careful analysis we found the root cause being, a column with ntext datatype was getting inserted with huge amounts of text content/data. In our case DATALENGTH T-SQL function came real handy to know the actual size of the data in this column.

According to books online, DATALENGTH (expression) returns the length of the expression in bytes (or) the number of bytes SQL needed to store the expression which can be of any data type. From my experience this comes very handy to calculate length and size especially for LOB data type columns (varchar, varbinary, text, image, nvarchar, and ntext) as they can store variable length data. So, unlike LEN function which only returns the number of characters, the DATALENGTH function returns the actual bytes needed for the expression.

Here is a small example:

Use AdventureWorksLT2012
GO
Select ProductID, DATALENGTH(Name) AS SizeInBytes, LEN(Name) AS NumberOfCharacters
FROM [SalesLT].[Product]

 

–Results

DATALENGTH

If your column/expression size is too large like in my case, you can replace DATALENGTH(Name) with DATALENGTH(Name)/1024 to convert to KB or with DATALENGTH(Name)/1048576 to get the size in MB.

Read Full Post »

An auto-growth event is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file should grow is determined by the value that is set in the SQL configuration. While every organization has its own standards for this setting, however there are some widely followed best practices like for instance the auto-growth setting should not be in percent value

In this post I have put down two scripts that you can customize per your organizational standards. The first script helps you to identify the current settings and the second one can be used to update auto-growth settings. Both the scripts are targeted for all the databases by default, however you can call out the databases that you want to exclude from this change. Please make sure that you test these changes before pushing out to production servers.

Script 1: Identify the current database settings

select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN
CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN
CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size)
END AS [max_size]
, physical_name
from sys.master_files mf

Script 2: Update database auto-growth values

/*
****MODIFICATION REQUIRED for AUTOGROWTH -- See line 64 below****
1) Use this script to change the auto growth setting of
   for all databases
2) If you want to exclude any database add the DBs in the
   WHERE Clause -- See line 50 below
3) Tested in 2012 and 2014 SQL Servers
*/

IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')
DROP TABLE ConfigAutoGrowth
GO
CREATE TABLE DBO.ConfigAutoGrowth
(
iDBID INT,
sDBName SYSNAME,
vFileName VARCHAR(max),
vGrowthOption VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth Created'
GO
-- Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT
SD.database_id,
SD.name,
SF.name,
CASE SF.status
WHEN 1048576 THEN 'Percentage'
WHEN 0 THEN 'MB'
END AS 'GROWTH Option'
FROM SYS.SYSALTFILES SF
JOIN
SYS.DATABASES SD
ON
SD.database_id = SF.dbid
GO

-- Dynamically alters the file to set auto growth option to fixed mb
DECLARE @name VARCHAR ( max ) -- Database Name
DECLARE @dbid INT -- DBID
DECLARE @vFileName VARCHAR ( max ) -- Logical file name
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql

DECLARE db_cursor CURSOR FOR
SELECT
idbid,sdbname,vfilename,vgrowthoption
FROM configautogrowth
--WHERE sdbname NOT IN ( 'master' ,'msdb' ) --<<--ADD DBs TO EXCLUDE
--AND vGrowthOption = 'Percentage' or 'Mb'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)

/******If you want to change the auto growth size to a different 
value then just modify the filegrowth value in script below *********/

SET @Query = 'ALTER DATABASE '+ '[' + @name + ']' +'
MODIFY FILE (NAME = '+ '[' +@vFileName + ']' +',FILEGROWTH = 5MB)' --<<--ADD AUTOGROWTH SIZE HERE
EXECUTE(@Query)

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption
END
CLOSE db_cursor -- Closing the curson
DEALLOCATE db_cursor -- deallocating the cursor

GO
-- Querying system views to see if the changes are applied
DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)
SET @SQL=' USE [?]
SELECT ''?'' [Dbname]
,[name] [Filename]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''
ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''
END [Autogrow_Value]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM [?].sys.database_files'

IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')
DROP TABLE ##Fdetails
CREATE TABLE ##Fdetails
(Dbname VARCHAR(50),Filename VARCHAR(50),
Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))
INSERT INTO ##Fdetails
EXEC sp_msforeachdb @SQL
SELECT * FROM ##Fdetails ORDER BY Dbname

--Dropping the staging table
DROP TABLE ConfigAutoGrowth
GO

Read Full Post »

In order to reduce Tempdb contention one of  the best practices is to maintain multiple sized Tempdb data files, matching the number of processors and up to a maximum of 8. In this post I will show you T-SQL script to identify current Tempdb configuration and number of logical processors along with adding additional Tempdb data files as required.

Script 1: Find current tempdb configuration


select DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'

Script 2: Find number of logical processors

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info

Script 3: Add tempdb data files as per processor count from the above query

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
FILENAME = N'D:\DBA\Data\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --<<--Update the data file location/Size/AutoGrowth
GO

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',
FILENAME = N'D:\DBA\Data\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
GO
---ETC, add files as per processors count

Reboot/Restart of SQL services is not required for making the tempdb changes. Here is a great post that explains how to best remove extra Tempdb files.

For more information:

 

Read Full Post »

Today morning when I was working on a huge database containing lots of LOB data, I was required to know what tables have LOB data, and the list of LOB columns by table name along with the data type.

I found that starting SQL Server 2005, we can easily retrieve this information from the Information Schema Views, by specifying “COLUMN_NAME” in view_name. There may be even a better way to do this, but here is what I came-up with.

USE [AdventureWorks2012]
GO
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE IN ('FILESTREAM','XML','VARBINARY','TEXT','NTEXT','IMAGE') 
OR(DATA_TYPE IN ('VARCHAR', 'NVARCHAR') AND CHARACTER_MAXIMUM_LENGTH = -1)
ORDER BY TABLE_NAME

HERE is an explanation by Pinal Dave, why I have to include CHARACTER_MAXIMUM_LENGTH = -1 in the above query

Sample Output:

Untitled

Below column types are considered LOB (Large Objects):
VARCHAR(MAX), NVARCHAR(MAX), FILESTREAM, XML, VARBINARY,
TEXT, NTEXT, IMAGE

Note: Data types TEXT, NTEXT and IMAGE were deprecated in SQL Server 2012 and are going to be removed in future versions, so Microsoft doesn’t recommend using them in new applications. For more information, please refer HERE

Read Full Post »

Even though there are more pros than cons, Striping database backups are often overlooked by many DBAs. Based on my observations in our environment, striping can significantly benefit larger database backups (~500+ GB).

As shown in the picture below, striping is nothing but splitting one backup file to multiple backup files (maximum 64 files). However, these files may or may not be the same size (depends on the storage disks IO).

Striped Backups

By Striping a backup we can:

  • Increase backup throughput and reduce the backup time window
  • Allow backups & restores to be written or to be read from all devices in parallel
  • Enable backup to different disks, thus distribute the space usage

Below are the simple T-SQL backup commands using AdventureWorks2012 sample database as an example.

T-SQL command for Striping a database backup

Note: In the below script, I used only Disk C to contain all the striped .bak files. However, we can  direct to multiple disks if required

-- Striped Backups -- Backup to multiple files - 4 files in this case
BACKUP DATABASE [AdventureWorks2012]
TO 
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10
GO

T-SQL command to restore from Striped database backup 

--Restoring from striped backup -- from multiple files
RESTORE DATABASE [AdventureWorks2012] 
FROM  
DISK='C:\AdventureWorks2012_1.bak', 
DISK='C:\AdventureWorks2012_2.bak', 
DISK='C:\AdventureWorks2012_3.bak',
DISK='C:\AdventureWorks2012_4.bak'
WITH STATS = 10 
GO

Also, we can apply the same striping concept on Log backups. Below is how we do it

T-SQL command for Striping transaction log backup

--Striped Log backup
BACKUP LOG [AdventureWorks2012]
TO
DISK='C:\AdventureWorks2012_1.trn', 
DISK='C:\AdventureWorks2012_2.trn', 
DISK='C:\AdventureWorks2012_3.trn',
DISK='C:\AdventureWorks2012_4.trn'
WITH STATS = 10
GO

Demo: Normal Backup Vs Striped Backup

Below are the results and screenshots from a live production environemnt. This once again proves striping backup files increase data transfer rate and reduce the time to backup

Results:

Backup Type Time to Backup Data Transfer Rate
Normal Backup (1 File) 537.6 Seconds 111.9 MB/Sec
Striped Backup (4 Files) 201.0 Seconds 299.3 MB/Sec

Screenshots:

StripedBackupDemo_1of2

StripedBackupDemo_2of2

However, the major downside of striping a backup is that if at-least one backup file is corrupt, restore operation cannot be performed using the other files.

Also, HERE is why I haven’t discussed striping backups using SSMS GUI.

Technical Reviewer(s): Hareesh Gottipati; Jaipal Vajrala

Read Full Post »

Ever noticed and wondered why the well-known SQL Server system administrator (sa) login is in a disabled state?

SA Login

The reason is simple, sa login account is disabled out of the box (by default) in Windows Authentication mode. You have to enable manually to use it.

On the other hand, if you request Mixed Mode Authentication during installation, SQL Server Setup prompts you to set an sa login password. So sa login account is enabled in this case.

Enable/Disable sa login using SSMS GUI:

  • From the Object Explorer, expand “Security” and then expand “Logins” –> Right click on sa and select “Properties”

SA Login - 1

  • On the “General” tab, create a strong password

SA Login - 2

  • On the “Status” tab, click “Enabled”, and then click “ok” (If sa is already enabled, you have to chose “Disabled” to disable the same)

SA Login - 3

Enable sa login using T-SQL script:

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '$trongPa$$w@rD';
GO

Disable sa login using T-SQL script:

ALTER LOGIN sa DISABLE ;
GO

Here’s some more info about “sa” account:

  • System administrator (sa) is a special login provided for backward compatibility
  • Usually there is no effect sa being in a disabled state though it pertains and owns the system databases
  • By default, sa login is assigned to the sysadmin fixed server role and cannot be changed
  • Microsoft do not recemmond using sa login in application (as it is often targeted by malicious users)
  • However, Microsoft recommonds using sa only when there is no other way to log in to an instance
  • The sa login cannot be removed/deleted

Technical Reviewer: Jaipal Vajrala

Read Full Post »

Most of the DBAs at some point of time in their career should have faced at-least one of the below situation(s):

1. The disk/SAN where the database files are located is going to be replaced
2. Disk is full and no more free space available
3. Data files and/or log files are not located on standard drives

There can be even more secnarios like the above where we may need to move the database files from current location to a new location. Starting SQL Server 2005 this can be simply achieved by using ALTER DATABASE T-SQL command

Let us take the below scenario for step-by-step Demo:

Database: AdventureWorks2012
Current Files Location: C:\Disk1
New\Target Files Location: C:\Disk2

Step 1: Get the current database files Logical Name and Physical Location

USE master
GO
SELECT name AS LogicalFileName, physical_name AS FileLocation
, state_desc AS Status 
FROM sys.master_files 
WHERE database_id = DB_ID('AdventureWorks2012');
1

Step 2: Take the Database offline

USE master
GO
ALTER DATABASE AdventureWorks2012 SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

2

Note: In the above T-SQL query, I used WITH ROLLBACK IMMEDIATE option. Please be careful when using this option as it rolls back those incomplete transactions immediately. You may exclude this option, but have to wait till all the transactions are committed to take the database offline.

Step 3: Move the database files physically in the Windows OS to the new location

3

4

Step 4: Use ALTER DATABASE to modify the FILENAME to new location for every file moved

Only one file can be moved at a time using ALTER DATABASE.

USE master
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE 
( NAME = AdventureWorks2012_Data, 
FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path

USE master
GO
ALTER DATABASE AdventureWorks2012 
MODIFY FILE 
( NAME = AdventureWorks2012_Log, 
FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path
5

Step 5: Set the database ONLINE

USE master
GO
ALTER DATABASE AdventureWorks2012 SET ONLINE;

Step 6: Now, verify the database files Physical location

USE master
GO
SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status 
FROM sys.master_files 
WHERE database_id = DB_ID('AdventureWorks2012');
6

Note: Same method can be used for moving files for any system or user defined database except for Resource database files

Read Full Post »

Older Posts »