Home » Posts tagged 'SQL 2014' (Page 2)

Tag Archives: SQL 2014

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/)

%d bloggers like this: