T-SQL Tuesday #73 Naughty or Nice?

TSQL2sDay150x150_388014A5This month’s topic for T-SQL Tuesday is hosted by Bradley Ball (blog | twitter) with the subject of server environments.

As you work with SQL Server look around you. Is your environment Naughty or Nice? If it is Naughty what’s wrong with it? What would you do to fix it? Do you have a scrooge that is giving you the Christmas chills? Perhaps you have servers of past, present, and future haunting you. Maybe you are looking at SQL Server 2016 like some bright shining star in the east.

I won’t discuss my current environment, but I do have a frightening story of SQL past.

At a previous employer, I worked in a hastily built environment that never had the benefit of a DBA or Database Architect during setup.  The company started small, so originally they used Microsoft Access to house all their data. At some point, the tiny IT department migrated all the data over to a SQL Server database, but they still ran an Access front end.  The migration was done by people who were not concerned about normalization.  The goal was to move the data, structure was a tertiary concern at best. Many sins were committed in that database, and as the years went by and the data grew, the sins became more apparent.

Querying became a nightmare.  The number of nested functions, within nested functions, within nested stored procedures, was exhausting. I remember finding a procedure that had 14 levels of nested objects. I had to keep notes of object names as I delved in, trying to understand what was happening in that query. I’m still not certain I understood it all.

The real problem though, was that every user also had Excel reports that were direct connections to the database. The wait times were high from all the external hits to SQL Server, but the connection strings…oh the connections strings…

Every single user connected to the database via the SA account. Which was not renamed.  At least the password (which was from the beginning of time) was moderately complex.  However, each user could display the connection string to the database in plain text with a bit of poking around. There was no limit to the mayhem they could commit.

Since every user was connecting as SA, the only identification possible was by IP address. And since everyone always locks their computer in small company… it was not the best method for identifying a nefarious user.

I did try to get the SA password changed and the account locked down, but it was an arduous process. The old database was delicate, but even more unknown were all the individual reports and local connection interfaces created over the years. Interrupting business was frowned on more than the unlikely possibility of a user misusing the SA account. A few changes got made, but until the database and user interface was rebuilt from the ground up, nothing was actually solved.

Security is a major concern, and something like a global server admin account should always be kept tightly controlled, no matter the company size or trust level.

T-SQL Tuesday #68: Just Say No to Defaults

TSQL2sDay150x150_388014A5This month’s topic for T-SQL Tuesday is hosted by Andy Yun (blog | twitter) with the subject of defaults

This month, I’d like to ask everyone to blog about SQL Server Defaults. While SQL Server CAN be run with just the default settings, doing so is far from ideal. As we progress through our careers, many of us build lists of things we change. Let’s blog about those! And don’t think this month’s topic is constrained to just the SQL Server engine. SSIS & SSAS are absolutely fair game as well. SQL Server developer tools – sure! Want to take a different spin on “defaults,” go right ahead and be creative!

My job almost revolves around building out new SQL servers, so having an organized method is imperative. The list of security changes we make is a mile long. I may not even be exaggerating…However, there are a few changes from the default settings that I feel are the most important. I’m only going to touch on a few avoid an impossibly long read, and the likely redundant mentions.

Add TempDB Data Files

While Aaron Bertrand has some amazing ideas for how TempDB configuration should look during installation, at least SQL 2016 is making some improvements to encourage multiple data files. No one should be using a single data file by default. A good start is to have one data file per core, up to eight. Afterwards, it gets a bit more complicated.

TempDB

If this doesn’t look wrong to you…well I’m sorry.

Adjust the Autogrowth Settings

One of my biggest pet peeves when I look at a new SQL Server and see default autogrowth settings. It just drives me crazy. Just yesterday, I found a database that was about a gigabyte in size, and yet the autogrowth was still set to 1MB. Can you imagine how many times it has to autogrow? That’s a resource hit every single time. Increase your autogrowth to a reasonable size so that it doesn’t have to autogrow every hour or more! Percent autogrowth is even more dangerous, especially for a log file. Logs can grow quickly, especially during something like index maintenance. While 10% of 100 MB may not seem like much, 10% of 1 TB can be temporarily devastating during high traffic times. Even worse, what do you do when your 10% growth triggers but your drive isn’t big enough to handle that?

test

This angers me so much!

Change Max Server Memory

