SQL SMO in PowerShell on server without SQL installed

In order to use SQL SMOs on a server where SQL is not already installed, download and install the following SQL Feature Packs for whatever version you’re using, in the order listed:

1–Microsoft System CLR Types for Microsoft SQL Server

2–Microsoft SQL Server Shared Management Objects

3–Microsoft Windows PowerShell Extensions for Microsoft SQL Server

Before you can add assemblies in your script, you’ll need to reboot the server where the Feature Packs were installed.

To add assemblies, use the Add-Type command:

Add-Type -Assembly Microsoft.SqlServer.SMO

SQL SMO in PowerShell on server without SQL installed

SharePoint failure after web part update

After putting a new web part in production on our SharePoint 2010 site and running Update-SPSolution to put the new code in place, we got the following errors:

IIS7 Application Pool is unavailable. The IIS 7 Application Pool named . . . is unavailable as the Application Pool has been stopped.

Web Site binding is already in use. The World Wide Web Publishing Service (WWW Service) did not register the URL prefix http://*:80/ for site 1. The necessary network binding may already be in use. The site has been disabled. The data field contains the error number.

The SharePoint site simply displayed a Service Unavailable message.

Rebooting the web and application servers had no effect.

In Sites, the Default Web Site was Stopped, but when I attempted to start it, I got the Web Site binding is already in use error message.

To fix it, I clicked on Application Pools. Two of the Application pools (one for our company intranet site and SharePoint Web Services Root) were stopped. I manually started them, and the intranet came back up.

SharePoint failure after web part update

SCVMM error after applying CU5 to SQL 2012 SP2

Virtual Machine Manager stopped working after I applied CU5 to the SQL 2012 SP2 database. The following error message was recorded in the Event Viewer on the VMM server:

Service cannot be started. Microsoft.VirtualManager.DB.CarmineSqlException: Unable to connect to the VMM database because of a general database failure.
Ensure that the SQL Server is running and configured correctly, then try the operation again. —> System.Data.SqlClient.SqlException: Login failed for user ‘{Service Account}’. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

The database was housed on a shared server and none of the other applications were reporting issues. There are several potential causes for this error reported in various blogs. One of the potential causes is Agent XPs being disabled during the update; however, when I ran sp_configure, it showed Agent XPs as being enabled.

None of the other causes made much sense in this case, and the following message was reported in the SQL Error Log:

Configuration option ‘Agent XPs’ changed from 1 to 0. Run the RECONFIGURE statement to install.

I ran the following even though it was showing Agent XPs as enabled:

sp_configure 'Agent XPs', 1

After that, the Virtual Machine Manager connected to the database and worked fine.

SCVMM error after applying CU5 to SQL 2012 SP2

Yes, I want to be mentored by you!

I’ve been giving this a lot of thought, and there are really two different questions here: 1) Why do I want to be mentored, or, more specifically, what do I hope to gain from this? and 2) Out of all the qualified applicants, why should Paul Randal choose me?

The first question is easier, so I’ll tackle that one first.

Why do I want Paul Randal to mentor me?

Because Kimberly Tripp didn’t offer.

I’ve never had a mentor. I’ve never even had a very good career adviser. I’ve substituted a lot of research, deliberation, and sometimes angst trying to decide where and how to focus my efforts. I try to learn from my mistakes, so I’ve done pretty well for myself despite a lot of wrong turns and bumps in the road.

But I’m at a turning point in my career, and I could really benefit from some good advice from someone with more experience and a wider viewpoint. I started my SQL career as an Accidental DBA at an internet startup that had outgrown Standard Edition. After a few years, I switched to a medium-sized retail organization with a strong quality of life focus. In both cases, I’ve been the authority in my environment (and completely indebted to the Google community). I’ve studied my butt off, joined the local PASS chapter, and I’ve reached a point where I’m confident that I’m a capable DBA, I’m sure that I’m in a career field that I love, but I’ve got a lot of questions about where to go from here.

Do I need to start consulting or switch to a different job to experience new SQL issues? Should I just keep reading and studying to gain more theoretical knowledge? What SQL-related skills or other technologies could I best leverage in my current environment? Will blogging, and hopefully, the concurrent change in my approach to my job be enough to keep me engaged for a couple years? I know that no one else can tell me the path that’s right for me, but I think the road could be quite a bit easier with good directions. I’ve gotten lost a lot less since I got GPS, so I can only imagine what good career advice can do for me!

Before this opportunity presented itself, I set New Year’s Resolutions for 2015 to join Twitter (based on Glenn Berry’s advice) and start blogging (for my own reasons as outlined in my inaugural blog post). I already knew that it’s time for me to start giving back. I’m just not really sure how best to do that.

