pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

12/06/2016

Actual Chats: RIGHT OUTER joins in the wild

Colleague:
I think I just encountered my first Right join
*shocked* 
Me:
huh. weird.
a rare Right Join has appeared!
Colleague:
catch it!
Me:
You use ON. It's super effective! 
Colleague:
haha
how often do you see them? 
Me:
I've used RIGHT join maybe twice in 10 years. 
Colleague:
lol
Me:
just makes things difficult to read.
Colleague:
yea it took me a while
it was at the bottom of the query
i kept wondering what was bringing in the extra rows till I found it

And that's why, whenever possible, avoid using RIGHT OUTER. It breaks the nice top-to-bottom flow of your complicated FROM clauses, forces you to page-split your brain around data sets, and generally makes it more difficult for the next DBA to understand.

For this overly-simplistic example, these two queries on the WideWorldImporters database are identical and have the same execution plans:
SELECT  s.CustomerName
, pp.FullName
, ap.FullName 
FROM Sales.Customers AS s
LEFT OUTER JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
INNER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID 
SELECT  s.CustomerName
, pp.FullName
, ap.FullName
FROM [Application].People AS pp
RIGHT OUTER JOIN Sales.Customers AS s
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
INNER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID

The first query allows you to do the easily relational math as you read. The second has you flipping the dataset before moving your eye back up to the top.

There are certainly legitimate albeit complicated cases for using RIGHT OUTER, but from a readability and maintainability, I'd prefer to stick with LEFT OUTER.

9/25/2016

SQL SSRS Express Edition Cannot Connect to Azure SQL Databases

This has bit a couple of clients recently, figured I'd put out a PSA.

Express editions of SQL Server Reporting Service, from SQL 2016 on down, cannot connect to Azure SQL Databases. Turns out, getting something for free does have some significant limitations.

For example, you'll see an error message "The Report Server has encountered a configuration error" on a data source page, when creating a new SSRS data source in the Report Manager website. What you may have not noticed on this page was the possible values in the Data Source Type drop down list.

In express edition, "Microsoft SQL Server" is the only option. In Standard and higher editions, there are many data types to choose from, including "Microsoft SQL Azure". Remember that Azure SQL Databases can't accept OLEDB connections.

But why? On this page (and see caption right), Microsoft lists a large number of "Built-in data extensions," Azure SQL Database included. The explanation lies here, where under possible "Supported data source", Express edition only supports Express edition, while Standard and above supports "All SQL Server editions."

This is what you're looking at in SSRS Express:


This is what you could be doing, in SSRS Standard or higher:


So what's the solution?

Upgrading the edition of a SSRS Express edition instance in-place is possible

Remember you can install various SQL Server features (Database, RS, IS, AS) on different Windows servers, each only once, as part of your license. So if you already own a Standard+ edition SQL Server somewhere but aren't using the SSRS feature, you now have a good use for it. 
Myth BUSTED: The above is incorrect! In the Licensing Guide for each recent version of SQL Server you'll find this: "The software components of a single SQL Server 2016 license cannot be separated. Any OSE running any of the licensed components of SQL Server 2016, requires a license. For example, if the SQL Server DB is deployed in one OSE and SQL Server RS is deployed in another, both OSEs must be fully licensed for SQL Server 2016 accordingly." The language was different and less specific for SQL 2005.

There are also a large number of fantastic (and also fantasy) ideas that developers will have, including SSRS alternatives. Listen to them, sure. But keep in mind that if your platform is SSRS and all the features that come with it, you will need to pay for it at some point. SQL Express edition and never been and never will be intended or suitable for standalone production usage. 

9/17/2016

"SQL Server Permissions and Security Principals" at SQLSaturday #560 Charlotte 2016

I'm proud to have been a part of SQLSaturday Charlotte 2016 this year, and happy that I was joined by a number of my colleagues from Sparkhound. We're establishing a presence in Charlotte and already have a couple big clients in the area, so Sparkies from both Baton Rouge and Charlotte joined me this weekend.

My presentation slide deck and demo scripts can be downloaded via the SQLSaturday website here.

The session is a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, consolidated databases, application roles and more. This class is perfect for DBA's, developers and system admins, and we'll cover security basics for Azure SQL Database as well.