There is a lot to be said about changing the maximum memory allowed by SQL Server. I’ve previously shared a way to get it for all your servers and some suggestions on what to set max memory to. The amount to limit it by varies, but the most important thing is to limit it, otherwise your OS may be memory starved and bring everything to its knees.

Change Cost Threshold for Parallelism & Max Degree

I listed these together because they go hand in hand. There are innumerable debates on what these should be, and it really depends on your server, but almost everyone agrees that the default settings are too relaxed. I’ve seen arguments for a Cost Threshold of 30-60 for a new default. Starting at a number in that range is a good idea. You can adjust it after testing your more problematic queries. However, as more people start using Columnstore indexes, it could mean you need lower thresholds or force serial plans. Perhaps someday we will need a specialized setting to set default cost thresholds for ColumnStores only?

As for the max degree, even Microsoft agrees the default is bad. Changing this appropriately requires knowing your system hardware and maybe doing a bit of math. It should be based on your number of CPUs, but you’ve got some wiggle room depending on how busy your system is. Many queries firing at once indicate a smaller Max DOP, while the inverse is also true.

Change the Default SQL Server Port

Everyone knows that the default port for SQL Server is 1433 and that the Browser uses 1434. You can change the default port. It’s a very easy way to prevent very simple hacking attempts. Sure, you can still be port scanned, but why skip an easy configuration change if it can help your security even by a little bit? You can even take it a step further and blacklist (or whitelist only what you want) any address that attempts to connect through the default port. That requires a bit more planning though.

Make Management Studio Yours

There are a lot more Server settings out there, but I want to mention something about Management Studio. Configure it so that it works for you. Personally, I’m OK with the color scheme, light on dark bothers my eyes, but some people love it.

I always dock my toolbars using AutoHide. I want to get to my registered servers, etc., quickly, but I still need full screen real estate when using the toolbars. Too much stuff.

AutoHide

Finally, my favorite change is to move the Status Bar to the top of the screen. It felt weird the first time, but now it’s distracting each time I open Management Studio for the first time and the bar is back on the bottom. It’s much more natural to see what is going on when the bar is at the top. The problem is, I have to change this so rarely, I normally spend 5 minutes hunting for the correct setting. You can find this under Tools -> Options -> Text Editor -> Editor Tab.

StatusBarI also change the defaults under Tab Text to only Include File Name. Everything else is just fluff that I can find on the status bar.

 

T-SQL Tuesday #66: Monitoring with Utility Explorer

TSQL2sDay150x150_388014A5This month’s topic for T-SQL Tuesday is hosted by Cathrine Wilhelmsen (blog | twitter) with the subject of monitoring.

The topic of the month is monitoring. We all monitor something while working with SQL Server: Maybe you’re a DBA who monitors database performance, an SSIS developer who monitors job and package execution, an application developer who monitors queries or a BI analyst who monitors report usage? Do you use T-SQL or PowerShell scripts? Have you created your own monitoring reports or applications? Have you invested in a complete monitoring solution from a vendor? What is monitored manually and what is automated with alerts? If a brand new SQL Server professional in your role asks you for advice, what do you say is the number one most important thing to monitor? Have you ever stayed awake for a week solving a problem that could have been avoided with proper monitoring? Did you implement a monitoring solution that saved your company?

I currently use so many different methods of monitoring, it was daunting to decide what to discuss. I could have written about a high level overview of everything, but that would have been nothing more than mentioning each one in order to fit it into one post. Policies, jobs, traces, audits, extended events, PowerShell…we use them all. Basically if it is not a third-party tool (spending money on any solution is tough to justify here), my office uses it or will be giving it a shot soon. Coincidentally, we just started using another form of monitoring in the past few weeks, so I will discuss some of the trials and tribulations of using the SQL Server Utility.

Utility Explorer provides a simplified dashboard for monitoring and baselining SQL Server CPU and storage resources. This feature is not available for Standard Edition, like almost anything else new and exciting. To utilize this feature, you will need to create a Utility Control Point (UCP). I’d suggest using your Central Management Server to host. Don’t have a CMS? If you have more than two servers, make one. It’s not hard. Unlike a CMS though, the UCP can monitor itself. This is one of the more obscure features added in SQL Server 2008R2, but you can access it in Management Studio by selecting View -> Utility Explorer.

