Speaking at SQL Saturday #287 – Madison, WI

http://www.sqlsaturday.com/287/eventhome.aspx

I am excited to get on my way to Madison. I will be speaking on TSQL Best Practices.  We have a great lineup and it will be a great time.

Uncategorized

Speaking at SQL Saturday 220 Atlanta #SQLSATATL

Posted over on sqlblog.com about my speaking at the SQL Saturday.

Hope to see you there.

Powershell, SMO, SQL Saturday

MCM SQL Server 2008 Achieved

Another step in my career as I have passed the Lab Exam and have become an MCM in SQL Server 2008.  I believed I could achieve it after passing my knowledge exam the first time.

The first attempt at the lab was back in October and it was disappointing to find out that I did not pass the first time. I thought about what I could have done better and determined that I let time get away from me.  I had the experience I thought I needed to do well on the lab exam, but could not get to the point of finishing all the objectives and then there were those that I had not had experience with.  I found myself rehashing my experience to find that important part of getting prepared.  I did not think that study would help enough, so I started with my own lab environment (VM with SQL Server on it) and I ensured that I knew how to work with most of the engine features in BOL and thought of my strategy for the second attempt.

As I reflect on my attempt and prior to that day, I am grateful for the time I took to teach the concepts I was learning in my lab environment, to others. I also love the User Groups in Utah for allowing me to solidify some concepts I felt were important to know and be very familiar with by presenting at the Group meetings.  I may have taken it sooner had I had a study buddy to bounce things off of, but I found that the post from Rob Farley I got a new lease on life for the test.  I was confident in what I knew, but I am not as great in managing my time in scenarios of stress.  It helped to have the test administered via Lync at my house, which was much better than where I took it last time.

The day of the test, the proctors were awesome and took good care of me and explained the process.  They were very understanding and that set my day off to a good start. During the test, I employed some techniques that I learned in preparing, that were all about time management to allow me to get through the Lab and demonstrate what I knew how to do. I think that my time management really helped me the most as I already had the experience I believed I needed to pass the test.

Thanks to all the other MCMs that shared their expertise in presentations, emails and all those that were totally encouraging me to attempt my MCM experience.  But most of all to a supportive manager and employer (www.healthequity.com) that stood by me in the expense and time to take this exam. I am honored to be among the MCMs in SQL Server and looking forward to the road ahead.

MCM, SQL Server

T-SQL Tuesday #39: More Efficient SMO with Server.SetDefaultInitFields

TSQL2sDay150x150 This T-SQL Tuesday is about PowerShell and how I use it with SQL Server. There are many more posts to come about PowerShell and SQL Server but I thought I would cut my teeth on this T-SQL Tuesday since it was on PowerShell.

If you read Rob Farley’s (b | t) post on the SMO topic, I am going to extend it a little and illustrate how you can get a little better TSQL out of SMO by using some things built into the SMO Server object.

The Server object contains a lot of properties and methods, but this post will focus on one called SetDefaultInitFields. This method takes an object and a list of properties that should be retrieved initially when the object properties are accessed. This allows much more efficiency when dealing with collections of items, such as Databases, Tables and Columns.

The code will retrieve me a list of databases with their PageVerify setting and the screenshots will show you what the Profiler output looks like with and without using the Server.SetDefaultInitFields method. Nothing rocket-science, but it will help illustrate that you can help SMO out in the efficiency department.

# This will get the Server object from the SQL Provider
# either by using Import-Module SQLPS with SQL 2012 installed
# or by using Add-PSSnapin SqlServerProviderSnapin100 with 2008/R2 installed
# or by using SQLPS.exe with 2008/R2 installed
# If you load the Module SQLPS with 2012, you don't have to load the 
# SMO Assembly for Microsoft.SqlServer.Smo
# Execute each set in a new PowerShell window to see the same information

# This set will get the list of databases, but will execute the query
# to get the PageVerify for each database (plus much more information)

$server= Get-Item SQLSERVER:SQLlocalhostdefault
$server.Databases | Select Name, PageVerify

# This set will get the list of databases and when it does, it will get them with 1 query
# along with the PageVerify at the same time

$server= Get-Item SQLSERVER:SQLlocalhostdefault
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], "PageVerify")
$server.Databases | Select Name, PageVerify

Here are the screenshots that show the output in Profiler for each query. Notice that you will see the first set of PowerShell actually gets the data for each database one at a time.  With the second set, you see one query.  The reason is because of SetDefaultInitFields on the Server Object.

