blocking query and header blocker query in sqlserver

 To check blocking

select * from sys.sysprocesses where blocked <> 0

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

head blocker

++++++++++

declare @handle binary(20), @spid int

select top 1 @spid = blocked

from master..sysprocesses a

where a.blocked != 0 

and a.blocked not in (select spid from master..sysprocesses b where blocked != 0)

if @spid is not null

begin

  select @handle = sql_handle from master..sysprocesses where spid = @spid

  exec sp_who2 @spid

  dbcc inputbuffer (@spid)

select lastwaittype from sysprocesses where spid=@spid

  select * from ::fn_get_sql(@handle)

end  

user permissions script for each of the databases

 Use the following script for this purpose for each database. 

REF :This script is also present in the link https://www.sqlservercentral.com/scripts/script-db-level-permissions-v4-3

SET NOCOUNT ON

/*Prep statements*/

IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_statements 

CREATE TABLE ##tbl_db_principals_statements (stmt varchar(max), result_order decimal(4,1))

IF ((SELECT SUBSTRING(convert(sysname, SERVERPROPERTY('productversion')), 1, charindex('.',convert(sysname, SERVERPROPERTY('productversion')))-1)) > 10)

EXEC ('

INSERT INTO ##tbl_db_principals_statements (stmt, result_order)

   SELECT   

      CASE WHEN rm.authentication_type IN (2, 0) /* 2=contained database user with password, 0 =user without login; create users without logins*/ THEN (''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME([name]) + '' WITHOUT LOGIN WITH DEFAULT_SCHEMA = '' + QUOTENAME([default_schema_name]) + SPACE(1) + '', SID = '' + CONVERT(varchar(1000), sid, 1) + SPACE(1) + '' END; '')

         ELSE (''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME([name]) + '' FOR LOGIN '' + QUOTENAME(suser_sname([sid])) + CASE WHEN [type] <>''G'' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) ELSE '''' END + SPACE(1) + ''END; '') 

         END AS [-- SQL STATEMENTS --],

         3.1 AS [-- RESULT ORDER HOLDER --]

   FROM   sys.database_principals AS rm

   WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */

     AND NAME <> ''guest''')

ELSE IF ((SELECT SUBSTRING(convert(sysname, SERVERPROPERTY('productversion')), 1, charindex('.',convert(sysname, SERVERPROPERTY('productversion')))-1)) IN (9,10))

EXEC ('

INSERT INTO ##tbl_db_principals_statements (stmt, result_order)

   SELECT   (''IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '' + SPACE(1) + '''''''' + [name] + '''''''' + '') BEGIN CREATE USER '' + SPACE(1) + QUOTENAME([name]) + '' FOR LOGIN '' + QUOTENAME(suser_sname([sid])) + CASE WHEN [type] <>''G'' THEN '' WITH DEFAULT_SCHEMA = '' + QUOTENAME(ISNULL([default_schema_name], ''dbo'')) ELSE '''' END + SPACE(1) + ''END; '') AS [-- SQL STATEMENTS --],

         3.1 AS [-- RESULT ORDER HOLDER --]

   FROM   sys.database_principals AS rm

   WHERE [type] IN (''U'', ''S'', ''G'') /* windows users, sql users, windows groups */

   AND NAME <> ''guest''')

DECLARE 

    @sql VARCHAR(2048)

    ,@sort INT 

DECLARE tmp CURSOR FOR

/*********************************************/

/*********   DB CONTEXT STATEMENT    *********/

/*********************************************/

SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],

      1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT   'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],

      1.1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],

      2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/

/*********     DB USER CREATION      *********/

/*********************************************/

   SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],

         3 AS [-- RESULT ORDER HOLDER --]

   UNION

   SELECT   

      [stmt],

         3.1 AS [-- RESULT ORDER HOLDER --]

   FROM   ##tbl_db_principals_statements

   --WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

   WHERE [stmt] IS NOT NULL

UNION

/*********************************************/

/*********    MAP ORPHANED USERS     *********/

/*********************************************/

