LOG-SHIPPING

Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover.

Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.

To setup a log-shipping you must have sysadmin rights on the server.

Minimum Requirements

1. SQL Server 2005 or later
2. Standard, Workgroup or Enterprise editions must be installed on all server instances involved in log shipping.
3. The servers involved in log shipping should have the same case sensitivity settings.
4. The database must use the full recovery or bulk-logged recovery model
5. A shared folder for copying T-Log backup files
6. SQL Server Agent Service must be configured properly

A log shipping session involves the following steps:

• Backing up the transaction log file on the primary SQL Server instance
• Copying the transaction log backup file across the network to one or more secondary SQL Server instances
• Restoring the transaction log backup file on the secondary SQL Server instances

Ref https://www.sqlshack.com/sql-server-log-shipping/

Happy New Year 2018

Elastic Block Storage (EBS) --AWS

Amazon Elastic Block Store (EBS) provides block level storage volumes for use with EC2 instances. 

EBS volumes are highly available and reliable storage volumes that can be attached to any running instance that is in the same Availability Zone. 

With Amazon EBS, you pay only for what you use. 

You can create EBS provisioned and throughput optimized  volumes up to 16 TiB.

You can mount these volumes as devices on your Amazon EC2 instances. 

Amazon Elastic Block Store provides block level storage volumes for use with EC2 instances.

EBS can be attached to any running instance that is in the same Availability Zone. 

EBS volumes that are attached to an EC2 instance are exposed as storage volumes that persist independently from the life of the instance. 

With Amazon EBS, you pay only for what you use.

You can mount multiple volumes on the same instance, but each volume can be attached to only one instance at a time.

You can dynamically change the configuration of a volume attached to an instance.

EBS volumes behave like raw, unformatted block devices. We can create a file system on top of these volumes.

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