I set up a new lab environment for SQL Server recently and wanted to use some of the new Windows Server technologies I hadn't tried before… which isn't that unusual because I'm a DBA not a sysadmin.
- Volume Activation Management Tool (VAMT) for managing my MSDN licenses.
- Which is installed as part of Windows Assessment and Deployment Kit (ADK).
- Which optionally installed SQL Server Express 2012 32-bit.
- Windows Server Update Services (WSUS) with its database on the Express instance.
With WSUS you can specify which kinds of patches to capture and being new I decided to pick everything!
The problem is that an initial pull can result in the Update Services console failing to open (especially on the Synchronizations tab) and also for running reports to time out. This is also documented on WSUS: Updates page times out while trying to view or approve updates but don't follow those instructions there as they're quite old.
The reasons WSUS seems problematic are:
- Nobody knows there's a manual with a whole appendix dedicated to database maintenance. This has a far better script. In my case however I used sqlcmd and popped in a copy of Ola Hallengren's Maintenance Solution. Then it was just a matter of running
Exec master.dbo.IndexOptimize @Databases = 'ALL_DATABASES'. - The initial pull consumes 5GB of database space. This isn't much until you consider the limitations on SQL Server Express (and even worse so in Windows Internal Database).
Those limitations include 1GB of memory though strangely I only saw 500MB in use, and 1 socket or 4 cores. I had configured VMWare Fusion in the GUI to assign 2 cores but as it was giving them a socket each only 1 was in use
Luckily being a lab I was able to use an MSDN license key to upgrade the Express instance to a fully fledged one without any limits. It's simply a matter of extracting the key from the DefaultSetup.ini file on the installation media, running the SQL Server Installation Center, going to Maintenance, and doing an Edition Upgrade.
Some other little snags include enabling TCP/IP in SQL Server Configuration Manager and configuring the Agent service to have access and start up. But it was surprisingly easy.
Since maintaining the indexes plus bumping up SQL Server Express I've had no further WSUS issues. But it makes me wonder how full-blown organisations survive; and they must be blaming WSUS as poor quality. Instead it just needs to be put onto a proper SQL Server so it can get the care and attention from a DBA that it needs and deserves.