SELECT '-- [-- ORPHANED USERS --] --' AS [-- SQL STATEMENTS --],

      4 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT   'ALTER USER [' + rm.name + '] WITH LOGIN = [' + rm.name + ']',

      4.1 AS [-- RESULT ORDER HOLDER --]

FROM   sys.database_principals AS rm

 Inner JOIN sys.server_principals as sp

 ON rm.name = sp.name COLLATE DATABASE_DEFAULT and rm.sid <> sp.sid

WHERE rm.[type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

 AND rm.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser')

UNION

/*********************************************/

/*********    DB ROLE PERMISSIONS    *********/

/*********************************************/

SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],

      5 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT   'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME([name],'''') COLLATE database_default + ') IS NULL' + SPACE(1) + 'CREATE ROLE'

   + SPACE(1) + QUOTENAME([name]),

      5.1 AS [-- RESULT ORDER HOLDER --]

FROM sys.database_principals

WHERE [type] ='R' -- R = Role

   AND [is_fixed_role] = 0

--ORDER BY [name] ASC

UNION

SELECT   'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(rm.member_principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) + 'EXEC sp_addrolemember @rolename ='

   + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') COLLATE database_default + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') COLLATE database_default AS [-- SQL STATEMENTS --],

      5.2 AS [-- RESULT ORDER HOLDER --]

FROM   sys.database_role_members AS rm

WHERE   USER_NAME(rm.member_principal_id) IN (   

                                    --get user names on the database

                                    SELECT [name]

                                    FROM sys.database_principals

                                    WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas

                                    and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

                                   )

UNION

SELECT '' AS [-- SQL STATEMENTS --],

      7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/

/*********  OBJECT LEVEL PERMISSIONS *********/

/*********************************************/

SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

      7.1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT   'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +

      CASE 

         WHEN perm.state <> 'W' THEN perm.state_desc 

         ELSE 'GRANT'

      END

      + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(perm.major_id)) + '.' + QUOTENAME(OBJECT_NAME(perm.major_id)) --select, execute, etc on specific objects

      + CASE

            WHEN cl.column_id IS NULL THEN SPACE(0)

            ELSE '(' + QUOTENAME(cl.name) + ')'

        END

      + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

      + CASE 

            WHEN perm.state <> 'W' THEN SPACE(0)

            ELSE SPACE(1) + 'WITH GRANT OPTION'

        END

         AS [-- SQL STATEMENTS --],

      7.2 AS [-- RESULT ORDER HOLDER --]

FROM   

   sys.database_permissions AS perm

      INNER JOIN

   sys.database_principals AS usr

         ON perm.grantee_principal_id = usr.principal_id

      LEFT JOIN

   sys.columns AS cl

         ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

  WHERE /* Include System objects when scripting permissions for master, exclude elsewhere */

      (    DB_NAME() <> 'master' AND perm.major_id IN (SELECT [object_id] FROM sys.objects WHERE type NOT IN ('S'))

        OR DB_NAME() =  'master'

        ) 

UNION

/*********************************************/

/*********  TYPE LEVEL PERMISSIONS *********/

/*********************************************/

SELECT '-- [-- TYPE LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

        8 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +

      CASE 

            WHEN perm.state <> 'W' THEN perm.state_desc 

            ELSE 'GRANT'

        END

        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(tp.schema_id)) + '.' + QUOTENAME(tp.name) --select, execute, etc on specific objects

        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

        + CASE 

                WHEN perm.state <> 'W' THEN SPACE(0)

                ELSE SPACE(1) + 'WITH GRANT OPTION'

          END

            AS [-- SQL STATEMENTS --],

        8.1 AS [-- RESULT ORDER HOLDER --]

FROM    

    sys.database_permissions AS perm

        INNER JOIN

    sys.types AS tp

            ON perm.major_id = tp.user_type_id

        INNER JOIN

    sys.database_principals AS usr

            ON perm.grantee_principal_id = usr.principal_id

UNION

SELECT '' AS [-- SQL STATEMENTS --],

   9 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/

/*********    DB LEVEL PERMISSIONS   *********/

/*********************************************/

SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

      10 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT   'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(usr.principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +

      CASE 

         WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

         ELSE 'GRANT'

      END

   + SPACE(1) + perm.permission_name --CONNECT, etc

   + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>

   + CASE 

         WHEN perm.state <> 'W' THEN SPACE(0) 

         ELSE SPACE(1) + 'WITH GRANT OPTION' 

     END

      AS [-- SQL STATEMENTS --],

      10.1 AS [-- RESULT ORDER HOLDER --]

FROM   sys.database_permissions AS perm

   INNER JOIN

   sys.database_principals AS usr

   ON perm.grantee_principal_id = usr.principal_id

WHERE   [perm].[major_id] = 0

   AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas

   AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],

      11 AS [-- RESULT ORDER HOLDER --]

UNION 

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

      12 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT   'IF DATABASE_PRINCIPAL_ID(' + QUOTENAME(USER_NAME(grantee_principal_id),'''') COLLATE database_default + ') IS NOT NULL' + SPACE(1) +

         CASE

         WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

         ELSE 'GRANT'

         END

            + SPACE(1) + perm.permission_name --CONNECT, etc

            + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

            + QUOTENAME(SCHEMA_NAME(major_id))

            + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default

            + CASE

               WHEN perm.state <> 'W' THEN SPACE(0)

               ELSE SPACE(1) + 'WITH GRANT OPTION'

               END

         AS [-- SQL STATEMENTS --],

      12.1 AS [-- RESULT ORDER HOLDER --]