As a SQLSat organizer in Baton Rouge myself, I know exactly how much work and sweat equity it takes to put on a SQLSaturday event. The Charlotte team knows what they're doing after five years or so, and did a great job in 2016. Excellent work by the entire organizing team!




9/15/2016

"SQL Server Permissions and Security Principals" at SQLSaturday Charlotte This Weekend!

I'll be presenting my talk on SQL Server Permissions and Security Principals this weekend at SQLSaturday #560 Charlotte, NC.

As a SQLSat veteran and organizer, I regularly receive feedback that there needs to be "more" content at the elementary level for new folks in the field, students, etc. I try to give this presentation as often as possible to make sure there is useful and practical content at any community conference for entry-level technical folks. They are, after all, our future coworkers.

The session is a ground-floor introduction to SQL Server permissions starting with the basics and moving into the security implications behinds stored procedures, views, database ownership, application connections, consolidated databases, application roles and more. This class is perfect for DBA's, developers and system admins, and we'll cover security basics for Azure SQL Database as well.

This will be my first time at SQLSat Charlotte, looking forward to meeting everyone there. Fun Fact: this will actually be my first time speaking at a SQLSaturday in the US Eastern Time Zone.

My colleagues from Sparkhound will also be presenting in the same room immediately afterwards, Using PowerBI and SQL Server to Create Management Cadence Dashboards.

See you there!

8/09/2016

SQL UG Leaders: Reach Out to Your .NET Friends

In Baton Rouge we make no secret that our user groups and our 578-person SQLSaturday combines the volunteers and attendees of our local PASS chapter with our neighborly Baton Rouge .NET User Group. It's not just SQL folks attending our monthly meetings and annual event.

When discussing this, I've always been very honest and enthusiastic that holding joint events of local technical communities really works.

We all joke that developers and database administrators don't get along. The playful banter back and forth between devs and DBA's in Baton Rouge is never mean spirited, and is actually embraced (see image to the right). I personally work with 20+ developers every day and enjoy the dialogue and camaraderie, at both a technical and social level.

I'd recommend collaborating with your local .NET UG community to any PASS user group looking to
1) increase the number of folks available for networking at monthly/annual events
or
2) increase the ROI to sponsors of monthly/annual events.

If your SQL Server User Group isn't reaching a critical mass of folks at your monthly user group meetings or annual SQLSaturday events, reach out to your .NET group. They might be in the same boat. 

Gaining a critical mass of attendees is important. That doesn't mean they all have to do the same job. That critical mass of attendees each month makes the room feel full, it makes folks want to invest in becoming a technical speaker, user community leader, and recruiter for future user group attendees. IT professionals are far more likely to recommend to their friends and colleagues a meeting that monthly gathers twice as many people.


Here's how we meet in Baton Rouge with both .NET and SQL User Groups:

5:45pm: Meet in one room for professional networking, dinner (usually pizza)
6:15pm: Introduce the user groups and each group's leadership. Give out announcements about upcoming events applicable to both groups, facility information, etc.
6:20pm: Announce the sponsor for the meeting. (Thank them for the pizza!) If present, the sponsor can give a demo or a talk or a giveaway.
  • Also allow for your host facility, whatever it may be, to talk briefly about their mission/purpose as well. In our case, the Louisiana Technology Park sometimes sends us a representative (or CEO!) to give a short pitch on what the Tech Park does and the kind of companies they foster.
6:30pm: Sometimes, a member of the user group will give a lightning round (10-15 minute presentation) on a topic that applies to both .NET and SQL crowds.
  • Past examples: SQL Server Reporting Services, Power BI, source control, professional development, communication skills, tools and utility applications, Visual Studio tips and tricks, security, cool upcoming tech gifts for holiday shopping.  
6:45pm: The crowd splits up into different rooms for their main .NET and SQL presentations
6:50pm: Once separated, sometimes each user group will have its own Lightning round and announcements.
  • For example, the Baton Rouge SQL group shows the monthly PASS slidedeck now, not to the combined crowd.
8pm: The crowd gets back together in the big room for raffle giveaways, see-you-next-time, more professional networking.
9pm: Usually, folks are still networking and chatting in the facility by the time we need to kick them out! Sometimes, they'll continue the tech talk and networking at a nearby bar or restaurant.

