Tuesday, June 23, 2015

Exams 70-457 & 70-458 - Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012

This past week I finally completed the 70-458 exam to upgrade my certification to MSCA: SQL Server 2012. I never thought it would take me over a year to pass two upgrade exams, but sometimes life just gets in the way. I will say this is probably the hardest SQL Server exam I've taken so far.

The format of the upgrade exam is to combine questions from the three standard MCSA SQL Server 2012 exams, 70-461, 70-462, and 70-463. My hardest problem was dealing with the data warehouse questions; mainly the ones around Data Quality Services, Master Data Services, and especially the Project Deployment Model. I had never touched DQS or MDS before studying for this exam, and I can guarantee you that I would not have passed it without studying those topics. I found both to be very interesting features, but I have yet to come across a client that is using them.

The Project Deployment Model is a new concept for SSIS, and I really love it. I have worked with SSIS ever since it was introduced in SQL Server 2005, and this is a major improvement over the previous versions. Gone are the days when you had to design your own configurations for different environments or database connection strings. Everything is built into the SSIS catalog. The new features are awesome, but again, I had to really, really study the new deployment model and how to create packages that use environment variables.

All in all, I think it was a good test of SQL skills. Now let's see if I can pass the 70-459 before the end of the year.

Tuesday, April 28, 2015

Use PsPing When Working in Azure IaaS

Recently I’ve been learning more about how Azure functions and how it can help my customers. One of the best ways for me to learn about Azure was to build out my own environment using VMs, or Infrastructure as a Service (IaaS). All of that was easy; however, once the VMs were built I soon learned that Azure functions differently than an On-Premise solution.

The most basic network connectivity test that administrator use is the PING command. It is part of the ICMP protocol, but it’s disabled by default on each VM that I deployed. While it was easy enough just to configure the Windows Firewall to allow that traffic through, I decided to search for an alternative.

I found PsPing from SysInternals. It performs the same functionality as PING, but it uses the TCP protocol instead. When working in Azure, it can give you the same functionality without having to reconfigure your VMs.

For example, when I setup a SQL Server instance on one VM, I had difficulty establishing a connection to it from another VM. The standard PING proved to be useless, but PsPing proved its worth. By specifying the server name and a port number, I was able to successfully connect directly to the port number that SQL Server was listening on.

Immediately, I could see the benefit of using this new tool for more than just testing SQL Server connectivity. I can use it to specify any port number for any service. For example, I could ping port 80 if I’m testing a website, port 21 if it were an FTP server, or port 23 for telnet.

As I dig deeper into Azure, I’m sure I will discover many other new utilities that I can add to my own toolbox.

You can download PsPing from here.

Tuesday, March 10, 2015

SQL Nexus Might Just Save Your Bacon

The year is 2015, and I’m still surprised by how many people have never heard of SQL Nexus; although it has been available since approximately 2008. SQL Nexus is a tool that that is designed to collect and analyze data to help you troubleshoot SQL Server performance issues. There are plenty of tools on the market that can easily do this, but this one is free and it can be used to monitor SQL Server 2005 through SQL Server 2014. The tool was originally created by the SQL Server product support team, and is still used today as the primary tool for collecting and analyzing performance issues.

SQL Nexus does not require you to install anything on the server that you intend to monitor, but you will need to copy some data collector files to it. The collector files just run SQLDIAG and some other TSQL scripts to collect data from your monitored server. Below is a list of some of the data that is collected.
  1. Windows Performance Monitor counters
  2. Windows event logs (system, application, and security logs)
  3. Windows configuration settings
  4. SQL Server trace events
  5. SQL Server wait stats
  6. SQL Server blocking info
  7. SQL Server configuration settings
  8. SQL Server dump files
Once the data is collected from the monitored server, you copy all the data files back to another machine for analysis within SQL Nexus.