from sys.database_permissions AS perm

   inner join sys.schemas s

      on perm.major_id = s.schema_id

   inner join sys.database_principals dbprin

      on perm.grantee_principal_id = dbprin.principal_id

WHERE class = 3 --class 3 = schema

ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp

FETCH NEXT FROM tmp INTO @sql, @sort

WHILE @@FETCH_STATUS = 0

BEGIN

        PRINT @sql

        FETCH NEXT FROM tmp INTO @sql, @sort    

END

CLOSE tmp

DEALLOCATE tmp 

IF OBJECT_ID('tempdb..##tbl_db_principals_statements') IS NOT NULL DROP TABLE ##tbl_db_principals_statements

Tempdb shrink in sqlserver

 

Process can take a long time to complete.  Status can be checked by

select session_id, 

blocking_session_id, 

db_name(database_id) as [Database],

command, 

percent_complete, 

wait_type,

wait_time, 

wait_resource, 

scheduler_id, 

Qry.text 

  from sys.dm_exec_requests req 

cross apply sys.fn_get_sql(req.sql_handle) as Qry 

This SQL will show all currently running processes and percentage done

if the tempdb is not shrink after two attempts then follow the below steps.

DBCC FREEPROCCACHE

Run 

   use tempdb

   go

dbcc shrinkfile (tempdev, 'target size in MB')

   go

   -- this command shrinks the primary data file

Use same command above using the file name of the secondary datafiles to shrink all data files

Datafile names can be found U:\MSSQL\TempDB\Data

dbcc shrinkfile (templog, 'target size in MB')

   go

   -- this command shrinks the log file

script to check the role for users in sqlserver:

SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '--Add Users to Database Roles--'

FROM sys.database_principals DBUser

INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id

INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id

Backup and restore in sql server command

Backup with copy only
+++++++++++++++++

backup database BR_EINVOICE_CONNECTOR25  to
disk =N 'F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_EINVOICE_CONNECTOR25_1.bak',
disk =N 'F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_EINVOICE_CONNECTOR25_2.bak',
disk =N 'F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_EINVOICE_CONNECTOR25_3.bak',
WITH  COPY_ONLY,compression,stats=10;

or

BACKUP DATABASE [BR_eInvoice_COLD_SEARCH] TO 
DISK = N'L:\Bkup01\MSSQL11.SQLX\MSSQL\Backup\ManualBackups\Xnet_content_NAEU1_1.bak'
WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'BR_eInvoice_COLD_SEARCH-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO


backup normally


backup database BR_eInvoice_COLD_SEARCH  to disk='F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_eInvoice_COLD_SEARCH_1.bak'
with compression,stats=10;





Restore comman in sql server 