You may find that technology professionals will attend your joint SQL+.NET meeting, then decide which one to attend based on the topic. Many .NET coders know and work with SQL Server, and vice versa. In practice, we very rarely hear the complaint that they cannot attend both meetings. It hasn't happened in recent memory. User group presenters frequently make their presentation slidedecks available for download after the fact, or have the content recorded otherwise. Attendees can still visit with presenters from both presentations, regardless of what room they choose.


Here's what you may need to invite the .NET folks:

  1. Find a two-room venue, typically with one big room and one smaller room. Already have a meeting venue for your monthly meetings? Ask if they have another room you can use.

    Don't have a venue?
    • Reach out to universities, specifically the alumni coordinators or faculty members who are interested in connecting students with professionals in the field. Ask for a consistent, scheduled-in-advance location, don't get moved around every semester, it will hurt attendance.
    • Reach out to technology parks or incubators who cater to tech startups - tell them that your attendees are their target audience for future startup tenants.
    • Reach out to local consulting firms or training providers who have conference rooms in use during the day but vacant at night.
    • Reach out to local Microsoft offices or facilities.
  2. Don't get caught up in leadership competition, don't propose merging the user groups or taking away group sovereignty. That's not the idea. Figure out which folks among the user group leadership can emcee the meetings, remembering that it doesn't have to be one person.
  3. Recruit sponsors using the edge that you have a wide audience of technology professionals on the front end and back end of application development. Consider sharing finances between the two user groups for the purpose of user group sponsorship and supplies instead of splitting the sponsorship income and the food bill and arguing over "who pays for who". This is very similar to a marriage. :) If one user group has already incorporated as a not-for-profit, this means the other group doesn't need to go through this expense and hassle to enjoy the benefits.
  4. Order more pizza :) and drinks, make sure you have space and supplies to serve more folks. Combine resources when it comes to bulk purchases of cups, paper plates, napkins, etc.
  5. You don't have to combine main topic presentations, but sometimes it's possible. We've had the SQL and .NET groups stay together for topics like Database Performance tuning for Entity Framework, or SQL Server security and permissions.
  6. Host everyone-invited events like Networking Nights, Speaker Idols or Holiday Parties, where multiple speakers from both groups can give soft-skills, non-technical or highly-entertaining presentations, or no presentations at all.
    • Our Networking Night is the July meeting preceding our annual SQL Saturday event in August. It's our largest meeting every year, features short-format (<10 minute) entertaining presentations on career topics. It's part of the SQLSaturday sponsorship package too, giving a Career Night speaking opportunity to Gold and Platinum sponsors. 
    • Our Speaker Idol nights are entertaining and informative "competitions" where speakers create short (<10 minute) demos or "tips and tricks" presentations. "Celebrity judges" can include local CIO's or IT professionals who are witty with a talent for giving constructive feedback, congenial heckling and creating a fun, positive atmosphere. 
    • Holiday Parties are really tricky to schedule in December because of all the conflicts every night of the week. But we have in the past tried and had success with hosting a User Groups Holiday party. No main presentations at all, just networking. Perhaps do a "white elephant" or "sneaky santa" gift exchange. Find a restaurant with a low rental fee for a private room. Find a sponsor interested in connecting with the groups on a less formal level, like consulting companies or recruiters. (For liability purposes, don't have any of the user group funds pay for alcohol.)
  7. Add and recruit .NET tracks to your local annual SQLSaturday and ask .NET UG leadership to help recruit and select speaker sessions for the event. Expanding your tent can attract more folks, helping the event get a critical mass of attendees and greatly increasing the ROI for sponsors.
  8. Adding a partner group your PASS Chapter is not just for .NET by the way. You may find a lot of topical crossover and technical interest with a local Windows Server, PowerShell, virtualization platform, or IT Pro User Group, or perhaps with a SharePoint user community. 
    • Regardless, reach out to leadership of other user groups and introduce yourself. At your user group meetings, evangelize for other groups in the area, even non-Microsoft ones.

In summary, try reaching that important critical mass of attendees by making a bigger, wider tent. Yes, this will have much more appeal to sponsors, especially recruiters and staffing agencies, and this isn't an insignificant reality. 

But it will also incentivize attendance by exponentially increasing the monthly opportunities for:
knowledge transfer,
cross-technical training, 
professional and social networking, 
development of new local technical speakers,
fostering leadership enthusiasm in user group leadership, 
and fun.


If you'd like to discuss this more, please do not hesitate to ask via Twitter, LinkedIn or the comment section of this blog post.

7/16/2016

SQLSaturday Baton Rouge 2016!

Free SQL Server, SharePoint and .NET training

SQLSaturday is a global event to bring Information Technology speakers and professionals together for a community-driven, community-attended free day of technical training. The Baton Rouge SQLSaturday event is sponsored by the Baton Rouge SQL Server and .NET User Groups, and will be our annual event on campus at LSU. We expect 600+ regional IT professionals and national speakers to join us. This free conference is open to the public and is perfect for students, CIO's, database administrators, developers, IT managers, server admins and job-seekers. 


Then...



What: 
An all day FREE training event with SQL Server, Development and SharePoint related sessions spread out over multiple tracks of Business Intelligence, Database Administration, SQL Development, SharePoint, IT Pro and .NET development. 

Who Attends SQLSaturday? 
Folks with the following skillsets are drawn to SQL Saturday Baton Rouge because of the professional networking, free training, and giveaways:
  • SQL Server Administrators
  • Business Intelligence Developers
  • Data Analysts
  • ETL Developers
  • C#/VB.NET Developers
  • Mobile Developers
  • Windows Server Admins
  • SharePoint Architects
  • SharePoint Developers
  • Network Administrators
  • Quality Assurance Analysts
  • IT Managers
  • Students
  • Project Managers
  • Hiring Managers
  • Jobseekers of all levels of experience
  • Students
  • CIO's
  • CEO's

At the end of 2015, we polled our user group membership and got this amazing feedback on SQLSaturday Baton Rouge #423 in August of 2015:

"As a former speaker and attendee at Sql Saturday, I must say you guys knock the ball out of the park with SQL Saturday. You guys put on a top notch event for both presenters and attendees."
"SQL Saturday is always top-notch!"
"SQL Saturday is always on point. Love this event and always promote it to other groups."
"I am happy that the talks are open to more topics than just .Net and MSSQL"


When:
Saturday, August 6, 2016. Online registration is now open, but it is filling up fast so reserve your spot now. Attendee check-in will begin at 8:00 AM first sessions beginning at 9:15 AM. A full list of session tracks and schedule is available.

7/12/2016

Training To-Do List for New DBA

Are you an entry-level DBA, or looking to make the career switch? Feel like you are looking up from the bottom of a very tall ladder? Don't have much of a budget for training?

Here's a list of resources I'd recommend, in no particular order.

EDIT: I presented this content, along with a bunch of other content and some lame jokes, at the Baton Rouge User Groups Networking Night on July 13. You can download the slidedeck here.

Register for PASS and join your local SQL Server User Group and also some Virtual Chapters. Pay special attention to:
o DBA Fundamentals - http://fundamentals.sqlpass.org/
o Database Administration - http://dba.sqlpass.org/
o Security - http://security.sqlpass.org/

Register for SQLSaturday Baton Rouge on Aug 6, or the nearest SQLSaturday to your location. Somewhere in the world, a free SQLSaturday community event is held almost every week.
o Register to volunteer too: http://www.sqlsaturday.com/515/Volunteers.aspx 
o Check out the pre-cons before this SQLSaturday and others like it - they are a bargain for the quality and quantity of training.

Register at SQL Server Central and look into some of the "Stairways". Pay special attention to:
o Stairway to Integration Services: http://www.sqlservercentral.com/stairway/72494/
o Stairway to SQL Server Indexes: http://www.sqlservercentral.com/stairway/72399/
o Stairway to SQL Server Security: http://www.sqlservercentral.com/stairway/110890/
o Stairway to Transaction Logs: http://www.sqlservercentral.com/stairway/73776/

Register for the Microsoft Virtual Academy. Pay special attention to:
o Database Fundamentals: https://mva.microsoft.com/en-US/training-courses/database-fundamentals-8243
o Updating your Skillset to SQL 2014: https://mva.microsoft.com/en-US/training-courses/updating-your-database-management-skills-to-sql-server-2014-8313?l=S3j693Yy_704984382

Codecademy.com free online learning labs for various languages including non-vendor specific tutorials on the SQL language standard:

Pluralsight.com contains many hours of high quality instructional videos for many, many technical platforms.
o Look for Pluralsight coupons at your SQL Server user group via giveaways, they're a big sponsor of user groups
o Pluralsight will give free access to recently unemployed and military, email unemployed@pluralsight.com 
o MSDN subscriptions come with some access

• There are a ton of books out there for SQL Server and the goal of this blog post isn't to do a book review any of them in particular. I don't intend to rank or rate any of them here, as my first-hand experience with the breadth of SQL books is limited.
o But, here's an incomplete, not-exhaustive list to check out first. If you want, please add some to this list in the comments of this blog post. Special emphasis on free here.
o An excellent ground-floor introduction to being a SQL Server DBA is the SQL Server 2012 Step by Step book by Patrick Leblanc from Microsoft Press. Full disclosure: I was a technical editor and writer for this book but do not collect any sales residual.
o SQL Sentry eBooks series: High Performance Techniques for SQL Server (free)
o SQL Server eBooks from Microsoft Press (free)
o SQL Server PDF's from Red-Gate (free)
o SQL Server Execution Plans (PDF) by Grant Fritchey (free)

Got additions and suggestions? Please add them to the comments in this blog post.

6/05/2016

SQL Server Admin Best Practices with DMV's at SQLSaturday Pensacola 2016

Great attendance and awesome questions from my 1130AM session on "SQL Server Admin Best Practices with DMV's". As promised, all the script files, including the demos we skipped for sake of time, and the entire info-packed slidedeck are available for download here on the official SQLSaturday Pensacola 2016 website.

If you have any questions or would like to get in touch, please let me know, contact info inside. Happy to meet, and thanks again for participating in the session and asking so many great questions. Thanks also to the brave contingent of developers from Alabama who survived the experience with a room full of anti-GUID DBA's. :)

It was an awesome event and I have very thankful to Karla Landrum for resurrecting the event,  the first SQLSaturday in Pensacola since 2012. Back home in Baton Rouge now I had a great weekend and was very glad that the wife and I were both able to be a part of a tremendous speaker lineup.

Thanks also to photographer/SQL extraordinaire Jamey Johnston for taking photos at the event and also for agreeing to photograph SQLSaturday Baton Rouge in August!


5/20/2016

SQLSaturday Pensacola Up Next For Me, Baton Rouge on the Horizon

Looking forward to the triumphant return of SQLSaturday Pensacola on June 4, thrilled that this start-of-summer tradition is back! This has been in the past a pilgrimage that the wife and I enjoyed in early June, and we'll both be there again now that the event is returned for the first time since 2012.

I'm speaking at 11:30AM on SQL Admin Best Practices with DMV's in room 1776.

But my end-of-summer SQLSat has been, since 2009, my own hometown SQLSaturday Baton Rouge at LSU. Please help us spread the word about SQLSaturday Baton Rouge, to be held on August 6 at LSU!

Register now for SQLSaturday Baton Rouge, and check the Volunteer box to help us out!

Key dates for SQLSaturday Baton Rouge: 

Call for Speakers is OPEN, closes June 1Call for Speakers
Call for Sponsors is OPEN, closes July 25Sponsor Plan

All Sponsor bag materials must be received by July 30 

Baton Rouge User Groups Networking Night: July 13 at the La. Tech Park.
Call for Speakers for Networking Night 10-minute talks is OPEN: Networking Night Call for Speakers

5/13/2016

Hello SQLSaturday Houston!

I've been looking forward to speaking at SQLSaturday Houston again all year!

Big thanks to the organizing crew behind this great event, poised to be Houston's biggest ever.

I'll be giving a presentation on SQL Server Permissions and Security Principals at 8:30am in room 148.

Also don't miss my colleague Robert Bishop's presentation on "Hostile Takeover", about inheriting legacy SQL Server systems, at 9:45am in room 117.

The slides and sample scripts for my presentation are available for download here or here.

UPDATE: The video from this session is available via UserGroup.TV here: http://usergroup.tv/videos/sql-server-permissions-and-security-principals



5/09/2016

SQL Server 2016 at the BRSSUG Meeting on May 11

Looking forward to the BRSSUG/BRPBIUG meeting this Wednesday night May 11, I'll be giving a presentation all about SQL 2016!

This user group is sponsored by a free SQL 2016 sampler book from Apress, which is available for download for free from BRSSUG.org.

As have all meetings this calendar year, this is a joint meeting of the Baton Rouge SQL Server User Group and the nascent Baton Rouge Power BI User Group (started in January).

See you there!

4/21/2016

Columnstore Indexes: Finally Awesome in SQL 2016

Columnstore indexes are amazing for optimizing the performance of large rowcount queries that would otherwise look like a SCAN operation in an execution plan. Introduced in SQL Server 2012, they render the table read-only, however, and needed to be dropped (not even disabled) in order to insert/update/delete rows.

If you tried to update a table with a nonclustered columnstore index in SQL 2012 or SQL 2014:

Msg 35330, Level 15, State 1, Line 2
UPDATE statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, and then rebuilding the columnstore index after UPDATE has completed.

In SQL 2014, you can now make a clustered columnstore index, but it has only narrow, specific uses because clustered columnstore indexes don't support traditional (B-tree) nonclustered indexes, a primary key, foreign keys, etc.

All those limitations above are going away in SQL 2016. Right now: SQL 2016 will release on June 1 2016. Release candidates are available for download but aren’t for production use.

SQL 2012 - Nonclustered columnstore indexes can be created, but aren’t updateable. 
SQL 2014 - Nonclustered columnstore indexes can be created, but still aren’t updateable. Clustered columnstore indexes can now be created, are updateable.
SQL 2016 - Nonclustered and clustered columnstore indexes can be created, and are updateable. Plus, you can mix clustered columnstore with traditional B-tree indexes. (Lots of other features too!)

More about these features as Columnstore indexes evolve here: https://msdn.microsoft.com/en-us/library/dn934994%28v=sql.130%29.aspx


Want to benefit from columnstore indexes, but not using SQL 2016 yet?

You have three options.

1) Disable/rebuild columnstore nonclustered indexes when you do updates to the table. Not a big deal for nightly-build data warehouses, but painful for frequently-updating tables. Note that the ONLINE feature to create traditional indexes isn't available here.
DROP INDEX [IDX_CS_Table_Covering] ON [dbo].[Table]
GO
<do updates/inserts>
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IDX_CS_Table] ON [dbo].[Table] ( Column1, Column2... )
GO
(When you move to SQL 2016, these drop/create steps won’t be necessary any more.)

