Category Archives: SQL Admin

MTC Chicago Visit

Microsoft has datacenters that are open to customers for training and consulting of all kinds. I’ve been to the newer one in Detroit and to the MTC Chicago twice now with the most recent time being this week. It was a good visit sponsored and encouraged by our storage vendor.

The key topic being ways to manage copies of data. We have some specific needs surrounding reporting, agile development, and quality assurance that request several copies of large databases. Thin provisioning, compression, and deduplication help control costs but several arrays do best when the copy is a snapshot done at the block level. This reduces the cost of having that copy and keeping it around. These users however, generally want to create the copy at the database level. This has a few complexities, even in a fairly standard virtual environment.

Some of these copies require the source system to be quiesced so the snapshot technology needs to integrate with your database technology to cause a pause in writes while the snapshot completes. SQL handles crash consistency very well, but if some require quiescing, I may as well setup a system that does that for all of them. The virtual layer has abstraction layers between what the guest operating system sees and what the storage system presents. This also needs to be coordinated. All of these separate scripts that use different APIs need to be scheduled and some needs to be able to be called from another application or run on demand.

This problem has been mostly solved by some of the automation I have done surrounding backup and restores. We have a working solution but it takes technical time and more storage than it needs. Whenever you have a solution that is good enough, it prevents you from creating a great solution. It is a good solution up to a point until the number of copies, and time it takes to make a copy become prohibitive.

It was a great trip to see another datacenter in action that has the up and coming technologies. It gave me motivation to work on the great solution instead of settling for one that barely works.

Leave a comment

Posted by on July 29, 2016 in SQL Admin, Storage


Learning about an old join syntax, the hard way

Today I discovered that there is an interesting way to join tables

select, o.order_amount 
from customers c, orders o
where *= o.customer_id

But before you get excited, this syntax has been long gone for years. *= is no more.

We have had a critical system that started a never-ending migration from one version of the application to another. Remaining were a few sets of users that were still on the old version for a couple years. I kept putting off upgrading the SQL Server in hopes that it would one day go away but the time came that I couldn’t wait any longer. In a farm of 200+ SQL Servers, this was my last SQL 2005 server.

During the migration planning I took a trace and ran it through upgrade adviser. It was about a 15 minute trace during peak business hours and captured a decent amount of activity. Everything checked out with no critical findings. We did discover that the backup client would need upgraded but that would be done anyway as part of the parallel server upgrade.

I got a little greedy, skipped Windows 2008, and spun up a Windows 2012/SQL 2012 server and restored a copy of the database to that server. Some testing was done but not quite enough.

When the big day came to migrate the database to a new server, I followed these steps
1. restore the most recent full backup and leave it in recovery (done a day before)
2. set single user mode and disable all the user accounts on the old server
3. take a differential backup on the old server
4. restore the differential and recover the database on the new server
5. power down the old server and change its host A record to a cname to the new server (I’d rather change connection strings, but this thing is really old and we couldn’t be sure we found them all)
6. Map logins and fix any orphans
7. Take a full backup and start a new log backup chain

Everything went better than expected. I actually planned for 3 hours of downtime in case I had to do a full backup and restore for some reason. It ended up only taking 20 minutes and I could see the clients automatically reconnecting so things looked good. We had planned a thorough checkout, but after the 3 hours so there was about 2 hours and 40 minutes of users connecting before we were really sure everything was ok.

As it turns out, my upgrade adviser trace wasn’t enough data. This old syntax was used in some startup procedure so it didn’t show up in the trace. I’m not sure if upgrade adviser would have caught this but the deprecated code is clearly documented.

Another interesting thing I learned was SQL2012 does not have the SQL 2000 compatibility level for databases available. That means during my restore, the database was upgraded to 2005 and this old syntax broke. However, SQL 2008 R2 does have the SQL 2000 level available.

I was considering aborting, changing dns back, powering down the new server and powering up the old server. That would have been relatively quick and I could have had my day back. But, that would sign myself and others up for another weekend maintenance window.

