Blog

Dec 12
Scaling TempDB across Disks in a VM

The SQLCAT team gives some good metrics on what kind of performance improvement you can expect when you split your TempDB into different secondary data files (.NDF), and shows the differences when you increase the number of disks you spread the NDF files over.  Very cool! 

Note that this applies as much to your on-prem virtual machines as it does to Azure.

http://blogs.msdn.com/b/sqlcat/archive/2014/01/16/scaling-out-sql-server-disks-and-data-files-on-windows-azure-virtual-machines-a-real-world-example.aspx

Dec 10
Featured Whitepaper: Troubleshooting SQL Server Wait Stats

Passing this along from the PASS newsletter:

SQL Server MVP Kevin Kline discusses the most common problems facing IT teams who focus their performance troubleshooting activities solely on wait statistics. This can serve as a useful and effective template for performance troubleshooting which you will be able to use over and over again whenever SQL Server performance is called into question. Read the latest whitepaper by our Global Alliance Partner, SQL Sentry, along with other whitepapers by our other Global Alliance Partners, here.

Note that this is kind of an advertisement (for SQLSentry's Performance Advisor), but there's a ton of useful info in there.  I don't mind plugging a product if it will make a DBA's life easier.   

Nov 10
"SharePoint? Uggghhh!"

​Last week I attended the PASS Summit 2014 conference in Seattle.  I must say it was an amazing experience, way better than any previous conference I had attended.  I'll write more about it later, but I wanted to talk a little about something I noticed every time I mentioned I worked with SharePoint.  Here are the most common responses from every DBA I met:

  • "I'm sorry."
  • "Oh god."  (eye roll)
  • "They didn't actually talk to any DBAs when they designed it, did they?"
  • "SharePoint's not an application, it's a beast."
  • "Uggghhh."

And so on.  I mean, it's one thing to be hatin' on Microsoft for writing complex software, but these DBAs clearly thought of SharePoint as the enemy and something that needed to be destroyed.  Not one considered it useful.  If it was in their environment it was a burden they were shouldered with.  Even one of the speakers, who had written a book on PowerPivot that included several chapters on SharePoint, couldn't really bring himself to endorse it.

A Solution in Search of a Problem 

First, there's the common complaint about SharePoint, that it's just a glorified file share, something no one really knows how to use and just complicates the process of document management.  One guy talked about how his company keeps reinstalling SharePoint every year, trying to get it right, and that consultants are making a fortune every time they're brought in.  These are pretty common complaints actually, which are the result of people deploying SharePoint without any user buy-in or a real objective.  Or, as one customer put it, "SharePoint's a solution in search of a problem."

SharePoint Wants Its Own SQL Instance

The SharePoint workload has some unique requiments, in terms of security and configuration.  For example, SharePoint 2013 requires that MAXDOP be set to 1 at the Instance level.  MAXDOP (Maximum Degree of Parallelism) pertains to how many threads a single query can use, and SharePoint is designed to expect every process to be single-threaded (MS Dynamics has the same requirement).  This makes a DBA's head explode, because MAXDOP (in its default configuration) is usually a good way to scale performance. 

You could certainly set up SharePoint share a SQL instance with other databases, and I've found it to be a good neighbor, but you might find that its workload dominates other databases and tends to pound on TempDB. 

You Can't Touch the Databases

Next, DBAs complain about the myriad databases used by SharePoint.  They are closed off: Microsoft says you can't modify these or even directly query them (I'll get into why in a minute).  You can't create indexes or even do defrag/rebuilds. How is a DBA supposed to be able to maintain a database if they can't even optimize performance?  So if SharePoint is slow (and it is) then they can only get blamed for it, with no way to solve the problem. 

Why is SharePoint such a closed system?  It comes down to the fact that most of the configuration is contained in content and config databases, coordinated via timer jobs.  If you make a direct change to a table or row without using the SharePoint API (or PowerShell), you have a chance of corrupting services or data.  Compare this to editing the Registry.  For most people, the "Keep Out" sign is for their own protection, and for good reason.

Also, a SharePoint content database has a somewhat, shall we say, cryptic design.  All documents are stored as BLOBs.  In a single site collection, all document libraries and lists use just one table to store all content.  This means if you run a SELECT statement on the wrong table, you can end up locking an entire site collection.  It leaves one scratching their head as to why the SharePoint team designed it this way, because it becomes a limiting factor for performance and scale.  Although querying content directly can be useful, it's not supported. 

