Home » Posts tagged 'SQL Server 2014'
Tag Archives: SQL Server 2014
SQL Server Cluster LooksAlive and IsAlive Check
Windows Cluster Service use the “heartbeat” process to check whether each node is alive at both the OS level and SQL Server level. Heartbeat signals works on UDP.
Microsoft Windows Cluster service perform 2 health checks to perform node & resource availability check.
LooksAlive Health Check
1) LooksAlive is a quick lightweight health check
2) Run at interval of 5 Seconds
3) Does not impact performance but does not perform a thorough check
4) The check will succeed if the service appears to be running even though it might not be operational
5) If Fails calls “IsAlive” health check
6) Polling interval can be changed by adjusting LooksAlivePollInterval property of Cluster service
IsAlive Health Check
1) Run at interval of each 60 Second
2) Perform more detail check then LooksAlive
3) Run @@SERVERNAME to ensure that SQL Server is responding to queries
4) Does not ensure that all user databases are operational
5) Retired 5 times & then try to reconnect SQL Server
6) If all 5 retry fails, the SQL Server resource fails & Windows Cluster service will try to bring it online on other node as per configuration
7) Polling interval can be changed by adjusting IsAlivePollInterval property of Cluster service
Reference : Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN :-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Creation & Deletion of Database Snapshot by SQL Agent Job
Some days back, I received one query on my facebook page that user want to create Database Snapshot frequently & want to delete the oldest with same frequency. Quite interesting scenario. I did not go in deep why he has such requirement but I am clear that It can be done very easily.
So let’s do it. I have setup 2 scripts one for creation of snapshot & another is for deletion of snapshot. We can schedule a SQL agent jobs with 2 steps using these scripts.
Step 1 :-
DECLARE @CREATE_SS VARCHAR(MAX)
DECLARE @DT VARCHAR(100)
SET @DT = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),’-‘,’_’),’ ‘,’_’),’:’,’_’)
SET @CREATE_SS =
‘CREATE DATABASE TEST_’+@DT+’ ON
( NAME = TEST, FILENAME =
”C:\TEST_’+@DT+’.SS” )
AS SNAPSHOT OF TEST’
EXEC (@CREATE_SS)
Step 2 :-
IF(SELECT COUNT(*) FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’))>1
BEGIN
DECLARE @DROP_SS VARCHAR(MAX)
DECLARE @SS_NAME VARCHAR(100)
SELECT TOP 1 @SS_NAME = NAME FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’)
ORDER BY CREATE_DATE ASC
SET @DROP_SS = ‘DROP DATABASE ‘+ @SS_NAME
EXEC (@DROP_SS)
END
Reference : Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN :-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
Renaming or Changing SQL Server Cluster Instance
Renaming or Changing SQL Server cluster instance process is bit different from renaming a stand-alone instance.
Refer : Rename or Change SQL Server Standalone Default Instance http://mssqlfun.com/2014/06/30/rename-or-change-sql-server-standalone-default-instance/)
Refer : Rename or Change SQL Server Standalone Named Instance (http://mssqlfun.com/2014/07/29/rename-or-change-sql-server-standalone-named-instance/)
The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). You can change the name of the virtual server, you cannot change the instance name like in the case of standalone named instance. You can change a virtual SQL server named SQLServerinstance1 to some other name, such as SQLServerNewinstance1, but the instance part of the name, instance1, will remain unchanged.
Rename a virtual server :-
1. Using Cluster Administrator, change the SQL Network Name or SQL Server DNS Name to the new name.
2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.
3. Bring the SQL Server resource back online.
Verify the Renaming Operation :-
1) Using @@ServerName
2) Using sys.servers
Minimize network propagation delay :-
ipconfig /flushdns
ipconfig /registerdns
nbtstat –RR
You are done ! You should ask your application teams to reconnect with new Name or if they are using IP address then no changes required.
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
How to add node to SQL Server Failover Cluster from SQL Server 2008 onwards ?
Steps : How to add node to SQL Server Failover Cluster from SQL Server 2008 onwards ?
1) Open SQL Server Installation Center > Click Installation from Left > Click Add node to a SQL Server Failover Cluster from right
2) Click Run to start setup
3) Click Ok to cont., In case of any failure, you need to fix that first
4) Enter Product Key & click Next
5) Accept EULA & Click Next
6) Click Install to install setup files
7) Click Next, In case of any failure, you need to fix that first
8) Enter Product Key & Click Next
9) Accept EULA & Click Next
10) Select SQL instance to add node
11) Enter Service account password & Click Next. You cannot change service account here, it must be same as existing setup
12) Click Next after Error reporting option
13) Check Setup Rules & click Next. In case of any failure, you need to fix that first
14) Check Configuration & Click install to start installation
15) Installation in progress
16) Installation completed successfully
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
How to install SQL Server Failover Cluster from SQL Server 2008 onwards ?
Steps to install SQL Server Failover Cluster from SQL Server 2008 onwards :-
1) Open SQL Server Installation Center > Go to Installation from Left > Click on New SQL Server Failover installation from right
2) Click RUN to start setup
3) Click ok after validation check. In case of any failure, you need to clear that first
4) Click INSTALL, to install setup files
5) Click Next to cont.
6) Enter Product Key & click Next
7) Accept the EULA & Click Next
8) Select Features, you want to install & Click Next
9) Specify SQL Server instance name & Click Next
10) Check Disk space requirement & Click Next
11) Mention SQL Server Cluster Group Name & Click Next
12) Select Cluster Disk, You want to use for installation & Click Next
13) Provide SQL Server Cluster VIP & Click Next
14) Check your security policy & Click Next
15) Specify Service accounts & Click Next
16) Add User to work as SYSADMIN & Click Next
17) Set error reporting options & click Next
18) Click Next (In case of any failure, you need to fix that)
19) Check all configurations & Click Next
20) Installation Started
21) Installation Completed successfully
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
SQL Server 2005 Onwards – Mirrored Backup || Cool Feature
SQL Server 2005 onwards includes the mirroring of backup media sets to provide redundancy of your critical database backups. Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions. These malfunctions are very serious because backups are the last line of defense against data loss. SQL Server 2005 Standard Edition supports only a single backup copy during your backup operations. Depending on your requirements, SQL Server Enterprise allows you to create up to four mirrored media sets.
Benefits
Mirrored backup media sets improve availability by minimizing downtime during restore operations. A damaged backup could result in a longer restore time, or a restore failure. As databases grow, the probability increases that the failure of a backup device or media will make a backup unrestorable. Restoring a database is time sensitive, and mirrored backup media sets give you added protection to get your application fully functional more quickly.
In SQL Server 2005 and later versions, there is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. The maximum limit of additional locations that can be specified with MIRROR clause is 3.
Mirrored backup can be taken in local computer system as well as in a local network. Let us now see two examples of mirror backup.
Example 1. Single File Backup to Multiple Locations using Mirror
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘d:AdventureWorksBackup1.bak’
MIRROR TO DISK = ‘d:AdventureWorksBackupCopy.bak’
with format
If this command is being run for the first time, it is mandatory to use the WITH FORMAT clause; but for sub sequential runs it is not required. WITH FORMAT reinitializes the backup.
Example 2. Multiple File Backup to Multiple Locations using Mirror
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘d:AdventureWorksBackup1.bak’,
disk = ‘d:AdventureWorksBackup2.bak’
MIRROR TO DISK = ‘d:AdventureWorksBackupCopy.bak’,
DISK = ‘d:AdventureWorksBackupCopy1.bak’
with format
Example 3. Only 4 Mirror are allowed (1 Backup file + 3 Mirror files)
Example 4. Different number of backup file & Mirror files – Error Occur
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
How to find current/particular transaction level?
There is 2 ways of finding current / particular transaction level :-
1)
SELECT CASE transaction_isolation_level
WHEN 0 THEN ‘Unspecified’
WHEN 1 THEN ‘ReadUncomitted’
WHEN 2 THEN ‘Readcomitted’
WHEN 3 THEN ‘Repeatable’
WHEN 4 THEN ‘Serializable’
WHEN 5 THEN ‘Snapshot’ END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID
2)
DECLARE @UserOptions TABLE(SetOption varchar(100), Value varchar(100))
DECLARE @IsolationLevel varchar(100)
INSERT @UserOptions
EXEC(‘DBCC USEROPTIONS WITH NO_INFOMSGS’)
SELECT @IsolationLevel = Value
FROM @UserOptions
WHERE SetOption = ‘isolation level’
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
How to Insert datetime with different Timezones
In SQL Server 2008, Microsoft has introduced a number of new date and time data types. One of these is the datetimeoffset data type. This data type includes an offset from UTC time as well as the datetime value and ensures that the datetime can be retrieved in UTC or a particular timezone based on this offset. There are also new functions to allow for conversions between different time zones using the new function SWITCHOFFSET().
select
converT(datetime,SWITCHOFFSET(converT(datetimeoffset,getdate()),’+05:30′)) Date_India,
converT(datetime,SWITCHOFFSET(converT(datetimeoffset,getdate()),’+03:30′)) Date_Iran
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
Query Execution Plan from XML to Graphical View
Convert Your SQL Server Query Execution Plan from XML to Graphical View
1) We can get queries execution plan from below query
SELECT
QS.*, CP.*
FROM SYS.DM_EXEC_QUERY_STATS AS QS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)AS ST
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(PLAN_HANDLE) AS CP
2) Column query_plan is having execution plan in XML form
3) When we click hyperlink in query_plan column, in SQL 2005,2008 & 2008 R2 it open as XML but in SQL 2012 it converted to graphical view
4) To convert XML to graphical view, save XML file with extension .sqlplan & open it with SSMS
5) Now plan will show in graphical view
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
MSSQLFUN 