File Table Database Backup and Restore

There is not much change in restore process, the additional process is to backup and restore file stream file and data. Enable dbcc traceon(3004, 3605, -1) to write backup and restore background process in error log. RESTORE DATABASE [SQL2016DB] FROM DISK=’H:\DatabaseBackup_FileTable\SQL2016DB_FULL.BAK’ WITH REPLACE,RECOVERY,STATS=5 Backup Background Process 2015-10-21 00:33:42.940 spid54 Backup(SQL2016DB): BACKUP DATABASE started 2015-10-21 00:33:42.940 spid54 […]

Working with FileTables in SQL Server 2012 – Part 1- Troubleshooting

Catalog Views select db_name(database_id) DatabaseName,non_transacted_access,non_transacted_access_desc,directory_name from sys.database_filestream_options select object_name(object_id),object_name(parent_object_id) from sys.filetable_system_defined_objects select * from sys.filetables select * from sys.tables where is_filetable=1 [Function] FileTableRootPath : Returns the root-level UNC path for a specific FileTable or for the current database. USE <Database Name> GO select FileTableRootPath(null,null) [Function] GetFileNamespacePath : Returns the UNC path for a file or […]

Working with FileTables in SQL Server 2012 – Part 1- Configuration

Enabled filestream from the SQL server Services and restart the SQL Server services for the changes to take effect. — Enable Filestream, by default its set to 0 (disabled) EXEC sp_configure filestream_access_level, 2 RECONFIGURE After configuration change take effect the “FILESTREAM Access Level” will change to “Full access enabled” Add FileStream FileGroup and file to […]

Remove Availability Group -Replica -Databases-Listener-

Dropping the Availability Group will remove all associated objects . 1.Database (s) will be removed from AG databases 2.Listener will be deleted (object will be dropped from Active Directory). 3.Replicas will be removed from the AG group configuration   USE [master] GO DROP AVAILABILITY GROUP [AG Group Name] GO Dropping Availability Group USE [master] GO ALTER […]

Partition Scripts

Script to get table and its partition details SELECT fg.groupname FileGroupName, [FileLocation],sf.fileid, [filename],ds.type,ds.type_desc,object_name(sp.object_id) ObjectName,sp.index_id,sp.rows,ds.data_space_id ,au.type_desc,au.total_pages,au.used_pages,au.data_pages, sp.partition_number FROM sys.sysfiles sf INNER JOIN sys.sysfilegroups fg ON sf.groupid=fg.groupid INNER JOIN sys.data_spaces ds ON fg.groupid=ds.data_space_id INNER JOIN sys.allocation_units au ON ds.data_space_id=au.data_space_id INNER JOIN sys.partitions sp ON au.container_id=sp.hobt_id INNER JOIN sys.tables st ON sp.object_id=st.object_id –LEFT JOIN sys.partition_schemes ps ON ps.data_space_id=ds.data_space_id […]

Monitor Stretch Database Activity

Microsoft SQL Server has provided couple of DMV’s to Monitor Stretch Database ,Stretch Objects and Data Movement to Azure SQL Server database sys.remote_data_archive_databases sys.sysservers sys.remote_data_archive_tables sys.dm_db_rda_migration_status I have a video on how to use these DMV’s and what information do they have

Enable Table for Stretch Database – Part 3

Enabling Stretch Database on a database or a table requires db_owner permissions. Enabling Stretch Database on a table also requires ALTER permissions on the table. USE <DATABASE NAME> GO ALTER TABLE <table name> ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE = ON ); GO; example: use SQL2016DB GO ALTER TABLE t1 ENABLE REMOTE_DATA_ARCHIVE WITH (MIGRATION_STATE = ON […]

Enable Database for Stretch – Part 2

You can enable database for stretch using T-SQL and to existing Azure Database ALTER DATABASE <database name> SET REMOTE_DATA_ARCHIVE = ON (SERVER = server name); GO Check if Database is enabled for Stretch use master go select is_remote_data_archive_enabled,name from sys.databases where is_remote_data_archive_enabled=1 go example ALTER DATABASE [SQL2016DB] SET REMOTE_DATA_ARCHIVE = ON (SERVER = N’’)

Enable Database for Stretch – Part 1

Create Azure Database , user name and password.Configure the Firewall Rules (Local IP address) to connect to Azure. Oct 11 2015 17:21:30: Log opened. TraceLevel:Informational Oct 11 2015 17:21:32 [Informational] UI Authentication via ADAL:CommonAzureUserAuthenticationUIControl Message:Session expired. Oct 11 2015 17:21:33 [Informational] UI Authentication via ADAL:CommonAzureUserAuthenticationUIControl Message:Token Cache cleared; Going to sign in screen. Oct 11 […]

Monitor Alwayson Using SQL Server Management Studio

With every release of Microsoft SQL Server , SQL Server Management Studio has improved a lot and many new feature were added. AlwaysOn Dashboard is one of them which come with SQL Server 2012 and later. To access the AlwaysOn Dashboard Right click the [AlwaysOn High Availability] folder or Right Click the Availability Group   […]

Configure SQL Instance for Stretch

Stretch database feature is default disabled. To use it , it must be enabled. In case you try to use it without enabling it , GUI will show a message you to enable the feature Pre-Check Change the configuration value of [remote data archive]   to 1 use master go sp_configure ‘remote data archive’,1 reconfigure with […]

Self Abort Transaction

We always faces challenges that a transaction is blocked for hours. In SQL Server 2014 Microsoft introduced MAX_DURATION and ABORT_AFTER_WAIT=SELF. Which means  that the session will kill itself if any locks are not released inside MAX_DURATION ALTER INDEX idx_Col1 ON Foo REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1,ABORT_AFTER_WAIT = SELF) ) ) GO