[us_page_title description=”1″ font_size=”1.8rem” inline=”1″]

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.

Read More

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:\SQL\localhost\default
$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.



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.


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

Read More