First screenshot, shows the retrieval of the Name of the database for one database and you get the CompatibilityLevel for free as SMO will interrogate that automatically for it’s own internal purposes. But you will see that the second screenshot will have a lot more information that it retrieves, just because you asked for one of the properties in a group that SMO has defined. It retrieves it all. Notice on each of these, that it is specifically for the database named ‘Bob1’ and not for any other. Each database will have these 2 queries run for it, and without any help it could be a long process to get 1000 database attributes.

Profiler_1_NoHelp

Profiler_2_NoHelp

Now in the third screenshot you will see that using the second piece of PowerShell in the code, that it issues 1 query and gets all the information in one round trip to the server. Look Mom, no WHERE clause.  Pretty efficient.

Profiler_4_WithHelp

Hopefully this gave you a taste of what you can do to help SMO be a little more efficient and return with less round trips to the server. Do not underestimate the power of SetDefaultInitiFields.

Have a great T-SQL Tuesday.

Ben Miller

Powershell, SMO, SQL Server

Log Shipping encounters corrupt log backup

I ran into an issue the other day and wanted to ensure that I shared it out there.  In a Log Shipping scenario, you can occasionally run into a corrupt transaction log backup. This causes the database to stay in restoring mode (as mine was previously in a standby/read-only state for reporting), but requires the next LSN to be restored, which you don’t have.

In this case, it went overnight and was out of sync a ways, but at night I do a differential backup of the database.  By restoring the differential backup with norecovery to the log shipped database, it allowed me to continue restoring with log backups from that time on.

Hope that helps anyone in the www realm, it is a nice save since databases can be large and the initialization could be very impactful.

SQL Server

Get SQL Server Service Accounts with WMI

Today I was tasked with retrieving the SQL Server Service accounts to validate them in Active Directory.  I wanted a simple way to get them and show the account names for each SQL Server service.  I knew that you could get the service in PowerShell with Get-Service.  I also new that PowerShell could get the service with Get-WmiObject –class Win32_Service. But neither one had the ServiceAccount property.

So I turned to WMI under SMO with PowerShell.  The script below has the details of a script I named GetSqlServices_WMI with a parameter of $sqlserver.

param (
	[string]$sqlserver
)

# Call this with .filename.ps1 "servername"

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$wmi = New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $sqlserver
$wmi.Services | Select DisplayName, Type, StartMode, ServiceState, ServiceAccount | ft -auto

The call is in the code.  The results will have DisplayName, Type, StartMode, ServiceState and ServiceAccount listed in a table with column headers.

Great way to interrogate the Services from SQL Server.

Available Properties to you are as follows:

  • Parent
  • AcceptsPause
  • AcceptsStop
  • Description
  • DisplayName
  • ErrorControl
  • ExitCode
  • PathName
  • ProcessId
  • ServiceAccount
  • StartMode
  • Type
  • IsHadrEnabled (because I am using 11.0 SMO)
  • StartupParameters
  • Dependencies
  • AdvancedProperties
  • Urn
  • Name
  • Properties
  • UserData
  • State

With Methods

  • Alter
  • ChangeHadrServiceSetting (because I am using 11.0 SMO)
  • ChangePassword
  • Equals
  • GetHashCode
  • GetType
  • Initialize
  • Pause
  • Refresh
  • REsume
  • SetServiceAccount (handy little guy)
  • Start
  • STop
  • ToString
  • Validate

The link to the documentation for this object is here (Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer).

Enjoy and happy scripting.

@DBAduck

Powershell, SMO, SQL Server , ,

Opportunity to download Eval of Windows Server 2012

Use this link to download your copy of Windows Server 2012 for evaluation.  I know that I have downloaded and installed it for evaluation.

http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200128600

Have fun, it is a great upgrade.

 

SQL Server

SMO and Index Fragmentation

When working with SMO and Indexes there are times that you want to get the fragmentation of this particular index.  In SMO there is a method on the Index called EnumFragmentation.  When you call this method, there is a bug in SMO out on Connect (SMO Index Enum Fragmentation…) that you should vote for.  This is a very impactful bug as this is the only way in SMO to get the Fragmenation information on an Index.

The bug is this.  When you call EnumFragmentation() you get the entire Database fragmentation by index instead of for the Index that you are calling it from.  Normally what you would expect is to see

select *
from sys.dm_db_index_physical_stats(9, 39399393, NULL, NULL, 'SAMPLED')

Instead you see

select *
from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED')