Installation & Setup
There are a few requirements to setting up SQL Nexus. First, make sure you have the .NET Framework 4.0 installed. Next, SQL Nexus requires an instance of SQL Server to store and analyze the imported data. Ideally, you would put it on a TEST server, but you can also use SQL Server Express Edition running on your workstation. If your workstation that will do the analysis is running SQL Server 2008 or SQL Server 2008 R2, then download and install the Microsoft Report Viewer 2010 control. If your workstation is running SQL Server 2012 or higher, then you can skip this step. Regardless of the version, download and install the Report Viewer hotfix. The hotfix addresses some bugs that will prevent some of the SQL Nexus reports from working properly.

Next, download and install the RML Utilities.

Now that you have all the prerequisites installed, download the latest version of SQL Nexus from the CodePlex website. Just uncompress the Release4.0.0.64.ZIP file and place on your desktop. Within the Release4.0.0.64 folder, you should see a subfolder called DataCollectors, and another compressed file called SqlNexus4.0.0.64.ZIP. Uncompress that zip file and place it on your workstation C drive. The SqlNexus4.0.0.64 folder will contain the SQL Nexus executable and all the other files needed to analyze the collected data. From there, you can create a shortcut on your desktop or your start menu for sqlnexus.exe.

The DataCollectors subfolder contains the data collector files. There is one ZIP file for each version of SQL Server. In our example, we want to monitor a SQL Server 2012 instance, so we need to uncompress the PerfStatsScript2012.zip file. Once uncompressed, you will see 3 CMD files and their accompanying XML configuration files.
  1. StartSQLDiagTrace2012.cmd & SQLDiagPerfStats_Trace2012.XML
  2. StartSQLDiagDetailed_Trace2012.cmd & SQLDiagPerfStats_Detailed_Trace2012.XML
  3. StartSQLDiagForReplay2012.cmd & SQLDiagReplay2012.XML
StartSQLDiagTrace2012.cmd will likely be the batch file you will run most often. By default, it will collect the most common Performance Monitor counters and run a SQL trace to capture batch level commands. The StartSQLDiagDetailed_Trace2012.cmd batch file will capture the same counters, but it will also capture statement level commands and the Showlan Statistics Profile. The StartSQLDiagForReplay2012.cmd batch file captures the trace events needed to replay it against another server.

Collecting Data
To monitor our server, copy the PerfStatsScript2012 subfolder to the SQL Server. This folder cannot be on a network drive; it must be local to that server. It’s best if you do not place it on the C drive or on a drive that hosts your transaction log (LDF) files. The data collector can quickly capture a lot of data, so you want to avoid filling up the C drive or impacting the write performance of your transaction log files. Open a command prompt to that folder location and run StartSQLDiagTrace2012.cmd. SQLDiag will start up the collectors based on what’s in the XML configuration file. Once you see the green line that says “SQLDIAG Collection started”, then you can try to reproduce your performance problem on the server.

Once you are done, just press Ctrl+C to shut down the data collector. Sometimes it will take several minutes for the collector to fully shutdown, so be patient and do not press Ctrl+C more than once.

You can leave the data collector running for a period of time, but please keep in mind that it can collect a lot of data and the SQL trace can potentially impact your performance. It’s usually best to start it, reproduce your performance issue, and then shut down the data collector.

After you stop the data collector, you will see a sub folder called SQLDiagOutput. Copy that entire folder back to your workstation to begin the analysis of the data.

Analysis of the Data
Run sqlnexus.exe using the shortcut you placed on your desktop. The first step is to specify which SQL Server will be used to perform the analysis. Enter the name of your SQL Server and click Connect.

The tool will automatically create a sqlnexus database on that SQL Server.

Now we need to import the data we collected. In the bottom left hand pane, click Import.

Choose the location of the SQLDiagOutput folder and then click the green Import button. If the collector captured data for more than one instance on the monitored server, then you will get a popup window asking which instance you want to analyze. Choose your instance and click OK.

Depending on how much data was collected, the import process could take a while; maybe even several hours. Be patient and just give it time to complete. While it’s running the Import button will change to a red Stop button. Once it’s complete, it will turn back to a green import button.

