Category Archives: SQL Server

Triad Developers Conference – My Debut

I did it!  I did my first “real world” presentation this morning at the Triad Developers Conference in Winston-Salem.  What I mean by “real world” is not a PASS audience.  These were total strangers off the street that I didn’t know, well, there were some familiar faces and even a friend or two, but for the most part total and complete strangers that had varying backgrounds, not all technical in nature.

The feedback I received was very positive and even helpful, so I can make this presentation even better when I present it in Richmond, VA next weekend at SQL Saturday #610.

Huge thank you goes out to the organizers, volunteers and sponsors who made this event happen.  And a special thank you goes out to Doug Purnell (Blog | Twitter) for recommending me in the first place.

Pesky Percent File Growth

As DBAs we all know setting your file growth to grow by percent is not optimal.  It can cause all kinds of issues, which rear their ugly heads as performance problems (see these articles by Brent Ozar, & Tim Ford).  So, when I have to support a third party application that automatically adds data files using percent instead of fixed size, it really irritates me.  I got tired of seeing these new files show up on my daily exceptions report, so I decided to do something about it.  This post explains what I did.

I have a home grown process that goes out and collects all kinds of information about my servers on a daily basis.  Once that process is complete it sends reports, via email, so I can get a quick look at things when I first arrive at work in the morning.  One of those reports is my file exception report.  It reports things like excessive data/log file growth, data/log files that are almost full, data/log files that use the percent file growth, etc.  The first time I had a file show up on my exceptions report with a percent file growth, I decided I needed to be notified before the report landed in my inbox, so I created a server level trigger that is triggered by the ALTER DATABASE command.  This trigger captures all the relevant information and sends me an email.  Here’s the code I used for my trigger:

CREATE TRIGGER [ddl_trig_alterdatabase]
ON ALL SERVER
FOR ALTER_DATABASE
AS
   DECLARE @Subject nvarchar(255)
      , @Body nvarchar(MAX)
   SELECT @Subject = N’A database was altered on ‘ + @@Servername
      , @Body = EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
   exec msdb.dbo.sp_send_dbmail
      @recipients = ‘myemail@emaildomain.com’, — varchar(max)
      @subject = @Subject, — nvarchar(255)
      @body = @Body
GO
ENABLE TRIGGER [ddl_trig_alterdatabase] ON ALL SERVER
GO

 

This worked great, I found out before my report showed up and I could address the issue when it happened.  Unfortunately I discovered that one of the applications was making this change in the middle of the night.  I certainly didn’t want to have to wake up in the middle of the night to address this issue, since it really isn’t a “production down” type of problem (and let’s face it, no DBA wants to be woken in the middle of the night for anything, let alone something that is not production down).

I decided I needed to do something else other than just send an email notification, I needed to take corrective action when it occurred.  So I wrote a little stored procedure that will take the ALTER DATABASE statement as a parameter, parse it and take the appropriate corrective action. 

Simple enough, right?  Now I just need to add the call to my newly created stored procedure in my server level trigger and we are good to go.  But wait, you can’t ALTER a database within an ALTER DATABASE statement (don’t believe me? Use this as a learning exercise to see what happens when you try).  So what could I do?  There are several things you could do, but I chose to create a table that could hold this newly created ALTER DATABASE statement and insert the record there.  Then I created a SQL Agent job that runs once every hour and reads that table and executes any entries it finds, then deletes them after successfully executing.

Here’s the code for my stored procedure:

CREATE PROCEDURE [dbo].[ChangePercentGrowthMaxSizeUnlimited]
@SQLText nvarchar(max)
AS
SET NOCOUNT ON

/* We start with something like this
ALTER DATABASE [DatabaseName]
ADD FILE (NAME = N’DataLogFileName’
         ,FILENAME = N’X:\DataLogFileName.ndf’
         , SIZE = 20
         , FILEGROWTH = 5%
         , MAXSIZE = UNLIMITED)
*/

/*  We want to produce something like this
ALTER DATABASE [DatabaseName]
MODIFY FILE ( NAME = N’DataLogFileName’
            , MAXSIZE = 102400KB
            , FILEGROWTH = 10240KB )
*/