I decided to double check this was the problem and I powered up the old server and verified that the query worked on the old database server and not on the new database server. I then checked another 2008 server and tried the syntax out on another old database I found, sure enough it worked so I had another option.

I took the next couple hours to build a new VM, install SQL 2008. I followed the same steps as before and fortunately this time things worked out much better. Just a 6 hour upgrade that should have taken 20 minutes. If you have any SQL 2005 servers still around, waiting until that April 12th end of support deadline, make sure to watch out for the “*=”.

Leave a comment

Posted by on January 12, 2016 in SQL Admin


The many different approaches to performance tuning SQL Server

Ever since we started the GLASS user group this spring, I’ve had the idea that we would have a lightning talk style meeting. This is where we have several shorter presentations instead of one long one. My goal was to get newer speakers a chance to dip their toes in the water and help build on a full session that they could present later.

Everyone has a different approach to tuning SQL Server. Different is good, at least on this topic. There can be a lot of friction when trying to troubleshoot where the slowness is happening especially when an organization has a lot of silos. If the tier 1 support has to talk to tier 2 support who has to talk to a developer who has to talk to a server admin who has to talk to a dba who has to talk to a storage admin who… you get the point. I want to get as many perspectives of real world solutions to performance problems together in the same room. Some may think of it as a WWE style smackdown but I think the collaboration would be insanely beneficial.

I couldn’t have been more right :]

We had Kyle talk about implicit conversions specific to SSRS, Mike talk about partitioning, Dave talked about the optimizer, Tom talked about the speed of a single DECLARE or multiple DECLARE statements and I wrapped it up with performance triage with metrics, queries and real world fixes.

The performance tuning process is just that, a process, not a single answer to a problem. There are several ways to approach slowness of an application, and it depends on the situation of how you proceed. Dive right into active queries? Look at the VM CPU graph? Fire back with a bunch of questions? I’ve personally taken all of these angles and found some successes, and a bunch of failures along the way.

Leave a comment

Posted by on September 20, 2015 in PASS, SQL Admin, SQL Dev


Final Preparation for 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012

This is a continuation of this post

Two fellow bloggers have been posting more quality information on this test.

When reading the book I skipped over all of the practice sections. I did read the exam tip sections inside of the practice but never actually practiced. I don’t have a lot of hands on experience with SSIS and even less with mds/dqs. I spent about 9 weeks making through the book while skipping the practice and most of the reviews. I probably would have needed an additional 18 weeks to properly make it through all of the practice or lab type sections of the book. Learn one, do one, teach one is my favorite method to mastery but with 2nd shot deadline, I didn’t have a lot of time to prepare.

To supplement, I attempted to find videos on youtube and watched videos on the Microsoft Virtual academy. Both sources were not very demo heavy. What I did find is CBT nuggets that give a 7 day trial. The 70-461 videos that I was able to watch were very high quality, fast paced and demo heavy. This is exactly what I needed at this time. I’d recommend a membership if you have a bundle of money burning in your pocket.

Since my trial was up I decided to type up my CBT nugget notes.

CBT connections managers
control flow -> doesn’t involve data
bottom level are private connection managers, a.k.a package level
right solution explorer is project level connection managers which are global
you can enable/disable sequence containers
precedence constraints, go to properties to define AND or OR logic
copy-> paste package connection managers
delay validation -> doesn’t check structure
email doesn’t have a port option but could purchase add-ins or write your own
fix for NULLs is COALESCE

Data Flow
rows, buffers, pipeline,transformations
raw file -> ssis only -> good for sharing data between packages
raw file -> good for resuming packages
recordset->variable used to loop through
for performance, aggregate at the source since that is blocking
import export col -> for blob data
term matching is like CTRL+F
blocking tasks take lots of memory -> sort, aggregate
partial-blocking -> merge chuncks

