Thursday, December 4, 2014

Getting SQLPSX to work with SQL Server 2012

A few years ago, I wrote PowerShell scripts to assist our IT Risk department with auditing and ID management. With the deployment of SQL Server 2012 in our environment those scripts are starting to show their age. 

The scripts were designed to run from a central server and they use SQL Server PowerShell Extensions version 1.6.1. 

The scripts are throwing errors when trying to connect to SQL Server 2012 instances.

The SMO assemblies are loaded with the following code by LibrarySmo.ps1:



If I run this bit of code on our central server it returns the Version 10 SMO assemblies that allow it to connect to SQL Server 2008 R2 and below.

Running this code on my laptop or server that has the SQL Server 2012 tools installed returns the Version 11 SMO assemblies which allow the scripts to run on SQL Server 2012.

In addition, if the scripts are copied to a server with SQL Server 2012 installed, they run successfully because the code above is loading the later version of the SMO assemblies.

So, the fix for our central server is to either install the SQL Server 2012 client tools or just the compatible SMO assemblies. We now know we need to keep the central server up to date with the latest assemblies to keep these scripts working.

Helpful references:
http://sqlpsx.codeplex.com/discussions/353631
http://sqlserverpowershell.com/2012/07/24/sqlpsx-sql-server-2012/

Monday, November 10, 2014

Veteran's Day Trivia - Name That Aircraft

Feeding a wild pig



WORKING CONDITIONS: Works indoors and outdoors, in inclement weather, and on icy, wet, or slippery ramps and aircraft surfaces in temperature and humidity extremes. Subject to dangers from exposure to toxic fumes, high-pressure air and fluids, fast actuating aircraft surfaces such as landing gear, speed brakes, and flaps.  Dangers also include engine noise, jet blast, jet intakes, explosive munitions, electrical voltage cartridge actuated devices, liquid oxygen, fire, aircraft fuels, lubricants, paints, hydrazine, and solvents.

Working in IT is a piece of cake.

That airman on the left is me!
I salute all veterans on Veteran's Day!

Bonus points if you can name the aircraft I'm working on.

Friday, October 31, 2014

Training on the T's - SQL Server Security Easy Button Q&A

On October 7, 2014, I spoke for Pragmatic Works' Training on the T's webinar series. 
Here's the rest of the questions I didn't get to answer. 

Q: Where can I get the slide deck and presentation?
A: Orlando SQL Saturday , Tampa SQL Saturday , Jacksonville SQL Saturday

Q: All of our database tables are under the dbo schema. How can I set up programmers to be able to modify stored procedures but not give them the dbo schema permission with which they can modify tables?
A: I think the only possible way to do this is to split the stored procedures and tables into different schema. I do not see a way in the permission models to grant permission to modify stored procedures without also granting the same permission to modify tables.  Granting ALTER ON OBJECT gives access to multiple object types in the database. See longtime SQL Server MVP Erland Sommarskog's comment here on this question. See the next question for additional information. 

Q: Where i can get the SQL Server permissions PDF?
A. Google "sql server permissions poster" or go to this link.

Q: Good morning, Where I can find the current session recording? I missed half of the beginning part.
A: Consider yourself lucky. You missed most of my 'ums' in the first half. ;-)
You can find it here.

Q: Would you recommend using this security script along with C2 auditing?
A: Yes, but I would recommend using the Common Criteria Compliance option instead. C2 audit mode is deprecated and will be removed from a future version of SQL Server.

Q: The password vault you keep referring to is the windows credential manager found under user accounts in control panel?
A. No, it's called the Cyber-Ark Enterprise Password Vault

Q:You mentioned a couple of names of people we should know and I missed the name of the first guy.

Q: How does the DBA sign out a user id with sysadmin rights?
A: In our environment, they log in to Cyber-Ark and provide a valid change or incident ticket.

I apologize for too many 'ums' and completely forgetting to use Zoomit while reviewing the script.
Thank you for attending! 

Friday, September 19, 2014

MagicPASS September meeting

I spoke at the MagicPASS September User Group meeting in Celebration, FL.

Kendal Van Dyke (b|t) does a great job running this group.

Home-cooked meals for attendees! Need I say more? But, I will. 

The Disney Vacation Club provides an excellent facility.