This is crazy as you only get a limited set, which I know you can change with the method above by specifying the type of Detail (‘DETAILED’, ‘SAMPLED’, ‘LIMITED’) but it defaults to LIMITED.

Vote up the bug if this is something that affects you and your work.

Powershell, SMO , , ,

Networking: What exactly is it, and how do you use it (appropriately)?

I have had the opportunity lately to tap into “Marketing” something to get some traction on a Vote for Me scenario.  It made me wonder about Networking and what it really is and what I would use it for.  There are many spammers and others out there who would just send anything and everything out to the masses in the name of Marketing.  I thought about this concept and this contest for a Cute Girls Hairstyle blog, that I am trying to get people driven to. This a neighbor of mine and I think that it is a nice gesture to support their cute family in this contest.

Now when people think of Marketing, that word, in and of itself can make you think of many things about what it is.  That is why I wanted to write this post about Networking.

When someone wants to “tap his network”, what kind of things are fair game?  I am part of many networks, LinkedIn, FaceBook, Microsoft MVP, Twitter, Blogging and some others.  Now when you think of the purpose of those networks, are there topics in them that are taboo?  If you opened the floodgate on them, would you have problems with complaints?  What exactly is “tapping the network” in these cases?  Many could successfully argue about LinkedIn being more professional contacts, Facebook being highly social and only that, MVP being Technical and Twitter being a hybrid of all those.  So how do you use them to get traction on something of a cause or contest or whatever, that you feel strongly about helping out with?  Would you cry foul if this came over a professional networking site?  Maybe, maybe not.

Here are my views, and I encourage you to weigh in on this topic, because it would be interesting to compile thoughts and post them about how this affects the online universe we all live in every day.

I think that networks are a great tool for finding jobs, friends, viewpoints and information about topics.  Each one seems to have a purpose, and I think it would be out of line to violate the purposes of those networks. A list of purposes are below and what I think they are used for.

  • Professional networks are mainly used to connect people in like professions for a few reasons.  The first reason I think of is needing help with problems that you just need another set of eyes.  This can get you in trouble a little in the IT world as it could be construed as “free consulting”, but could be valuable to both parties, so you could choose to overlook or to scold in this one. 
  • The next reason would be Job Search and contacting about jobs. This is a tricky one because of the recruiting that goes on.  Recruiters get in your network, and yet they are not technical like, at least in most cases.  So do they belong?  The job potential may be one that you could overlook their involvement in your network at that level, but I am not sure that it is the right networking opportunity and should be shied away from.
  • People connections.  This one can be done on many networking sites, and is probably appropriate on the majority of networks.  But at the same time, probably the most tricky, because of the boundaries of the above networks, when your topic is not in harmony with the purpose of the networks.  You can be friends with many in the professional networks, but how would your message be received about this contest above that I have pointed to?  This is tricky, is the best answer for me to this one.
  • Association networking.  You belong to a group or an association and you want to use this network for the above purpose.  This is probably out of line here as well, due to the charter of the organization or association.

With these thoughts in mind, I just find it frustrating that there is not a better way than Facebook to communicate out something that you want to get people involved in.  Now there is one network that I neglected to put down for a reason, and that is because it deals with a valuable sanctuary that most if not all of us have, and that is the “Email Box”.  Because of all the spam we get out there, it seems that the email box is being protected and is not seen at first as a networking method.  I just have been having a dilemma on how to get the word out to people that I know would vote or participate, but being overly respectful of all the requests for our time that we have in this ever changing world.  As we get more fast-paced, and crazy with time constraints, it becomes easy to have 5+ networks and to not be sure how to use each one of them appropriately.

For this contest, I have turned to Facebook, Blogging, LinkedIn as a status update instead of a mass message, Twitter as a plea for assistance, and I will turn to my family on Email and certain others that I know would not be offended in this venture.  What do you all think?  Where would this contest be appropriately submitted for help to get votes?  What are networks used for in your opinion?  And a topic for another day, how do you build networks to a level of effectiveness to assist you when you are in need of assistance?  I am sure that I have not done this topic justice, but wanted to get it out there so that I can continue to think about it, but get some weigh-in about your thoughts about “Networking” as it has been very much on the forefront of my mind lately with requests to “tap into my network” for assistance with a myriad of things.

Thanks for listening.

Networking

Powershell, SMO and Database Files

In response to some questions about renaming a physical file for a database, I have 2 versions of Powershell scripts that do this for you, including taking the database offline and then online to make the physical change match the meta-data.

