SQL Server 2012 Version

Version	Published	Name
11.0.5582	Sunday, March 1, 2015	SQL Server 2012 SP2 CU 05
11.0.5569	Thursday, January 1, 2015	SQL Server 2012 SP2 CU 04
11.0.5556	Saturday, November 1, 2014	SQL Server 2012 SP2 CU 03
11.0.5548	Monday, September 1, 2014	SQL Server 2012 SP2 CU 02
11.0.5532	Tuesday, July 1, 2014	SQL Server 2012 SP2 CU 01
11.0.5058	Sunday, June 1, 2014	SQL Server 2012 SP2
11.0.3487	Sunday, March 1, 2015	SQL Server 2012 SP1 CU15
11.0.3486	Thursday, January 1, 2015	SQL Server 2012 SP1 CU14
11.0.3482	Saturday, November 1, 2014	SQL Server 2012 SP1 CU13
11.0.3470	Monday, September 1, 2014	SQL Server 2012 SP1 CU12
11.0.3449	Tuesday, July 1, 2014	SQL Server 2012 SP1 CU11
11.0.3431	Thursday, May 1, 2014	SQL Server 2012 SP1 CU10
11.0.3412	Saturday, March 1, 2014	SQL Server 2012 SP1 CU09
11.0.3401	Wednesday, January 1, 2014	SQL Server 2012 SP1 CU08
11.0.3393	Friday, November 1, 2013	SQL Server 2012 SP1 CU07
11.0.3381	Sunday, September 1, 2013	SQL Server 2012 SP1 CU06
11.0.3373	Monday, July 1, 2013	SQL Server 2012 SP1 CU05
11.0.3368	Wednesday, May 1, 2013	SQL Server 2012 SP1 CU04
11.0.3350	Monday, April 1, 2013	SQL Server 2012 SP1 CU03 Hotfix KB 2832017
11.0.3349	Friday, March 1, 2013	SQL Server 2012 SP1 CU03
11.0.3339	Tuesday, January 1, 2013	SQL Server 2012 SP1 CU02
11.0.3321	Thursday, November 1, 2012	SQL Server 2012 SP1 CU01
11.0.3000	Thursday, November 1, 2012	SQL Server 2012 SP1
11.0.2424	Sunday, December 1, 2013	SQL Server 2012 RTM CU11
11.0.2420	Tuesday, October 1, 2013	SQL Server 2012 RTM CU10
11.0.2419	Thursday, August 1, 2013	SQL Server 2012 RTM CU09
11.0.2410	Saturday, June 1, 2013	SQL Server 2012 RTM CU08
11.0.2405	Monday, April 1, 2013	SQL Server 2012 RTM CU07
11.0.2401	Friday, February 1, 2013	SQL Server 2012 RTM CU06
11.0.2395	Saturday, December 1, 2012	SQL Server 2012 RTM CU05
11.0.2383	Monday, October 1, 2012	SQL Server 2012 RTM CU04
11.0.2332	Monday, October 1, 2012	SQL Server 2012 RTM CU03
11.0.2325	Friday, June 1, 2012	SQL Server 2012 RTM CU02
11.0.2316	Sunday, April 1, 2012	SQL Server 2012 RTM CU01
11.0.2100	Sunday, April 1, 2012	SQL Server 2012 RTM
11.0.1750	Tuesday, November 1, 2011	SQL Server 2012 RC0
11.0.1440	Monday, August 1, 2011	SQL Server 'Denali' CTP3
11.0.1103	Monday, November 1, 2010	SQL Server 'Denali' CTP1

How to map network Drive ?

Computer > Right Click on Network > Map network drive >Folder Box put the path of  Drive
Example: \\MSSQLWORLD\Z$

 

How upgrade SQL Server from Evaluation/standard edition to Enterprise edition ?

Method 1:
All Programs > Microsoft SQL Server 2005/2008/2008R2/2012 >
SQL Server Installation Center > Maintenance > edition Upgrade.... Use product key for requied Version