Data Quality services
cleansing matching
server is 3 databases
dqs client is used for creating KBs
creating a knowledge base
-open xls sheet -> job title list for KB example
-KB needs a domain, circle with * button is domain
State length of 2 is an example domain rule
composite domain (EX: address which includes city state zip)
reference data source RDS (ex: mellisa data for addresses)
KB’s get published
activity is automatically logged

Implementing DQS
data profiling task in SSDT
-profile types
–null ratio request
–pattern generator RegEx for formatting
–column statistics
-then specify column
Quick profile: runs against all columns
Open data profile viewer
suggested confidence level
corrected confidence level
DQS cleansing task
Job title source job_title _output
jobtitles table
newKB->domain->source column (survivor record)
the table with the + button to add a rule and use the Rule Editor

Implementing MDS
proactive management
people place concepts or things
non-transaction data is good for MDS
includes auditing and versioning
MDS Componenents(Database, config mgr, MD mgr, web service, mds model deploy, excel Add-In)
MDS Objects(Models: the container db, Entities: like tables, Attributes: like columns, Hierarchies, Members: Actual data)
Install requires powershell 2.0 and IIS 7.5, silverlight and a database
has integration with DQS
to deploy packages that contain data must use CLI (deploynew -package “” -model)

Data flow
merge join requires sort -> advanced editor, pick isSorted and the column
MetaData problems: double click on flow and change types
Lookup transformation
-cache connmgrs for re-use
–redirect rows
–multi output popup
slowly changing dimension task (wizard)
fixed attribute fail on change
changing attribute type 1 overwrite type 2 new records (history)
inferred member flag goes in dimension
blocking oledb command
redirect error rows to flat file

executing packages
dtexec.exe is fire and forget style
built-in SPs in ssisdb
catalog.set_obj_param value
restartable packages
-checkoint file
-tracking last successful step in control flow
project properties
-select file name
-set usage never
–if exist
-save checkpoints = true
-set property fail package on failure = true
to test, can set task property to force a failure

Leave a comment

Posted by on April 9, 2015 in SQL Admin, SQL Dev


More Preparation for 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012

This is a continuation of my previous post. This is just some very quick notes that I am posting for my benefit and so that readers may get an idea of the preparation necessary for this test. They are my notes from this book:

PART II: Developing SSIS Packages

simple data movement – can use import export wizard
complex data movement – SSDT
SSDT is visual studio shell used to develop IS,AS,RS projects
Control Flow connection managers can be package or project scoped
Connection manager types:
ADO – backwards compatibility – compatible with sql server
AS – analysis services
File – SSIS data type
Flat file – delimited file
ftp – security option is only basic auth
http – web services or file, no windows auth
OLE DB – sql server, will be removed in favor of ODBC
ODBC – open database connection
SMTP – basic email auth only

package scoped connection managers will override the higher level project scoped connmgrs

control flow tasks and containers
containers help control execution of tasks
transformations include
cleansing – remove invalid data or unwanted data
normalization – XML value to varchar
conversion – byte[] to varbinary(max)
translation – “F” to “Female”
data calculation and data aggregation
data pivoting and data unpivoting

ssis tasks categories, data prep, workflow, data movement, SQL admin, SQL maintenance

containers, for loop, foreach loop, sequence

Precedence Contstraints ( the arrows that come off of tasks)

success, failure, completion
dotted lines mean OR and solid means AND logic used when multiple tasks are involved in flow

Designing and Implementing Data Flow