— Local Vars
DECLARE @AddFileText VARCHAR(8) = ‘ADD FILE’
   , @ContainsAddFileText BIT = 0
   , @AddFileStartPosition BIGINT
   , @FileGrowthPercentText VARCHAR(13) = ‘FILEGROWTH = %!%’
   , @ContainsFileGrowthPercent BIT = 0
   , @ContainsMaxSizeUnlmitedText BIT = 0
   , @StartPosition INT
   , @EndPosition INT
   , @Length INT
   , @DatabaseName VARCHAR(128)
   , @FileName VARCHAR(128)
   , @AlterDatabaseLength INT = LEN(‘ALTER DATABASE ‘)
   , @AlterDatabaseSQL NVARCHAR(MAX)

   — Is it an ADD File operation?
   SELECT @AddFileStartPosition = PATINDEX(‘%’ + @AddFileText + ‘%’, @SQLText)
  
   IF @AddFileStartPosition > 0
   BEGIN
      –It’s an ADD File operation
      SET @ContainsAddFileText = 1

      IF @SQLText LIKE ‘%’ + @FileGrowthPercentText + ‘%’ ESCAPE ‘!’
      BEGIN
         — it’s adding a file using percent file growth
         SET @ContainsFileGrowthPercent = 1
     
         — Is it setting MAXSIZE to UNLIMITED?
         IF PATINDEX(‘%’ + @MaxSizeUnlimitedText + ‘%’, @SQLText) > 0
         BEGIN
            SET @ContainsMaxSizeUnlmitedText = 1
         END
        
         — Now we need to parse the ADD FILE expression and build a MODIFY FILE operation from the parts
         — Get database name
         SELECT @StartPosition = @AlterDatabaseLength + 1
         SELECT @Length = CHARINDEX(‘ADD FILE’, @SQLText, @AlterDatabaseLength) – @AlterDatabaseLength
         SELECT @DatabaseName = LTRIM(RTRIM(SUBSTRING(@SQLText, @AlterDatabaseLength + 2, @AddFileStartPosition – 1 – @AlterDatabaseLength – 2)))

         — Get filename
         — Start by finding the start of the logical file name
         SELECT @StartPosition = CHARINDEX(””, @SQLText, PATINDEX(‘%’ + ‘[^FILE]NAME %”’ + ‘%’, @SQLText)) + 1
         SELECT @Length = CHARINDEX(””, @SQLText, @StartPosition) – @StartPosition
         SELECT @FileName = SUBSTRING(@SQLText, @StartPosition, @Length)

         — Now Create the alter database operation
         SELECT @AlterDatabaseSQL = N’ALTER DATABASE ‘ + @DatabaseName + N’ MODIFY FILE ( NAME = N”’ + @FileName + N”’, ‘
         IF @ContainsFileGrowthPercent = 1
            SELECT @AlterDatabaseSQL = @AlterDatabaseSQL + N’FILEGROWTH = 10240KB’

         IF @ContainsMaxSizeUnlmitedText = 1 AND @ContainsFileGrowthPercent = 1
            SELECT @AlterDatabaseSQL = @AlterDatabaseSQL  + ‘, MAXSIZE = 102400KB’
         ELSE
            IF @ContainsMaxSizeUnlmitedText = 1 AND @ContainsFileGrowthPercent = 0
               SELECT @AlterDatabaseSQL = @AlterDatabaseSQL + N’MAXSIZE = 102400KB’
           
         SELECT @AlterDatabaseSQL = @AlterDatabaseSQL + N’ )’

         INSERT dbo.DBAAlterDatabase
         (SQLText)
         VALUES
         (@AlterDatabaseSQL)
      END
   END
   ELSE
   BEGIN
      — It’s not an ADD FILE operation
      PRINT ‘It”s not an ADD FILE operation, no work to do.’
   END

RETURN 0

 

Here’s the code for my modified server level trigger:

CREATE TRIGGER [ddl_trig_alterdatabase]
ON ALL SERVER
FOR ALTER_DATABASE
AS
   DECLARE @Subject nvarchar(255)
      , @Body nvarchar(MAX)
   SELECT @Subject = N’A database was altered on ‘ + @@Servername
      , @Body = EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
   exec msdb.dbo.sp_send_dbmail
      @recipients = ‘myemail@emaildomain.com’, — varchar(max)
      @subject = @Subject, — nvarchar(255)
      @body = @Body 
   EXEC dbo.ChangePercentGrowthMaxSizeUnlimited @SQLText = @Body
GO
ENABLE TRIGGER [ddl_trig_alterdatabase] ON ALL SERVER
GO

Works like a charm!  But wait, you might notice that I’m making more than a few assumptions in my stored procedure, and you would be correct.  I feel like I need to add a disclaimer to this post, the same way they add disclaimers to pharmaceutical commercials.