Why should Paul Randal want to mentor me?

This one is nearly impossible to answer. I’m certain that there are many intelligent and talented people who have applied for this opportunity–it’s sort of crazy for someone like me not to at least take a shot at it.

The best answer that I’ve come up with is that I began my career by studying to become an English professor. I taught writing to college students for four years and studied literature for nine. It was long enough to learn that I hadn’t chosen the best career for me, but it did set me up with fairly strong writing and teaching skills. Because I have those, I think I can become a strong contributor in the SQL community.

And, because I don’t think it can hurt, here are a few other things to consider:

  • I can stand on my head for a full two minutes.
  • I’m quite good at recommending wine pairings for food.
  • I’ve adopted and grown to love Molly, who is quite possibly the worst dog ever. Seriously, she’s the anti-Lassie: she’d push Timmy in the well.
  • I’m a good person. I recycle. I mow my lawn. I even stop for pedestrians in crosswalks.
  • I can recognize that I need to stop this bullet list at this point because it’s not trending well.

I would love a little help.

And I’ll do my best to pay it forward.

Yes, I want to be mentored by you!

Permissions to Backup to a Network Share with Ola Hallengren’s Maintenance Solution

I recently moved my SQL backups to a new file server and got tripped up granting permissions to a couple of servers. When I figured out my mistake, I realized that I had made the same mistake when I originally implemented Ola Hallengren’s Maintenance Solution a couple years ago, so I’m going to blog about it in hopes that I can save myself an hour or so next time!

The first gotcha: if you’re using separate accounts for the SQL Agent Service and the SQL Database Engine, the SQL Database Engine account needs full control of the network drive and the shared drive.  Per the documentation, BOTH accounts need full control of the network drive and share for the backup to succeed, but on SQL 2012 running on Windows 2012 R2 writing to a Windows 2012 R2 share, only the Engine account needs permission on the remote server to backup successfully.

If you grant permission to the SQL Agent Service account only, the error message that is generated in the DatabaseBackup log file isn’t very helpful: xp_create_subdir() returned error 183, ‘Cannot create a file when that file already exists.’

The second gotcha applies to SQL Server Express edition installations that are running the SQL Database Engine under the NT AUTHORITY\NetworkService account. For these installations, permissions need to be granted to {DomainName}\{ServerName}$.  So if my domain is DOMAIN1 and my server is named Server1, permission would be granted to DOMAIN1\Server1$.

Permissions to Backup to a Network Share with Ola Hallengren’s Maintenance Solution

Why I Decided to Start Blogging

On Twitter this morning, Paul Randal announced his SQLSkills blog count: 865! That’s impressive. And kind of intimidating for someone who’s just getting started–I’m not sure why someone would spend time reading my little blog when they could be reading any of his 865 posts, but since he’s offered to mentor six lucky SQL students and the application is via blog post, I decided that it’s time I got started. If you stumble across this and decide that I’ve got no business being in the blog-o-sphere, you can blame Paul Randal for his unintentional encouragement.

In all fairness, Paul Randal is the reason that I’m finally blogging. I decided that I should blog over a year ago. At that time, I was planning to rename sa on our ERP system database server and add a fake sa login with more limited permissions. Our ERP system vendor requires that we use the sa account with a very insecure 4-character password that’s hard-coded into their system. My predecessor’s predecessor had made this change, but when the system was upgraded by my predecessor, the configuration was botched and the real sa account was again being used.

To prepare, I googled changing the sa password to find out if there were any hidden gotchas that I should be aware of. And I stumbled across my predecessor’s predecessor’s blog post describing the steps he took to do the project the first time. I was shocked. It was exactly the information that I was looking for–specific to my environment.

That day, I went on to read all of his posts from the times that he worked for my company. He had posts that explained why he had purchased certain software packages, some of the things he struggled with when he worked at the company, and some posts that just helped me know him better. Reading about his experiences first hand was invaluable.  I knew that he was well respected at the company, which made it more challenging when I wanted to take a different approach from what he had established.  Understanding where he was coming from made it much easier to decide how to move forward.

That was the day that I decided that I should blog.  It was the day that for me, it sank in at a personal level that even though there are many more authoritative voices in the community than mine, I still may be able to record information that will help someone else.

I owe a huge debt of gratitude to all of the fantastic leaders of the SQL community who regularly and freely share their expertise–I could not be where I am today without the benefit of all of your information. For this, my first blog, I owe a special thanks to Brent Ozar for all of the practical advice on how get started blogging:


Not that I want more competition, but since I referenced Paul Randal’s mentorship competition, here’s that link, too:


Why I Decided to Start Blogging