2) SQL 2014 only. Drop all existing clustered/nonclustered indexes and foreign keys on the table, change the table to have a single clustered columnstore index. This requires performance testing for smaller-scope queries, but should provide the big performance gains you’re looking for, at the cost of dropping the primary key and related foreign keys. This is suitable for narrow use only in reporting database designs and not in normalized environments.
<drop all existing indexes on [dbo].[Table], including the primary key and linked foreign keys>
CREATE CLUSTERED COLUMNSTORE INDEX [IDX_CS_Table] ON [dbo].[Table]
GO
3) SQL 2014 only. Create a copy of the table that implements 2) above, so you’ll have a table with traditional indexes plus an exact copy of the table but with only a clustered columnstore index. You would then need to point big report queries at dbo.Table_CS and other queries (that use your traditional nonclustered indexes) at dbo.Table. And we would need to modify all insert/update/delete operations in the ETL on dbo.Table in order to replicate any row operations into dbo.Table_CS. This is painful, I understand, but should provide the big performance gains you're looking for. (It would be totally unnecessary to design two tables and redirect some reports in this way if you intend to move to SQL 2016 in the near term, so this step is not recommended.)

That's it!

Need proof that columnstore indexes are awesome and will improve the performance of your large queries? Get used to loving columnstore scans

