Archive

Author Archive

Copy backup file between different domains using mapped network drives

September 27, 2015 Leave a comment

How to copy backup files between to different domains having trust relationship?

 

use master
go
exec xp_cmdshell 'net use Z: \\sourceserver\H$\Backups password /user:domain\username'
go
exec usp_copybackupfiles 
@searchdirectory='Z:\',
@filefilter='.bak',
@database='AdventureWorks2014',
@Destination='H:\Backups\copyfiles\'
go
Exec master.dbo.xp_cmdshell 'net use Z: /delete'
go
Categories: T-SQL-Scripts

Copy backup files between locations –TSQL

September 27, 2015 Leave a comment

Handy Script to Copy the latest backup file between locations

 

/*
Written by : Mohammad Sufian
Location : Singapore

Copywrite : None.
Suggestion/Feedback : gosgenq@gmail.com

Supported versions: SQL Server 2008 R2 to SQL Server 2014
*/


CREATE PROCEDURE dbo.usp_copybackupfiles 
@searchdirectory	 nvarchar(4000),
@filefilter			 varchar(7),
@database			 varchar(128),
@Destination		 nvarchar(4000)

AS
BEGIN

	SET NOCOUNT ON


	DECLARE @command						varchar(8000)
	--DECLARE @searchdirectory				nvarchar(4000)	='H:\Backups'
	--DECLARE @filefilter					varchar(7)		='.bak'
	--DECLARE @database						varchar(128)	='AdventureWorks2014'
	DECLARE @useallmatchingfiles			int				=		0

	DECLARE @RestoreDataLocation					nvarchar(4000)
	DECLARE @RestoreLogLocation						nvarchar(4000)

	DECLARE @start int=1
	DECLARE @end int

	DECLARE @Source				nvarchar(4000)
	--DECLARE @Destination		nvarchar(4000)='H:\Backups\copyfiles\'
	DECLARE @cmd				nvarchar(4000)
	DECLARE @cmd2				nvarchar(4000)
	DECLARE @backupfile			nvarchar(4000)
	DECLARE @checkfile			nvarchar(4000)
	DECLARE	@combineBackups		varchar(max)

	IF NOT EXISTS (select * from master.sys.sysobjects where name='CopyBackupFileStatus')
		BEGIN
			CREATE TABLE master.dbo.CopyBackupFileStatus
			(
			id int identity,
			sourcelocation nvarchar(4000) not null,
			destinationlocation nvarchar(4000) not null,
			[filename]			nvarchar(4000) not null,
			[copydate]			datetime
			)
		END



	create table #Output (outputtext varchar(max))

	DECLARE @BackupFiles Table (id int identity,BackupFileName nvarchar(4000))
	DECLARE @fileexists TABLE (File_Exists BIT, File_Is_A_Directory BIT, Parent_Directory_Exists BIT)
	DECLARE @MovedBackupFiles Table (id int identity,BackupFileName nvarchar(4000))
	CREATE TABLE #vDBHList 
	(
	DBID INT NOT NULL       IDENTITY(1, 1),BackupName        nvarchar(128),BackupDescription  nvarchar(255),
	BackupType              smallint,ExpirationDate          datetime,Compressed              tinyint,
	Position                smallint,DeviceType              tinyint,UserName                 nvarchar(128),
	ServerName              nvarchar(128),DatabaseName       nvarchar(128),DatabaseVersion    int,
	DatabaseCreationDate    datetime,BackupSize              numeric(20,0),FirstLSN           numeric(25,0),
	LastLSN                 numeric(25,0),CheckpointLSN      numeric(25,0),DatabaseBackupLSN  numeric(25,0),
	BackupStartDate         datetime,BackupFinishDate        datetime,SortOrder               smallint,
	CodePage                smallint,UnicodeLocaleId         int,UnicodeComparisonStyle       int,
	CompatibilityLevel      tinyint,SoftwareVendorId         int,SoftwareVersionMajor         int,
	SoftwareVersionMinor    int,SoftwareVersionBuild         int,MachineName                  nvarchar(128),
	Flags                   int,BindingID                    uniqueidentifier,RecoveryForkID  uniqueidentifier,
	Collation               nvarchar(128),FamilyGUID         uniqueidentifier,HasBulkLoggedData       bit,
	IsSnapshot              bit,IsReadOnly                   bit,IsSingleUser            bit,
	HasBackupChecksums      bit,IsDamaged                    bit,BeginsLogChain          bit,
	HasIncompleteMetaData   bit,IsForceOffline               bit,IsCopyOnly              bit,
	FirstRecoveryForkID     uniqueidentifier,ForkPointLSN    numeric(25,0) NULL,RecoveryModel           nvarchar(60),
	DifferentialBaseLSN     numeric(25,0) NULL,DifferentialBaseGUID    uniqueidentifier,
	BackupTypeDescription   nvarchar(60),BackupSetGUID       uniqueidentifier NULL
	)
	Declare @version varchar(2)
	select @version = substring(convert(nvarchar(100),serverproperty('ProductVersion')),0, 
                                    charindex('.',convert(nvarchar(100),serverproperty('ProductVersion'))))
	--print @version
	if (convert (int,@version ) <12)
		begin
		 ALTER TABLE #vDBHList  ADD CompressedBackupSize int
		 ALTER TABLE  #vDBHList  ADD Containment int	 
		end

	else if (convert (int,@version ) >=12)
		begin
		 ALTER TABLE #vDBHList  ADD CompressedBackupSize int
		 ALTER TABLE  #vDBHList  ADD Containment int	 
		 ALTER TABLE  #vDBHList  ADD KeyAlgorithm nvarchar(32)	 
		 ALTER TABLE  #vDBHList  ADD EncryptorThumbprint varbinary(20)	 
		 ALTER TABLE  #vDBHList  ADD EncryptorType nvarchar(32)	 
		end

	--select @command = 'powershell "$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (1500, 25); $a = Get-Date; $a = $a.AddDays(-6); ' 
	select @command = 'powershell "' 
	select @command += '$path = '''+@searchdirectory+''';$files = @();$path.split('','') |% {$innerPath = $_; $files += get-childitem $innerpath}; '
	--select @command += '$files = $files  |? {$_.CreationTime -gt $a  -AND $_.Extension -eq '''+UPPER(@filefilter)+''' -AND $_.name.toupper().contains('''+UPPER(@database)+''')};  ; '
	select @command += '$files = $files  |? {$_.Extension -eq '''+UPPER(@filefilter)+''' -AND $_.name.toupper().contains('''+UPPER(@database)+''')};  ; '
	select @command += ' $files '

	if @useallmatchingfiles = 0 
	select @command += ' |? {$_.CreationTimeUTC -gt (($files | sort CreationTimeUTC -desc | select CreationTimeUTC -unique -first 1).CreationTimeUTC).AddSeconds(-1)} '

	select @command += ' | ft fullname;"'

	----print @command
	insert into #Output exec xp_cmdshell @command

	DELETE from #Output where outputtext IS NULL or outputtext ='FullName                                                                       ' 
	or  outputtext='--------                                                                       '
	insert into @BackupFiles
	SELECT * from #Output
	SET @end=@@ROWCOUNT

		WHILE @start <= @end

			BEGIN

				SELECT @Source=BackupFileName FROM @BackupFiles WHERE id=@start	
				SET @cmd='copy '+@Source+ ' ' +@Destination
				--print @Source

				SET @backupfile=REVERSE(SUBSTRING (REVERSE(@source),0,CHARINDEX('\',REVERSE(@source))))

					--//--
					--Check if file exists and do the verfication
					--//--
							SET @checkfile=@Destination+@backupfile
				
							insert into @fileexists
							exec master..xp_fileexist  @checkfile
				
							if (select File_Exists from  @fileexists)=1
								begin								
									set @cmd2='RESTORE HEADERONLY FROM DISK= '+''''+@checkfile+''''
									insert into #vDBHList
									exec(@cmd2)								
									insert into @MovedBackupFiles values (@checkfile)
								end						
							else
								begin
									EXECUTE xp_cmdshell @cmd,no_output
									insert into @MovedBackupFiles values (@checkfile)
									insert into master.dbo.CopyBackupFileStatus(sourcelocation,destinationlocation,[filename],copydate) values (@Source,@Destination,@backupfile,GETDATE())
								end
							DELETE FROM @fileexists
							--print @backupfile			
				SET @start=@start+1
			END
		
	drop table #vDBHList
	drop table #Output

	SET NOCOUNT OFF

END

 

How to use it?

 

usp_copybackupfiles 
@searchdirectory='H:\Backups',
@filefilter='.bak',
@database='AdventureWorks2014',
@Destination='H:\Backups\copyfiles\'

 

The script simple reads the file information from disk and copy the files to destination.

Categories: T-SQL-Scripts

Map/disconnect network drives – TSQL

September 27, 2015 Leave a comment

 

Configure Map Network Drive using T-SQL

 

exec xp_cmdshell 'net use Z: \\ServerName\H$\FolderName <password> /user:domain\username'

Disconnect Mapped Network drives

 

Exec master.dbo.xp_cmdshell 'net use Z: /delete'

Categories: T-SQL-Scripts

Resource Governor and In-Memory OLTP

September 23, 2015 Leave a comment

from BOL

 

A resource pool represents a subset of physical resources that can be governed. By default, SQL Server databases are bound to and consume the resources of the default resource pool. To protect SQL Server from having its resources consumed by one or more memory-optimized tables, and to prevent other memory users from consuming memory needed by memory-optimized tables, you should create a separate resource pool to manage memory consumption for the database with memory-optimized tables.

A database can be bound on only one resource pool. However, you can bind multiple databases to the same pool. SQL Server allows binding a database without memory-optimized tables to a resource pool but it has no effect. You may want to bind a database to a named resource pool if, in future, you may want to create memory-optimized tables in the database.

Before you can bind a database to a resource pool both the database and the resource pool must exist. The binding takes effect the next time the database is brought online

 

USE [master]
GO

CREATE RESOURCE POOL [mem_xtp_pool] WITH(min_cpu_percent=0,
        max_cpu_percent=100,
        min_memory_percent=50,
        max_memory_percent=60,
        cap_cpu_percent=100,
        AFFINITY SCHEDULER = AUTO
,
        min_iops_per_volume=0,
        max_iops_per_volume=0)

GO

Bind the database to the resource pool.

EXEC sp_xtp_bind_db_resource_pool 'DatabaseName', 'mem_xtp_pool' GO

Confirm the binding.The resource pool id for database should be NULL.

SELECT d.database_id, d.name, d.resource_pool_id
FROM sys.databases d
GO

 

Make the binding effective

Take the database offline and back online after binding it to the resource pool

 

ALTER DATABASE DatabaseName SET OFFLINE

GO

ALTER DATABASE DatabaseName SET ONLINE

Categories: In-MemoryOLTP

xtp Transaction stuck

September 23, 2015 Leave a comment

 

Why the xtp transaction do not kill or rollback when a long running process is stopped?

 

A long running transaction inserting millions of records in a In-Memory OLTP Table  is stopped but the spid remains in running state, it do not kill or rollback.

 

Even you try to kill the session it do not actually kill the session. Run the Kill spid WITH STATUSONLY. It might give the below message

SPID 56: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

 

We checked the sys.databases log_reuse_wait_desc column and it says waiting for xtp_checkpoint

 

image

 

We never thought the xtp transaction might keep on waiting for a checkpoint.

 

Before we manually issued the checkpoint we enabled TRACE flag 3502 & 3605 to see what happens in the background

 

DBCC TRACEON (3502, 3605, -1);

checkpoint

 

image

Categories: In-MemoryOLTP

The Service Broker endpoint is in disabled or stopped state. Replica Fails to Join the Availability Group

September 18, 2015 Leave a comment

We recently configured Always On AG group and Listeners , using T-SQL we added a Replica which fails to join the AG Group

Evey time we try to join the AG Group it fails with “The Service Broker endpoint is in disabled or stopped state.” which is unrelated and misleading. To get to the root of the problem we start troubleeting the issue with Endpoints and ports

 

  • Check Mirroring Endpoint  exists and LISTENER_PORT is configured
  • Check Primary Replica ENDPOINT_URL , the ENDPOINT_URL should be using the same port as Mirroring Endpoint LISTENER_PORT

In our case Mirroring Endpoint  was missing from Primary and Secondary Replica. The DBA created the AG group manually which do not check if mirroring endpoint exists or not. The ENDPOINT_URL was configured with a port no which was not he default for Mirroring Endpoint

Primary Replica was configured with a different ENDPOINT other then the default mirroring end point, so we can’t modify it and we don’t want to drop AG group and listener.

 

So, to fix the issue we removed the secondary Replica from  AG Group , dropped Mirroring Endpoint, recreated mirroring endpoint with the port no of Primary Replica ENDPOINT_URL. Add Secondary Replica to the AG group and issues the alter Availability group [AG Name ] join command , which completed successfully.

 

 

Categories: AlwaysOn 2012

BUCKET_COUNT for Hash Indexes for SQL Server Memory Optimized Tables

September 12, 2015 Leave a comment

 

Indexes are used as entry points for memory-optimized tables.Data in memory is quickly read by index to locate in-memory data.

[From BOL]

A hash index consists of a collection of buckets organized in an array. A hash function maps index keys to corresponding buckets in the hash index. The following figure shows three index keys that are mapped to three different buckets in the hash index. For illustration purposes the hash function name is f(x).

Index keys mapped to different buckets.

 

The hashing function used for hash indexes has the following characteristics:

  • SQL Server has one hash function that is used for all hash indexes.

  • The hash function is deterministic. The same index key is always mapped to the same bucket in the hash index.

  • Multiple index keys may be mapped to the same hash bucket.

  • The hash function is balanced, meaning that the distribution of index key values over hash buckets typically follows a Poisson distribution.

    Poisson distribution is not an even distribution. Index key values are not evenly distributed in the hash buckets. For example, a Poisson distribution of n distinct index keys over n hash buckets results in approximately one third empty buckets, one third of the buckets containing one index key, and the other third containing two index keys. A small number of buckets will contain more than two keys.

If two index keys are mapped to the same hash bucket, there is a hash collision. A large number of hash collisions can have a performance impact on read operations.

The in-memory hash index structure consists of an array of memory pointers. Each bucket maps to an offset in this array. Each bucket in the array points to the first row in that hash bucket. Each row in the bucket points to the next row, thus resulting in a chain of rows for each hash bucket, as illustrated in the following figure.

 

The in-memory hash index structure.

 

The figure has three buckets with rows. The second bucket from the top contains the three red rows. The fourth bucket contains the single blue row. The bottom bucket contains the two green rows. These could be different versions of the same row.

 

Create In-Memory OLTP table

 

USE [AdventureWorks2014]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sales].[SalesOrderDetail_imoltp4]
(
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
	[CarrierTrackingNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[SpecialOfferID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount4]  DEFAULT ((0.0)),
	[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderDetail_ModifiedDate4]  DEFAULT (getdate()),

INDEX [IX_SalesOrderDetail_ProductId_4] NONCLUSTERED 
(
	[ProductID] ASC
),
CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID_IN4] PRIMARY KEY NONCLUSTERED HASH 
(
	[SalesOrderID],
	[SalesOrderDetailID]
)WITH ( BUCKET_COUNT = 128)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

 

Created PRIMARY KEY NONCLUSTERED HASH Index with BUCKET_COUNT=128. BUCKET_COUNT indicates the no of buckets in the hash index.In this case we have 128 buckets.The max value we can have for BUCKET_COUNT=1,073,741,824

 

CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID_IN4] PRIMARY KEY NONCLUSTERED HASH 
(
	[SalesOrderID],
	[SalesOrderDetailID]
)WITH ( BUCKET_COUNT = 128)

 

using Dynamic Management view we can monitor how BUCKET_COUNT is used.sys.dm_db_xtp_hash_index_stats

image

 

select * from sys.dm_db_xtp_hash_index_stats

 

image

 

HASH Index Statistics

 

Execute the below code to see how BUCKET_COUNT is used.

SELECT 
   object_name(hs.object_id) AS 'object name', 
   i.name as 'index name', 
   i.index_id,
   hs.total_bucket_count,
   hs.empty_bucket_count,
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',
   hs.avg_chain_length, 
   hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs 
   JOIN sys.indexes AS i 
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id

 

 

image

 

As you can see PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID_IN4 Primary Key Hash Index has a total of 128 BUCKET_COUNT, and as we insert the records  empty_bucket_count will decrease, max_chain_length column will show no of records in each bucket.

 

 

image

 

image

 

After inserting few  millions of records all the BUCKET_COUNT where used and each BUKCET has 90156 rows

 

image

Categories: In-MemoryOLTP