restore database [Contacts] from disk = 'J:\AZRWUS2UIS_SQLD\BAckup\ManualBackups\Contacts_1.bak'
with replace,stats=10,norecovery,
move 'Contacts_Data' to 'I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\Contacts_Data.mdf',
move 'Contacts_Log' to 'I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\Contacts_Log.ldf'

for logshipping restore
++++++++++++++

restore database [BR_eInvoice_COLD_SEARCH] from disk = 'F:\BKup01\MSSQL11.SQLD\MSSQL\Backup\ManualBackups\BR_eInvoice_COLD_SEARCH_1.bak'
with replace,stats=10,norecovery,
move 'BR_eInvoice_COLD_SEARCH' to 'I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\BR_eInvoice_COLD_SEARCH.mdf',
move 'BR_eInvoice_COLD_SEARCH _log' to 'I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\BR_eInvoice_COLD_SEARCH_1.ldf',
standby='I:\MSSQL_SQLI\MSSQL11.SQLD\MSSQL\DATA\BR_eInvoice_COLD_SEARCH.tuf'

sql server Ref

Sql server Ref blog


http://dbasqlserver13.blogspot.com/


https://channel9.msdn.com/Series/SQL-Server-2014-Essentials-for-Oracle-DBAs

File Movement in DR setup

on standby

declare @databaseName varchar(300)
set @databaseName = 'net_contentV3' -- Secondary Database Name
-- 0 = Restore log with NORECOVERY.
-- 1 = Restore log with STANDBY.
select secondary_database,
case restore_mode
when 0 then 'No Recovery'
when 1 then 'Stand by' end AS 'restore_mode'
from msdb.dbo.log_shipping_secondary_databases
where secondary_database = @databaseName


 declare @databaseName varchar(300)
set @databaseName = 'Xnet_content_1_V3' -- Secondary Database Name
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(@databaseName);

go to primary

select the the database and right click --then transaction log --change to no recovery.

then open job monitor and disable the copy job and start the copyjob in production.


come to dr .. start the copy and restore job

ALTER DATABASE Xnet_content
MODIFY FILE ( NAME = wss_prod_contentdata3, FILENAME = 'L:\SQL01\MSSQL11.SQLX\MSSQL\DATA\wss_prod_content_1_V3_4.ndf' );


bring the db down.

copy the file physically and delete the old one.

bring up the db.


go to primary and enable the log shipping --change to standby.


start the copy

start the restore

check it is sync

Restore last refresh time in sql server

++++++++++++++++++++
Select Destination_database_name,
       restore_date,
       database_name as Source_database,
       Physical_device_name as Backup_file_used_to_restore,
       bs.user_name,
       bs.machine_name
from msdb.dbo.restorehistory rh
  inner join msdb.dbo.backupset bs
    on rh.backup_set_id=bs.backup_set_id
  inner join msdb.dbo.backupmediafamily bmf
    on bs.media_set_id =bmf.media_set_id
ORDER BY [rh].[restore_date] DESC




USE msdb ;
SELECT
DISTINCT
        DBRestored = destination_database_name ,
        RestoreDate = restore_date ,
        SourceDB = b.database_name ,
        BackupDate = backup_start_date
FROM    RestoreHistory h
        JOIN MASTER..sysdatabases sd ON sd.name = h.destination_database_name
        INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
        INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
GROUP BY destination_database_name ,
        restore_date ,
        b.database_name ,
        backup_start_date
ORDER BY RestoreDate DESC
GO

_________________________________________________________________________________

WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

New Features of 2014 in sql server

Below are the new or enhanced features of SQL Server 2014

1.       Memory Optimized Objects

In SQL Server 2014, memory-optimized Engine is basically integrated in SQL Server engine. You can define SQL Server objects such as Tables and Store procedures to run in the memory. In previous versions, almost all the operations on sql server objects contained I/O process that means it involved disk back and forth during sql server operations such as select, insert, update and delete. With Memory Optimized Engine, these operations will take place in memory instead of SQL Server going back and forth to Disk drives.

