Install SQL Server 2012 Cluster on a Windows Server 2012 -Part 1

Prerequisite for SQL Server Cluster Installation

 SQL Server 2012 – Run Failover Clustering Validation tool.
 Two instances of SQL Server or 1 and if 2 then both should have different cluster group.
 .NET Framework 3.5 with Service Pack 1 & Windows Installer 4.5.
 The nodes for the cluster and their ip addresses and access to both nodes.
 OS version & Edition – (Windows Server 2008 R2)
 SQL Server version & edition – (Enterprise or Standard)
 Verify Storage SAN/iSCSI
 Configure the quorum disk
 Create a Local Disk for Page File
 Verify that anti-virus software is not installed on your MSCS cluster
 Verify that you have disabled NetBIOS for all private network cards before beginning SQL Server Setup
 Remote Administration must be enabled
 The SQL Server installation product key.
 The SQL Server components to be installed.
 The SQL Server virtual server name.
 Default or named instance?
 The shared disks to select.
 The SQL Server virtual IP address.
 The subnet mask.
 The dedicated domain group for the SQL Server services and dedicated domain account to be added in admin group for running SQL server Engine and SQL Server Agent services.
 The dedicated domain accounts for each of the services if needed, to be installed (SSAS, SSIS, and/or SSRS).
 The SQL Server Collation to select mostly default.
 Windows or Mixed authentication mode
 The list of users to be the SQL Server administrator for the instance.
 The default locations for the data and log files for the user databases and tempdb and also the location for the backup directory.
 Create Domain Group with SQL Admin permissions and use that group for cluster security and management.
 MSDTC is to be installed prior to install sql server clustering with dedicated MSDTC drive and IP address.
 Cluster nodes should not be a part of domain controller.
Infrastructure Details:
 Ethernet LAN – 10.0.0.0/21
 Primary iSCSI LAN – 10.10.10.0/24
 Secondary iSCSI LAN – 10.10.20.0/24
 Heartbeat LAN – 1.1.1.0/24

 Node 1 LAN – 10.0.1.201
 Node 1 iSCSI1 – 10.10.10.201
 Node 1 iSCSI2 – 10.10.20.201
 Node 1 Heartbeat – 1.1.1.1

 SQL Node 2 LAN – 10.0.1.202
 SQL Node 2 iSCSI1 – 10.10.10.202
 SQL Node 2 iSCSI2 – 10.10.20.202
 SQL Node 2 Heartbeat – 1.1.1.2

 SQL Server:
 Authentication Mode – Mixed
 Database mode – SQL 2012 Compatible
 Management Cluster Name: SQLCLSTADMIN
 SQL Management Cluster IP – 10.0.1.203

 Virtual Cluster Name –
 Virtual Cluster IP –
 SQLCLST Cluster Name – SQLCLSTDB
 SQLCLST Cluster IP –

SQL Server Accounts Info
 Use sql Domain group
 SQL Database Engine domain group ID – Domain\sqladmin
 SQL server Agent domain group ID – Domain\sqlagent
Drive Usage :-
DATA FILE:
Data – L-Drive

Transaction Log:
Log file: P Drive
TempDB
Data – N-Drive
Backup Drive:
Backup: G:\
Windows Server 2012 Validate Cluster Configuration
Word of caution: do not skip any error message that this wizard generates in the final report. Doing so would simply mean that our configuration going forward will be unsupported. We only need to run this wizard on either of the nodes.
To run the Validate Cluster Configuration Wizard:
1. Open the Failover Cluster Management console
2. Under the Management section, click the Validate a Configuration link. This will run the Validate a Configuration Wizard
Cluster Validation1
3. In the Select Servers or a Cluster dialog box, enter the host names of the nodes that you want to add as members of your cluster and click Next.
i.e:- SQLCLST01, SQLCLST02

Cluster Validation2

4. In the Testing Options dialog box, click Next to run all the necessary tests to validate whether or not the nodes are OK for clustering. If this is the first time to run the wizard, we must run all the tests for validation. For succeeding runs, especially when adding hardware like disk subsystems ot network cards on your cluster nodes, we can selectively choose which tests to run as long as we have initially validated our hardware by running all tests.

Cluster Validation3

5. In the Confirmation dialog box, click next. This will run all the necessary validation tests.
In the Summary dialog box, verify that all the report returns successful.

Cluster Validation 4

Cluster Validation 5

Fail over Cluster Resource dependencies in sql server
When you install Microsoft SQL Server on a cluster as a SQL Server fail over cluster instance, a specific set of SQL Server resources that have dependencies on other resources in the cluster group are created.
Dependency Report:
Step 1: Right click on sql server  more actions Show Dependency Report

