RSS

Category Archives: Uncategorized

Query SQL Server from PowerShell

Consistency is king. Either consistently broken, or consistently working, automation will help you get there.

PowerShell has come a long way. Early on I avoided it and I’m happy I did that. Still to this day, we don’t have a consistent version of PowerShell across our servers. Execution policies are broken. All the code examples online didn’t provide me with a simple way to do the garbage in, garbage out data processing I needed. Time and time again, my first couple attempts would work, then I would find that some servers just wouldn’t cooperate. Error handling was a bear. But there were so many blind fans out there who would look down their nose at me I started to wonder if I was in the wrong.

Fast forward five more years and PowerShell has gotten a lot better. Working with rest APIs, tight integration with VMware, SharePoint, Exchange, and easy .csv manipulation got me started and learning the language. But even today, we still don’t have an elegant way to manage data with SQL Server.

The power is in the “|” for most things PowerShell. With great power, comes terrible error handing. Most things I will end up doing don’t really need to pipe data from one cmdlet to another so I’ve tossed in the towel for now.

Getting Started…

First, get administrator on your windows machine.

Then, lets get the latest bits. SQL Server 2016 Developer Edition is now free. That really helps us bloggers and speakers not have to load up a 180 day evaluation edition and then find out it has run out right before our talk. SSMS is also free

After checking out your local instance and connecting with SSMS to make sure you did everything right, lets enable powershell by doing the dreadful execution policy changes and module import.

Run powershell as administrator and these two commands. The first one giving you some information to switch it back if needed.

Get-ExecutionPolicy
Set-ExecutionPolicy unrestricted

For the remainder we can run without administrator.  I’d suggest opening up the Windows PowerShell ISE and you can get some syntax highlighting in the top Script window. To do anything with SQL we’ll want to run this command

Import-Module SqlServer

Phew! That was a lot of setup.

Lets actually write some code!

I am not a fan of one liners. Its great in a conversation to say that was 1 line of code to trivialize the complexity but we are realist here and we are going to use some variables so we don’t have to feel like we are reading the worlds longest run-on sentence.

$ConnectionString = 'Server=localhost;Database=VMDB;Trusted_Connection=true'
$sql = 'SELECT name,physical_name FROM sys.database_files'
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

If the stars have aligned, after you run those three commands, you will have some results in the $results variable. Lets take a look at the results.

$results.name[0]

Taking a look at the query, we are getting 4 cells back if the database you have in your connection string has the default of 2 files, we get 2 rows in our $results variable. We also get a file “name” and a “physical_name” column. So we can reference the column by name, and the row, assuming we know the number of the row we want with an integer.

Making it useful by accepting variables…

Lets take a look at adding a dynamic where clause. I’d like to filter on the name column by whatever the user wants me to. In this example we will build the query string by asking for input with read-host.

$filename = read-host "Enter database file logical name"
$ConnectionString = 'Server=localhost;Database=VMDB;Trusted_Connection=true'
$sql = "SELECT name,physical_name FROM sys.database_files WHERE name = '$filename'"
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
$results

This is what I love about blogging. I can act like I got that right on the first try! I did not. Not even the second try, or third. I’m not going to mention how many tries it took. That said it is fairly easy with one catch, the quotes.

This particular problem(debatable) with PowerShell makes it rather tricky to build a string with a variable. Notice my two strings $ConnectionString and $sql are either surrounded in single quotes or double quotes? The double quotes allow me to include single quotes in the string. I also get to include the variable $filename and not get the literal “$filename” but instead the variable value.

Summary

This code is crap. You should not allow a user to free form enter text into a query string. That allows for SQL injection. In my case, the scripts I need will be protected and I will be the user so it is not a concern.

Appendix

I changed my mind, I am going to include the embarrassing details which is my PowerShell command window I used to write this blog. I find it hilarious to make fun of my coworkers when I see their screen full of red text on a blue background.

typical_powershell_window

I figured I’d include the text too in case someone is search for errors.

PS C:\Users> $ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files’
Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Invoke-Sqlcmd : The ‘Invoke-Sqlcmd’ command was found in the module ‘SqlServer’, but the module could not be loaded. For more information, run ‘Import-Module SqlServer’.
At line:3 char:1
+ Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
+ ~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CouldNotAutoloadMatchingModule

