At noon tomorrow October 11th (PST), voting for the PASS Board of Directors closes. As members of the organization we are tasked with choosing leaders that we think will positively impact PASS. All 6 candidates are excellent and have been vetted by the nomination committee. Please keep in mind that this is an opinion blog. I have placed my vote, have you?

One candidate that meets my qualifications and stands out for a US/Canada & Open opening is Jason Strate.  With the hours ticking down, I thought I would share with you why I would like Jason to a member of the PASS board of directors.

I was first introduced to Jason via his Blog. The amount of and depth of his blog material has been a tool belt over the years. His Blog continues to provide me information on all aspects of SQL Server as well how to conduct my daily activities. His dedication to sharing his knowledge is a key part of the SQL Server community in my opinion.

I then had the privilege to work with Jason at Pragmatic Works where he played the key role of Architect. Jason and I were on a project together that was challenging and provided us with many opportunities. During this time together I saw Jason take lead roles, provide clear direction, and take on challenges most would shy away from. Jason played a large role in helping me adjust to the role of a consultant. There were many things that made him successful at Pragmatic Works, however, I think his strong communication and organization are what made him stand out amongst his peers.

From a work ethic perspective, Jason is solid! I really can’t say more than that.

Without getting wordy and long winded, Jason is a loyal and good friend. Qualities that I look for in a person I would like to work with, follow, and go to bat for. PASS will always provide challenges to its board due in part to the size and the number of varying opinions. The qualities I see in Jason as a friend are the qualities I would want at the helm of PASS. Jason is also a stellar family man. He is a proud father, a great husband, and a friend to his kids. He is always there for them. Also a quality I like to see in a leader. He is also a very gracious person. He and I play video games into the wee hours of the morning. During these gaming sessions, he has allowed my son to play and join in the reindeer games. He is much younger than both of us; however, Jason allows him to be a kid during our gaming sessions. This quality of patience is also a quality I like to see in a leader.

I respect all 6 candidates (many friends) running for the PASS board of directors. To volunteer for this position takes a very dedicated person and each member running I believe is dedicated to us the members of PASS.  That being said …

VOTE JASON STRATE TO BE ON YOUR PASS BOARD OF DIRECTORS.

the-taste-of-alpharetta-ga

Well it’s that time again. Join us at SQL Saturday Atlanta a place to learn and meet new friends while networking. SQL Saturday Atlanta has set the bar when it comes to SQL Saturdays. If you haven’t been to one this is a great first time event!  If you have been to one, but not Atlanta, you will be amazed at the sessions as well as the sheer number of people that attend.

The SQL Saturday team is providing me the opportunity to share with you this weekend and it’s an honor to present at an event like this. I will be presenting my session More than Just a basic Database Backup and Recovery in the afternoon right before the raffle. I promise to get you out the door in time so that you can attend. We all love free stuff and Atlanta has lined up some great sponsors.

Provided is a link to the full schedule.

Caution
What I am showing impacts your registry. Do all the precautionary steps to backup and ensure that if something bad happens when making this change you can get back to a stable state.

About 6 or 7 years ago, I had an issue where my SQL Server performance counters were not available when looking within performance monitor. I went thru the steps of unloading and reloading the counters according to Microsoft documentation and by looking at many of the blog posts out there. The performance counters still would not show.

My next step was to dig into the registry and look at the performance counters entry for SQL Server.

We can drill into the provided registry path by running regedit.exe:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance
*If you have a named instance you will need to choose it in place of MSSQLSERVER*

I found a curious registry value; one I had not seen in the past, “Disable Performance Counters”. Just recently I ran across this little gem again. However, I forgot about it until I started digging further. So this time I decided to blog about it in order to help you, the audience, and me to not forgot about this in the future and save time trouble shooting the issue.

In the provided screenshot I highlighted the registry setting, and you can see the value is set to 1. Having the value set to 1 indicates that our SQL Server performance counters are disabled, and we will not be able to choose them within performance monitor.

Image_registry_prechange

To show this I have provided the following screenshot. Within the screenshot we can clearly see that the SQL Server performance counters are not available to us.

Perfmon_prechange

In order for us to see the expected performance counters we need to modify the registry value to 0. You can do this by double clicking on the “Disable Performance Counters” registry value. Enter 0 into the window and it will convert it to the Hexadecimal equivalent. After you make the change, your registry setting should appear as it does in the provided screenshot below.

Image_registry_post_change

Open up performance monitor, scroll down, and you should see your SQL Server performance counters. Happy monitoring!

Perfmon_post_change