Now just close the Data Import window. In the SQL Nexus window, click SQL Perf Main from the upper left hand pane. This is main page where we will drill into the various reports.

Each report name that appears in blue is a hyperlink to that report. Report names that are in black did not have any data to analyze. Most reports have a summary of what they display, but some do not, like the Bottleneck Analysis report. It shows CPU utilization by SQL Server (yellow) and everything else (purple). If CPU utilization is higher for purple, then you could assume the CPU is being used by a process other than SQL Server.

If SQL Server is the higher consumer of CPU (yellow), then look further down on that page to see the wait stats analysis. This is the waits that accumulated while the data collector was running. This will help guide you toward what SQL Server was waiting on while executing the queries. You can click on each wait type to see more details about each wait. If you need to the definition of a wait stat, then look at Books Online.

To go back to the previous page in a report, just click the blue left arrow in the toolbar.

The Perfmon Summary report shows an aggregation of the counters collected from Performance Monitor. They are broken out by CPU, IO, Memory, and Network.

You can click on each blue link to get the graphical view. For example, in the IO report details you can see the Avg Disk Sec/Read and the Avg Disk Sec/Write which will show you how fast your IO subsystem is responding to requests.

The ReadTrace Report is really my favorite group of reports. The main report gives you a graphical view of the overall utilization of resources.

From here, you can drill into the data from various points, such as application name, database id, or login name. The Unique Batches report will show you the top TSQL batches by CPU, reads, writes, and duration. Each graph is inverted, so the one on the bottom is the largest consumer of that resource.

On the Y axis of each graph you will see a number. That number corresponds to the TSQL batch command that is listed further down on the page. For example, Query #1 was the largest consumer of reads. From that table, I can see that was a stored procedure named “SLEV”.

You can click on each batch to get even more detail about it in a graphical view over time.

Below the chart, you can click the + button next to “View Chart Details” to get the detailed info about that TSQL batch. This will display the number executions, CPU, reads, and writes, broken out by interval.

There are many more reports, so be sure to click through each one to view the information they provide.

Customize the Data Collector
By default, the data collectors will capture only a subset of perfmon counters and SQL trace events, and it will do that for ALL instances on the server being monitored. If you need to add or remove certain counters or monitor only a specific SQL Server instance, then you will need to edit the SQLDiagPerfStats_Trace2012.XML configuration file. You can open the file in Notepad, but opening it in SQL Server Management Studio color codes it and make everything easier to read.

The top section of the XML file is for the perfmon stats, which takes up about 75% of the file. The bottom section is for the SQL Server trace events. All of the possible options are listed for every counter; however, the majority are set to disable. Therefore, to add additional counters, just search for the counter name and set the “enabled” value to “true”. For example, if you wanted to add the TSQL:StmtStarting and TSQL:StmtCompleted, then just scroll down to about line 2775 and change the enabled value to “true” and save the XML file. Now when you run the collector it will also collect those two trace events.

If you need to set the name of the server then about line 7 you will see Machine name=".". Change the "." to the actual server name. If the server being monitored is a Failover Cluster Instance, then you must enter the SQL Server Virtual Server Name.

If you need to set the SQL Server instance name, then about line 2564 you will see Instance name="*". Change the "*" to the instance name. Only type the instance name here, not the ServerName\InstanceName. If your instance to monitor is the default instance, then enter "MSSQLSERVER".

Also on the same line, the default authentication to connect to that instance of SQL Server is Windows Authentacation. If you need to use SQL Server Authentication, then set windowsauth to “false” and user to the sql server login name. When you run the StartSQLDiagTrace2012.cmd batch file, it will prompt you to enter the password for that account.

Other Hints and Tricks
Before collecting the data, you need to make sure the Windows account is a member of the local administrators group a member of the sysadmin server role for the server you want to monitor. If you use a SQL Server login to collect the SQL trace events, then it also has to be a member of the sysadmin server role.