4/13/2016

SSRS 2016 then SQL Jeopardy! tonight at BRSSUG!

See you tonight for our special SQL Jeopardy! competition where I will be your Trebek and three lucky user group participants will be our contestants!

Also a "special appearance" by our BRSSUG founder and Microsoft architect Patrick Leblanc, who will do a lightning round presentation on What's New in SSRS 2016.

See you there tonight!
brssug.org








4/05/2016

SQL Server PerfMon Counters Not Listed in dm_os_performance_counters

Had an issue with a new Azure VM install of SQL Server, but have had this issue in other, older places as well. SQL Server's performance counters in dm_os_performance_counters and/or perfmon have gone missing. How to get them back?

You might see a small subset of all performance counters here in the DMV view. (There should be 1335 rows in this DMV for SQL Server 2014 RTM, 1245 for SQL 2012 SP2. More are added regularly with major releases.) In the example I saw this morning, only 51 rows were returned, and only for the XTP series of objects (XTP Transaction Log, XTP Cursors, etc).

Strangely, I had now found this problem on brand-new Azure VM's running SQL Server, specifically in the pre-built SQL 2014 Availability Group resource group, which can be deployed easily from the new Azure portal.

The issue can be resolved with a pair of PowerShell commands which re-register performance counter names and explanation text to the SQL Server service in the Windows registry.