I would like to note when I did my demos, this change did not require a restart of my SQL Server instance or the server I was working on. I have read instances where a recycle of either was required. So please keep that in mind when making this change.
As always thank you for reading and hanging out with me. If you have any questions please reach out.
@DBABulldog
Dan

First off I would like to thank all of the sponsors, volunteers, and coordinators of SQL Saturday Jacksonville. It was very well run and overall a great experience. I also want to give huge thanks to all of the attendees, without you coming on a beautiful Saturday, we would have had an event of just speakers. That would have proved to be interesting. To all the new folks I me,t you all are awesome and I look forward to meeting up again in the future.

I was excited to share my presentation More than Just a basic Database Backup and Recovery with the group. Overall the presentation went well. I thought my cadence was good and you the attendees were attentive when I was speaking. But, yes there is a but for this one.  As I moved forward with the presentation I realized my timings were off. I moved into my demos and the first couple went well. However 50% of the way into my demos I noticed that I had not reset my environment. This did throw some kinks into my presentation, as well as, my demos. I was able to cover some really cool stuff; however, I did not get to the real nuts and bolts of the recoveries. Part of my goal was to show you how to do partial and piecemeal recoveries. Unfortunately time got away from us on Saturday morning. I had done this presentation prior to this but I did not tweak and account for the 60 minutes we had on Saturday.

I received good reviews on the topic and the material that was covered. I also received excellent feedback that provided insight into how the session could have gone better. I have taken this feedback to heart and will be tweaking this presentation for future events.

Provided is a link to a Webinar in which I cover all the topics of the slide deck.

http://pragmaticworks.com/Training/Details/More-than-Just-a-Basic-Database-Backup-and-Recovery

Take a ways from this session:

  • Add to my checklist to reset my demo environment before each session (I have already put these measures into place. Not sure what it wasn’t there from the beginning. )
  • I need to watch my timings. I like to talk and sometimes I can’t get out of my own way…

Follow-up

  • There was a great question with regard to differential backups and how the differential change map behaves when SQL Server decides it would be more beneficial to do a full backup. I plan on doing a blog post on this to show what happens.
  • Prep this session for SQL Saturday Atlanta

As always thank you for reading and hanging out with me and if you have any questions please reach out to me.

Your @DBABulldog

Dan

This is going to be one busy, crazy, and exciting week for the Bulldog. I took a break from SQL Saturdays and the community for a couple of years as I moved between jobs. As of late I have become more active in the community that has given so much to me. I knew Pragmatic Works would provide me with opportunities as well as invigorate me. I didn’t realize how much.

This week I will be doing a pre-con with my good friend Bradley Ball. A Day of DBA Fundamentals: Install, Maintenance Plans, and Security is near and dear to me. We will be providing information that both of us wish that co-workers or friends had shared when we were starting out in the industry. This information is geared toward the individuals interested in become a SQL Server DBA or have just been placed into the role of a DBA. There is still time to join us May 6th!

Then on May 7th I will be giving one of my favorite presentations – More Than Just a Basic Database Backup and Recovery. Some people love fast cars; I really enjoy playing with database backups and recoveries. Every time I do a backup and recovery I think of or find something new. If you are attending the SQL Saturday Jacksonville stop on by my morning session. If you are unable to attend my session over the week I will upload my slides and demos to the site for you to exam.

If you can’t attend either of the sessions, we would love to chat with you at the Pragmatic Works booth…….

A little over 2 1/2 years ago I started a new career at New York Life. I fully expected to spend a lengthy employment and retire from New York Life. Up until 2 weeks ago I was having a successful career in which I was thriving and growing as an individual. I was presented multiple “Thanks To You” awards that recognize employee effort’s and was presented many opportunities to take place in key company initiatives.

During my 2 plus year stint at NYL I was able to help remediate security gaps, assist in virtualizing the DB environment, assist in identifying servers that should remain physical, install/configure/tune vendor installs and take place in many debugging sessions. While taking part in these projects/tasks I was able to make many new colleagues, co-workers and friends. These folks helped me grow technically, professionally and personally. I will miss working with the teams and challenges that were presented to me while at NYL.

So let me rewind the last 17 plus years of my career. During these 17 years I have worked for Nielsen Media Research, Publix Supermarkets and New York Life. All of these major corporations have assisted with my growth. If we look at the common dominator here I have always been comfortable working in stable large-scale environments.  Like a good pair of shoes an old t-shirt or a favorite comfort food, it is hard to step away from this type of stability.