PS C:\Users> Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin : The Windows PowerShell snap-in ‘SqlServerCmdletSnapin100’ is not installed on this computer.
At line:1 char:1
+ Add-PSSnapin SqlServerCmdletSnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (SqlServerCmdletSnapin100:String) [Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

Add-PSSnapin : The Windows PowerShell snap-in ‘SqlServerProviderSnapin100’ is not installed on this computer.
At line:2 char:1
+ Add-PSSnapin SqlServerProviderSnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (SqlServerProviderSnapin100:String) [Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

PS C:\Users> import-module SqlServer
import-module : File C:\Program Files\WindowsPowerShell\Modules\SqlServer\SqlServer.ps1 cannot be loaded because running scripts is disabled on this system. For more
information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ import-module SqlServer
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [Import-Module], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

PS C:\Users> Set-ExecutionPolicy unsigned
Set-ExecutionPolicy : Cannot bind parameter ‘ExecutionPolicy’. Cannot convert value “unsigned” to type “Microsoft.PowerShell.ExecutionPolicy”. Error: “Unable to match
the identifier name unsigned to a valid enumerator name. Specify one of the following enumerator names and try again:
Unrestricted, RemoteSigned, AllSigned, Restricted, Default, Bypass, Undefined”
At line:1 char:21
+ Set-ExecutionPolicy unsigned
+ ~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Set-ExecutionPolicy], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.SetExecutionPolicyCommand

PS C:\Users> Get-ExecutionPolicy
Restricted

PS C:\Users> Set-ExecutionPolicy unrestricted
Set-ExecutionPolicy : Access to the registry key ‘HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell’ is denied. To change the execution
policy for the default (LocalMachine) scope, start Windows PowerShell with the “Run as administrator” option. To change the execution policy for the current user, run
“Set-ExecutionPolicy -Scope CurrentUser”.
At line:1 char:1
+ Set-ExecutionPolicy unrestricted
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : PermissionDenied: (:) [Set-ExecutionPolicy], UnauthorizedAccessException
+ FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.SetExecutionPolicyCommand

PS C:\Users> import-module SqlServer

PS C:\Users> Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin : The Windows PowerShell snap-in ‘SqlServerCmdletSnapin100’ is not installed on this computer.
At line:1 char:1
+ Add-PSSnapin SqlServerCmdletSnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (SqlServerCmdletSnapin100:String) [Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

Add-PSSnapin : The Windows PowerShell snap-in ‘SqlServerProviderSnapin100’ is not installed on this computer.
At line:2 char:1
+ Add-PSSnapin SqlServerProviderSnapin100
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (SqlServerProviderSnapin100:String) [Add-PSSnapin], PSArgumentException
+ FullyQualifiedErrorId : AddPSSnapInRead,Microsoft.PowerShell.Commands.AddPSSnapinCommand

PS C:\Users> $ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files’
Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

name physical_name
—- ————-
VMDB C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB.mdf
VMDB_log C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB_log.ldf

PS C:\Users> $results=Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

PS C:\Users> $results

name physical_name
—- ————-
VMDB C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB.mdf
VMDB_log C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB_log.ldf

PS C:\Users> $results.name
VMDB
VMDB_log

PS C:\Users> $results.name[0]
VMDB

PS C:\Users> $ConnectionString = ‘Server=localhost;Database=VMDB;’

PS C:\Users> Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Invoke-Sqlcmd : Login failed for user ”.
At line:1 char:1
+ Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd :
At line:1 char:1
+ Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException
+ FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users> $filename = read-host “Enter database file logical name: ”
Enter database file logical name: : VMDB_log

PS C:\Users> $filename
VMDB_log

PS C:\Users> $filename = read-host “Enter database file logical name”
$ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = $filename’
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Enter database file logical name: VMDB_log
Invoke-Sqlcmd : Invalid pseudocolumn “$filename”.
At line:4 char:12
+ $results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users> $filename = read-host “Enter database file logical name”
$ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ‘ + $filename
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Enter database file logical name: VMDB_log
Invoke-Sqlcmd : Invalid column name ‘VMDB_log’.
At line:4 char:12
+ $results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

PS C:\Users> $sql
SELECT name,physical_name FROM sys.database_files WHERE name = VMDB_log

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”$filename”
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”’$filename”’
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = `’$filename`’
At line:1 char:74
+ … name,physical_name FROM sys.database_files WHERE name = `’$filename`’
+ ~~~~~~~~~~~
Unexpected token ‘$filename`” in expression or statement.
At line:1 char:83
+ … name,physical_name FROM sys.database_files WHERE name = `’$filename`’
+ ~~
Unexpected token ‘`” in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : UnexpectedToken

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = `’$filename`”
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $temp = ‘this is ” a quote’

PS C:\Users> $temp
this is ‘ a quote

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”$filename”
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”$filename”’

PS C:\Users> $sql
SELECT name,physical_name FROM sys.database_files WHERE name = ‘$filename’

PS C:\Users> $filename = read-host “Enter database file logical name”
$ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”$filename”’
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
Enter database file logical name: VMDB

PS C:\Users>
PS C:\Users> $results

PS C:\Users> $sql
SELECT name,physical_name FROM sys.database_files WHERE name = ‘$filename’

PS C:\Users> $filename = read-host “Enter database file logical name”
$ConnectionString = ‘Server=localhost;Database=VMDB;Trusted_Connection=true’
$sql = ‘SELECT name,physical_name FROM sys.database_files WHERE name = ”’ + $filename + ”’
$results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql
The string is missing the terminator: ‘.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

PS C:\Users> $sql = “SELECT name,physical_name FROM sys.database_files WHERE name = ‘$filename'”

PS C:\Users> $sql
SELECT name,physical_name FROM sys.database_files WHERE name = ‘VMDB’

PS C:\Users> $results = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query $sql

PS C:\Users> $results

name physical_name
—- ————-
VMDB C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\VMDB.mdf

PS C:\Users>

 
Leave a comment

Posted by on December 6, 2016 in Uncategorized

 

#sqlsat552 Lincoln, NE recap

Before you call me crazy SQL guy (I think that name is already taken) I had several motives for driving 10 hours to Nebraska. In no particular order:

  • My wife and I work in different states, Lincoln happens to be on the way.
  • We like nature, this one was in a State Park
  • There was a cabin available for 3 nights and at a decent rate that allowed dogs
  • SQL Saturdays are awesome
  • Free sessions and networking
  • Free volunteering (sounds weird but if you have done it, you understand)
  • I’m considering coordinating my own SQL Saturday

However, with every opportunity there is a cost

  • All-in I probably spent $700 I would not have spent otherwise
  • I missed some football games (no big deal)
  • I missed opening day of hunting season (kind of a big deal)

Here are some of my notes from the sessions I attended

Getting Started with Extended Events – Andy Galbraith
Switching from a working solution to something new when you don’t necessarily have to can cause some anxiety. This anxiety causes paralysis. Sometimes we all need a little nudge to really get going.
I was a little confused about the statement that extended events fire synchronously which causes them to be lightweight. It is a little bit of a contradiction I still have to sort out but I think I understand that some parts are always running, and you can asynchronously choose to record them.
The key bits are packages which contain events, actions which is the info you track, and predicates which filters like a where clause
The event file is a .xel file and is least impactful one of a few ways to save off the information
System_health is like the default trace and always running, don’t modify this.
Use caution which what events you pick and where you store the events, query plans can cause a lot of overhead and storing in the buffer can bloat in memory
Set state=start to make the XE work

Get Your Optimizer to Give up All Its Secrets – Brian Hansen
Brian had a nice organized flow which made me compare his style to more of a college professor
Logical order of operation is important and starts with From to ON
Intellisense doesn’t work until you have FROM
He pointed out some confusion with select meaning something different in Algebra(it’s a filter). In Algebra a projection is like a select
The query tree is the output of the algebrizer and he had a nifty app to visual query trees
Distinct is like a group by
Contant scans are usually good because they can help avoid data access – ex: where clause A>50 and A<50

Tips and Tricks for Performance Troublshooting – Phillip Podhrasdsky
Phillip had a faster pace and covered a lot of ground for an intro session.
Query Plans are key along with statistics. Stats have 200 levels, so you might not get stats for every item in a “colors” table. Creating additional filtered stats can help with this issue

Query Plan Deep Dive – Kevin Boles
Kevin is a great speaker and had a well-organized slide deck.
Ref: Paul White – cross apply blog post
Spot Nested loop joins can be inefficient if a large data pipe is on top
The optimizer will sometimes choose to sort the data to do some joins
Merge join works well if there is order to both tables
Hash joins – the table is built in memory and will spill to tempdb, works well if not ordered
Stream aggregates require order (non-blocking)
Fast first – can cause more overall work, but create a better user experience for poorly tuned query
Asserts- check things like True or False conditions (ex: finding a key to delete)
Pick fill factor intelligently
Tsqlt.org
ADO.net timeout is 60sec ish
Optimize for unknown or recompile are options if you run into sniffing issues
UDFs will generate warnings in the query plans and cause some bad estimates

Dammit Jim! Dr McCoy’s Field Guide to system_health (and the default trace) – Ed Leighton-Dick
Finding answers to “who done it” with the things that record data by default
System_health is XE and trace if profiler – they both get trimmed after a while

Afterwards we met up at one of the Peter Kiewit Lodge cabins for some drinks and food. I talked to the organizer a bit and got some great information from volunteering and the after party to help me consider organizing my own SQLSaturday.
I had a great time and look forward to attending more SQLSaturday events next year.

 

 
Leave a comment

Posted by on December 3, 2016 in Uncategorized

 

PASS Summit 2016 Recap

Introduction

I have a fairly massive bundle of lanyards in my drawer at work. vmworld, ciscolive, emcworld, sql connections, sql rally, grr con, bsides detroit, a bunch of sql saturdays and more. All are great learning experiences and if I didn’t have to do actual work, I’d love to attend more conferences. I definitely plan to attend the PASS Summit next year and many years to come.

All employers should commit to a couple weeks of training for all associates. Most new people can learn from the experience of more senior members of their team but both the trainer and trainee need to be available for this to work. Classroom training from companies like New Horizons can come in handy for those associates who don’t like to travel or for companies that don’t have a good mentor in that field. Conferences that require travel can sometimes cost 3-4k but with the right person, are totally worth it. In fact, sending multiple associates to the same conference gives them a chance for teambuilding somewhat outside the stuffy bounds of the office.

Being a PASS chapter leader grants me free admission to the 3 main conference days. That got my reported expenses down to 2k, which the majority of that being flight, hotel and cabs. Chapter leader is a time consuming job, but very rewarding in many ways including this free ticket.

The Summit stands out from all other conferences I’ve been to for a couple reasons. First, the content is mostly not vendor content and more relevant to the “how” part of my job. Tips and tricks instead of products are explained in many sessions. We as attendees are mostly not getting sold a particular product because a majority of the speakers are community speakers. Sure, definitely lots of fans of SQL Server and not much mention of Oracle/MySQL/Postgresql/DB2/etc.. but I think you get the point. Second, it has a feeling of a family reunion for a lot of people. We have that crazy uncle, annoying younger brother, CEO cousin, and a poor bunch of Cleveland Indian fans. Networking is hard but the most beneficial aspect of the conference. There seems to be a greater number of people willing to meet new people at PASS than any other conference I’ve been to. There are also many other PASS events that give you an opportunity to reconnect with these people.

Without further ado, here is my play-by-play.

Monday

Alaskan Airlines (23” rule is the largest free carry on that I am aware of)

I may have liked to attend a pre-con this day but I would have had to fly out Sunday.

Tuesday

Registration

Chapter Leader Meeting

I didn’t know this last year but this is a mandatory meeting for chapter leaders who get the free Summit ticket. Lots of good ideas discussed for my local chapter (http://glass.sqlpass.org) in regards to sponsorship, speakers, and locations.

Welcome Reception

A single drink ticket was provided and cash bar after that. Light food and very tasty. Great chance to network.

Wednesday

Keynote

Discussed HTAP, hybrid transaction analytical processing. Basically the reason we do ETL is to avoid too much stress on the primary system. Some of this can go away with multiple object types that store the same data in different ways. In-Memory OLTP table with a Cluster Column Store Index can serve both reports and transactions at the same time. This is interesting, but the reason CCSI was read only in version 2014 was because updates to this type of structure are slow, updates can happen in version 2016, but I’m not sure how fast they are.

Microsoft R can be installed on SQL Server or on a standalone server. They have promised huge performance gains through multithreading and scale out options. Not sure if there is a licensing benefit for us to go standalone, built into SQL, or just keep using open source R.

Azure IO bandwidth is 4x any of the other cloud competitors. SQL Server running on linux was demoed, even works with AlwaysOn availability groups. Isn’t that appealing to me and won’t be GA until later 2017.

Architecting SQL Server for Mission Critical and High Performance (HPE Vendor Session)

Discussed some superdome hardware architecture. One interesting trend is using NVDIMMs. This non-volatile memory can be used to harden log records. DAX bypasses the storage stack, but can still be configured as a hard drive in windows. SQL can take advantage of this for a secondary small log file with a trace flag and eliminate LOGWRITE waits. The size (8GB) and availability of these devices is prohibitive.

Inside SQL Server In-Memory OLTP

Bob Ward has a fast paced, in-depth speaking style that I like. I attended a couple of his sessions and he made his slides available at http://aka.ms/bobwardms

The goal of In-Memory OLTP was to be 100x faster that previous versions. It sounds like they got about 30x faster, mostly due to the removal of latching. The important durability differences between an In-Memory table and a regular engine table was discussed. No UNDO process is required for durability because there is no concept of write ahead logging. These transactions are just dropped on failure. 701 is the error we will get if we don’t size memory properly to the table we want to enable for In-Memory functionality. There are management objects, but they don’t show up in the database system catalog since they are in-memory tables and recording that information in regular tables would slow transactions down. There are two main types of indexes, hash and range. Bucket sizes need to be determined for hash indexes but they are much faster if configured properly. XTP_OFFLINE_CKPT is a wait that is reading the transaction log and making decisions to flush to disk. This isn’t a wait that affects transactions but runs asynchronously.

Lightning Talks

Vendor Reception

Nimble Storage and Pure Storage were there, not sure if any other storage vendor was. Talked to AWS about some of the things they do. Providing a database as a service separate from a database server as a service might be worth us looking into. VMware was there and working the booth was one of the speakers from EMCworld and CiscoLive. I got some information about some of the max VM sizes in the wild and found out it might be worth trying to put Actuarial SQL Servers as a single VM on a host.

Thursday

Keynote

It was refreshing to see several options in cloud data warehousing presented. Dr. David Dewitt compared a roll your own DW with Amazon’s redshift and Microsoft’s SQL DW and Snowflake. He talked about Oracle RAQ and Teradata. I liked the comparison of different vendors with a fairly open minded approach at a conference that focuses on one vendor. He explained some of the pros and cons of any scale out data platform.

SQL 2016: It just runs faster

Clock speed hasn’t improved much in 10 years but the number of cores per processor has gotten really high. Several pieces of the SQL engine needed to be re-written to process more in parallel. Parallel redo, parallel insert select, automatic scaling of number of threads and more partitioning allows for more parallel operations in 2016. We need to work on our ISVs to support later versions of SQL Server if we want to improve overall user experience.

Recovery Models Made Simple

Great session that explains recovery and a lot about the SQL Server log file. Active transactions that affect space used in the log file was explain very well. Virtual log files and their reuse was explained. Fn_dblog(null,null) can be used to explore more information stored in the log file.

Monitoring and Alerting of Availability Groups

Picked up a lot of useful tips and tricks for the couple AGs we have in production. The SSMS dashboard is pretty useful for monitoring performance. Reviewed the basic steps of an AG transaction. Overall makes me more comfortable with 2016 AGs even though we try to avoid them since it is a copy of the data and a windows cluster on top of a vmware cluster.

Vendor Session: Storage Testing

Learned about a fairly new tool VD bench which can simulate more realistic virtual workloads. With dedupe&compression inline, synthetic tools do not provide a while lot of useful information. Argenis and David and awesome speakers.

Microsoft R Sessions

I attended a SQLCAT session and introduction session to try to get a handle on how best to manage R for Actuarial. The open source R doesn’t operate in parallel and Microsoft has created some open source ScaleR functions (rx*) that are comparable but operate in parallel. I saw an interesting demo of predicting the date that a database is going to run out of space with R. That might be a good way to practice with usage of R that is built into SQL Server. There wasn’t much around memory management with R built-in other than I learned it can spawn a lot of extra processes outside of the SQL process.

 
Leave a comment

Posted by on November 21, 2016 in Uncategorized

 

That Time I Made SQL Server Too Fast

Things break. We don’t usually intend them too, they just do. Sometimes, maybe we do intend them to break. I’m still suspicious about cars breaking on purpose so we will buy a new one. Other times, they break in ways we don’t expect, like these times I made queries respond too fast.

For example, several years ago I was a fairly new DBA. I wasn’t used to the enterprise class roasting one gets when things are not quite right. We had a multi tier application, DB, app server with a desktop application that started to respond with errors after a hardware upgrade of the SQL Server.

Darn. We tested everything we could. We reviewed the code and sent it through upgrade analyzer. We restored production backups and tested those on the new hardware.

But it wasn’t enough load for the system to break in a peculiar way. The server had a limited number of threads to deal with a response from SQL. Seriously, hard-coded value of 3 thread to go to SQL, get a visual basic script stored in the database, and execute that on the app server. I kid you not, sometimes the sausage factory isn’t worth the tour.

This dynamic script was now loading up these 3 threads all day, and it was a lengthy script so sometimes it would hit the other hard coded timeout values and the users would start to get errors in large numbers instead of just waiting a little longer that usual.

We tried, and tried some more to work with the vendor. But in the end I had to make SQL slower so the app could keep up. We moved this SQL Server back to old hardware until we could also upgrade the app server.

The most recent time I made SQL too fast was a couple days ago. Another third party vendor who is XML happy. Surprisingly, the problem had nothing to do with XML.

Re-wind several years ago, I convinced a co-worker that SQL 2012 would be a great choice for their application even though the vendor didn’t technically support it yet. Who cares! Microsoft supports 2012, not this vendor. We tried and tried to get the thing to work but the application just cooperate with SQL 2012. Fortunately, we didn’t have any data so we just reinstalled the app and used SQL 2008R2. I conceded defeat, probably too soon and rolled it back after delaying their upgrade a couple weeks.

Back to a few days ago, that same co-worker fell for my tricks again with a different app. This time SQL 2016, because Bob Ward says it just runs faster ( http://www.sqlpass.org/summit/2016/Sessions/Details.aspx?sid=51194 ). Let me tell you how hilarious this error is as briefly as I can.

This XML happy application has a boatload of tables. Fairly normalized but lots of chatter with XML back and for the the application even with just a single user. We are testing a new version and a single user was getting a primary key violation. Consistently happens on SQL 2016 so I had him send it back to the vendor, even though he was threatening physical violence. Given our prior experience of jumping on a new version, I don’t blame him. No way could this be SQL 2016, primary keys are are similar across all versions right?

Vendor comes back with some confused email. “We can’t recreate the issue.” Blah blah, co-worker convinces me to give him access to create the database on a SQL 2012 instance and bam, no error. The beatings start. Even though he knows its bogus, still I have bruises and my fear of the new shiny is reinforced.

Are you ready for the shocking news? SQL 2016 is fast. Really fast. Even with absolute garbage design and code, its fast. That is how we got primary key violations.

The table we are working with has a primary key. But the interesting part is, it is a composite key meaning there are a few columns that create uniqueness between rows. So two of the columns can be identical across rows, as long as the third is different. Then we are unique.

Well, in an absolutely vomit enduing way, the vendor chose a datetime datatype to create this uniqueness. Well, when several rows come back in less than 3ms, even with a boatload of garbage XML to process, we no longer have uniqueness because datetime isn’t precise enough.

Not sure what they might consider a fix, but I explained to them what is happening. Hopefully, they don’t jump on the datetime2, knee jerk reason and go with an actually unique key.

 

 

 
Leave a comment

Posted by on November 21, 2016 in Uncategorized

 

Cisco Live 2015 San Diego Recap

This was an impressive conference! Photo Alubum

I flew in Sunday and enjoyed a day getting familiar with sunny San Diego and getting a good nights rest before my test Monday morning. I passed the CCENT exam after a grueling month of preparation. I’m not sure what direction I will take with my Cisco certifications, but the CCNA Data Center track looks appealing and has some overlap with what I have already learned.

The number and quality of sessions makes me consider this conference one of the best I have attended. I’ve been to VMworld, EMC World, SQL Rally and SQL Connections and this one ranks at the top for overall quality. I’d recommend it to anyone remotely close to managing a network.

I focused on storage networking, security and UCS for the sessions I attended. I was able to get some time at the whiteboard with a Fiber Channel expert who helped me walk through a possible upgrade path. In the storage networking sessions I had some interesting discussions about flash arrays with my peers. Looks like a lot of people are getting into testing “seed” units that were provided for free.

The conference food was just ok but the exibit hall had some good appetizers and drinks. The Gas Lamp Quater is a hotbed of excellent restaurants including Fogo De Chao which well worth my $70 for dinner. The entertainment was great. OK GO opened up the conference keynote and Areosmith rocked Petco park. Mike Rowe had some hilarious stories and a good closing message.

I like to travel and learn about technology. Its always re-invigorating to attend a conference and I hope there are many more in my future.

 
Leave a comment

Posted by on July 3, 2015 in Uncategorized

 

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

I’m writing this post to force myself to spend some quality time with the materials for this exam. I have been at it for almost two months now and am nearing my exam date. I accelerated my plan so I could get into the 2nd shot window offered by Microsoft and also so I could finish my MCSA within 1 year. It has been a battle at times and is not an easy certification to get. Microsoft has really increased the difficulty since the MCITP for SQL 2008 which only required 2 exams.

My employer is assisting with the costs in a few ways. They will reimburse me for the cost of a passed exam. They are giving me a $500 bonus if when I pass all three exams and prove my MCSA. And they have loaned me the Training Kit book along with the other tests books that I have already returned.

My plan has been going fairly well. I’ve been able to put at least 10-15 minutes in about 6 days a week. Some of those have lasted and hour or more but that is pretty rare. Data warehousing is interesting to me because we have a lot of things starting up at work that may take off and require these skills. Before I started studying I had deployed only a few packages for my own small data collection and reporting tasks as an administrator. I also do not get too involved with database design since we rely on a lot of 3rd party applications. That world is changing for me and that is why I have been able to be a fairly good student for this last test.

So lets get to my plan.

The percentages are the first thing to note on this page: https://www.microsoft.com/learning/en-us/exam-70-463.aspx

11% – Design and implement

23% – Extract and Transform

27% – Load

24% – Configure and deploy SSIS

15% – DQS

_______

100%

I like to sit down with the book and read as much as I can while taking notes. I write down a lot. When I look at it later I think, “duh I knew that why did I write it down?” But it actually helps me stay focused. Even if I just write down the title of the section, it keeps me on track. At this point, I am ready to go back and review a lot of those notes and type them up so here they are.

The book is split out into those same 5 “Parts” as listed on the exam website.

Part 1: Design and Implement
Use snowflake in a POC since it will be easier to design from the complex OLTP environment.
Star schema for everything else.
Star is just a simplified, denormalized, merged, cleansed, historical schema with fewer joins
Star schema works well for SSAS cubes, SSAS won’t be on the test (phew).
A fact is: “Cust A purchased product B on date C in quantity D for amount e”
Dimension table: Customer, Product, Date
One star per business area
The Granularity level is the number of dimensions or depth you can slice by (thinks sales by quarter or sales by day)
Auditing: Who, What, When
Lineage: Where is the data coming from?
Dimensions: The goal is to make it look good in a pivot chart
-descretizing: putting values into bins and not keeping too much granularity because it doesn’t graph well
-Member Properties: columns not used for pivoting
Slowly changing: type 1- no history, overwrite; type 2 – keep history with current flag or validto-validfrom cols; type3 – limited history with additional cols like prevAddr
Keep business keys intact, create additional DW specific keys (surrogate keys) for linking fact to dimensions, probably INDENTITY
Use a SEQUENCE if you need to know the number before inserting, request multiple at once, or need a multi-table key
FACT TABLES: made up of FKs, Measures, Lineage cols, Business keys
consider the additivity of measures. EG: can’t sum an AvgDiscCol
Fact tables should be on the Many side of the 1->many relationship
Dimensions contain the lineage data
Age is a common computed column
design dimensions first, then fact tables
use partitioning on your fact table
Fact tables contain measures
Every table should have a clustered index
Do not index FKs of fact table because HASH joins dont need it?
If you are doing merge joins and nested loop joins indexes on FKs help
indexed views are useful in some cases
Row/page compression automatically applies unicode compression
batch mode is faster and will show in the query plan
column store indexes: one per table, not filtered, not on indexed views
Partitioning function maps rows to a partition
partitioning scheme maps partition to filegroups
aligned index: table with same schema which allows for partition switching
optimizer can eliminate partitions
inferred member: row added in dimension during fact table load

PART II: Developing SSIS Packages
To be continued…

 
2 Comments

Posted by on April 2, 2015 in Uncategorized

 

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(http://redmondmag.com/articles/2013/11/21/windows-azure-outages.aspx)

Widespread Amazon outages (http://www.zdnet.com/amazon-web-services-suffers-outage-takes-down-vine-instagram-flipboard-with-it-7000019842/)

NASDAQ (http://www.bloomberg.com/news/2013-08-26/nasdaq-three-hour-halt-highlights-vulnerability-in-market.html)

The POTUS’s baby (http://www.healthcare.gov)

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. (http://en.wikipedia.org/wiki/High_availability)

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.”
Source: http://www.theguardian.com/technology/2013/aug/23/nasdaq-crash-data

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.

stack

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.

 

Cannot open the disk: VMWare maintenance mode bug?

Bug? Well I don’t want to jump to conclusions but I recently had some less than desirable behavior when putting a host into maintenance mode.

In this 3 node cluster I had recently created some larger storage VMs to replace a single VM. Both the old and new guests were running for the parallel upgrade so it doubled my storage requirements for a short period of time.

While this upgrade was happening I needed to do a driver update on the hosts. I planned to put one in maintenance mode near the end of the day and do the driver update the next day. Maintenance mode started without incident and all VMs started vmotioning off of that host.

The next morning I got some complaints that one of the storage VMs was having some issues. I checked for vmware error messages but didn’t have any. I could ping this VM but I couldn’t log in or do anything else with it. I couldn’t even send a remote reboot command: shutdown -m \\storagevm /f /r /t 00

The error

I was forced to power the VM off. When I tried to power it on vmware gave me this message: Cannot open the disk ‘/vmfs/volumes/xxxx/vm/vm.vmdk’ or one of the snapshot disks it depends on.

I was stumped, I first thought maybe some kind of pathing issue but that did not make any sense because the paths are not define on hosts individually. While the VM was off I was able to migrate it back to the original host and power it on. This worked, so I was back in business but I still needed to update that driver. I then tried to manually vmotion this server to the same host and was presented with this, slightly more descriptive error: The VM failed to resume on the destination during early power on. Cannot open the disk ‘/vmfs/volumes/xxxx/vm/vm.vmdk’ or one of the snapshot disks it depends on. 0 (Cannot allocate memory)

The problem

What happened next was why I use the b word in my title. The manual vmotion does fail but the guest is still happy. It continues run because instead of forcing itself on the destination, it hops back to the source host. The previous evening maintenance mode failed to foresee this problem and rammed my VM onto the host making it unresponsive. No error or message was presented other than maintenance mode was completed successfully. My VMs running properly is far more important than my maintenance mode completing successfully.

The cause

My problem was a vmware storage limitation. A single host can have a limited amount of VMDKs open. I do take responsibility for not knowing how close I was to one of these limitations. But, in my defense, I just got back from VMWorld where they were bragging about 64TB datastores.

This KB was quite helpful and let me know that if I complete my ESXi upgrade I won’t be pushing the limits anymore: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004424

I also found these other two resources useful.

https://communities.vmware.com/thread/451279?start=0&tstart=0


PowerCLI > Get-HardDisk -Datastore | select -ExpandProperty CapacityGB | measure -sum -ave -max -min

http://pibytes.wordpress.com/2012/11/13/esxi-vmfs-heap-size-blockade-for-monster-virtual-machines-in-bladecenter-infrastructure/

 
Leave a comment

Posted by on October 13, 2013 in Uncategorized

 

#VMWorld 2013 recap

Virtualize All The Things!

Thatisallfornow.

 
Leave a comment

Posted by on August 30, 2013 in Uncategorized

 

My favorite posts from 2012

My all time favorite post happened in 2012: https://nujakcities.wordpress.com/2012/11/25/im-speaking/

This was my first PASS speaking engagement and pretty big milestone for me. Fact is, I really just found a topic that I am truly passionate about. I don’t get to practice hacking at work so it is really a second life for me. If you want to take a journey to the dark side, here are some of my favorite posts on hacking.

There was one other topic I enjoyed writing about, statistics. I imagine this is because one of our main production servers had some issues finding bad query plans because of stale statistics. Whenever I spend that much time at work digging into a problem it gives me a lot to write about.

I took another leap this year and started adding and suggesting indexes. I had always considered this a design issue (ie: not my problem) but sometimes things get pretty bad. I have access to all the tools to sift through an environment of over 100 SQL Servers and find the worst offenders. This year I actually had some success with that process.

 
Leave a comment

Posted by on December 27, 2012 in Uncategorized