Memory Optimized OLTP is completely transactional based, all the processing take place in the form of transactions, however it is being done in memory if you declare those objects to take advantage of memory-optimized feature. There are some prerequisites that need to be considered before you can use this feature. I will be covering all that in a separate post.

2.       SQL Server Data Files in Windows Azure

This features is mainly to move or have your database files on windows Azure Blob storage, Azure Blob storage basically is huge storage system that’s chunked in 1TB of Blobs, you can use this storage to keep your database files, it provides benefits of migrating database from one location to another, replicating database files from different location within the Azure cluster or you can copy it back to on-premises system in order to restore the database using attach/detach method.

3.       Hosting SQL Server in Windows VM

This feature enables you to deploy your existing on-premises database to Windows Azure SQL Server VM, in other words you can migrate your existing on-premises SQL Server to Azure using migration Wizard.

4.       Backup and Restore

If you are familiar with SQL Server 2012 SP1, There is new option introduced in 2012 SP1 was to backup SQL Server Database to URL, however this option can only be utilized using either T-SQL , PowerShell and SMO. But in SQL Server 2014, you can use management studio to accomplish backing and restoring your databases to URL.

Keep in mind when scheduling SQL Server Database backups, the only URL supported in this operation is Windows Azure URL, you can’t use your existing URL’s i.e. Sharepoint file system URL. May be Microsoft next releases of SQL Server 2014 include this feature to use other URLs than Windows Azure.

5.       Redesign of Query Plans and Performance boost

Query Plans use cardinality estimator to provide query execution plans in SQL Server, in SQL Server 2014; estimator is redesigned to improve accuracy as well as quality of query plans which helps to improve the performance of your query. Again this is just an enhancement to provide a quality query plans. Personally, I didn’t see much of a change using it, there might be some queries which can take advantage of this enhancement while others remain the same.

6.       Reduced Latency – Delayed Durability

I found this feature quite beneficial, however it totally depends on organizational structure for example, Finance oriented organization, it is very important to NOT lose any data, while other organizations, it is tolerable to lose some data as long as they can recover most of the data. So in SQL Server 2014, there is a concept introduced called delayed durability, which actually means that client application will not wait from SQL Server that its transactions are written on the disk, transaction on sql server side will be stored in memory and will be written in batches so it will reduce lots of I/O operations hence reduce the latency. But again you have to be very careful making your transactions delayed durable or not.

7.       Always On – Availability Group (AG)

a)      Always on concept was introduced in SQL Server 2012. Purpose of this concept was high availability of the database and isolates some of the heavy read such as reporting from the database. Always on was accomplished using feature called Availability group, basically you can provide replica of your production database as read only database onto another SQL Server instance so that reporting or other heavy read of database can run on secondary replica without causing lots of load on actual production database. In SQL Server 2012, secondary copy of your database were limited to 4 replicas, however in SQL Server 2014, it is increased to 8 secondary replicas.

b)      If primary replica goes down, secondary replica will remain online for read operations in SQL Server 2014.

c)       Failover Clustered instances can share clustered shared volumes as clustered shared disks

8.       Table Partitioning and Indexing

In SQL Server 2014, you can rebuild individual partition of a partitioned table

9.       Columnstore Indexes

This feature is one of my favorite, columnstore indexes were introduced in 2012, however in 2012, you can only create non-clustered columns store indexes, but in SQL Server 2014, you can create Clustered Columnstore indexes and they are updatable. It greatly enhanced the data warehouse performance.

10.   Buffer Pool Extension

Buffer Pool Extension is a great feature of SQL Server 2014, it enables buffer pool to extend buffer pool size between RAM and SSD (Solid State Drive). Just to give you a little background about buffer pool if you already don’t know it, buffer pool is primary memory allocation source of SQL Server. It is a key component in achieving efficiency. It serves two purposes.

1. Access and update database pages

2. Buffer pool to reduce I/O

             This feature helps SQL Server Buffer pool to store larger worksets in the pool, hence make  
             SQL Server 2014 more efficient in its operations. 

11.   Resource Governor Enhancement
       Resource Governor helps to control your resources such as CPU, IO etc. In SQL Server 2014 you can assign physical I/O resource of a particular resource pool, depending upon priority and urgency of the operation, this value can be set to manipulate the resources in SQL Server 2014