It is 17 years later and I am always amazed at how far I have come. Around May 17th, I received a text asking me if I knew anyone who would be interested in a full- time job at a consulting firm. After many hours of discussion on the topic with Missy and my son Parker, we said let’s hear what they have to say and see if this move is for us. A good portion of this discussion took place at the Epcot Flower Festival which with its good food and beautiful flowers made the discussion less stressful.

At this point you are probably saying enough Dan get to the point. Drum roll please…   As of Wednesday 6/2/2015 I will no longer be working at New York Life. My family and I have decided to step outside of our comfort zone and accept a full-time position at Pragmatic Works. I guess one could say “out of the frying pan into the fire”.  I am excited at the prospects that this new position will provide me. I really enjoy being a DBA, but I have always had a secret love for other facets of the Microsoft Stack. I plan on branching out into Cloud Services, SSRS, SSAS and SharePoint advanced topics/functionality. Yes,  I said SharePoint!  None of this is guaranteed but I am going to build on my current skillset providing value to Pragmatic Works. I am a darn good admin but I am much more than that and need to tap into those skillsets more. At Pragmatic Works I will be challenged to do just that. I am excited to start working for a company that promotes and encourages self-growth and encourages motivated individuals to grow.  So as of 6/3/2015 I will be in Jacksonville walking thru the doors of Pragmatic Works and becoming a member of an already stellar team.

If you have seen my SQL Server tools Tips and Tricks presentation at a SQL Saturday you may remember my brief discussion on Central Management Server (CMS). A 45 to 60 minute session is not near enough time to dive into all the tools including this cool hidden gem that the Microsoft SQL Server team provided us with the SQL Server 2008 release. Even though this is a well blogged about tool a recent conversation at my new job reminded me that I needed to and wanted to blog about CMS. I run into and speak with individuals who connect with SQL Server on a regular basis and are not aware of CMS or what CMS can and can’t do for you. So let’s get this hidden gem installed and start utilizing its POWER!

The 1st thing you will need is a SQL Server 2008 or greater instance installed with MSDB available. I will be setting up and taking screen shots from my SQL Server 2012 instance for the purpose of this post. I will be setting up and configuring CMS as sysadmin on this database instance. For this post I will be using my local instance however you could also use an instance in your datacenter. There are advantages and disadvantages to both based on your working environment.

Let’s get started with the CMS setup.

Open up SQL Server Management Studio (SSMS). Dependent on how you have SSMS setup it will look something like the image below.

The next step is to open up our registered services view. This can be done by Ctrl+Alt+G (2012) or by choosing view and then clicking on registered servers.

Once completing the above you should see a screen similar to the following image.

At this point we are on the screen that allows us to register CMS. Right click on Central Management Servers choosing the Register Central Management Server….

You should see the New Server Registration screen appear. For this example I chose SQL Server Authentication , you can also use Windows Authentication when registering the server. For Server name I am using (local) to connect to my local SQL Server 2012 instance. The following example is using SQL Login cmsadmin and its secure password to authenticate into the instance. At this time I am not making any changes to the Connection Properties. In rare cases I have had the need to modify one or more of the Connection Properties. Click Test once you receive a successful connection click Save.

You should see the local instance registered underneath the Central Management Servers Folder. Note (local) can be replaced with your database server name which would appear underneath the Central Management server folder.

Underneath our local server let’s set up some server groups. Normally I start off by creating development, test, staging and production. This can vary based on your environment. I then later add additional Server groups. For example I supported a scaled out Biztalk and SSRS environment. I would create a server group for each of the scaled out environments registering the server/servers that are part of those environments. Our next step is to create our basic server groups. This can be done by right clicking over (local) which will display the image below.

Click on the new Server Group to display the next screen. I filled in Group name with Development and the Group description with information about the group. Click OK.

After creating the Server Groups you will end up with something like the following image.

The next step in the process is to register some servers to take advantage of the power of CMS. Right mouse click over the Server Group you would like to register your Database server under. When you see the following screen click on New Server Registration…

You will fill in the Server name with a server you want to access utilizing CMS. When registering a server you MUST use Windows Authentication. Click on Test and once you have a successful connection click on save.

Please read the included link for further explanation on why Windows Authentication must be used to connect. An excerpt from the site has been provided.

http://msdn.microsoft.com/en-us/library/bb934126(v=sql.110).aspx#Security

“Because the connections that are maintained by a central management server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.”

Once you have registered your servers you can end up with something like this. Each person I run into that utilizes CMS has their own take on how they like to set the groups and registered servers. I typically find myself migrating to the following setup which is included in the image below.