Here are my assumptions: 

  1. Only one file is being created at a time. 
  2. We always want to change our file growth to 10MB. 
  3. We don’t want a max file size of unlimited and we always want to set our max file size to 100MB. 
  4. There will be no errors.
  5. I am in NO way responsible if this code breaks something on your server.

Explanation of assumptions:

  1. What fun would it be if I did all the hard work for you?  This can easily be adapted to work with multiple files being created at the same time.  You can do it, I have faith in you.
  2. For my particular instance, I know the model database settings for this server and I hard coded them, because that’s what I wanted.  You could easily adapt the code to use your model database settings or any other value for that matter (HINT: think sys.sysfiles).
  3. See explanation of assumption 2 above.
  4. I removed all my standard stored procedure framework code (which includes error checking) for brevity.  You should ALWAYS have error checking in your stored procedures!
  5. You should NEVER assume code is not malicious in nature and add it to production without a thorough understanding of what it’s doing.  Shame on you if you did.

 

SQL Saturday Charlotte is Coming!

That’s right, SQL Saturday Charlotte is coming September 17, 2016.  Next to the annual PASS Summit, this is my favorite SQL Event!  This is the fifth year that the Charlotte BI Group (CBIG) has put on this great event.  They’ve expanded the event this year to include two Pre-Cons on Friday, September 16, 2016, one from BI and data analytics queen Jen Underwood and the other from performance guru Adam Machanic.  While the Pre-Cons aren’t free like the event on Saturday, they certainly are a bargain for an entire day of training with well known experts in their respective fields.

If you haven’t already registered, I’d suggest you do it now before they go to a wait list.  This event is always jam packed full of great sessions from great speakers, both local and national.

Oh yeah, I will be there.  I’m helping out again this year with registration, so be sure to say hello if you see me.  I am always happy to see my #SQLFamily.

SQL Server 2016, Database Mail and .Net 3.5 Framework

There were so many cheers when Microsoft announced that the .Net 3.5 Framework was no longer a pre-requisite for installing SQL Server starting with SQL Server 2016.  Folks were so excited, until they started testing certain pieces of the product, specifically Database Mail.  That’s right, if you want to use Database Mail in SQL Server 2016 you have to install the .Net 3.5 Framework.

If you are installing SQL Server 2016 on Windows Server 2012 R2, this might be a problem for two reasons.  First, the .Net 3.5 Framework is no longer included by default when spinning up a new install.  Second, you will get no errors when testing Database Mail in SQL Server 2016.  Mail will be queued, but will not be sent and there will be no errors in the Database Mail log, the SQL Agent log or even the SQL Server error log.

So if you’ve gone through all the usual steps to troubleshoot Database Mail (or these steps) in SQL Server 2016 to no avail, be sure to double check that the .Net 3.5 Framework has been installed.  If not, you will need to install it, then apply ALL the patches for it.

Did you know?

Did you know that the call for speakers for PASS Summit 2016 opened on February 3, 2016?

Did you know that the call for speakers for PASS Summit 2016 ends on March 2, 2016?

Did you know that there’s a Speaker Resource Page?

Did you know that you can have your abstract reviewed confidentially BEFORE you submit it for the Summit?

Did you know that your abstract is being reviewed by members of the SQL community?

Did you know that this is the second year the Program team has offered this service?

Did you know that only 32 people took advantage of this service last year?

Did you know that even experienced speakers use this service?

Did you know that you only have until February 26, 2016, to submit an abstract for confidential coaching?

Did you know that you have nothing to lose?

So what are you waiting for?  You’ve been thinking about submitting for a while now but were unsure if your abstract was good enough.  Well, now you have no excuse, use the confidential coaching service and find out.

What is Power BI?

One of the “benefits” of being a chapter leader is that sometimes it means doing a presentation yourself when you can’t get a speaker.  I fell into this exact scenario for February’s meeting of Triad SQL.  I was trying to figure out what to present when the planets aligned. After reading the #EntryLevel post in this month’s PASS Connector News and my boss asking me about Power BI.  He wanted to know more about it and if it was something we could use.

I decided to put a presentation together to answer those questions.  This post is basically the flattening out of my PowerPoint presentation.

The What/Who/Why/Flavors of Power BI

What is Power BI?

When I Googled (yes, I used that as a verb!) “What is Power BI”, this is what I got, “Power BI is an amazing business analytics service that enables anyone to visualize and analyze data.”  This sounds cool, but isn’t all that helpful.  After further research, I found this definition, courtesy of powerbi.microsoft.com