12.   Incremental Statistics
       This feature will allow you to create or update statistics of a particular partition if your table is partitioned. Before that, you had to update statistics of the whole table

13.   Online Index Operation
       Online index operation progress can be monitored real time as partitionID and PartitionNumber column is added in SQL Server 2014 and you can query the progress in order to get real time status of online index rebuild Job

14.   Database compatibility Level 

      Compatibility 90 (SQL Server 2005) is not valid in SQL Server 2014  

Blocking on sqlserver

To check blocking


select * from sys.sysprocesses where blocked <> 0


head blocker

++++++++++

declare @handle binary(20), @spid int

select top 1 @spid = blocked

from master..sysprocesses a

where a.blocked != 0 

and a.blocked not in (select spid from master..sysprocesses b where blocked != 0)

if @spid is not null

begin

  select @handle = sql_handle from master..sysprocesses where spid = @spid

  exec sp_who2 @spid

  dbcc inputbuffer (@spid)

select lastwaittype from sysprocesses where spid=@spid

  select * from ::fn_get_sql(@handle)

end  

Last successful backup in sqlserver script

SET quoted_identifier OFF
DECLARE @dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3

SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset

Procedure to attend SQL server memory issues

Check SQL installed on Server

Confirm SQL server is installed on the server through services.msc or SQL services configuration manger.

Start à Run àservices.msc verify SQL Services are visible like SQL server, SQL Server Agent. Etc.,

SQL services configuration

Start àProgramsàMicrosoft SQL server 20xxàConfiguration Tools àSQL Server configuration  Manager

Verify SQL maintenance jobs status

Verify SQL maintenance jobs like Reindex , Update Stats or CheckDB  is running during business hours. These jobs are created and scheduled to run on week end or off business hours.  If any maintenance jobs in running state.

Check blocking

select *from sysprocesseswhere blocked >0

If many blockings are found, head blocker can be determined by using the query below.

declare @handle binary(20), @spid int

select top1 @spid = blocked

from master..sysprocesses a

where a.blocked != 0

and a.blocked not in (select spid from master..sysprocesses b whereblocked != 0)

if @spid isnot null

begin

  select@handle = sql_handlefrom master..sysprocesses where spid = @spid

  execsp_who2 @spid

  dbccinputbuffer (@spid)

--exec ('kill ' + @SPID)

select lastwaittype from sysprocesses where spid=@spid

  select* from ::fn_get_sql(@handle)

end

Verify SQL Server Max. Memory settings

 The minimum and maximum memory values are important, especially since we gave the SQL Server account the permission to lock its pages in memory. If other applications are running on this server, we need to specify how much memory want SQL Server to take.

SQL Server application is Memory consuming application. We need to restrict SQL instance with minimum and maximum memory setting by leaving some amount of memory to Operating system.

Go into SQL Server Management Studio, right-click on the server name and click Properties, go into Memory, and just verify SQL max. memory set for the SQL server. 

Check Memory Pressure

This query will provide information about how many queries are requesting for memory , how many are granted and how many are pending to be granted.

--Query 3: SELECT * FROM sys.dm_exec_query_resource_semaphores --Query 4: SELECT TEXT,* FROM sys.dm_exec_query_memory_grants cross apply sys.dm_exec_sql_text (sql_handle) order by requested_memory_kb desc

Checking resource semaphore wait type during memory pressure.

SELECT * FROM SYSPROCESSES  ORDER BY lastwaittype

SELECT * FROM sys.dm_exec_query_resource_semaphore

File movement in sql server

USE master;
GO

ALTER  database <Database name>
MODIFY FILE (NAME = <database>_Data,
 FILENAME = ' J:\sqldb03\MSSQL12.MSSQLSERVER\data\<database>.mdf');

go

ALTER DATABASE  <Database name>
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

ALTER DATABASE  <Database name>
SET OFFLINE
GO

 Move files from
U:\SQL01\MSSQL11.SQLE\MSSQL\Data to U:\SQL07\MSSQL11.SQLE\MSSQL\Data

