Press "Enter" to skip to content

Author: Kevin Feasel

How To Shrink A Database

Published 2016-04-29 by Kevin Feasel

If “shrink that database” makes you cringe, that’s a good sign.  Nevertheless, sometimes it has to happen.  On that rare occasion in which it needs to happen, Andy Mallon gives you a how-to guide:

Perhaps you just implemented data compression, and have 60% free space in your database.
Perhaps you just deleted a bunch of old data, and have significant free space in your database.
There are a handful of scenarios where you have free space in the database, you’ll never use it, and you need to shrink. Just don’t make a habit of it.

Shrink should not be part of your normal routine, but it does come in handy sometimes.  Auto-shrink, though?  Burn that to the ground.

Comments closed

Testing Cumulative Updates

Published 2016-04-29 by Kevin Feasel

Kendra Little discusses how to test cumulative updates:

Install the cumulative update into a non-production environment (prod test or staging) and run application activity against it.

Ideally, you want this to be representative activity for your real, production applications, run from multiple application servers. You also want the hardware and storage to be as similar as possible.

If that’s not possible for you to set up, you need some sort of tool to generate concurrent activity. There are a lot of options — Michael Swart gives a great run down here.

This is some very good advice, and boils down to “test everything that you do in production.”  It’s easy to forget some of these things, though, so I’m glad Kendra enumerated them.

Comments closed

Windows Server Core And SQL Server

Published 2016-04-28 by Kevin Feasel

Slava Murygin walks through running SQL Server on Windows Server core.  First, the steps for installing SQL Server at the command line:

There are three reasons why Windows Server with Core is better than Server with a GUI:
1. Security: Core has less features. That means less surface for intruders to attack.
2. Management: No unnecessary updates and planned reboots.
3. Resource consumption: Less memory usage and less CPU overhead

Even if you aren’t using Windows Server Core, it’s a good idea to be able to script out your SQL Server installation so you can automate it later (or at least so you don’t forget options and have some servers look different than others).

Second, Slava notes that certain features are not compatible with Windows Server core:

The following error occurred:
You have selected a feature that is not supported on Windows Server Core.
The supported features on Windows Server Core are:
Database Engine Services,
SQL Server Replication,
Full-Text and Semantic Extractions for Search,
Analysis Services,
Client Tools Connectivity,
Integration Services,
and SQL Client Connectivity SDK.

That list is a bit more limiting than I’d like, to be honest.

Comments closed

Scripting SQL Server Objects With Powershell

Published 2016-04-28 by Kevin Feasel

Sander Stad provides a script to export SQL Server objects using Powershell:

Scripting SQL Server objects manually through the SQL Server Management Studio can be a tedious task. Last week I published a script to export database objects with PowerShell. I wanted to take this a little further and create a solution to export SQL Server objects as well.

Because this would be a nice addition to the PSSQLLibmodule, this function is also included in the library from today.

I haven’t tried using this cmdlet yet, but it does look handy.

Comments closed

Credentials And Proxies

Published 2016-04-28 by Kevin Feasel

Kenneth Fisher shows how to use credentials and proxies to run external objects (like SSIS packages and Powershell scripts) through the SQL Server Agent:

There are purposes for credentials other than a proxy, but for our purposes you are just going to enter an AD username and password. Just to be even more clear, this is an AD/Windows user. Not a sql server login.

In Object Explorer: ServerName -> Security -> Right click on Credentials and select New Credential -> Fill in the Name, Identity and Password fields.

Kenneth’s getting fancy with animated GIFs, and gives us a good walkthrough of this aspect of SQL Agent security.

Comments closed

Polybase

Published 2016-04-28 by Kevin Feasel

Ayman El-Ghazali talks Polybase:

HDFS is a distributed file system that works differently than what we’re used to in the Windows OS side of things; the general principle is to use cheap commodity hardware that replicates data in order to account for availability and to prevent loss of data. With that in mind, it makes a great use case to store a lot of data cheaply for archiving purposes or can be used to store large quantities of data that been to be processed in large quantities as well.

For more information please visit: https://msdn.microsoft.com/en-us/library/mt143171.aspx

Now if you want to try it out for yourself, make sure you install the PolyBase Engine (from the SQL Server setup) and feel free to try the modified code sample below.

Polybase is, without a doubt, my favorite SQL Server 2016 feature.  I am excited to put this through its paces in a production environment.

Comments closed

Primary Keys On TVPs And Plan Forcing

Published 2016-04-28 by Kevin Feasel

Michael J. Swart notes that you cannot force query plans if you’re using a user-defined table type with a non-named primary key constraint:

When defining table variables, avoid primary key or unique key constraints. Opt instead for named indexes if you’re using SQL Server 2014 or later. Otherwise, be aware that plan forcing is limited to queries that don’t use these table variables.

Helpful advice when dealing with user-defiened table types.  Read the whole thing.

Comments closed

Registered Servers

Published 2016-04-28 by Kevin Feasel

Erik Darling shows how to set up registered servers in Management Studio:

And if you hip and hop over to the Connection Properties tab, you can set all sorts of nifty stuff up. The biggest one for me was to give different types of servers different colored tabs that the bottom of SSMS is highlighted with. It’s the one you’re probably looking at now that’s a putrid yellow-ish color and tells you you’re connected and that your query has been executing for three hours. Reassuring. Anyway, I’d use this to differentiate dev from prod servers. Just make sure to choose light colors, because the black text doesn’t show up on dark colors too well.

In team environments, I’m more a fan of Central Management Servers.

Comments closed

AcquireConnection Failures

Published 2016-04-27 by Kevin Feasel

Ginger Grant explains what to do when you get AcquireConnection errors:

AcquireConnection method call to the connection manager failed with error code 0xC0202009/0xC020801C

Try as I might, the only thing I was able to do after an hour was periodically change the error code from 0xC0202009 to 0xC020801C. Nothing I did worked. I created a new connection, created a new OleDB Source, changed the Run64BitRuntime to False in Configuration Properties in the Debugging Section of the project execution, set the Data Flow task DelayValidation from False to True. None of these various suggestions that I got from various websites worked at all.  I thought about changing the SSIS Service ID’s execution properties, but since I was running in Debug mode I determined that this would not make any difference, so I abandoned that idea. Nothing worked. The only thing I was able to do was change the error code, not eliminate it. I could log into SQL Server with the same ID and password in my package and run the simple query in the data flow task and return data.  I could preview the data, what I couldn’t do is execute the SSIS package.  Out of desperation I rebooted, which also did nothing.

SSIS security issues.  Gotta love ’em.

Comments closed