SQL DP

Depedency report

Advertisements

SQL Server Upgrade

Prerequisite for upgrading SQL Server from one version to another for a smooth upgrade process.

Step 1: We should be aware of the Microsoft supported upgraded paths.

Example: SQL server 2005 X64 Enterprise Edition can’t be upgraded to SQL Server 2008 R2 standard edition. Only option to upgrade to SQL server 2008 Enterprise edition or Datacentre Edition.

http://msdn.microsoft.com/en-us/library/ms143393.aspx

Step 2: Run the upgrade advisor
It’s a free utility tool that analyses existing SQL Server deployments and highlighted the issues that need to be addressed prior to performing an upgrade.

Step 3: Verify the hardware and software requirements

It’s very important to verify that existing SQL Server meet the entire hardware requirement for running SQL Server.

Step 4: Perform a Full Backup

Take full backup prior to perform a SQL Server upgrade. Upgrade process usually go smoothly however sometime may go wrong.
Hence it’s important to have a backup so that we can revert to its previous state if the upgrade does not go as planned.

Step 5: Two main options for an upgrade in SQL Server.

 In place Upgrade

An in-place upgrade is the fastest and easiest upgrade method because it upgrades all system and user databases and settings for you. In addition, you do not have to update client applications to connect them to a new instance of the relational Database Engine. However, an in-place upgrade is an all-or-nothing approach. In the unlikely event that an in-place upgrade of the relational Database Engine fails, you cannot quickly roll back to SQL Server 2000 or SQL Server 2005.

 Side by side upgrade

With a side-by-side upgrade, the SQL Server 2008 R2 relational Database Engine is installed as a second instance and the original SQL Server 2000 or SQL Server 2005 relational Database Engine remains installed. Then move or copy one or more SQL Server 2000 or SQL Server 2005 user databases to the SQL Server 2008 R2 instance (each moved database is automatically upgraded).
This approach help maximize ability to quickly roll back to the prior instance whenever difficulties arise. A side-by-side upgrade might also result in simpler testing scenarios because both versions are available at the same time.

Step 6: Things to consider while upgrading the database engine.

If you are running analysis service, make sure you upgrade analysis service before database engine. The analysis service must be upgraded first.

Before upgrade temporarily disable stored procedure .During the course of upgrade, various SQL- related services will be stopped and restarted. If there are procedures configured to run when services start, there may be chance those stored procedure will interfere with upgrade.

Check the master, model, msdb and tempdb databases and verify that they are set to auto grow.

Disable database replication prior to performing an upgrade.

Even though Microsoft SQL Server 2008R2 is designed to preserve max worker thread, Microsoft recommend settings the MAX worked thread value to 0.

Step 7: Run DBCC UPDATEUSAGE

Post upgrade run DBCC UPDATEUSAGE on all databases to ensure database integrity.

Attached one Microsoft upgrade document for complete detail.

A significant part of SQL Server process memory has been paged out

A significant part of SQL Server process memory has been paged out. This may result in performance degradation.

A significant part of sql server process memory has been paged out.

This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 48864, committed (KB): 124224, memory utilization: 39%.

Cannot use Large Page Extensions: lock memory privilege was not granted.

What is working set: Memory allocated by the process which is currently in RAM.

Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)

Working Set trimming:  Windows is moving the allocated bytes of the process from physical RAM to page file because of memory pressure. Memory pressure is most commonly caused by applications or windows components that are requesting more memory causing OS to start trimming working set of other processes to satisfy these new requests.

Before we step in to troubleshooting working set trimming warnings, here are few basics about how SQL Server memory management is designed to dynamically adjust the committed memory based on the amount of available memory on the system.

SQL Server uses CreateMemoryResourceNotification to create a memory resource notification object  and SQL Server Resource monitor threads calls QueryMemoryResourceNotification every time it runs to identify if there is any notification. If a low memory notification comes from Windows, SQL Server scales down its memory usage and when Windows sends the high memory notification, SQL Server Server can grow its memory usage target. Low memory notification is signaled by windows when the available physical memory drops approximately below 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value. As soon as the SQL Server resource monitor threads finds low-memory-resource notification it scales down SQL Server memory usage.

Why do I see “A significant part of sql server process memory has been paged out. This may result in performance degradation.” By SQL Server In spite of having above mechanism to detect the system level memory pressure and scale SQL Server memory?

There are couple of situations where SQL Server Process working set might be paged out by Windows despite these memory resource notification mechanism.

1.If windows is not sending the correct notifications to all listening processes at the right moment and thresholds

2.If SQL Server is not responding fast enough to the low memory resource notification from Windows

3.Conditions in Windows where working sets of all processes are trimmed