GettingStarted

After selecting your instance, you need to specify the account to run the utility collection set. Using the Agent account is the easiest way to set up, but you have to use a Windows domain account as the collection account, regardless of which path you choose.

SpecifyAccount

Select the instance where data will be collected. Again, I suggest your CMS. A utility database will be created to store data, and the default collection period is one year, so make sure you have sufficient drive space or be ready to reduce your retention period. No worries though, this new dashboard will remind you if your disk space starts getting low! The utility database will grow about 2GB, per monitored instance, per year (typically), while the msdb database should grow around 20MB, per instance, per year. Warning: if you also use Data Collector, your data growth can be exponentially higher.

SpecifyInstance

Now the selected instance will be validated. The instance cannot be enrolled if it is already a member of another UCP. I’m using Collection Sets as well, so validation failed on that. You can bypass this issue by simply disabling your collection sets during the enrollment process. That will allow validation to succeed. After enrolled, you can turn collection sets back on.. The two pieces are related (they use the same database) and yet separate enough that they don’t always play well together.

Validation

Once you have your UCP created, you can begin enrolling instances, which is a very simple process. Just right-click on Managed Instances, or within the white space of Utility Explorer Content, and select Enroll Instance. Specify the instance name and the agent account, like you did above while creating the UCP, and then validate the enrollment. The instance may throw a warning about WMI. Since it’s just a warning, the enrollment will still succeed. My instances have not had any issues reporting data after throwing this warning. Researching the subject has thus been low priority, but it appears to be a permissions issue. Troubleshooting with that article has shown nothing of concern though…

Test

Once you have a few instances enrolled, open the Utility Dashboard, which will look similar to this.

UtilityDashboard

Select Managed Instances and select the tabs to view CPU usage, storage space, policy configuration for utilization pass/fails, and computer properties. Each enrolled instance will show a pass/fail mark based on the configured policies.

ManagedInstances
The Storage Utilization tab provides a quick look at your volume free space. You can drill down to see data file and log file sizes as well.
StorageUtilization
The Property Details tab summarizes the instances software and hardware.
PropertyDetails

Finally, in the Utility Administration, there are a few settings you need to be aware of. This is where you can set Global policies for storage warnings, set read privileges for user accounts on the Utility dashboards, and specify data retention. I suggest you start small and monitor how fast your sysutility database grows. Like I said earlier, combining this with Data Collection, we had massive data growth, more on that in the future.

Overall, Utility Explorer provides an easy to read dashboard for quick looks at your SQL Servers. If you are a graphical person and can spare the disk space, it’s a great feature. Sure, you can get the current settings via queries, but the really valuable part is the automatic retention which allows quick baselining of your CPU and storage. Capacity planning made easy.

StorageOverTime

T-SQL Tuesday #065 – Teach Something New – APPLY

TSQL2sDay150x150_388014A5

This is my second contribution to T-SQL Tuesday. This month’s topic is hosted by Mike Donnelly (blog | twitter) with the subject of learning/teaching something new.

The topic this month is straight forward, but very open ended. You must learn something new and then write a blog post explaining it. One of the reasons I present and blog is because it forces me to really learn a subject if I am going to explain it to someone else. I am now giving all of you that same opportunity. You’re welcome.

I didn’t see this topic until Friday afternoon, so luckily it is a very open-ended topic. I have to find a better way to track T-SQL Tuesday. Right now the plan is to hope that I see something appear about it in my Twitter Feed. Anyways, I had to scramble together an idea while finding something I could learn and write about in one day. I recently scheduled another article that used the APPLY operator in one of the examples, but it’s something I have yet to use much. This seemed like a great time to explore it.

APPLY has two basic forms, CROSS APPLY or OUTER APPLY. CROSS APPLY works essentially like an INNER JOIN, whereas OUTER APPLY is like a LEFT OUTER JOIN. The primary purpose of the APPLY operator is to evaluate a Table-Valued Function against every row returned by a query. The expression acts as the left input while the function acts as the right input. OUTER applies will return NULL values on the column evaluated by the function and CROSS applies will exclude NULL results – again, just like joins.

The following example is fairly simple, but it displays the SQL script used for currently running processes. The CROSS APPLY example will return fewer results as it filters spids without a sql_handle, basically system processes. The OUTER APPLY will still return the processes, just with a NULL Text column. The APPLY operator allows you to avoid performing subqueries to evaluate Table-Valued Functions.

