How Can Add Disks in SQL Cluster ?

Step1 :Add Disk

Step2:Add from Storage

Step3:Add from Available Storage

Here is standard Figure to configure 3 Nodes 2 SQL Clusters


3 Nodes 2 SQL Clusters

SID

Error:

“SqlException: The database owner SID recorded in the master database differs from the database owner SID recorded in database 'xyz'. You should correct this situation by resetting the owner of database 'xyz' using the ALTER AUTHORIZATION statement.”
I ran the below command and saw 2 different SID
Use xyz
Go
--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
Go
--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principals WHERE name=N'dbo'
Go


Application owner got this error message during installation application. Whey application is ran, it creates database xyz

Solution:
Step1 :Drop that database and run the app.During application running , just watch the database and run the below command whenever you will see database xyz is created. You should run it as soon as you can.
Use xyz
Go
ALTER AUTHORIZATION ON Database::XYX TO [sa]
Go
--I used lower case for sa because my server was in case sensitive collation
--Then run below script
ALTER AUTHORIZATION ON Database::Summix_fsdb TO [Domain\user]
Go
--Application requires domain user for this app for my case
 Step2 : I ran the below command and I saw SID is sameUse xyzGo
--To get owner SID recorded in the master database for the current database
SELECT owner_sid FROM sys.databases WHEREdatabase_id=DB_ID()
Go
--To get the owner SID recorded for the current database owner
SELECT sid FROM sys.database_principalsWHERE name=N'dbo'
Go

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