First, there is an article about this at http://msdn.microsoft.com/en-us/library/ms345483.aspx.  This explains that you start by setting the database offline, then alter the database and modify the filename then set it back online.  This particular article does not show the physical renaming of the file, but it is necessary since the ALTER DATABASE MODIFY FILE statement only changes the metadata of the database in master.

There is a procedure to do this with SQL CLR and TSQL, but I chose to illustrate it in Powershell and SMO (using SQL Provider as well as straight SMO).  The SQLCLR version is by a SQL Server MVP, Ted Krueger (Blog | Twitter) and can be found at this link.

My version will be based on Powershell and SMO with a smattering of SQL Provider provided in SQL 2008+.

Code Snippet
  1. Add-PSSnapin SqlServerCmdletSnapin100
  2. Add-PSSnapin SqlServerProviderSnapin100
  3.  
  4. $servername = "localhost"
  5. $instance = "default"
  6. $dbname = "N2CMS"
  7. $logicalName = "N2CMS"
  8. $NewPath = "c:\program files\microsoft sql server\mssql10_50.mssqlserver\mssql\data"
  9. $NewFilename = "N2CMS_4.mdf"
  10.  
  11. if(Test-Path "sqlserver:\sql\$servername\$instance\databases\$dbname")
  12. {
  13.         $database = Get-Item("sqlserver:\sql\$servername\$instance\databases\$dbname");
  14.  
  15.         $fileToRename = $database.FileGroups["PRIMARY"].Files[$logicalName]
  16.         $InitialFilePath = $fileToRename.FileName
  17.  
  18.         $fileToRename.FileName = "$NewPath\$NewFilename"
  19.  
  20.         $database.Alter();
  21.         $database.SetOffline()
  22.         Rename-Item -Path $InitialFilePath -NewName "$NewFilename"
  23.         $database.SetOnline()
  24.         Write-Host "File Renamed"
  25. }
  26. else
  27. {
  28.         Write-Host "Database does not exist";
  29. }

If you notice, I first add the Snapin that is the SQL Provider.  If you already have it loaded in your Powershell Profile, then you can just omit those lines.  If you do not have SQL 2008 objects installed on this machine, then you will notice that the Snapins don’t load either.  In that case you would just use the version below.

In the SQL Provider version you will see a Powershellism with Test-Path and you are using a path, it just is a SQL provider path that points to the Database and makes sure that it exists.  Compare it to the other version where you are looking at the Server.Databases[$dbname] and then you get the filegroups and then the files.

This version of the script is purely SMO and PowerShell.  First you load the objects from SQL Server 2005/2008 SMO.

Code Snippet
  1. # Always
  2. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
  3.  
  4. # Only if you don't have SQL 2008 SMO Objects installed
  5. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | out-null
  6.  
  7. # Only if you have SQL 2008 SMO objects installed
  8. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | out-null
  9.  
  10. $servername = "localhost"
  11. $instance = "default"
  12. $dbname = "N2CMS"
  13. $logicalName = "N2CMS"
  14. $NewPath = "c:\program files\microsoft sql server\mssql10_50.mssqlserver\mssql\data"
  15. $NewFilename = "N2CMS_4.mdf"
  16.  
  17. $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $servername
  18. if($server.Databases[$dbname] != $null)
  19. {
  20.         $database = $server.Databases[$dbname];
  21.         $fileToRename = $database.FileGroups["PRIMARY"].Files[$logicalName]
  22.         $InitialFilePath = $fileToRename.FileName
  23.         $fileToRename.FileName = "$NewPath\$NewFilename"
  24.         $database.Alter();
  25.         $database.SetOffline()
  26.         Rename-Item -Path $InitialFilePath -NewName "$NewFilename"
  27.         $database.SetOnline()
  28.         Write-Host "File Renamed"
  29. }
  30. else
  31. {
  32.         Write-Host "Database does not exist";
  33. }

This version will use the Server object to get the database that you are looking for so that you can get at the files.  The file you are looking for is the $logicalName so that you can get the PhysicalName of the file.  Then using the builtin CmdLets to handle Files operations, a Rename-Item is issued to rename the initial path to the new one.

Operation order:

  • ALTER DATABASE dbname SET OFFLINE
  • ALTER DATABASE dbname MODIFY FILE
  • Rename-Item from old to new
  • ALTER DATABASE dbname SET ONLINE

I did not put these in a function, but they could easily (or not too hard that is) put together using Parameters and make it versatile for other situations, but that is for you to do so that it works for your scenario.

Love PowerShell!

Powershell, SMO, SQL Server