1) APPLY for Table-Valued Functions

CROSS APPLY

SELECT
 session_id,[status],blocking_session_id,wait_type,
 wait_time,wait_resource,transaction_id,t.[text]
FROM sys.dm_exec_requests r
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) t

OUTER APPLY

SELECT
 session_id,[status],blocking_session_id,wait_type,
 wait_time,wait_resource,transaction_id,t.[text]
FROM sys.dm_exec_requests r
 OUTER APPLY sys.dm_exec_sql_text(sql_handle) t

2) Reuse Column Aliases

First we will create a temp table with some numbers for sample data.

CREATE TABLE #Numbers (Num1 INT NOT NULL,Num2 INT NOT NULL)
INSERT INTO #Numbers VALUES (1,3),(3,5),(5,7),(10,12)

Traditional Script (No Aliasing)

SELECT Num1,Num2,
[SumNums] = Num1+Num2,
[SumNumsTimes3] = (Num1+Num2)*3,
[SumNumsTimes3Plus2] = (Num1+Num2)*3+2
FROM #Numbers

Window Function to Reuse Column Aliases

WITH Alias AS
(
SELECT Num1,Num2,
[SumNums] = Num1+Num2
FROM #Numbers
)
SELECT Num1,Num2,
SumNums,
[SumNumsTimes3] = SumNums*3,
[SumNumsTimes3Plus2] = SumNums*3+2
FROM Alias

APPLY to Reuse Column Aliases

SELECT Num1,Num2,
SumNums,
SumNumsTimes3,
[SumNumsTimes3Plus2] = SumNumsTimes3+2
FROM #Numbers
CROSS APPLY (SELECT Num1 + Num2) a (SumNums) 
CROSS APPLY (SELECT SumNums * 3) b (SumNumsTimes3)

Obviously if you have to calculate anything complex, not being able to use a column alias would require a lot of copying and pasting and room for error. Using a CTE to alias requires nested CTEs for multiple aliases and much more typing. Using an APPLY operator provides the most flexibility and could provide a lot of code reuse.

3. Unpivot

Again, first thing is to create some sample, denormalized data.

CREATE TABLE #ContactInfo
(
 ContactInfoID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 Phone1 INT, Phone2 INT, Phone3 INT,
 Email1 VARCHAR(50), Email2 VARCHAR(50), Email3 VARCHAR(50)
)
INSERT INTO #ContactInfo VALUES
(5555510,5555511,5555512,'ASmith@Test.com','ASmith1@Test.com','ASmith2@Test.com'),
(5555520,5555521,5555522,'BJones@Test.com','BJones1@Test.com','BJones2@Test.com'),
(5555530,5555531,5555532,'CDoe@Test.com','CDoe1@Test.com','CDoe2@Test.com')

Traditional Unpivot

