Monday 13 July 2015

SQL Server Configuration Manager - QuickFix - Cannot connect to WMI Provider

Sometimes it reminds of Murphy's Law when people complain about issues which in fact can be solved in a minute or two.

I was working in a environment where my client also have full access to all DB servers, databases like DBA and he sent a weird email stating that SQL Server Configuration manager is not working and the error screen.


It reminded me again that whoever having elevated privileges should have good understanding of the tools they use, otherwise, we end up with disaster sometimes. Somehow my client doesn't have SQL background however he was expert in SharePoint development . And yes, I took this an advantage , somehow convinced him explaining the cause of the issue and explained the consequences having too many people with elevated privileges.

So to my readers, here is the explanation:

Cause:
By any chance , WMI provider is removed from the server where your installed SQL Server Instance,causes this error.

Resolution:
  1. Open command prompt and
    Navigate to %programfiles(x86)%\Microsoft SQL Server\100\shared  -- SQL Server 2008/R2 
    %programfiles(x86)%\Microsoft SQL Server\110\shared  -- SQL Server 2012
 2. Execute the command - mofcomp sqlmgmproviderxpsp2up.mof
 

Now try to open the SQL Server Configuration manager, it works.
Have a nice day !!


SQL Server – Assessment for Intake

In order to assess any existing SQL Server to be taken over and managed by Production Operation’s SQL DBA, the SQL Server needs to go through the acceptance process.  The process is as listed:

  • SQL Server audit – Production Operation’s SQL DBA will audit the condition of the existing server to determine what is required to bring the SQL Server into compliance with standards.  This includes things such as security, database sizing and file layout, service accounts, maintenance jobs, etc.
  • Audit Review – Production Operation’s SQL DBA will review results of audit with stakeholders.  During the review process, the DBA recommendations will be signed off on, the SLA (Service Level Agreements) established, and the Change Management Process approved. Below are the Audit details :-
Assuming that the installation is not done by SQL DBA, we will check the following parameters at Server level \database level:
Server level checks:
  •  Verify that you have newest hardware\newest OS with latest SP\patches
  • Verify that if SQL Server was installed properly from BOOTSTRAP\logs.
  • Verify that ports(1433,1434) opened or see if any dynamic ports used for SQL Server
  • Verify whether its running with newest SQL Server version with latest SP & Hot Fixes
  • Verify whether Instant File Initialization is enabled on the server
  • Verify whether the disk space has been properly distributed for binaries, data \ log files, backups,tempDB,Quorom and MSDTC
  • Verify whether backup compression enabled
  • Verify the authentication of the sql server either  windows or Mixed
  • Verify whether do we have domain service accounts for SQL Services
  • Identify the users who has SA permission
  • Verify if BUILDIN\ADMINISTRATORS,NTAUTHORITY\SERVICE has SA on SQL Server
  • Verify if the environment has high availability/ DR supported, if No. Go to next step.
If yes,
Check whether it’s Log shipping\Mirroring\Replication or clustering or AlwaysON and understand whether the implemented techniques working properly irrespective of HA/DR methods used and find out the configurations \ replicas etc.
Verify whether any configurations enabled after installation of SQL Server such as
i)        Fillfactor
ii)      Min & MaxServerMemory
iii)    Max. degree of Parallelism
iv)    FileStream
v)      SQLAgentXPS
vi)    Optimize for ad hoc workloads 
vii)  SQLMail
viii)xp_cmdshell etc.

Database :
  • Identify the recovery models of the databases \ compatibility level of all databases
  • Check the current size of all databases, understand the growth since a month.
  • Check whether Auto Create Statistics and Auto Update Statistics are set to true for all databases.
  • Check if there any backup jobs (full\differential\tlog)  and their schedules
  • Verify that the old backups are stored on local disk or on file share to access them when required.
  • Verify if the databases got ChangeDataCapture , TDE etc are enabled
  • Verify if any health checks are being implemented on all the databases
  • Go through the SQL Agent jobs, history and errorlogs etc for more understanding of the activities .
  • Verify whether we are checking fragmentation on the databases. If there are no maintenance plans, determining the level of fragmentation level, we will setup a maintenance plan to re-organize (5%-30%) \rebuild(Above 30%) on weekly basis.
  • Check if there are any maintenance plans for running the following :-
i)      Deleting old backup files\history of the backup jobs
ii)    Database consistency checks
iii)  Index re-organize\rebuild
iv)     Update statistics
Things to know from the application owner(Good to have):

1)      What is the functionality of the application?
2)      At any given point of time, how many users connected to the database?
3)     Approx avg. reads\avg. writes per sec on the database
4)     What are peak\non-peak hours
5)     Application Priority (LOW\MED\HIGH)
6)     Escalation Matrix

Upon noting down all the details, give a DBA report on what is good and where it requires improvements.

Performance of SQL Server can be monitored for at least 2 months to understand the flow of the application and then we can have our recommendations to make it better.

Upon Intake,work on all the improvement areas to make your instance consistent wrt availability, performance etc.

Monday 6 July 2015

Microsoft SSMS : Property Owner is not available for database - Issue & Fix

Microsoft SSMS : Property Owner is not available for database - Issue & Fix:


Issue:

On your daily routine checks , you may encounter the following error while expanding database properties(+):


Ahhhh ! whats this - how is this possible , it was alright yesterday and now what ?

Don't be surprised with this error since this is very common when you have a invalid user\login assigned as db_owner for the database.

Well we can check the db_owner details like as below:
sp_helpdb <yourdbname>

You may see NULL or ~~UNKNOWN~~ , then fix the error by assigning to a valid login.

USE <yourDBName>

go

exec sp_changedbowner 'sa' -- In my case, it can be any valid login


go


Once the commands are executed successfully, verify the db_owner details by executing the following command-
sp_helpdb <yourdbname>

Now you can expand your database properties and continue with your activities...

Good luck...