Method 2:
Run the required SQL Exe  software(EnterPrise Edition) >Maintenance > edition Upgrade.... Use product key for requied Version

 

How install/uninstall .Net Framework, IIS and Telnet Client ?

For IIS: Server Manager > Role > Add Role

For .Net Framework and Telnet Client :  Server Manager > Features > Add Features

For uninstall: Select Remove Features

How uninstall IIS ?

Administrative Tools > Server Manager >Roles > Remove Roles >

How Change collation Setting in Analysis service ?

Step 1: Using Management Studio, connect with Analysis service>Server Properties > Language/Collation

Step 2: Remove Collation from collation box

Step 3: Select Required collation

Step 4: Restart Services

 

How can enable clr in SQL Server ?

SELECT * FROM sys.configurations WHERE name = 'clr enabled'

GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

What is Robocopy ?

Robocopy is a command in cmd prompt which is used to copy large file/files between 2 servers.

robocopy \\source path destination path
If you would like to use multi thread(Assume You are using Maximum Multithread)
robocopy \\source path destination path  /MT:128

Example:
robocopy \\MSSQLWORLD.ABC.DEF.GHL.COM\Z$\FULL_BACKUPS Z:\BACKUPS /MT:128

What is recommemded allocation memory for SQL Server ?

Best recommended allocation memory for SQL Server: 80 % of Total Memory

Step 1:
Find out the total server Merory

Step 2:
Select OBJECT_NAME, counter_name, cntr_value
From sys.dm_os_performance_counters Where counter_name = 'Total Server Memory (KB)'
GO
sp_configure 'show advanced options',1;
go
reconfigure
GO
--- Assume  Server Memory in total is 10GB
---- Allocate 80% of 10GB = 8GB = 8192 MB for SQL Server
sp_configure 'max server memory',8192
----in MB
go
reconfigure
go
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Step 3:
Select OBJECT_NAME, counter_name,cntr_value
From sys.dm_os_performance_countersWhere counter_name ='Total Server Memory (KB)'
GO

What is recommemded Maximum Degree of Parallelism(MAXDOP) in SQL Server ?

By default , Maximum Degree of Parallelism(MAXDOP) in SQL Server is zero. MAXDOP value is zero means all available process/thread will work together  for each single tasks.To optimize SQL Server for the day to day workload, My recommendation is to configure Maximum Degree of Parallelism(MAXDOP) rather than defaault value.  How many processes/threads do you want to allow to work together for each workload - it is really hard to calculate. Best recommendation is the value of MAXDOP = No of CPU on that server.

Steps for configure MAXDOP:

Step 1.

Following query lists how many CPUs in a particulat instance of SQL Server

Select cpu_count from sys.dm_os_sys_info

 

 

 

 

Step 2. Assume, Server has 8 CPUs 

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OVERRIDE

GO

----Use MaxDOP = no of CPU

 

EXEC sp_configure 'max degree of parallelism', 8

GO
RECONFIGURE WITH OVERRIDE
GO

Step 3.
Restart SQL Server Services

 

SQL Server Version and Edition

https://support.microsoft.com/en-us/kb/321185

How rebuild Master Database

cd /d " E:\"

cd  Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER
/SQLSYSADMINACCOUNTS=accounts [/SAPWD= Sql14$erver]

Change Directory from C to D in cmd

From any directory  to D: cd /d "D:\"
 

Unable to create database in SQL Cluster

Figure : Error to create database

Figure : Adding Disk

Change System Database default path in SQL ?

Steps Followed to Move System DBs in SQL Server
1. Stop Services
2. Copy system databases (master, model, msdb) data files and log files and paste in new destination
3. Change in Startup Parameter for moving master Databas
--Old Path in Startup Parameter
-dG:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eG:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lG:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