SELECT
ContactInfoId,ContactPriority,PhoneNumber,EmailAddress
FROM (SELECT * FROM #ContactInfo) p
UNPIVOT ( PhoneNumber FOR ContactPriority IN (Phone1,Phone2,Phone3) ) up1
UNPIVOT ( EmailAddress FOR EPriority IN (Email1,Email2,Email3) ) up2
WHERE RIGHT(EPriority,1) = RIGHT(ContactPriority,1)

A basic unpivot won’t work here, as we need to unpivot multiple columns. Luckily you can unpivot twice, but the syntax is a bit weird and you’ll probably have to look it up every time you try to unpivot like this. The WHERE clause at the end filters the results so the result doesn’t become a cartesian join. The RIGHT operator has the results match on just the numerical values of the Email/Phone Priority.

Unpivot Using Apply

SELECT
ContactInfoId,ContactPriority,PhoneNumber,EmailAddress
FROM #ContactInfo
CROSS APPLY (VALUES(1,Phone1,Email1),
(2,Phone2,Email2),
(3,Phone3,Email3)) p (ContactPriority,PhoneNumber,EmailAddress)

Unpivoting this way might take a bit of practice, but the syntax seems a bit more intuitive to me for occasional use. Just remember that you have to define the new column names at the end of the statement. This scales a lot smoother than using unpivot.

4. Select TOP N Results

Using Window Functions

WITH Columns AS (
SELECT
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,
[RN] = ROW_NUMBER() OVER(PARTITION BY TABLE_SCHEMA,TABLE_NAME ORDER BY ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
)
SELECT
t.TABLE_SCHEMA,t.TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.tables t
INNER JOIN Columns c ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.RN <=3

Using APPLY

SELECT
t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.tables t
OUTER APPLY (SELECT TOP 3 *
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
) c

Comparing syntax alone, APPLY is a bit easier to type out, but performance-wise, it depends on your use case, SQL version, etc. I’m used to writing CTEs, so I’ll probably stick with that syntax.

Summary These were all the uses I’ve been able to find so far for the APPLY operator, but please feel free to identify any others you know of. Sometimes the syntax using APPLY is more intuitive than the alternative, but the main use is definitely the originally intended purpose, evaluating Table-Valued Functions.

T-SQL Tuesday #63: How do you Manage Security

TSQL2sDay150x150_388014A5

This is my first attempt at contributing to T-SQL Tuesday. This month’s topic is hosted by Kenneth Fisher (blog | twitter) with the subject of security.

 

 

Security is one of those subjects that most DBAs have to deal with regardless of specialty. So as something we all have to work with at some point or another what are some tips you’d like to share? What’s the best security design? You’ve picked up a legacy system and the security is awful, how do you fix it? Any great tools out there you’d like to share? Hate it or love it I’m betting we all have something to say.

Security is an important subject that can be addressed on countless levels. I’m going to focus on an incredible way to monitor the security settings on any number of servers. This is a topic that I think is very poorly documented, very powerful, and largely ignored by Microsoft and the SQL Server Community. Today’s topic will be Policy Based Management.

Policy Based Management was introduced in SQL Server 2008, and since then has seen very little support. There is basically one book over the topic that only skims the surface over the myriad of options that it provides.  While the book explains the idea very well, there remains very little documentation over what exactly PBM can do.

Basic policy creation is very simple. The problem is wading through the dozens of facets for a condition (what piece of the server the policy is checking) and creating a valid target to evaluate against.

Test

The Policy Creation Window appears simple at first

The difficult part of policy based management is deciding which facet you should use for your condition. Microsoft provides many facets, each with many drop down options. You can stack boolean operators to combine a few fields from one facet, but you cannot evaluate more than one facet in a single policy.

Test

Creating a condition to check that each database is using the default SA account, please rename your account!

Once you have your condition created, you can create a specific target or just evaluate against everything within the facet. Target conditions are grouped together by facet, so if you have a dozen target conditions created for one facet, then create a policy using another facet and are suddenly distressed that your target conditions are missing, remember that targets are unique to a facet.

Test

Policy creation with a valid condition and target selected

Once your policy is created, you can evaluate it against a single server, or use a CMS to evaluate against a selection of servers. That is the real purpose for policy based management. Create a handful of policies on your CMS, then evaluate them all targeting a group of servers. In moments you have verified that your security is correct for all your servers!

Test

Policy evaluation against a single server on all databases

The way that most of my policies are created is through Advanced Edit. This allows you to perform an ExecuteSQL or WMI query targeting anything you can script out for evaluation. The downside of this is that SQL Server will not be able to automatically correct failed policies, but I rarely use that feature. By using Advanced Edit, you can essentially combine facets, and you don’t have to hunt through each facet for the one field you need. It’s much faster, and you can easily convert existing jobs or scripts into a policy by modifying them to return an expected numeric or string value. Below I’ve used an ExecuteSQL statement to verify that each database owner is the default SA account (which you should rename in your real environment) and that the account is disabled. Using existing facets, this would have taken two facets and thus two policies. If  you have figured out a way to do this in a single, built-in facet, please feel free to correct me, but even if you do, creating an ExecuteSQL statement was faster I bet!

Test

Advanced Edit is faster and easier than guessing which facet to use

Currently we use over 700 security policies targeting over a hundred servers. It took forever to make, but audits are a breeze now since we have instant verification of security policies and a history of how often they have been met. Most of the policies are ExecuteSQL statements verifying SQL settings (including Availability Groups, Mirroring, Drive Space, etc.) and Windows settings. We also use Enterprise Policy Management Framework to provide easily read reports and statistics on all of our policies. I highly suggest you take the time to look into this project if you use Policy Management.