Once the data collector is started on the monitored server, don’t log off. That will shut down the collectors and you will have to restart them again.

If you cannot stay logged into the server, then you’ll need to schedule the collection by modifying the StartSQLDiagTrace2012.cmd batch file using the /B and /E parameters for SQLDIAG or register it as a Windows service. Books Online has more information about those options.

When you import data into SQL Nexus, it populates the tables in the sqlnexus database. If you already have data in the sqlnexus database, you’ll get the following error when attempting to import new data.

At this point, you will need to do one of two things: overwrite the existing data or create another sqlnexus database. To overwrite the existing database, click the Import link and then click Options. In the dropdown menu, just select “Drop Current DB Before Importing”. This will drop and recreate the sqlnexus database.

Keep in mind, sqlnexus is just another database on a SQL Server. If you want to keep that data for later analysis, you CAN backup that database before overwriting, or you can create a new database. Before clicking the Import link, click the database dropdown list on the tool bar. From there, you can select “<New Database>”. This just creates another database on your local SQL Server and will be the target of the imported data.

This is the way to go if you need to analyze data from multiple servers, because you can use the dropdown database list to change to a different sqlnexus database and view the reports.

Report Parameters is another feature that people tend to overlook. When viewing a report, such as the IO Metrics, click Parameters button on the toolbar. In the popup window, you can change the start and end timeframe that you want to view. This is extremely helpful if your data spans hours or even days.

Finally, you can export the reports to an Excel file by clicking the Save button on the toolbar.

For example, through your analysis you find the offending query; you can export the batch details report to an Excel file and share that report with the development team or other DBA team members.

As you can see, SQL Nexus can be a very powerful tool to help troubleshoot performance issues within SQL Server. This is why the tool is widely used internally be various SQL Server support teams. The best way for you to get more info is to practice collecting and analyzing data from your servers. However, be sure that you practice on a TEST server and not in your production environment. Soon you’ll be able to quickly diagnose and resolve performance issues in your environment. And one day, it might just save your bacon!

Tuesday, February 24, 2015

SQL Cruise Caribbean 2015 Recap

UPDATED: Don't just take my word. Read the feedback from others.

I wish I could just re-post my article from last year about SQL Cruise and change the dates and pictures.The event is just that good.

I still believe this is one of the more premier training events offered for SQL Server. The amount of 1-on-1 time you get with the technical leads is absurd. You can get classroom time at any training conference, but what happens when the session is over? Most instructors head off to their next engagement and you never get a chance to speak with them. On SQL Cruise, that interaction continues well beyond the classroom session, beyond the next session, beyond the day, and even well beyond the week.

Last year I met Andrew Kelly (b|t) and Argenis Fernandez (b|t) aboard SQL Cruise. Andrew was one of the technical leads and Argenis was an attendee. To this day I correspond frequently with both of them about SQL Server as well as other stuff. Andrew has become a great mentor to me and has helped me develop my presentation skills. Argenis was instrumental in helping me decide if joining Microsoft was the right fit for my career goals. I will say that both of those individuals have had a lasting impression on my life. How many times has that happened to you at the PASS Summit? I’m guessing not too many.

This year I got to spend time with a few of the same technical leads from 2014, Grant Fritchey (b|t) and Kevin Kline (b|t), but I also got to meet and learn a lot from the new ones, Jes Borland (b|t), David Klee (b|t), and Jeff Lehmann (w). This year’s topics covered things such as query performance tuning, infrastructure optimization, virtualization, data recovery, PowerShell, and even cloud solutions. Every one of the sessions provided me with valuable information that I can use immediately upon return to work.

But I also got a chance to meet and hang out with a lot of the attendees. As it turns out they are just like me. They are passionate about SQL Server, they want to learn as much as possible, and they want to succeed in their career. That’s the cool thing about SQL Cruise; the attendees are dedicated to SQL Server and want to learn. This is not a vacation for them; it’s an educational experience above all else. The other great thing is all the attendees have a wide range of background, and of course issues to resolve. That’s a great combination for learning new tricks from each other.


