Cannot connect to WMI provider-SQL Server configuration manager.

On one of our SQL Servers, I was planning to make some changes to network configurations and I was welcomed with below error message when I try to open SQL config manager. FYI…This is a SQL Server 2016 Instance running on windows 2016 machine.

cannot connect to wmi provider. you do not have permission or the server is unreachable. note that you can only manage sql server 2005 and later servers with sql server configuration manager.invalid class [0x80041010]

This error mesage is pretty misleading IMHO at a high level, I am a local admin on the box and I am trying to manage a SQL 2016 Instance 🙂

Before proceeding to the final resolution, I would like you folks to read these statements from microsoft documentation.

“The Managed Object Format (MOF) compiler parses a file containing MOF statements and adds the classes and class instances defined in the file to the WMI repository. MOF files are usually automatically compiled during the installation of the systems with which they are provided, but you can also compile MOF files by using this tool.”

“To make the contents of a MOF file effective (by placing them in the CIM Repository), the file must be compiled. MOF files are usually automatically compiled during the installation of the systems with which they are provided, but you can also compile MOF files by using the MOF Compiler (Mofcomp.exe). The MOF Compiler is available in the %Windir%\System32\wbem directory. You must specify the MOF file as the parameter of the MOF Compiler.”

References:
https://docs.microsoft.com/en-us/windows/win32/wmisdk/compiling-mof-files
https://docs.microsoft.com/en-us/windows/win32/wmisdk/mofcomp?redirectedfrom=MSDN

In my case, For whatever reason, SQL Server installer coudn’t compile and register .mof files correctly. I am not sure if this happened during some patching or when SQL was installed initially on this box, I have no idea.

Resolution:
Navigate to shared features folder from cmd (as admin) and run below command:
mofcomp sqlmgmproviderxpsp2up.mof

In my case since it’s SQL 2016, the path for “sqlmgmproviderxpsp2up.mof ” file is
C:\Program Files (x86)\Microsoft SQL Server\130\Shared.
For SQL 2019, that would be:
C:\Program Files (x86)\Microsoft SQL Server\150\Shared.

Anyways…once the mof file got parsed successfully, I was able to open and manage my SQL Instance without any issues using SQL Server Config manager.

dbccLastKnownGood – The 2016 way.

How to check when was the last known good CheckDB for a given database? As of today on all the versions including SQL 2017(Except 2016 SP2), the easiest way we can get that information is by querying boot page of the database and look for “dbi_dbccLastKnownGood” value. You could do that by using DBCC page or DBCC DBINFO(). For folks who are paranoid about running DBCC PAGE or DBCC DBINFO command in production, If you have a monitoring tool which is displaying this information, I am 100% sure that’s exactly what your tool is running behind the scenes. Nothing to worry about, we are just reading the contents, not modifying anything in the boot page. Anyways, what got changed or I should say what was introduced in SQL 2016 SP2?

Well, we can pull this information from our good old friend DATABASEPROPERTYEX()  🙂

The Microsoft documentation says “Starting SQL 2016 SP2”. Okay, let’s test this on SQL 2017. I have a test database named “packers” on which I ran checkdb around 10:56 PM 05/14/2018 successfully. FYI

Let’s verify by pulling the info from DATABASEPROPERTYEX() on my SQL 2017 RTM Instance.

Oops! It returned NULL. So, basically it’s saying that we provided an invalid Input. Hmm, that’s not good!

Alright, Let’s check what does that report on a SQL Server 2016 SP2 Instance.
From Boot page:

Now, let’s pull the info from Databasepropertyex().


Yeyyy….That works! So….As of today, May 14th 2018, looks like this only works on SQL 2016 SP2 which was released just few weeks ago. I hope Microsoft fix their documentation or get this thing implemented in SQL 2017 in the next CU.

I wonder why it took so many years for Microsoft to implement this simple feature. Anyways we have it now and we don’t need to worry about querying boot page to pull this basic yet very useful/critical information.

Cheers!

SQL Server 2016 SP2 got released :)

Microsoft released SQL Server 2016 Service Pack 2 today. Visit https://support.microsoft.com/en-us/help/4052908/sql-server-2016-service-pack-2-release-information for all the details you need. There’s a looooong list of number of bugs fixed and enhancements made. Couple of DMVs and features from SQL 2017 were back ported as well.

Make sure you go all the way to the bottom of the page regarding GOTCHAS with uninstalling 2016 SP2 if needed. This is the section which I would like you guys to focus on if you are not on Enterprise Edition.

Cheers!

How to move AlwaysON AG Databases ?

Back in 2011, we have seen how to move a database which is participating in DB Mirroring here. In this blog post, let’s see how to move a database which is participating in a AG to a new drive(location). In my AG setup,I’ve three replicas(2 near Replicas-Sync mode and 1 far replica sitting in a different data center-Async mode). The database which I will be moving to a new location is “sales”. See below for current paths.

1

Now we shall move these files to below mentioned new location (In this post, let’s see method two mentioned below):

E:\Devices\MSSQL13.MSSQLSERVER\MSSQL\DATA ( MDF File)
F:\Devices\MSSQL13.MSSQLSERVER\MSSQL\Data ( LDF File)