In PowerShell, modify the below for your service.
  • In the first line, change the driver name parameter to match the SQL Server instance name, MSSQLSERVER is the name for the default instance. To do the same for a specific instance, it would be MSSQL$InstanceName. 
  • In the second line, replace my path below with the path for your instance.

unlodctr MSSQLSERVER

lodctr "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini"
A restart of the affected service, in this case the SQL Server service, is necessary for the changes to take effect.

3/07/2016

Azure ML and R at This Week's BRSSUG/BRPBIUG

This Wednesday night, not only are we going to be showcasing the technology of the internet with an online presenter from the other side of the world, but we're going to be seeing the first BRSSUG/BRPBIUG presentations on Azure Machine Learning and native R in SQL Server!

Really looking forward to hearing from Leila Etaati, PhD, who will be presenting from Thursday in New Zealand.

Abstract: How to get a better insight about the current situation of organization by analyzing the Data? How we can predict the next step? Machine Learning is a sub field of computer science, which is so pervasive today that you probably use it dozens of times a day without knowing it. Azure Machine Learning is a valuable tool that can be employed by data scientists with different skill levels. Azure ML also supports R custom code, which can be dropped directly into your work space. In this session, I will show a demo of interaction of R language with Azure Machine Learning, uploading R package into Azure ML, preparation of the data in R, and How to publish a R code into Azure ML.