4.Windows might decide to trim a certain percentage of working set of various or specific processes

We can also increase the LowMemoryThreshold value so the OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system is starving for memory.

How to set the LowMemoryThreshold value (in MB)?

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 1000

System Reboot is required to take effect.

Default values as per MSDN:

“The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value.”

We can use the below query to extract information about the condition of OS memory and SQL memory using a query like the following. Looking at this query, you will be able to easily determine the various indicators that would have triggered the Windows to page various processes including SQL Server. Use the following query to obtain the memory notification-related information from the XML data of the ring buffer.

Common Side Effects of Working set Trimming

1.  When OS starts trimming the working set of SQL Server we normally see non-yielding Resource Monitor dumps.

2.  IS-alive check failures resulting in SQL Server resource failure.

3. Resource monitor thread can start  Garbage collector  if SQLCLR is enabled on this instance of SQL. When Garbage collector  is kicked off during memory pressure all other threads in the process are suspended. So if Garbage collector is taking a long time reosurce monitor thread appears stuck and hence the non-yielding errors and dumps are generated. (Refer http://support.microsoft.com/kb/2504603)

How to troubleshoot?

1.  Capture perfmon counters (Process: Private bytes and Working set ) to determine which applications / windows component are requesting memory and causing OS to start trimming the working set of processes including SQL Server.

2.  Use this exe which will print the memory information of all the processes and system wide memory information (Global memory status) when the operating system signals  low memory notification.

3.  Cap the SQL Server MAX Server Memory after considering the memory required by other applications, Operating system, Drivers , SQL Server Non- Bpool allocations etc. Make sure you have adequate available physical memory even when the system is under heavy load.

4.  We can consider using the Lock pages in memory privilege. Remember it protects only the BPool from paging and Non-Bpool allocations can still be paged out.

Parameter Sniffing

What is parameter sniffing?

This problem occurs when you work with parametrized SQL statements like stored procedures in combination with the plan caching mechanism of SQL Server.

When you execute a parametrized SQL query in SQL Server, the Query Optimizer compiles an execution plan based on the first provided parameter values. Afterwards the generated query plan is cached in the plan cache for further reuse. This means that SQL Server will reuse the plan afterwards without regard for the parameter values that you provide at the time.

2 kind of parameter value:

  • Compile time values
  • Run time values

The Compile time parameter values are the values that are used by the Query Optimizer to generate a physical execution plan. And the Run time values are the values that you provide for the execution of the query plan.

For the first execution these values are identical, but for subsequent executions, these values will probably be different. This may introduce serious performance problems, because your execution plan is always optimized for the Compile time values, and not for the various Run time values that you subsequently provide.Imagine now what happens if a query plan with a Bookmark Lookup gets cached, and afterwards gets blindly reused with a non-selective value. In that case SQL Server doesn’t implement any protection mechanism and will just execute the found query plan from the plan cache. As a result   I/O costs (your logical reads) will just explode, and the query will end up with very bad performance.

To show you a concrete example of this behavior, the following code creates a simple table with an uneven data distribution in the second column.

— Create a test table
CREATE TABLE Table1
(
Column1 INT IDENTITY,
Column2 INT
)
GO

— Insert 1500 records into Table1
INSERT INTO Table1 (Column2) VALUES (1)

SELECT TOP 1499 IDENTITY(INT, 1, 1) AS n INTO #Temp
FROM
master.dbo.syscolumns sc1

INSERT INTO Table1 (Column2)
SELECT 2 FROM #Temp
DROP TABLE #Temp
GO

****************CREATE A NON CLUSTERED  INDEX ON COLUMN2**********************

CREATE NONCLUSTERED INDEX [indexTable1_Column2] ON [dbo].[Table1]
(
[Column2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Lets run the execute the select statement.

select * from Table1 where column2=1
select * from Table1 where Column2=2

This is an example of uneven distribution of data where value 1 occurs only once and that the value 2 occurs 1499 times.

We see 2 different execution plan for the same logical query.

SQL query1

Now when we create a stored procedure, the query optimizer will create a execution plan based on the initial parameter values and afterwards same execution plan will be used by the subsequent executions.

— Create a new stored procedure for data retrieval
CREATE PROCEDURE RetrieveData
(
@Col2Value INT
)
AS
SELECT * FROM Table1
WHERE Column2 = @Col2Value
GO

Now when we execute the procedure with value 1 it just return one record, query optimizer will choose a bookmark lookup in the execution plan.The query just itself produces a 3 logical reads. when you execute with the parameter value of 2, the cache plan get reused and bookmark lookup is done 1499 times as a result 1505 logical reads.This is a huge difference from the previous execution.