I liked the MagicPASS meeting format. They have a pre-meeting and a main meeting. I spoke during the main meeting at 7:30 PM. The meal is served between the two meetings. Baked ziti and salad. Nom, nom, nom.

During the pre-meeting, the presentation was a recording from the PASS Performance Virtual Chapter. This is a great idea because it makes attendees aware that virtual chapters exist and they get a chance to see a presentation that maybe they didn't get to see. I had seen this one. Oh, well.

Another informative agenda item in the pre-meeting is "Industry News".  Kendal talked about upcoming service packs, cumulative updates, and critical patches. He asked if anyone was using Ola Hallengren's scripts. (YES!) Then, he described an issue he had with the scripts caused by a memory leak in one of the SQL Server components that is fixed by a recent CU. Kendal also shared his experience on a recent trip to VMWare. I learned DBAs should have READ access to vSphere ( I do.) and that if you turn the correct "nerd knobs", SQL Server runs pretty well on VMWare. This is WHY I go to user group meetings! I always learn something I didn't know.

My take-away from this meeting is that I need to have a clear view of my notes in Presenter view. My laptop was on a table instead of on a podium and I think this caused too many hesitations in my talk at the beginning because I would pause to bend over and check my notes.  I like to stand when presenting instead of sitting. I'll need to expand the font size of the notes or commit more to memory.

The next day, I figured out how to increase the font size of the notes in Presenter View. 

It takes just over an hour to drive to the MagicPASS meeting location from my house so I'd be willing to do it again for the "Happiest SQL User Group on Earth". 
Thank you Kendal and MagicPASS for having me! 

I'll be there the next time you have tacos. ;-)

Wednesday, August 20, 2014

My upcoming speaking calendar

If you work in an organization that is finding it increasingly difficult to allow SQL Server database administrators unfettered sysadmin access to your Production servers, then you have four opportunities to hear me speak about how to control this type of access.

PASS Healthcare Virtual Chapter
August 21, 2014
http://healthcare.sqlpass.org/

MagicPASS 
September 17, 2014
http://magicpass.sqlpass.org/

SQL Saturday #318 in Orlando, FL 
September 27, 2014
http://www.sqlsaturday.com/viewsession.aspx?sat=318&sessionid=21152

Training on the T's
October 7, 2014
http://www.pragmaticworks.com/LearningCenter/FreeTrainingWebinars/FutureWebinars.aspx?ResourceId=692


Sunday, August 3, 2014

How do I get started with PowerShell?

My co-workers recently asked me how to get started with PowerShell.  
Many of them are still writing VBScript and I am VERY willing to help them stop writing VBScript.

So, I sent the following to them. Most of which is FREE.

Windows PowerShell Scripting


FREE E-books:


The VBScript-to-Windows PowerShell Conversion Guide

Don Jones’ "Learn 'Something' in a Month of Lunches" books are very good. 
I have this one.

For real world examples, I recommended:
Finally, The Stairway to SQL PowerShell looks like one of the easiest ways to start.
This is not meant to be a complete list because I didn't want to overwhelm them. You can always find more with your favorite search engine.
I'm hoping this post will help someone else get started with PowerShell. 

Tuesday, July 29, 2014

Checking for SQL Server AlwaysOn patches for Windows Server 2008R2...

Time moves slower in a big company's data centers.

Meaning, you might not get to run SQL Server AlwaysOn Availability groups on Windows Server 2012. If not, you'll have to check several patches are applied for it to work correctly on Windows Server 2008 R2.

This excellent resource provides a list of the needed patches in the FREE AlwaysOn Setup Checklist PDF.

I think the easiest way to determine if all the required patches are installed is by using the PowerShell cmdlet get-hotfix with the -computername and -id parameters.

Beware, the example below is a single line.

get-hotfix -computername YourServerNameHere -id KB2494036, KB2494162, KB2520235, KB2524478, KB2531907, KB2550886, KB2552040, KB2578103, KB2578113, KB2582281, KB2616514, KB2654347, KB2687741, KB976097, KB980915


If any of the patches are found, a five column result set is returned. 

If all the patches are found, you'll see fifteen rows. 

If none of the patches are found, the command throws an error and you'll see red.
White beats red any day.
While writing this post, Windows Server 2012 was approved for PRODUCTION deployment at my big company. 

Does that mean time moves faster when you blog? ;-D