Firstly a huge thank-you to everyone that attended the session on Saturday, and also a thank-you to BenF for assisting with the presentation (and letting me out of the cage for the day).
I am working with the organizers of Infrastructure Saturday to make the slides available for download, so stay tuned for more information on that front.
Some interesting questions were raised during the session and I thought I would provide some additional information here for those that were interested.
During the session some questions/discussions were raised about Virtalisation. I won't go into this in this post because I am actually expecting some interesting news on that front to come out of the SQLPASS which is being held this week in Seattle which although I am not attending I will be following closely.
At the end of the session I was approached with the question "We currently have multiple SQL 2005/2008 servers but our monitoring is only of the server layer, can you suggest/recommend how we could monitor the SQL layer".
In my experience a combination of monitoring solutions can offer a much more holistic view of your environment. This also helps when monitoring multiple different Operating Systems (e.g. Windows and Linux). Of cause this is based on my experience with monitoring 220+ servers.
The reason a combination of monitoring solutions can offer more depth is because some of monitoring platforms offer better network monitoring than others, and other monitoring platforms offer better Application / Software monitoring.
The challenge with monitoring a SQL Server is that you not only need to monitor the Infrastructure (e.g. ICMP, TCP Port tests, etc) but you need to be able to check Event Logs and that Services are running. Most monitoring systems can provide those tests, however you also need to monitor the actual SQL Instance layer and some of the best ways of doing that is through some form of script which can run a TSQL statement or use SMO to check aspects of the environment and return a status code to the Monitoring environment.
Some of the key SQL layer aspects that a good monitoring solution should be able to check are:
- SQL Server Memory
- SQL Server Error Log
- SQL Server Settings
- Database File Free Space
- Database Status
- Database Settings (for enforcing environment settings)
- Blocking Locks
My experience has been largely about using Microsoft Operations Manager (aka MOM) and custom VBScripts to ensure that our business rules are enforced. This has also allowed a number of automated actions to be implemented while allowing for the dynamics of the farm.
Here are some links to some monitoring platforms to look at:
Microsoft System Center Operations Manager (aka SCOM, the latest release of MOM)
http://www.microsoft.com/systemcenter/en/us/operations-manager.aspx
If I had to recommend an enterprise software it would be SCOM, only because that is where my experience has been with. Of cause there are other offerings from EMC and other vendors which might be more suitable to your environment.
If your looking for some free monitoring solutions purhaps the following might be of interest:
Zenoss
http://www.zenoss.com/ or http://community.zenoss.org
I have had some experience with using this and it can offer a good monitoring tool for network monitoring (ICMP) and auto discovery. It does take some amount of tweaking to get to work well with Windows (as with any Unix based monitoring tool) however it can provide that first level monitoring quite well and there are a number of ZenPacks available to provide SQL Server monitoring. However I do find it quite challenging to come up with a in-depth SQL monitoring from it, but this is purely because I am use to a Windows Scripting environment.
SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
I previously blogged about this (and some other open-source tools). This is still my fav free open-source offering, and a great option if you do not have but budget for SCOM and if you are willing to write your own client front-end, or if you need to plug into an existing monitoring front-end.
I hope this information helps you to find a way to monitor your SQL database environment. Feel free to post comments should you need more info.
Thanks again to everyone that attended the session.
MS SQL Server, C#, Powershell, SCOM and other technical interests....
Monday, November 8, 2010
Saturday, November 6, 2010
Infrastructure Saturday
Today is Infrastructure Saturday at Microsoft Brisbane, I'll be there presenting from 12:35pm – 1:20pm in Theater 2......
More info at http://infrastructuresaturday.org/
More info at http://infrastructuresaturday.org/
Tuesday, August 31, 2010
TSQL to report DB Mirroring status and configuration
Have you been faced with the need to report on, or verify the configuration of your SQL Server Database Mirroring. Well maybe you have or maybe you haven't, if you only have a small number of databases with DB Mirroring then this may not be very useful, but for me with 50-100 databases with DB Mirroring I needed a way to quickly check what the status and safety level of the mirrored databases were. Not to mention the fact that I needed to easily identify the databases that were no mirrored to report back to the application owners so they knew what was not in a DR configuration.
Hopeful this query can save you some time or at least give you a starting point if facing a similar situation:
SELECT a.name
, ISNULL(b.mirroring_role_desc, 'NOT MIRRORED') as mirroring_role_desc
, ISNULL(b.mirroring_state_desc, '') as mirroring_state_desc
, ISNULL(mirroring_safety_level_desc, '') as mirroring_safety_level_desc
FROM sys.databases a
INNER JOIN sys.database_mirroring b on b.database_id = a.database_id
WHERE a.name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
ORDER BY a.name
Hopeful this query can save you some time or at least give you a starting point if facing a similar situation:
SELECT a.name
, ISNULL(b.mirroring_role_desc, 'NOT MIRRORED') as mirroring_role_desc
, ISNULL(b.mirroring_state_desc, '') as mirroring_state_desc
, ISNULL(mirroring_safety_level_desc, '') as mirroring_safety_level_desc
FROM sys.databases a
INNER JOIN sys.database_mirroring b on b.database_id = a.database_id
WHERE a.name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
ORDER BY a.name
Monday, August 2, 2010
Export the SSRS Report Server's folder / item structure using TSQL
If your faced with the task of migrating your Reports from SSRS 2000/2005 to SSRS 2008 as I am then you're probably looking for a way to export your folder structure to help with the planning. You might be even using the migration as a way to clean up the security and how some of the items are stored (e.g. Data Sources). All of this is not easy without a way to export the folder/item structure from your existing environment. The following TSQL should help you gain an overview of your structure and allow you to start planning the migration.
Use ReportServer;
SELECT dbo.Catalog.Path
, dbo.Catalog.Name
, dbo.Catalog.Description
, dbo.Catalog.Type
, (CASE
WHEN dbo.Catalog.Type = 1 THEN 'Folder'
WHEN dbo.Catalog.Type = 2 THEN 'Report'
WHEN dbo.Catalog.Type = 3 THEN 'Image'
WHEN dbo.Catalog.Type = 4 THEN 'Linked Report'
WHEN dbo.Catalog.Type = 5 THEN 'Data Source'
WHEN dbo.Catalog.Type = 6 THEN 'Model'
ELSE 'Type_'+CONVERT(char(2), dbo.Catalog.Type)
END) as Type_Name
, dbo.Catalog.CreationDate
, dbo.Catalog.ModifiedDate
, ISNULL(dbo.Catalog.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
ORDER BY dbo.Catalog.Path, Parent.Name
In addition to this the following TSQL will provide you with a list of the assigned users and roles to each folder to help with preparing permissions during migration:
SELECT dbo.Catalog.Path
, dbo.Users.UserName
, dbo.Roles.RoleName
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE dbo.Catalog.Type IN (
1 -- Folder
)
ORDER BY dbo.Catalog.Path, dbo.Users.UserName
When it comes time for the actual migration you might want to look at using a tool such as RS Scripter..... I know I will be using that tool for most of my migration
Use ReportServer;
SELECT dbo.Catalog.Path
, dbo.Catalog.Name
, dbo.Catalog.Description
, dbo.Catalog.Type
, (CASE
WHEN dbo.Catalog.Type = 1 THEN 'Folder'
WHEN dbo.Catalog.Type = 2 THEN 'Report'
WHEN dbo.Catalog.Type = 3 THEN 'Image'
WHEN dbo.Catalog.Type = 4 THEN 'Linked Report'
WHEN dbo.Catalog.Type = 5 THEN 'Data Source'
WHEN dbo.Catalog.Type = 6 THEN 'Model'
ELSE 'Type_'+CONVERT(char(2), dbo.Catalog.Type)
END) as Type_Name
, dbo.Catalog.CreationDate
, dbo.Catalog.ModifiedDate
, ISNULL(dbo.Catalog.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
ORDER BY dbo.Catalog.Path, Parent.Name
In addition to this the following TSQL will provide you with a list of the assigned users and roles to each folder to help with preparing permissions during migration:
SELECT dbo.Catalog.Path
, dbo.Users.UserName
, dbo.Roles.RoleName
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE dbo.Catalog.Type IN (
1 -- Folder
)
ORDER BY dbo.Catalog.Path, dbo.Users.UserName
When it comes time for the actual migration you might want to look at using a tool such as RS Scripter..... I know I will be using that tool for most of my migration
Friday, July 23, 2010
Useful Open Source SQL Tools
The following are some useful Open Source tools available to assist with managing and administering a MS SQL Server:
SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
Also mentioned in http://www.codecapers.com/post/free-scom-alternative-for-sql.aspx
Open DBDiff
http://opendbiff.codeplex.com/
Performance Analysis of Logs (PAL) Tool
http://pal.codeplex.com/
Hopefully others will find them as useful as I have with simplifying my day-to-day DBA tasks.
SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
Also mentioned in http://www.codecapers.com/post/free-scom-alternative-for-sql.aspx
Open DBDiff
http://opendbiff.codeplex.com/
Performance Analysis of Logs (PAL) Tool
http://pal.codeplex.com/
Hopefully others will find them as useful as I have with simplifying my day-to-day DBA tasks.
Tuesday, March 30, 2010
Client Alias's.... don't forget your 32 bit apps
I have been caught out by this in the past myself and a colleague wasted a great deal of time trying all sorts of things only to find out there was a simple fix to all his connectivity woes.
Since the introduction of the 64-bit operating system we have seen applications become split into 32-bit (x86) and 64-bit (x64) branches. While the introduction of 64-bit was a god send for the application developer and server administrators who had been looking for ways to push more memory to applications to allow them to handle the huge tasks that today's business (and sometimes even the home user) wants to throw at them. The unfortunate down side to this was that 32-bit applications were cast out like the lepers of the Bible days and given their own registry space (ahhh you say, now he gets to the point of this, well all I can say is that it was worth it!!!)
If you don't know 32-bit and 64-bit applications have different registry spaces, because 32-bit applications cannot read anything within 64-bit memory. If you were to look at the registry on a 64-bit operating system it may not look different to the naked eye but if you look closely you will find that there is a new branch HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node and it is here were all the 32-bit applications store their registry settings.
So why is this important to anyone that has anything to do with database connections, because as you would be aware SQL Server allows you to configure Client Alias's within it's Configuration (of the connectivity tools) and it is through the use of these Alias's that we can map a Server Name (or even a made up friendly name) to a FQDN server address and non-standard TCP port. The problem is that on a 64-bit operating system there are both 32-bit and 64-bit Client Alias and depending on what the application you are connecting from is will depend on which type of alias it tries to read.
The times that I have usually come across this as a problem is when an installation package is asking for the SQL Server details of where it will create a database and the user is unaware if they are using a 32-bit or 64-bit application and create the Client Alias in the wrong branch.
So my rule is:
When you setup a Client Alias always set it up in both the 32-bit and 64-bit branches of the SQL Server Configuration Manager.
Since the introduction of the 64-bit operating system we have seen applications become split into 32-bit (x86) and 64-bit (x64) branches. While the introduction of 64-bit was a god send for the application developer and server administrators who had been looking for ways to push more memory to applications to allow them to handle the huge tasks that today's business (and sometimes even the home user) wants to throw at them. The unfortunate down side to this was that 32-bit applications were cast out like the lepers of the Bible days and given their own registry space (ahhh you say, now he gets to the point of this, well all I can say is that it was worth it!!!)
If you don't know 32-bit and 64-bit applications have different registry spaces, because 32-bit applications cannot read anything within 64-bit memory. If you were to look at the registry on a 64-bit operating system it may not look different to the naked eye but if you look closely you will find that there is a new branch HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node and it is here were all the 32-bit applications store their registry settings.
So why is this important to anyone that has anything to do with database connections, because as you would be aware SQL Server allows you to configure Client Alias's within it's Configuration (of the connectivity tools) and it is through the use of these Alias's that we can map a Server Name (or even a made up friendly name) to a FQDN server address and non-standard TCP port. The problem is that on a 64-bit operating system there are both 32-bit and 64-bit Client Alias and depending on what the application you are connecting from is will depend on which type of alias it tries to read.
The times that I have usually come across this as a problem is when an installation package is asking for the SQL Server details of where it will create a database and the user is unaware if they are using a 32-bit or 64-bit application and create the Client Alias in the wrong branch.
So my rule is:
When you setup a Client Alias always set it up in both the 32-bit and 64-bit branches of the SQL Server Configuration Manager.
Friday, February 5, 2010
SQL Failure After Upgrade from VMWare 3.5 to VSphere 4 Fix
Came across this issue today and found this blog so useful I thought I would throw a ping back to it and help others
SQL Failure After Upgrade from VMWare 3.5 to VSphere 4 Fix
http://retr0rob.blogspot.com/2009/09/sql-failure-after-upgrade-from-vmware.html
In short the issue is that after an upgrade of VMWare, SQL may not start and the event log will report that a timeout occurred when trying to connect but the failure happens immediately. If you try and start SQL from the command line (e.g. in single user mode) then you will receive an application pop-up error "This application has failed to start because MSVCP71.dll was not found. Re-installing the application may fix this problem". To fix this issue download the missing MSVCP71.dll (get it here) and copy it to %windir%\system32, then reboot.
NOTE: No warrant is held that this is the solution in all cases but it certainly worked for me, and care should be taken when dealing with system files.
SQL Failure After Upgrade from VMWare 3.5 to VSphere 4 Fix
http://retr0rob.blogspot.com/2009/09/sql-failure-after-upgrade-from-vmware.html
In short the issue is that after an upgrade of VMWare, SQL may not start and the event log will report that a timeout occurred when trying to connect but the failure happens immediately. If you try and start SQL from the command line (e.g. in single user mode) then you will receive an application pop-up error "This application has failed to start because MSVCP71.dll was not found. Re-installing the application may fix this problem". To fix this issue download the missing MSVCP71.dll (get it here) and copy it to %windir%\system32, then reboot.
NOTE: No warrant is held that this is the solution in all cases but it certainly worked for me, and care should be taken when dealing with system files.
Subscribe to:
Posts (Atom)