Tag Archives: MimboloveSQL
I’ve setup 2 servers for the SMA environment in my lab a while back. Yesterday, I loaded the SMA MP (version 18.104.22.168) into my OpsMgr management group. Needless to say, I followed the MP guide and configured the Database RunAs profile. However, soon after the MP is loaded, I started getting these 2 alerts:
- The Service Management Automation web service could not connect to the database.
- The Service Management Automation worker server could not connect to the database.
To troubleshoot these alerts, I firstly unsealed the management pack Microsoft.SystemCenter.ServiceManagementAutomation.2012R2.mp, as this is where the monitors are coming from. The Data Source module of the monitor type uses System.OleDbProbe probe action module to make the connection to the database.
To simulate the problem, I used a small free utility called Database Browser Portable to test the DB connection. I launched Database Browser using the same service account as what I configured in the RunAs profile in OpsMgr, and selected OleDB as the connection type:
I populated the Connection String based on the parameters (monitoring object properties) passed into the data source module: Provider=SQLOLEDB;Server=SQLDB01.corp.tyang.org\;Database=SMA;Integrated Security=SSPI
Note the Database Instance property is empty. this is OK in my lab because I’m using the default SQL instance. I’ll explain this later.
The test connection result is positive:
However, after connected, when I clicked the connection, nothing happened, the list of tables did not get populated. I then tried using my own account (which has god rights on everything in the lab), and I got the same result.
Long story short, after trying different configuration changes on the SQL server, I finally found the issue:
On the SQL server, the Name Pipes protocol was disabled
After I enabled it, I was able to populate the tables in Database Browser:
And within few minutes, the alerts were auto closed.
While I was troubleshooting this issue, I came across a blog post from Stanislav Zhelyazkov. In the blog post, Stan mentioned adding the DB instance name in the registry (where the discoveries are looking for). However, when I added “MSSQLSERVER” in the registry and forced re-discovery, the monitors became critical again and I received several 11852 event in Operations Manager event log:
I email Stan and he got back to me and told me he’s using a named instance in his lab and these monitors are working fine in his lab after he added the SQL instance name in the registry. He also told me he didn’t recall specifying the SQL Instance name during the SMA setup but the setup went successful. My guess is that the SQL Browser service must be running on his SQL server, so the setup had no problem identifying the named instance.
Based on my experience and Stan’s experience, we’d like to make the following recommendations:
- Enable the Name Pipes protocol
- If using the default SQL instance, please do not manually populate the registry key
- If using a named instance, please add the SQL instance name in the registry if it’s not populated after setup.
Thanks Stan for his input on this one!
System Center Data Protection Manager is not something I normally play with. Recently, I’ve been dobbed in to troubleshoot an issue with remote sites network performance at work and the issue ended up was caused by Auto Discovery in DPM 2010.
So basically, DPM has this built-in function called “Auto Discovery” which queries the domain controller of its’ own home domain and stores every single domain member servers in its database. This job runs once a day, you can choose the time window of this job, but you can’t really disable it.
One of my colleagues has posted this issue in DPM TechNet forum: http://social.technet.microsoft.com/Forums/en-US/dpmsetup/thread/df3dc4ae-200a-4778-8a91-1d7e68d564f2/ and I also logged a premier support call with Microsoft. We got 2 very different solutions from TechNet forum and the Microsoft support engineer in China. After evaluating both solutions, I have decided to go with the solution from the TechNet forum since it’s more robust, but make some modifications.
I have made 3 modifications from the original SQL scripts from TechNet forum:
- The solution from TechNet forum involves creating a custom SQL agent job called ‘Cancel DPM Auto Discovery’ that runs once a day, prior to the DPM Auto Discovery job. I noticed if you manually change the Auto Discovery start time from DPM console, a new SQL agent job for Auto Discovery is created. So I can’t really guarantee that the original schedule for ‘Cancel DPM Auto Discovery’ job is still valid. Therefore, I changed the schedule from daily to hourly, and runs at the 55th minutes of each hour(i.e. 12:55am, 1:55am, 2:55am, etc.). Because the Auto Discovery job can only run at the full hour (1:00am, 2:00am, 3:00am), by changing the schedule, I can make sure no matter what time the Auto Discovery is scheduled to run, the SQL agent job that I have created will always disable it 5 minutes prior to it.
- As I mentioned in the forum thread, I had to change the SQL job category to something other than DPM otherwise DPM will delete my job.
- Since we have over 2000 DPM servers in the environment, manually running the SQL script on each DPM server is impossible. Therefore I created a PowerShell script to run the SQL scripts and use SCCM to push it out. During testing, I found the SQL script works if I manually run it from SQL management studio, but when running in PowerShell using either System.Data.SqlClient.SqlConnection object or COM ADO object, the script complained about not able to find @owner_sid at the step of creating the job. I fixed it by changing the job owner from “MICROSOFT$DPM$Acct” to “sa”.
Below is the SQL Script and the PowerShell script after my modifications.
Note: Both SQL script and Powershell script assume the DPM database is configured as default (which is located locally on the DPM server and the SQL instance name is left as default of ‘MSDPM2010’). If your DPM server is located elsewhere, please modify the SQL script and the SQL connection string in the Powershell script accordingly.