Power BI is a cloud-based business analytics service that enables anyone to visualize and analyze data with greater speed, efficiency, and understanding. It connects users to a broad range of data through easy-to-use dashboards, interactive reports, and compelling visualizations that bring data to life.

Why use Power BI?

There are lots of reasons to use Power BI, other than, it’s so cool.  For instance, Power BI makes it easy to see, in one glance, all the information needed to make decisions.  It also allows you to monitor the most important information about your business.  Power BI makes collaboration easy and when I say easy I mean EZ!  You can also create customized Dashboards tailored to those C-Suite folks or make a completely different dashboard based on the same data for those that actually do the work.

Who can use Power BI?

Anyone who has a work or school email address can use Power BI.  Sorry, no personal email addresses.  Also no government (.gov) or military addresses (.mil).

Flavors of Power BI

There are two flavors of Power BI, Free and Pro.  You can do everything with Pro that you can do with Free plus a few other things.  Here’s a little comparison of the two, there are more differences, but these are the big ones.

Free

Pro

Data refresh frequency: Daily

Data capacity Limit: 1GB/user

Streaming rate: 10K rows/hour

Data sources are limited to content packs for services and importing files

Data refresh frequency: Hourly

Data Capacity Limit: 10GB/user

Streaming rate: 10M rows/hour

Data Sources include free ones plus direct query dataset and on-premises data

Collaboration with content packs

As of January 21, 2016, the Pro flavor goes for $9.99 USD per month per user.

Also, there is the previous version/flavor of Power BI referred to as Power BI for Office 365, which will be deprecated on March 31, 2016, so I am not including this version/flavor in this post.

The How of Power BI

The building blocks of Power BI are Dashboards, Reports & Datasets.

Dashboards

Dashboards are made of Tiles that contain a single visualization created from the data of one or more underlying Datasets.  When I first read this all I heard was “blah blah blah Datasets”.  What this means is simply this, it’s a collection of reports that are all displayed together for a specific reason.  It could be that you want all your sales guys to see different views of how they are doing compared to budget/forecast or it could be that you want to give your C-Suite people a quick overview of how the company is doing as a whole.  You can tailor these dashboards to whatever suits your purpose.  Now the only reference to the limit on the number of dashboards I could find was on the Office 365 site and it was listed as 100 per user or group.  I’m thinking of the old adage “just because you can doesn’t mean you should” would apply here though.

Reports

A report is one or more pages of visualizations.  Reports can be created from scratch within Power BI or Power BI Desktop.  They are very easy to create, you simply click on the type of visualization you want to display then select the data to be used.  One caveat that I will mention here is be sure your data is formatted so that is can be more easily consumed by Power BI.  See this link for tips and tricks on how to build a “proper dataset” for Power BI.  Just as with Dashboards, you have a limit as to the maximum number of reports, which is the same as Dashboards, 100 per user or group.

Datasets

A Dataset is something that you import or connect to.  It contains the actual data you want to translate into visualizations.  Right now you are limited as to the types of files you can import in to Power BI to Excel, Comma Separated Values (.csv)  and Power BI Desktop files (.pbix).  As far as connecting to data sources you can choose from many of the content packs that are available via the Power BI site like Google Analytics, Bing, Mail Chimp, Sales Force and GitHub, just to name a few or you can connect to a database.  As with anything that sounds too good to be true, you are limited to the databases you can connect to.  Right the now current list is limited as well, to Azure SQL Database, Azure SQL Data Warehouse and SQL Server Analysis Services (tabular model only).  There is a 250MB limit to the size of the dataset that you can import in to Power BI and a limit of 100 Datasets.

References

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-get-started/

https://powerbi.microsoft.com/en-us/documentation/powerbi-videos/

https://powerbi.microsoft.com/en-us/documentation/powerbi-webinars/

https://technet.microsoft.com/library/mt282164.aspx

That’s it.  I hope this post provided a little bit of insight into Power BI and whether it’s something that can be useful to you and/or your company.  Check out the following links if you want a deeper dive into Power BI.

Power BI Blog

Melissa Coates Blog

Reza Rad Blog

Chris Webb Blog

What Do You Want?

It’s that time of year, planning for PASS Summit 2016.  We’ve already put out the Call for Volunteers, which closes today, January 22, 2016, so get those applications in and be part of the team that helps determine content for the Summit.  Don’t want to volunteer but still want to help determine content for the Summit?  Then take the survey to tell us what you want to see.  It’s a quick survey, less than five minutes and you only have until Wednesday, January 27, 2016 to tell us what you want.  What are you waiting for, get to it!  You may even win a USB of the session recordings.