Now of course the cruise and exotic islands are quite a lot of fun, but they are not a distraction from class. They actually help everyone relax and become more comfortable around each other, which leads to more open discussions in and out of the classroom. There really is no stress when this is the backdrop of a conversation.

Once again, a great conference always has great sponsors. This year the sponsors were Amazon Web Services, Red-Gate, SQL Sentry, and B-Side Consulting. All of these companies use and make products that enhance the SQL Server product. Being a sponsor of the conference shows their commitment to the product and the community. Of course, a great conference always has great organizers, so I have to thank Tim Ford (b|t) and his wife Amy for putting together another great conference. Their hard work and dedication has been a huge help to me and I know to a lot of others.

In the group picture below, almost half of the people are referred to as the "Plus Ones". They are the family members and/or friends of the attendees and technical leads. SQL Cruise encourages the attendees to bring their family members, so they can enjoy the adventures on the ship as well as on the islands. The Plus Ones are invited to every group event, group dinner, and even are allowed to sit in on the classroom sessions. My wife absolutely loves being a Plus One. She has a bubbly personality and can quickly make friends, which turns her week of vacation into quite the adventure. So much so, that she has already made plans for SQL Cruise 2016 without even asking me if I plan to sign up.

This was my second SQL Cruise, and it sure won’t be the last. Now it’s your chance to become part of this elite group with the SQL Family. SQL Cruise 2016 will be setting sail in January 2016, so head on over to the site to learn more or sign up. If you need help convincing the boss, just let me know and I’ll put in a good word for you. And if you need help convincing your own Plus One, let me know, because my wife would be happy to explain all the advantages of attending.

Tuesday, February 3, 2015

Get Out There and Challenge Yourself

It’s been almost five months since I posted my last article, and so much has changed since then. I have a new job, a new house, a new address, and of course a new mortgage.

I had been working as a DBA for Wells Fargo Securities for nearly 15 years when I decided that I could no longer grow in that role. I had a lot of good years at the bank, and I will definitely miss all of my friends. But in order for me to be happy, I really needed a change. My decision to move was more about needing to break loose from the daily routine of checking error logs, backup jobs, and dashboards. I really needed a job that challenged me, so that I could gain even more knowledge about SQL Server. There was only one place I felt I could get that type of challenge in a job. So I decided to apply at Microsoft. I figured since I loved SQL Server so much, there was no better place to learn more about it than from the company that developed it.

After talking to a number of friends that already worked at Microsoft, I decided to focus on the Premier Field Engineer positions for SQL Server. A Premier Field Engineer, or PFE for short, is focused on directly supporting a customer whether it’s over the phone or being onsite with them. The type of tasks performed by a PFE can vary greatly. It could be simple performance troubleshooting, delivering training, or something more in-depth such debugging minidumps. Just the thought of not knowing what problems lay ahead, just sounded like so much fun.

The entire process took about six months, from the first day I submitted my resume to the day I was offered a job. This may sound strange, but I really enjoyed the interview questions, because I felt like it help validate my knowledge of SQL Server.

I have already been working onsite with customers, and so far, I have no plans to go back to a normal DBA job. I’m glad that I had the courage to leave such a good job and to challenge myself in my career goals. Most of all, I’m glad that I have a wife who is very supportive of such a gamble.

For those of you out there that might feel stuck in a boring DBA job, find a way to take a gamble. You don’t have to leave a great company, but you might be able to find a way to make your job more interesting. Start simple, such as learn more about SQL Server and then teach your coworkers. That’s how it all started for me. I started reading more and more about SQL Server, then I tried to pass that knowledge onto the junior DBAs on my team. From there, I got into public speaking at user groups and SQL Saturdays. I started small and with every win, I kept challenging myself to do more. So get out there and start challenging yourself, just as I did.