ALTER DATABASE  <Database name> SET ONLINE
go

ALTER DATABASE  <Database name> SET MULTI_USER

Go

SQL Server 2017 Installation on linux

1.  Introduction

This document is to install and configure  sql server on Rhel server . It provides a step-by-step procedure in configuring of sql server on linux flavor.

2.  Requirements of Packages for Installation on RHEL 7

• Minimum of 3.25 GB of RAM.
• Minimum of 4 GB of disk space
• XFS (default on RHEL) or EXT4 file system
• Network connectivity to the Internet to download the package
• 1433 TCP port open on the firewall for SQL Server connections
• A hostname of less than 15 characters.
• wget is a required package to run the configuration script.

3.     INSTALL SQL SERVER ON RHEL

• Connect to the RHEL virtual machine or linux server and log on to the server by keying username and password

• Switch to root user (superuser)

Step 1:

Su root

Password :*****

• Download the Microsoft SQL Server Red Hat repository into the RHEL Server

Step 2:

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo Step 3: install SQL Server on RHEL by using the below command

sudo yum install -y mssql-server

From the above command the required packages and sql server installation will completed as successfully

Step 4:

Once SQL Server installation gets completed successfully, kindly run the configuration script. During the configuration setup, It will ask you to;

• Select the SQL Server Edition which you want to install
• Accept the SQL Server License terms
• Key “sa” password

       sudo /opt/mssql/bin/mssql-conf setup

         Once you issue the above command it will ask to choose the edition of the server.

            Like express,standard,entrerprise..so depend on the licence enter the edition

            Then accept the license terms.

            It will prompt for sql server system administrator password.

Step 5:

Now verify, whether the SQL Server instance is running on RHEL Server or not.

      systemctl status mssql-server

Step 6:

• To allow remote connections, open the SQL Server port on the firewall on RHEL. The default SQL Server port is TCP 1433. If you are using FirewallD for your firewall, you can use the following command;

      sudo firewall-cmd --zone=public --add-port=1433/tcp –permanent

Step 7:

• Let’s apply the firewall configuration right now on the runtime.

1

sudo firewall-cmd –reload 4.    INSTALL SQL SERVER COMMAND-LINE TOOLS ON RHEL The mssql-tools package contains Command Line utilities & Bulk Import-Export utilities. The SQLCMD utility allows users to connect to, send Transact-SQL batches from, and output rowset information from SQL Server instances. The BCP utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. Installing SQL Server Tools will also install the Microsoft ODBC driver and all its dependencies by default.

Su – root

Step 1:

Download the Microsoft Red Hat repository configuration file.

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

Step 2:

• Install the SQL Server Common Line Tools  with the unixODBC developer package run the following command;
•  

sudo yum install -y mssql-tools unixODBC-devel

During the installation of the tool, it will prompt you to accept the license terms for MSSQL-Tools and Microsoft ODBC driver

• By default, sqlcmd program is not in your environment variable PATH. So, you can take opt/mssql-tools/bin and add it to the Bash_profile path. Let’s run the below command. This enables you to run the tools without specifying the full path.

·       echo 'export PATH="$PATH:/opt/mssql-tools/bin"'>> ~/.bash_profile

·       echo 'export PATH="$PATH:/opt/mssql-tools/bin"'>> ~/.bashrc

·       source ~/.bashrc

Step 3:

After the successful installation we need to verify the database connectivity on linux machine using command line tools

sqlcmd -S <Host Name OR IP> -U <Username> -P<P@ssw0rd>

OUT PUT AS BELOW:

5.    Install SQL Server Agent

1	sudo yum install mssql-server-agent

• Once you install the SQL Server Agent package successfully, you need to restart the SQL Server to enable the SQL Server Agent.

         sudo systemctl restart mssql-server

• Reconnect/Refresh the SQL Server instance to check the current status of SQL Server Agent.

Creating database on linux server :

Sqlcmd –s <hostname or ip address> -U sa –Q ‘create database sampledb’

Output :

List of databases on sqlserver.

Command

Sqlcmd –s <hostname> -U sa –p <password>

Ø  Select name from sys.databases

Ø  go