Lastly DBAs complain that you can't even tune indexes in SP databases.  That's not strictly true (you're not blocked from doing it) but SharePoint has its own timer jobs that do the same thing, and they might undo or repeat some of your work.  You're also told to not auto-enable statistics for the same reason, because SharePoint likes to maintain its own execution plans. 

So if it's the worst thing ever, why use it?

 I got into SQL Server way before SharePoint was a thing.  I developed my own applications (in VB6) and I was responsible for everything, from the controls on the form to the triggers / sprocs behind every event and action.  Every time I started a new app, I had to reinvent the wheel again, even if I developed reusable classes.  I had to design everything, which turned a simple assignment into a complex task.  (Note: I was never a very good developer, but then again I was self-taught and worked alone). 

When SharePoint came along it wasn't exactly revolutionary but I saw a lot of potential, even in the 2001 version.  You could create code-free solutions that ran on top of the SharePoint platform.  You didn't have to reinvent the wheel.  You could focus on solutions and not framework.

SharePoint is a platform that allows you to build your own solutions, often without invoving IT, programmers, and yes, even DBAs.  It's designed to empower users.  I will be the first to admit that the layers below the surface are pretty ugly, but I think the end result is a valuable product.  Microsoft sure seems to think SharePoint is the cornerstone of their Office suite, and it's only going to keep growing.  As a consultant I can't ignore that. 

References:

Sep 15
12-11-14: PowerShell Cmdlets for the DBA

December 11, 2014 Meeting

 

We'll be meeting at the Network Business Systems office in their conference room at their new Midtown location:

3000 C Street, Suite 210

Anchorage, Alaska  99503 

Click here for directions.

 

PowerShell Cmdlets fot the DBA

You've been hearing about this newfangled craze, and it's time to learn exactly why it's called POWERshell. You'll want to take notes, or better yet, bring your laptop and start using Powershell immediately. We will get Posh running and then I'll introduce you to the basic concepts, with examples to make things happen right away. We'll work on SQL Server with Powershell, and learn some cool techniques. Come see this all-demo, hands-on session!

Speaker: Jen McCown (Midnight DBA) 
Jen McCown is a Microsoft Certified Master in SQL Server, and co-owner of MidnightSQL Consulting, LLC. Jen is Senior Editor at MidnightDBA.com, where she creates training videos, the DBAs@Midnight webshow, blogs, reviews, and podcasts. Jen is a member, volunteer, and speaker in NTSSUG, PASS, and the PASS Women in Technology virtual chapter. She has presented at multiple technology conferences, including SQL Saturday, SQL Bits, and the PASS Summit.

More here: http://midnightdba.itbookworm.com/

​Watch now on Youtube​

 

 

Sep 15
11-13-14: SQL Server Optimization for SharePoint

November 13, 2014 Meeting

 

We'll be meeting at the Network Business Systems office in their conference room at their new Midtown location:

3000 C Street, Suite 210

Anchorage, Alaska  99503 

Click here for directions.

 

SQL Server Optimization for SharePoint

DBAs know that SharePoint just doesn't play nice with SQL Server. SharePoint administrators know that DBAs just don't understand SharePoint. This session will show hands-on how to get a SQL Server instance set up more effectively for SharePoint 2013. It's not black magic and it doesn't involve undocumented trace flags. Maybe DBAs and SharePoint admins can't get along, but at least you'll be able to cooperate after this session.

[Note: Sven will also be presenting this topic to the Alaska SharePoint User Group on 10-23-14].

Speaker: Sven Aelterman 
I am a Lecturer in Information Systems at Troy University's Sorrell College of Business in Troy, Alabama, USA. I am also the Director of IT for the College of Business, which means I am principally responsible for all technology in the College.  When possible, I also continue to be involved in consulting for the Microsoft platform, which includes .NET, SQL Server, Windows Server, and COM technologies. Most recently, I've worked on SQL Server high-availability and disaster recovery projects.

http://svenaelterman.wordpress.com/

View Sven's Slides here

Watch on YouTube:
 
 
Sep 14
10-9-14: How Active Directory Affects SQL Server

October User Group Meeting is at 12pm October 9, 2014

 

We'll be meeting at the Network Business Systems office in their conference room at their new Midtown location:

3000 C Street, Suite 210

Anchorage, Alaska  99503 

Click here for directions.

How Active Directory Affects SQL Server

If you have ever had a Kerberos or SSPI context error, then you won't want to miss this session.  SQL Server has a large surface area and Active Directory can influence a big part of it.  I will discuss AD DNS configuration, Group Policy Objects, Kerberos (of course), and how all of them affect your SQL Server.  By the end of the session you'll have a check list of things to discuss with your domain administrator when you return to work.

Speaker: Ryan Adams 
Ryan has worked for a Fortune 100 company for 15 years. He is the sole SQL Server DBA for his group. His primary focus is the SQL Server Engine and performance, but also works with SSRS and SSIS. Prior to becoming a SQL DBA, he was a Senior Active Directory Architect and Identity Management Consultant where he architected the company's worldwide Active Directory infrastructure supporting over 250k users.  He enjoys being involved in the SQL community and serves on the Board of Directors for the North Texas SQL Server User Group and is President of the PASS Performance Virtual Chapter. He also serves as a Regional Mentor for PASS.

More Information: http://www.ryanjadams.com/

 

Watch now: https://www.youtube.com/watch?v=HrczaaO4NdE
 
Aug 22
9-11-14: Secrets of Enterprise Data Mining

September User Group Meeting

 

Secrets of Enterprise Data Mining

If you have a SQL Server license (Standard or higher) then you already have the ability to start data mining. In this new presentation, you will see how to scale up data mining from the free Excel 2013 add-in to production use. Aimed at beginning to intermediate data miners, this presentation will show how mining models move from development to production. We will use SQL Server 2014 tools including SSMS, SSIS, and SSDT.


 
Speaker: Mark Tabladillo, SQL MVP

Mark is a consultant who provides enterprise data science analytics advice and solutions. He uses Microsoft Azure Machine Learning, Microsoft SQL Server Data Mining, SAS, SPSS, R, and Hadoop (among other tools). He works with Microsoft Business Intelligence (SSAS, SSIS, SSRS, SharePoint, Power BI, .NET). He is a SQL Server MVP and has a research doctorate (PhD) from Georgia Tech.

Watch video now:

https://www.youtube.com/watch?v=Ggpw5zhsbok

 

 

View / /Download slides here:

http://www.slideshare.net/marktab/mark-tab-secretsenterprise201408

Aug 01
8-14-14: Manage SQL Server Using Built-In Tools

 

Thinking Out of the Box: Manage SQL Server Using Built-In Tools

 

 

It’s often the case that your budget doesn’t stretch far enough for all the great third party tools that help manage your SQL Servers. So, how can the in-built components of SQL Server help you with your daily tasks? 

Policy Management, Data Collector and Extended Events, all included in both Enterprise and Standard Editions, provide a framework to keep your configurations consistent and give you some insights into the performance of your SQL Server instances. In this session, we will see how to make use of these components to formalize your configuration and track some performance attributes.After this session you’ll be able to build out an effective management and monitoring toolkit using only “out-of-the-box” features..
 
Speaker: Martin Cairney
Martin Cairney is a highly experienced Database Administrator for LobsterPot Solutions in Melbourne, Australia. He has worked across many industry sectors including London’s global financial institutions and both State and Federal Government Departments. He has presented a number of times at SQLBits, SQL Saturdays and User Groups in Australia and the UK. Martin is a MCT, has MCSE, MCITP and MCDBA certifications and runs the SQLSocial and SQL Saturday events in Melbourne.

 

Door Prize

We'll be giving away:

  • A voucher for a free book of your choice from O'Reilly Publishing
  • Other items TBD


In Person: We'll be meeting at the Network Business Systems office in their conference room at their new Midtown location:
3000 C Street, Suite 210
Anchorage, Alaska 99503

Click Here for Directions

WebEx Meeting Details:

Topic: Alaska SQL User Group - August 2014
Date: Thursday, August 14, 2014
Time: 12:00 pm, Alaska Daylight Time (Anchorage, GMT-08:00)
Meeting Number: 924 767 307
Meeting Password: alaskasql

-------------------------------------------------------
To join the online meeting (Now from mobile devices!)
-------------------------------------------------------
1. Go to https://nbsys.webex.com/nbsys/j.php?MTID=md20c9de8883281c38d1a89640b58ce25
2. If requested, enter your name and email address.
3. If a password is required, enter the meeting password: alaskasql
4. Click "Join".
5. Follow the instructions that appear on your screen.

To view in other time zones or languages, please click the link:
https://nbsys.webex.com/nbsys/j.php?MTID=m9482f5a0e7a98207fd35f3ae17da1c6c 

Jul 12
7-10-14: Manage Your Shop with CMS and Policy Based Management

The session recording for our JUly 10th meeting is now on YouTube.  In this 90 minute session, Ryan spoke about how to set up a Central Management Server for SQL Server, and how to implement policies.

"​In this SQLRally #3 rated session I will talk about Central Management Server and how it can help you manage a disperse environment.  I will also cover what Policy Based Management is and how you can leverage its power to better manage your environment.  With PBM we'll see what it can and cannot do to help you enforce standards in your enterprise.  I will demonstrate PBM from creating and evaluating policies to receiving alerts on policy violations."

 


Slides are available here: 

http://www.ryanjadams.com/wp-content/uploads/2011/01/PBM-Preso1.pdf​

Jun 23
6-12-14: What's New in SQL Server 2014

Hi All,

Unfortunately, the session recording for "What's New in SQL 2014" ​did not turn out.  Guy Glantser did share his slides, however.  Click the link below to download.

Download

1 - 10Next