Method one:

Remove the database from AG.
Detach the database.
Move the files physically to new location(s).
Attach the database.
Rejoin the database in your AG.

Method Two:
In this method we shall see how to move files without removing the database from AG.

Steps to perform in sequence:

Suspend data movement for the database which you are working on to all replicas.
Logically move the database files(On all the Replicas).
Stop the SQL Server services. – This step will create an outage for all other databases residing on this instance.
Now move the physical MDF and LDF files to your new location.
Start the SQL service from config manager.
Resume data movement.

Before proceeding any further, I made sure the new path exists on of my  replicas.

1.

2

2. Run this on all the replicas.

3

Now…I stopped SQL Services and moved physical files to new locations and started SQL Server.

3. Now resume data movement.

4

Voila…Now it’s all set as per my requirement.

5

That is it folks for today! Have fun…

Lab setup – AlwaysOn AGs in a Multi Subnet Cluster – Part 2

In part1, I’ve shown how to create a windows cluster in a multi subnet setup.  In this post let’s see how to create a AG and corresponding listener.

In my lab, I will be creating two AGs and two corresponding listeners.

Details:
I’ve two databases – > sales and customers.
Two AGs – > Sales_AG and Customers_AG.Two Listners – > sqllst_Sales and sqllst_Cust.
For sales AG, I’ve disabled “Database level health detection”, a new feature introduced in SQL 2016.

AG1

ag2

Now, for Listener two IPs from both subnets have been provided.

ag3

ag5

Same process has been followed for creating customers AG and listener as well(But this time I’ve enabled Database level health detection).

ag4

Since this is a multi-subnet setup two entries(one from each subnet) will be created in DNS for each listener name as shown below.

ag_final

That’s about it folks.

Lab setup – AlwaysOn AGs in a Multi Subnet Cluster – Part 1

Let’s see how to setup an AG(SQL 2016) in a multi subnet cluster(Geo cluster) in a lab environment.

Below is my lab setup:

Two Replicas sitting in my Production Data center.(Subnet 192.168.1.x) – Sync Mode Automatic Failover.
Third(Far) Replica sitting in my DR Data Center.(Subnet 192.168.2.x) – Asynch Mode Manual Failover.

So, What do we need to be able to setup multiple subnets and routing in a lab environment? Answer is “Routing and Remote Access“. Have that installed by going to Add Roles/Features on your AD/DNS server.

Pre-req Step: Created 2 NICs on my SANDC machine with IPs 192.168.1.100 and 192.168.2.100

Open Routing and remote access config tool; right click on the root node and select “Enable and Configure Routing and remote access”.

Now…under IPv4, under General right click and select new routing protocol and select “RIP Version 2 for Internet Protocol”.

1

Now right click on RIP and select new interface and select your NIC1 and hit okay and next repeat the same step and select NIC2 this time and click okay. You are done with routing…That’s all you need for routing to work(As long as you got all the IPs and DNS details right).

2

4

3

5

Now, I’ve setup 3 nodes(two nodes(Prd) in 1.x and one(DR) in 2.x) and installed Failover Cluster feature on all the nodes and disabled all firewalls.

My PRD nodes: STLSQLAG1 and STLSQLAG2.
My DR Node:AZSQLAG3

Do find all my NIC settings from all my nodes at the very end of this post.

Now, Let’s create Windows Cluster:

Please refer to my earlier posts on how to create a cluster under “Clustering category” for detailed steps. Below are the steps at a high level.

clu1

clu2

clu3

Now…My cluster is ready, but it’s missing Quorum which is very critical for a cluster to be healthy. For that I’ve created a File share witness as Quorum.

clu4

clu5

FYI, NIC settings from Cluster manager are shown below.

clu6

clu7

NIC settings on all of my nodes:

STLSQLAG1: (1 NIC Card)

nic1

STLSQLAG2: ( 1 NIC Card)

nic2

AZSQLAG3(DR Server): 1 NIC Card

nic_AZ_DR

AD/DNS Server: ( 2 NICs one for 1.x and other for 2.x)

nic3_sandc

nic4_sandc

In this post we’ve seen how to setup a geo cluster in a lab environment. So, this completes the prep work needed from Windows stand point…Let’s see how to create AGs and Listeners in our Multi Subnet environment in next part of this series.

SQL Server 2016 Cluster setup – What’s changed?

In this short blog post let’s take a screenshots tour of SQL Server 2016 Failover cluster installation and see if Microsoft made any significant changes to the setup process.

1

2

3

4

In the above two screenshots, I’ve provided my VNN and Instance name for SQL.

5

Add your Disks as needed.

6

provide your IP address.

7

Enter your Service accounts.

8

Select your Database directories accordingly and go to TempDB tab to check SQL installation wizard creating tempdb data files based on the number of CPUs you have got.

9

10

11

This is the first node of my cluster setup, hence you can see PRDSQLTREKA as the only node listed in the above screenshot. Next Next Done.

Now on the second node:

Select Add node to SQL Server Failover cluster and Next-Next-Next, provide service account and done.

12

13

Basically nothing has been changed specific to Cluster setup. If you are comfortable with earlier cluster setups, then you should be golden. Cheers!