See you there!

Complete info: brssug.org

2/10/2016

SQL Server Security Principals Lightning Round Presentation

Thanks for attending tonight's joint meeting of the Baton Rouge .NET/SQL/PowerBI User Groups, which was sponsored by Idera!

I presented a lightning round to the combined user groups on SQL Server Security Principals, which was a shortened version of a presentation I've given several times in the past. For this one though, I added some extra content about SQL Server Agent security and job permissions, which is a troublesome topic for some developers.

Hope you enjoyed my presentation!

Here's the slidedeck, with complete info and more links inside:
http://1drv.ms/1O4U51O

More information about these great Baton Rouge user groups: .NET | SQL | Power BI

1/21/2016

Easy Installing .NET 3.5 for SQL Admins

Rule "Microsoft .NET Framework 3.5 Service Pack 1 is required" failed.

This is a common problem and relatively easy fix for SQL admins installing a new SQL Server instance going back a few years, so it's about time to put all my notes about the solution into a single place. You'll get this error early on in the install:


"This computer does not have the Microsoft .NET Framework Service Pack 1 installed..." Similar error messages pop up going all the way back to SQL 2008 R2.

Before you go any further, click OK here but it is not necessary to abort SQL Server setup. Leave the "Feature Roles" page open, you'll be hitting "re-run" later.

This support article goes through the various solutions, some more painful than others.

Here's the fastest:

1. Extract the "...sources\sxs" subfolder from your Windows Server installation media .iso.
2. Run the below PowerShell, pointing at your extracted \sxs subfolder in the /Source syntax.
Dism /online /enable-feature /featurename:NetFx3 /All /Source:D:\sources\sxs /LimitAccess
3. Done.

For example:


Caveat- keep in mind that after installing .NET 3.5, you may now be eligible for additional Windows Updates/hotfixes that weren't there before.

When complete, back on the SQL Server Setup window, hit "re-run" in the "Feature Rules" window and move on with your pleasant SQL Server installation experience.



Then thank whoever should be thanked that you're not installing Oracle today.


Footnote:

Irrationally uncomfortable or afraid of PowerShell? Yes, you can use the "Add Roles and Features" feature of Windows to add this, via the ".NET Framework 3.5 Features" Feature. You'll still need to do step one above to extract the "...sources\sxs" subfolder, only instead provide it in the Alternative sources dialogue box like the below example.