Updating Extended Properties of a Database using SQL Server SMO

Updating the extended properties on a database using SQL Server’s excellent Server Management Objects API is not as straightforward as setting the value and calling update.

The database.Alter() method needs to be called both before and after updating the value. I had to lookup the code of El Pluto‘s awesome SQL Server Extended Properties Quick Editor project on CodePlex to figure this out.

using Microsoft.SqlServer.Management.Smo;
 
/// <summary>
/// Set's the extended property of a database.
/// </summary>
/// <param name="serverName">The name of the SQL Server.</param>
/// <param name="databaseName">The name of the database.</param>
/// <param name="propertyName">The name of the extended property.</param>
/// <param name="value">The value of the extended property.</param>
private void SetExtendedProperty(string serverName, string 
    databaseName, string propertyName, string value)
{
    var server = new Server(serverName);
    var database = server.Databases[databaseName];
 
    database.Alter();
    if (!database.ExtendedProperties.Contains(propertyName))
    {
        database.ExtendedProperties.Add(
            new ExtendedProperty(database, propertyName, value));
    }
    else
    {
        database.ExtendedProperties[propertyName].Value = value;
    }
    database.Alter();
}
 
Updating Extended Properties of a Database using SQL Server SMO

Limit SQL Server memory usage on your workstation, laptop or VM

Here’s a neat tip I learnt over the weekend.

All SQL Server instances are by default set up to use all the memory available on your workstation.

This is ideal when you have SQL Server running on it’s own dedicated server hosting, not so ideal when you have SQL Server installed on your laptop, workstation or even on a SharePoint VM.

Here’s what MSDN says

if SQL Server is one of several server applications running on a single computer, the system administrators may need to control the amount of memory allocated to SQL Server. In these cases, you can use the min server memory and max server memory options to control how much memory SQL Server can use.

In the Server Memory Options page they go on to say:

When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory

  • Use max server memory to control memory usage.
  • Use min server memory to control memory usage.
  • Do nothing (not recommended).

Which brings us to how we can set the maximum limit. Quite easy. Just connect to each SQL Server instance and set the maximum memory to a more palatable value.

Here’s a visual walk through to limit the maximum memory usage to 512MB for your SharePoint 2010 instance (if you installed it on Windows 7).

  1. Start SQL Server Management Studio (or SSMS Express) and connect to your SQL Server instance (SharePoint in this case): localhost\SharePoint

  1. Right-click on the instance node and select Properties.

  1. Click on the Memory node you’ll notice that the Maximum Server Memory is set to 2,147,483,647MB change it to a lower limit like 256 or 512MB. Click OK and your all set.

If you prefer SQL the same can be done with the following commands.

Enable advanced options:

USE master

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

Set the maximum amount of memory to 512 MB:

USE master

EXEC sp_configure 'max server memory (MB)', 512

RECONFIGURE WITH OVERRIDE

Display the newly set configuration:

USE master

EXEC sp_configure 'max server memory (MB)'

Set ‘show advanced options’ back to default:

USE master

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

Limit SQL Server memory usage on your workstation, laptop or VM

SQL Server 2008 Service Weirdness

Two weird things I learnt about SQL Server while building the SharePoint 2010 Service Manager.

1. SQL Server Agent service for SQL Express is bogus

Whenever Service Manager started, the SQL Server Agent service for the SharePoint (SQL Express) instance  would immediately stop with either of the following errors logged in the Windows Event Log.

  • The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]
  • SQLServerAgent could not be started (reason: Error creating a new session).

The self explanatory title of the bug filed on Connect says it all ‘SQL Express installs SQL Agent Service for no apparent reason‘. Apparently the team cutting down features for the Express edition forgot to tell the Agent team that they weren’t needed in Express.

2. SQL Server VSS Writer Service : Startup Type get’s reset to ‘Manual’

The Service Manager has a feature that let’s you set the startup type of all the SharePoint and related services to Manual. This way they wouldn’t automatically startup when Windows starts hence leaving the workstation to boot faster.

The Service Manager only shows the ‘Stop Automatic Startup’ button if the Startup Type of any of SharePoint services are set to Automatic. While testing the feature I released that after sometime the button automatically showed up even after I had set all the services to start manually.

That was when I figured out that even if I manually change the service (through Control Panel) to start manually, something would change the startup to Automatic after a while. I haven’t figured out what changes it’s startup type to automatic but I’m guessing that’s by design. My workaround for the Service Manager was to ignore the startup type of the VSS Writer service when checking if all the services were set to manual.

SQL Server 2008 Service Weirdness