What would you like to see at Summit 2016?

We Want You!

It seems like PASS Summit 2015 was just yesterday and here we are again, getting ready for Summit 2016 already.  This will be my seventh year of being a member of the Program Committee and my second year as a Program Manager.  If you have ever thought about volunteering for PASS this is a wonderful opportunity.  We need lots of volunteers to assist with everything from reading abstracts to special projects so that we can make Summit 2016 a great experience for the entire community.  Summit 2016 is still over nine months away but the work starts now.

The call for volunteers just went out this afternoon and we want you.  Use the link below to fill out the volunteer application.

PASS Summit 2016 Call for Volunteers

Aggregation Design is Back!

If you use SQL Server Data Tools (SSDT) and SQL Server Data Tools – BI (SSDT-BI) for your SQL Server 2012 development, then you have no doubt been frustrated, like me, by the fact that if you have both of these installed you no longer have the ability to create new Partitions and AggregationDesigns when working with the SSAS MOLAP model.   You can find others that have run into this issue here.

The solution I found was to install both SSDT & SSDT-BI on my laptop then have a VM with just SSDT-BI on it. That way when I needed to work on Partitions or Aggregation Designs (which is very infrequently), I just fire up the VM and I’m off and running.

Well, with SQL Server 2016 development we get to use Visual Studio 2015 and SSDT is now included in that install (although you do not get the BI project types, more on that here), no more do you have to have separate machines. I tested CTP 3 and Partitions and Aggregation Designs work once again. Hooray!

Aggregation Design

One Tool to Rule Them All – Almost

There we so many cool announcements at the PASS Summit this year, but one of my favorites was the “One Tool to Rule Them All”. The SQL Server Data Tools (SSDT) teams and the Visual Studio (VS) team have finally teamed up together to give us one tool to do all our development work for Databases, SSIS, SSAS & SSRS. No more will we have to install Visual Studio Shell, SSDT, SSDT-BI and for those source control minded folks (which should be everyone!) that use Team Foundation Server (TFS), Team Explorer. For SQL Server 2016 development we can do one install of Visual Studio 2015 and call it a day, well, almost.

SSDT Install

I was so excited when I got back from Summit, I downloaded SSDT (CTP3) from here. I was so happy to see the install screen.

SSDT Install Screen

There they were, in all their glory, all the SQL Server project types that I needed. No more having to download multiple install files. Oh happy day!

After the install completed, I was a bit dismayed to discover that it took 3GB of disk space to do this install but I guess that’s par for the course any more.

Visual Studio Install

Next I wanted to see if you got all these same project types with an install of Visual Studio. They announced at Summit that “SSDT” would now be “included” with Visual Studio. So I went out and downloaded Visual Studio (CTP3, Community Edition, i.e., free) from here. And look what shows up on the install features list, there it is in black and white, Microsoft SQL Server Data Tools, almost too good to be true.

Visual Studio Features

Well, we all know that if something seems too good to be true, then it usually is. This is no exception.  Let’s see if you can pick out the reason for my disappointment in the picture below.

Visual Studio Project Types

That’s right, the only SQL Server project types that are installed with Visual Studio are database projects. No SSIS, no SSAS & no SSRS. That was very disappointing. Also note that it installed the templates for Visual C#, Visual Basic, etc., when the only feature that I requested to be installed was SQL Server Data Tools. I guess that’s why this install took 5GB of disk space as opposed to the 3GB that SSDT required.

The good thing about the new Visual Studio is that if you use TFS as your source control, you no longer have to download the separate TFS Team Explorer, it is now built in to Visual Studio. No additional installs are required.

Visual Studio Team Menu

Right “out of the box”, you get the Team menu item. However, this is NOT included in the SSDT install. I guess someone thinks we don’t really need to source control our SQL Server projects <sigh>.

Almost One Tool

Because I use TFS as my source control, I still have to do two installs, SSDT to get ALL the SQL Server project types AND Visual Studio so I can add all my SQL Server project types to source control.

This is definitely better than what we have to do now if we are doing development work prior to SQL Server 2016, but it’s not “One Tool to Rule Them All” yet. I’m hoping that since this is a CTP, the final products will contain “all the things”, but I certainly won’t hold my breath.

Now I’m off to test if they’ve overcome the issue of database projects playing nicely with SSAS projects. For those that use the multidimensional model with partitioning, you know exactly what I’m talking about. I’ll keep you posted with my results.