Data Flow is a level deeper than the control flow
Control flow triggers data flow
data flow task builds execution plan from data flow definition
data flow engine executes the plan
*Validate external metadata – checks for existance of tables and objects and should be turned off if they are dynamically created
builk OLEDB = fast load
ODBC = batch
fast parse is available at the column level on some data types ( date, time, int )
Working with data flow transformations
-Blocking (ex: sort, aggregate) transformations that read all data in before passing any rows down the pipeline
-Non-Blocking -> lookup, multicast, conditional split or other row-by-row transformations
-partial-blocking -> merge, merge join, union all, data flows in chunks
cache transformations – good for multiple transformations on same data
import/export col – good for blobs
character map – upper case, lower, linguistic bit operations
advanced data prep: dqs cleansing, oledb command, slowly changing dimension, fuzzy grouping, fuzzy lookup, script component(
#NEW# Resolve references editor helps resolve mapping problems
Lesson 3: strategy and tools
lookup transformation caching
how to handle rows w/ no matches
sort is expensive, optionally perform sorts at source and use advanced editor to mark data as sorted
avoid update and delete on fact tables
do large table joins on database layer
do updates on loading or temp tables in set based sql operations
Chapter 6: Enhancing Control Flow
ssis variables and parameters
avoid retrieving external source variables more than once
parameters are exposed to the caller bu variables are not
parameters are read-only and can only be set by the caller
variables are helpful to reuseability
variables are user defined or system
variables can store rows foreach enum containers
-avoid storing large rowsets in memory/variables
variable data types
-object: last resort
Int16: -32,768 thru 32,768
UInt16: 0 thru 65,535
UInt32: 0 thru 4,294,967,295
Char: 65,353 unicode
Decimal: 28 or 29 significant digits
Variable Scope
-Package Scopre
—-Container Scoped
——–task scoped
property parameterization
explicit assignment
lesson 2: connmgr, tasks, and precedence constraint expressions
expression: combination of constants, variables, parameters, column refs, functions, and expression operators
-special ssis syntax close to C++
math functions: ABS, EXP, CEILING, etc…
precedence constraints can use AND/OR logic expressions
Lesson 3: Master Pakcage
just a normal package that uses the execute package task
use variables to expose results to parent
use project deployment model to make parameters available to child packages
use project scoped parameters
CHAP7: Enhancing Data Flow
Lesson 1: Slowly Changing Dimesions
-late arriving dims or early arriving facts
–1. insert row into dim, mark inferred… requires bit col
–2. use newly created surrogate key
–3. when loading dim overwrite inferred members
TYPE 1 SCD: overwrite
TYPE 2 SCD: keep all history
can use conditional split to see what columns changed
ex: source.fullname dest.fullname
using t-sql hashbytes can compare for changes
–then two cols for hash val Type1 & type2
use set based updates instead of wizard
Lesson 2: preparing a package for incremental load
dynamic sql
change data capture
Dynamic SQL in OLEDB source
1. select dataaccess mode of sql command and use ? to pass parameter
2. pass variable to sql command and use expressions to modify the sql string
cdc functionality – cdc source and cdc splitter
-ALL, ALL w/old, net, netw/update mask, net w/merge
lesson3: error flows
route bad rows – fail, ignore (copies null), redirect rows
chapter 8: creating robust and restartable packages
can set transactions at package control flow or task level
transactions use msdtc
transaction options are: required, supported, not supported
transactions work on control flow not data flow
can nest a not supported execsql that won’t rollback inside a transaction (ex: still want to audit on fail)
lesson2: checkpoints
save checkpoints need turned on, on package
creates a file and restarts if exists
starts from begining if not exists
lesson3: event handlers
can turn event handlers off for task
chapter 9: implementing dynamic packages
project level and package level connection mgrs and paramters
must be deployed to ssis catalog
parameter design values are stored in the project file
cannot change parameter value while package is running
property expressions are evaluated on access
lesson2: package configs
enable package deployment model
can get parent package configs
chapter10: auditing and logging
logging: package configuration
auditing: dataflow trnasformation component
lesson1: logging packages
providers are: txt file, sql profileer, sql server, event log, xml
boundry progress exception
use parent setting is default
ssis control flows can be configured for logging
lesson2: auditing and lineage
elementary auditing – captures changes
complete – adds usage or read activity
audit transformation editor
lesson3: preparing package templates
keep packages in source control

Part IV: managing and maintaing ssis packages
ssis service is required in production
ssisdb new
package install utility is legacy
can use ssdt or ssms to deploy packages
project model or package model
dtexecui is legacy
can use TSQL, powershell, manual dtexec cli to execute packages
agent to schedule packages
introduced master package concept
securing packages: uses sql security concepts of principals and securables
ssis_admin role
ssis_user by default allowed to deploy, and deployer is allowed to read, modify, execute
Chapter 13: troubleshooting and perf tuning
breakpoints work only in control flow
breakpoints and fire on a hit count
data viewers on path will show grid view of data
use error outputs to catch bad rows
test with a subset of data
basic logging is default
switch to verbose when there are problems
data taps are like dataviewers for production
must be predefined using catalog.add_data_tap for specific data flow
lesson2: perf tuning
buffers are a group of data in data flow
determined automatically
Transformation Types
-non-blocking: row based synchronous
-partial blocking: asynchronous transformation
-blocking: asynchronous
backpressure controls flow for best memory control
max buffer rows – 10,000 default
max buffer size – 10MB by default
fast load on destination
full-cache lookups
avoid oledb transformations
BLOBs get swapped to disk
data flow engine threds
max concurrent executables -1 = # of logical processors +2
perfmon counter: buffers spooled

PART V: Building Data Quality Solutions

chapter14: installing and maintaining DQS
Soft dimensions: timeliness, ease of use, intension, trust, presentation quality
hard dimensions: accuracy, consistancy
Schema dimensions: completeness, correctness, documentation, compliance w/theoretical models, minimalization
activites: understand sources and destinations
security and backups managed through ssms
Chapter15: implementing MDS
metadata, transactional, hierachical, semi-structured, unstructured, master
MDM goals: unifying or harmonizing, maximize ROI through reuse, support compliance, improving quality
MDM: coordinated set of tools policies to maintain accurate master data
map master data dimensions to DW
Installing MDS: DB, Service(Needs IIS), Manager, Excel Add-IN
Creating MDS model
2.Entities(like tables)
3.Attributes(like columns)
Derived hierarchies: Recursive with TOP = NULL (ex: Org Chart)
Explicit Hierarchies – Organization can go any way
Collection: flat list of members
MDS service performs business logic
Chapter16: managing master data
MDS Packages
-Model deployment package to move data to another server
-wizard only includes meta data
-permissions are not included
-MDSModelDeploy command prompt if you want to move data
exporting – tsql on subscription views, webservice
Security, system admin (one user, tsql to change), model admin (complete model access)
entity permissions apply to all attributes
mds add-in for excel (connect to to http://server:8080)
when model and member permissions are overlapping read-only > updated and deny > *
excel add-in can use DQS KB matching
Chapter17: creating a data quality project to clean data
knowledge disovery
domain managment
reference data services
matching policy
domain: semantic representation of column
properties: data type, leading values, normalize, format, spellchecking
Term basic relation: Inc. -> Incorporated

I skipped 18,19,20: Advanced ssis and data quality topics because only 5 parts are listed on the exam prep and I ran low on time.

1 Comment

Posted by on April 8, 2015 in SQL Admin, SQL Dev


Reasons you can’t connect to SQL Server

“I can’t connect, can you look at the logs?”

Nope, not today, this is not how we do this. How is the server to LOG if it never receives the request? Do you think the server somehow magically anticipated that you wanted to run a query for the first time? What server are you even talking about???

Connection errors are generally logged on the client side. First read the message carefully and thoroughly for best results. The majority of common errors can be broken down into three categories:

Client issue
Network issue
Server issue

The nature of the word “Connection” means there is some fuzzy areas where two of the three CNS creatures rub uglies. There is a network adapter on the client and a network adapter on the server, and well.. there is a network.

Lets look at one of my more popular reasons you can’t connect to SQL Server, Login Failed.

So which is that, C… N… or S? I can pretty much rule out the network since the client received a message from the server. Maybe it is not even an issue at all, it is a feature I implemented to prevent you from logging into a production server. I really want to put it in the server category, but as I look back on actual cases, it is mostly the fact that access was never requested until it didn’t work. So that is a layer 8 issue with the planning protocol.

Long story short, I really wanted to categorize this list and also provide solutions but it really depends on the situation of the error. Hopefully, this list doesn’t grow much more since I have gotten better at anticipating people who may eventually want to connect to my databases. Without any further complaints, here are the reasons off the top of my head that you can’t connect to SQL Server:

1. You don’t have login access
2. Your login doesn’t have connect privileges
3. The Windows firewall is blocking you
4. The network firewall is blocking you
5. The login doesn’t have public access to the database
6. The server is out of memory
7. The server is actually down
8. The database is in single_user mode
9. The service account is locked out
10. SQL Authentication isn’t enabled on the server
11. You are trying SQL Auth when you should be using Windows Integrated
12. You are typing the password wrong
13. The login is locked out
14. The login is disabled
15. Server cannot generate the SSPI context
16. The service was started with option -m (single user)
17. The vmware host doesn’t have the correct vlan defined
18. The SQL Server’s ip configuration is wrong
19. The network switch doesn’t allow the vlan on that port
20. The distributed switch doesn’t have LACP enabled on that port group
21. The SQL Service is being updated
22. The Windows server is being updated
23. You are not specifying the non-standard port
24. You have the wrong instance name
25. You have the wrong server name
26. You have the wrong port
27. You communicated the wrong port to the network firewall admin
28. You are using the :port syntax instead of the ,port syntax
29. SQL is not set to listen on TCP/IP
30. You ran the C: drive out of space causing a cascading failure
31. You are not connected to the VPN
32. You are connected to the guest wifi
33. You bumped the wifi switch on your laptop

Leave a comment

Posted by on January 21, 2015 in SQL Admin, SQL Dev


5 9s Lead to Nestfrastructure (and fewer 9s)

Off the top of my head,

Microsoft DNS issue a handful of hours before xbox one launch(

Widespread Amazon outages (


The POTUS’s baby (

I learned about 5 9’s in a college business class. If a manufacturer wants to be respected as building quality products, they should be able to build 99.999% of them accurately. That concept has translated to IT as some kind of reasonable expectation of uptime. (

I take great pride in my ability to keep servers running. Not only avoiding unplanned downtime, but developing a highly available system so it requires little to no planned downtime. These HA features add additional complexity and can sometimes backfire. Simplicity and more planned downtime is often times the best choice. If 99,999% uptime is the goal, there is no room for flexibility, agility, budgets or sanity. To me, 5 9s is not a reasonable expectation of uptime even if you only count unplanned downtime. I will strive for this perfection, however, I will not stand idly by while this expectation is demanded.

Jaron Lanier, the author and inventor of the concept of virtual reality, warned that digital infrastructure was moving beyond human control. He said: “When you try to achieve great scale with automation and the automation exceeds the boundaries of human oversight, there is going to be failure … It is infuriating because it is driven by unreasonable greed.”

IMHO the problem stems from dishonest salespeople. False hopes are injected into organizations’ leaders. These salespeople are often times internal to the organization. An example is an inexperienced engineer that hasn’t been around for long enough to measure his or her own uptime for a year. They haven’t realized the benefit of keeping track of outages objectively and buy into new technologies that don’t always pan out. That hope bubbles up to upper management and then propagates down to the real engineers in the form of an SLA that no real engineer would actually be able to achieve.

About two weeks later, the priority shifts to the new code release and not uptime. Even though releasing untested code puts availability as risk, the code changes must be released. These ever changing goals are prone to failure.

So where is 5 9s appropriate? With the influx of cloud services, the term infrastructure is being too broadly used. IIS is not infrastructure, it is part of your platform. Power and cooling are infrastructure and those should live by the 5 9s rule. A local network would be a stretch to apply 5 9s to. Storage arrays and storage networks are less of a stretch because the amount of change is limited.

Even when redundancies exist, platform failures are disruptive. A database mirroring failover (connections closed), webserver failure (sessions lost), a compute node (os reboots) and even live migrations of vms require a “stun” which stops the cpu for a period of time(a second?). These details I listed in parentheses are often omitted from the sales pitch. The reaction varies with each application. As the load increases on a system these adverse reactions can increase as well.

If you want to achieve 5 9s for your platform, you have to move the redundancy logic up the stack. Catch errors, wait and retry.


Yes, use the tools you are familiar with lower in the stack. But don’t build yourself a nest at every layer in the stack, understand the big picture and apply pressure as needed. Just like you wouldn’t jump on every possible new shiny security feature, don’t jump on every redundancy feature to avoid nestfrastructure.


#SQLSatDet has made the front page

The short list of upcoming events now includes SQL Saturday #292 in Detroit

Free training, free networking and only $12 for lunch. Best you cancel your plans for May 17 and find your way to Lawrence Technological University.

The speakers who submitted by the original deadline have been confirmed for at least one session. That means you will have a chance to listen to me talk about SQL Server Security in my Hacking SQL Server session. I really enjoyed speaking last year at this event and look forward to this years event including all the pre and post activities.

Here is my recap from last year:

Leave a comment

Posted by on April 10, 2014 in PASS, Security, SQL Admin



Toying with In-Memory OLTP

In six days the bits for SQL 2014 RTM will be available for download. I decided to fling myself into its hot new feature of In-Memory OLTP with the CTP2 release. I’ve attended one user group that gave an overview of the feature set ( Thanks @brian78 ) but other than that I have not read much technical information about In-Memory OLTP.

One advantage point that seems to pop up in literature surrounding the release is the ease of implementation. Not all tables in a database have to be In-Memory and a single query can seamlessly access both classic disk based tables and In-Memory tables. Since the product isn’t released yet, the information available on the featureset is heavily weighted towards sales. I wanted to see if achieving the 5x-20x performance boost was really as easy as it sounds. Instead of my usual approach of collecting lots of information and reading tutorials, I decided to blaze my own trail.

The first thing to do is create a new database. I noticed a setting that I heard referenced in the overview called delayed durability.


Scripting the new database out in T-SQL also shows this new setting. I’m assuming this will make things faster since they don’t have to be persisted to disk right away.


Before I run that script I decide to poke around a bit more. I see some In-Memory settings over on filestream. I’m not sure if that is a necessary requirement or not, but I am going to add a filegroup and file just in case.



Now that the database is created I want to create a table. There is a special option in the Script-to menu for In-Memory optimized tables. I’ll create a few dummy columns and try to run it.


There seems to be a problem with my varchar column. “Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.” Well that is unfortunate, I suppose I will change the collation in this test but that won’t be easy in real life.


After changing the collation I am able to create my memory optimized table.


I wondered if there would be any way to tell in my query plan if I’m actually optimized. It doesn’t appear so…


Was that a 5x performance boost?? I’m I doing it right?? Not sure, but for now I need to take a break.

I’m hoping ISVs start supporting this feature but it might be a lot more work than advertised. After getting that error I found a list of many things that are not supported on these tables and in the compiled stored procedures.

This list does not encourage me to blaze new trails and start testing this as soon as it comes out. I prefer to wait a bit and let the other trail blazers blog about the issues they have.

Leave a comment

Posted by on March 26, 2014 in SQL Admin


SQL Server Backup Infrastructure

What are your backups sitting on? How fast are restores? Compression? Dedupe? Magic? There are lots of questions to be answered before buying storage for backups.

What is your retention policy?

Space requirements vary greatly with compression and dedupe but nothing has a greater affect on space than the retention policy. If your legal department gets involved you may have “indefinite retention” on some or all of your backups. That means you can’t delete any backups. Better get that storage vendor on speed dial.

A more realistic retention policy would be 30 days of nightly backups. Another approach would be to keep a week of nightly backups, a month of weekly backups and a year of monthly backups.

What exactly are you sending over the wire?

Unless you are backing up on the same server or SAN, something is going over the wire. That wire is usually the bottleneck in a well tuned environment. A well tuned environment is actually shaped like a can than a bottle but you get my point.

A full backup would mean all data, or a copy of space used in your database is going over the wire. A differential would send only changed extents since the last full. Turning on compression reduces the size of these files by 50%-80% in my experience. SQL 2008 and up can natively apply this compression or you can use a 3rd party tools from Quest or RedGate to send less data over the wire.

EMC’s Data Domain Boost is not yet publicly available as far as I know but it’s worth mentioning. The generic data domain would be a full uncompressed copy of your data would have to go over the wire. That would be bad ( But with the addition of DDBoost, an integrated tool that is supposed to send only unique data over the wire, we have a possibly workable solution. This is slightly better than differentials which send change data over the wire, night after night until another full is taken.

Watch out for the simultaneous in/out.

One thing that cropped up and bit me in the arse was backups going in and out. This can happen in a couple different scenarios. For starters, heaven forbid you actually have to restore a database during your backup cycle. Can the drives and network support that operation? Or is your restore going to crawl?

Another time this can happen is if you are forced into backing up your backups. Say you have indefinite retention and backups have to be sent to tape. Depending on how fast things are, you might be reading and writing to disk at the same time. You might also be sending and receiving data over the wire at the same time.

Are you sending these backups offsite? If so that might be another opportunity to have multiple simultaneous ins and outs. If you tuned your system for only one operation at a time, you might want to rethink your RTO.


Unless you are restoring your database and running checkdb, you have to assume your backups are not good. Scrubbing is the process of verifying the data written long ago is still good. Some appliances have this process built in so they can at least verify the bits that were written are still the same. A small problem can be blown up with dedupe or compression. Small problems in a backup file can cause restores to fail and then you will have to call in the experts in data recovery.

Reused tapes would frequently have errors. I don’t know anyone backing up to SSDs but early models had some problems failing. That said, good old fashioned, enterprise class HDDs make a good home for data. Adjust your scrubbing intensity with your experience. Make sure you are not causing more problems than solving. This process might be pushing a whole lot of IOPS to a shared storage system. Know who your neighbors are and don’t piss them off.


I like to simplify a baseline throughput measurement into a simple clock time. A round of full backups took X amount of time. This translates well to the business who will be paying additional money so that you can achieve their RTO. That said when tuning the system we have to look at the throughput of each potential bottleneck.

The wire is generally the bottleneck. 1Gbps = 125MBps. Make sure you understand the difference in network terminology and storage terminology when it comes to bits and bytes of throughput. If you want to sound like an amateur, just say “MEG” or “MEGS” when the details really do matter. Your mileage may vary but I have not experienced 10x improvement when switching to 10Gbps from 1Gbps network ports and adapters. Tuning your MTU size across the network to support larger frames (aka jumbo packets) can help to utilize more bandwidth. Teaming multiple network cards can increase network throughput. Dedicating a backup network team of NICs can help with a busy SQL server that has users during the backup window.

I have experienced a RAID5 SATA configuration have the ability to write sequentially 500MBps. If you are not concerned about the simultaneous ins and outs, or potentially random workload from scrubbing, the storage cost can be really low. If you want offsite replication built into the storage product, costs will increase very fast. If you can do this with robocopy and little to no WAN acceleration, a simple pool of NAS drives could be a viable option.

Dedupe and compression can actually cause CPU contention on the SQL server if it is busy during the backup window. This is important to be aware of for test VMs. Test VMs might have a full set of data, but only 1CPU. This might be fine for users but it could be slowing down a backup stream. In a virtualized environment you may not want to kick off all of your backups simultaneously. Instead try to schedule backups in streams that you can adjust for the best performance. It is easier to setup and maintain streams than staggering start times.


I highly recommend a solution that gets a copy of your data off server, off SAN and offsite as quickly as possible. I suggest keeping 1 backup set onsite and 1 backup set offsite. This allows for fast restores and disaster recovery.

You may not have a superdome with 32 NICs but this is still a good read:

Leave a comment

Posted by on November 7, 2013 in SQL Admin, Storage