New Path in Startup Parameter (do not copy paste)
-dG:\master.mdf;-lG:\mastlog.ldf
4. Start services and Verify
5. Change destination path and Run below script to move msdb and model databases
Use master
Go
Alter Database Model
Modify File (Name = 'modeldev', FileName = 'G:\model.mdf')
Go
Use master
GO
Alter Database Model
Modify File (Name = 'modellog', FileName = 'G:\modellog.ldf')
Go
Use master
Go
Alter Database MSDB
Modify File (Name = 'MSDBData', FileName = 'G:\MSDBData.mdf')
Go
Use master
GO
Alter Database MSDB
Modify File (Name = 'MSDBlog', FileName = 'G:\MSDBlog.ldf')
Go
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO
6.Stop Services
7. Remove old mdf and ldf files for system databases
8. Start services and Verify

---------------Change Default Backup Path--------
1. Run > REGEDIT
2. HKEY_LOCAL_MACHINE  >  SOFTWARE  > Microsoft
> Microsoft SQL Server  > MSSQL10_50.MSSQLSERVER(For Default Instance)
> MSSQLServer > BackupDirectory> Modify > OK > Verify From SQL SERVER
 

How can get SQL Server Error log path ?

SELECT SERVERPROPERTY('ErrorLogFileName');

How can get SQL Trace File Path ?

SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc'
FROM sys.traces WHERE path LIKE '%\MSSQL\Log\log%.trc';

How can get SQL Server Startup Parameter path ?

Method 1:

DECLARE  @startup VARCHAR(255)

DECLARE  @arg VARCHAR(10)

DECLARE  @i INT

SET @i = 0

SET @startup = ''

WHILE(@startup IS NOT NULL)

BEGIN

SET @arg = 'SQLArg'+CAST(@i AS VARCHAR(2))

SET @startup = NULL

EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', @arg,  @startup OUTPUT

SELECT @startup

SET @i= @i+1

END

Method 2:

SQL Server configuration Manager > SQL server services >SQL Server(<Instance Name>)>properties >Advanced > Startup Parameters

How can get SQL Server Installation Path

declare @rc int, @dir nvarchar(4000)Exec @rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\Setup',
N'SQLPath',
@dir output, 'no_output'
Select @dir AS InstallationDirectory

How do you verify SSL Configuration ?

Start > Control Panel > System and Security > Administrative Tools > Internet Information Services(IIS) Manager > Expand Server Name > Expand Sites > Expand Default Website > Select specific sites > Under IIS - double click on SSL Settings > Client Certificate : Accept

How large maximum sql cluster/network name can be ?

15 charecters

How do you set up Net Backup

Application Name	Datacenter	Server Type	Cluster Name	Server Name	IP'S	NetBackup Main Paths
<App_Name>	<Location>	<VM/Physical>	N/A(For Stand Alone Server)	<SQL SERVER NAME>	<IP>	C:\ F:\Splunk_Logs Z:\Backups
<App_Name>	<Location>	<VM/Physical>	<SQL Cluster/Network Name>	<SQL Cluster/Network Name>	<SQL Cluster VIP>	Z:\Backups
				<Node1 _Name>	<Node1_IP>	C:\ F:\Splunk_Logs
				<Node2 _Name>	<Node1_IP>	C:\ F:\Splunk_Logs

DR

Step 1: Storage Team- Enable Image Access
Step 2: Wintel Team  - Enable Mount Access/Making Disk Online
Step 3: SQL Team- Start up SQL Services in DR
Step 4: App Team - Re-configure app to connect with App


 

Server Name : <Prod Server>
Drives	Description	Replicated/Not Replicated
C:	OS Program Files	Not Replicated for DR
E	User Data	Replicated for DR
F	Software binaries	Not Replicated for DR
G	System data and logs	Replicated for DR
L	Transaction Log Files	Replicated for DR
T	Temp Database	Replicated for DR
Z	Backups	Not Replicated for DR

Server Name :< DR Server >
Drives	Description
C:	OS Program Files
E	N/A(It will come from Prod)
F	Software binaries
G	N/A(It will come from Prod)
L	N/A(It will come from Prod)
T	N/A(It will come from Prod)
Z	N/A(No Need  in DR Side)