Here is where it really gets cool. Let’s utilize the power of CMS. You can 1st double click on any of the registered servers and it will open a connection to that server within SSMS. You can also right click on one of the groups and open a query window.  In this case let’s hover over the development group. Select new query from the selections provided and a window will open which can be used to run a provided statement against the servers registered within that Server group. Notice at the bottom of the query window we are connected to 1/1 servers. This however does not display the full power of this tool.

Execute the following query:

SELECT
SERVERPROPERTY(‘Edition’) AS [Edition],
SERVERPROPERTY(‘ProductLevel’) AS [ProductLevel],
SERVERPROPERTY(‘ProductVersion’) AS [ProductVersion]

You should see the statement return something similar to the output below:

Edition ProductLevel ProductVersion
Standard   Edition (64-bit) RTM 11.0.2100.60

 

Not to impressive yet. Let’s kick it up a notch and see what else CMS can do for us.

Hovering over (local) or your {server name} this time right mouse click and select new query. Selecting new query from the selections provided will open a new window which will allow us to run a provided SQL statement against the servers registered within the Server groups. Notice at the bottom of the query window we are connected to 4/4 servers. The connected value will vary based on the number of servers registered. *Note you can also do this at the group level.*

Execute the following query:

SELECT
SERVERPROPERTY(‘Edition’) AS [Edition],
SERVERPROPERTY(‘ProductLevel’) AS [ProductLevel],
SERVERPROPERTY(‘ProductVersion’) AS [ProductVersion]

You should see the statement return something similar to the output below which will be dependent on your registered servers:

Server Name Edition ProductLevel ProductVersion
StagingServer Standard Edition (64-bit) SP1 10.50.2500.0
DevServer Standard Edition (64-bit) RTM 11.0.2100.60
TestServer Standard Edition SP3 9.00.4060.00
ProdServer Enterprise Edition SP4 8.00.2066

 

In a matter of seconds I was able to pull which versions of SQL Server I had out there in my different environments. Imagine this, you have hundreds if not thousands of servers. You have become the “Accidental DBA” and know nothing about the environment you inherited and were left with just a list of servers.  You have no knowledge of PowerShell. This is a quick and powerful way to extract information from your SQL Server database instances just by understanding some basic SQL statements. Seeing just how powerful this is should give you some pause and think do I really want to run this statement against our live production database? Use best practices and common sense is my best advice. Possibly think about using set transaction isolation level read uncommitted when executing your queries.

Registering CMS will store information within the MSDB database. Security to CMS can be controlled utilizing a couple of database roles within the MSDB database. ServerGroupAdministratorRole provides you the ability to administer CMS.  ServerGroupReaderRole grants users the ability to connect to CMS. Two views of interest are the sysmanagement_shared_server_groups and the sysmanagement_shared_registered_servers.

The sysmanagement_shared_server_groups view will return information with respect to the groups you have created for your registered CMS instance.

You may want to execute the following statement against the view to see how this data is being stored.

SELECT server_group_id,name,description,parent_id,num_server_group_children,num_registered_server_children
FROM msdb..sysmanagement_shared_server_groups
WHERE is_system_object <> 1

server_group_id name description parent_id num_server_group_children num_registered_server_children
7 Developement Place Development Servers in this group. 1 0 1
8 Staging 1 0 1
9 Test 1 0 1
10 Production 1 0 1

 

The sysmanagement_shared_registered_servers view will return information with respect to the servers you have registered and the group they were registered within.

You may want to execute the following statement against the view to see how this data is being stored.

SELECT server_id,server_group_id,name,server_name,description,server_type
FROM sysmanagement_shared_registered_servers

server_id server_group_id name server_name description server_type
1006 7 DevServer DevServer 0
1008 8 StagingServer StagingServer 0
1009 9 TestServer TestServer 0
1007 10 ProdServer ProdServer 0

 

There are a number of procedures that are utilized to manage CMS which you can examine for yourself by executing the following query.

You can execute the following statement to view these stored procedures.

SELECT * FROM msdb.sys.sysobjects
WHERE name LIKE ‘%sysmanagement%’ AND xtype=‘P’

CMS is a power tool that the Microsoft team has provided out of the box at no cost to you. Once CMS has been setup and configured the insight into you systems and environments is priceless. One of the reasons I embrace the Microsoft SQL Server Suite is that the tools provided have been thought thru and have had time put into them. Some are good, some are ah. CMS is one of those tools that provides the DBA’s, Developers and even Management a view into our SQL world.

References utilized in the past when 1st setting up CMS:

One of the 1st links I ever hit when examining CMS

http://www.sqlskills.com/blogs/kimberly/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx

Microsoft link mention in this post

http://msdn.microsoft.com/en-us/library/bb934126(v=sql.100).aspx

This Post written while Jamming to DKM….