Issue – Today I faced very weird issue; on multi instance SQL Server machine, couple of SQL Services was not starting after Server reboot ( In my case it was SQL 2014 and SQL 2016 ) and every time I was trying to restart my SQL Services, I was getting below error message –
Solutions – On error message it’s look like some other processes was trying to use SQL default port 1433.
Hence it was not allowing to restart SQL Services with Port 1433.
On my server, we have multiple SQL Services installed ( SQL 2008 to SQL vNext ) hence I started checking if any of these SQL Server have assigned static port 1433.
I start checking assigned port for all SQL Server and I found SQL Server 2014 and SQL Server 2016 was having assigned static port 1433.
In order to resolve the issue, I removed static port entry and restart my SQL Services and it started successfully.
Microsoft Best Practice Analyzer –
Best Practices Analyzer (BPA) is a server management tool that is available in Windows Server® 2008 R2. BPA can help administrators reduce best practice violations by scanning one or more roles that are installed on Windows Server 2008 R2, and reporting best practice violations to the administrator. Administrators can filter or exclude results from BPA reports that they do not have to see. Administrators can also perform BPA tasks by using either the Server Manager GUI, or Windows PowerShell cmdlets.
You will find more details in –
Installation of BPA – SQL 2012
• PowerShell V2.0
• Microsoft Baseline Configuration Analyzer V2.0
• User should have admin access on server and SQL Server
o Note – You need to add your individual id or else you will get login error after running BCA.
Steps by Step Installation –
Step 1 – Download and Install the Microsoft Baseline Configuration Analyzer v2 (BCA) (follow default download instructions) – This will install to C:\Program Files\Microsoft Baseline Configuration Analyzer
Download link – https://www.microsoft.com/en-us/download/details.aspx?id=16475
Step 2- Install the Microsoft SQL Server 2012 Best Practices Analyzer (SQL BPA) (follow default download instructions) – This will install to C:\Windows\System32\BestPractices\v1.0\Models\SQL2012BPA\
Note – You will not able to see any icon for (SQL BPA) as it’s only install executables.
Step 1 – Open Microsoft Baseline Configuration
Note – If you wanted to connect remote computer, click on Connect to Another Computer.
Step 2 – Select “SQL Server 2012 BPA” from drop down.
Step 3 – Go to Enter Parameters and select parameters you wants to select.
Note – If it’s remote server provide server name in Alternate_Server_to_scan (Only server name not instance name), provide instance name in SQL_Server_Instanace_name fields.
Step 4 – Click on “Start Scan”
Step 5 – Analyze your result.
I was configuring BPA 2012 in windows 2012, after installing both BCA and SQL BPA, I was not able to see “SQL Server 2012 BPA” in drop down and to resolve the issue I have performed below steps –
1. Navigate to the directory C:\Windows\System32\BestPractices\v1.0\Models and right-click the SQL2012BPA folder and make a copy.
2. Navigate to C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\ and paste the SQL2012BPA folder in this new location.
(Note – ProgramData is hidden folder which you need to see after checking “Hidden Items” in view of your file explorer)
3. Start the BCA with Run As Administrator and you will now see the SQL Server 2012 BPA listed in the “Select a product” dropdown.
How Get SQL Server Job Name with help of program name
SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4)
Step -1 Create Below function
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (
DECLARE @start_of_job_id int
SET @start_of_job_id = CHARINDEX(‘(Job 0x’, @program_name) + 7
RETURN CASE WHEN @start_of_job_id > 0 THEN CAST(
SUBSTRING(@program_name, @start_of_job_id + 06, 2) + SUBSTRING(@program_name, @start_of_job_id + 04, 2) +
SUBSTRING(@program_name, @start_of_job_id + 02, 2) + SUBSTRING(@program_name, @start_of_job_id + 00, 2) + ‘-‘ +
SUBSTRING(@program_name, @start_of_job_id + 10, 2) + SUBSTRING(@program_name, @start_of_job_id + 08, 2) + ‘-‘ +
SUBSTRING(@program_name, @start_of_job_id + 14, 2) + SUBSTRING(@program_name, @start_of_job_id + 12, 2) + ‘-‘ +
SUBSTRING(@program_name, @start_of_job_id + 16, 4) + ‘-‘ +
SUBSTRING(@program_name, @start_of_job_id + 20,12) AS uniqueidentifier)
Step – 2
Execute below query –
job_id = dbo.GetJobIdFromProgramName (‘SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4) ‘)
SQL Server 2012 always on Step by step configuration –
Prerequisite to configure always on –
1. Domain accounts for SQL Server services should have local admin access
2. Two IP addess
One is for Cluster
Other one is for always ON listener
Note – These IP address should be in customer facing sub-net
3. UNC share on any non-cluster server and non-domain controller.
Cluster computer account and each node computer account should have R\W access to this share.
if you are not having domain controller access ask your Window Admin team
– Pre-staged cluster computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002.aspx#BKMK_steps_precreating
– Pre-staged AlwaysOn listener computer account in Active Directory. Account can be pre-staged according to the following article: http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_precreating2
Windows cluster installation:
1) Install Failover Cluster feature using Server Manager on both/all nodes.
2) Open Failover Cluster Manager and create a new cluster:
3) In “Select Servers” step add all your cluster nodes. Click Next.
4) Pass the validation, all test. Warnings regarding storage are OK – you’re setting up AlwaysOn, so you don’t have shared storage.
5) Specify cluster name and IP address on the next screen. If cluster account was pre-staged make sure to specify correct name (same as the pre-staged computer account name). Make sure to use IP, that was provided by customer and is 100% not in use.
6) Finish cluster installation.
7) Configure quorum or the newly created cluster:
How to configure Quorun –
a. Right Click on Cluster Name –> More action –>Configure Cluster Quorum setting
b) On the “Select Quorum Configuration Option” window select “Select the quorum witness”. Click “Next”.
c) On the “Select Quorum Witnes” window select “Configure a file share witness”. Click “Next”.
d) Specify a file share path, provided by the customer. Click “Next”
e) Click “Next”, then “Finish”
Installing SQL Server:
1) Install SQL Server as a stand-alone instance.
2) Following components are required and recommended for basing SQL Server installation:
3) Complete the installation.
4) Make sure to add SQL Server service account to sysadmins at each instance. This is required.
Enabling AlwaysOn on instance level:
1) Open SQL Server configuration manager.
2) Select SQL Server Services in the left-side menu.
3) Right click SQL Server service and select “Properties”:
4) Go to “AlwaysOn High Availablity Tab” and check “Enable AlwaysOn Availability Groups”:
5) Click “OK”. Make sure to restart SQL Server service.
6) Repeat 1-5 on each node.
Configuring AlwaysOn high availability group (single interface option)
Please skip to the next part if you want to use separate NICs for mirroring traffic and user connections.
1) Open SQL Server Management studio and connect to the first node.
2) Open AlwaysOn High Availablity -> Availablity groups and select “New Availability Group” (make sure to use 2012 Management Studio):
3) In the new window enter availability group name (it may be any name you wish), click “Add” and specify secondary replica name:
4) You can use default settings or customize them.
5) Click “OK”.
Configuring AlwaysOn availability group listener
1) Go to SQL Server Management Studio, expand AlwaysOn High Availablity, expand your Availability Group, created earlier, right click “Availabilty Group Listeners” and select “Add Listener”:
2) Specify Listener DNS Name (if AlwaysOn listener computer account was pre-staged, this should match the name of the computer), enter 1433 into “Port”, for “Network mode” select Static IP and click “Add” to add an IP address:
3) In the “Add windows” select the correct subnet and enter an IP address provided by customer:
4) Click “OK” and “OK”.
Adding/Removing databases to/from AlwaysOn availability group.
1) To add a database to availability group you select “Add database” in “Availability databases” and follow the Wizard.
– You can use fully automated addition. In this case you will need network share, accessible from both servers and with RW rights for SQL Server service account.
SQL Server will perform backup and restore of the database automatically.
– You can use “Join only” addition. In this case you will need to perform full backup and t-log backup and restore (as when configuring Mirroring, WITH NORECOVERY) and then use the Wizard.
2) To remove a database from availability group just right click the database and select “Remove database from availablity group…”:
Please note that before deleting database, that is part of AlwaysOn HA, you must delete it from Availability group.