Using SQL Server DB for DSC

Currently DSC Pull Server supports using ESENT DB or MDB as back-end to maintain DSC Clients registration. However, this approach is not scalable for highly available scenarios (such as two Pull Server behind a load balancer) or globally distributed environments. For these cases, wouldn’t it be awesome to be able to use SQL Server as back-end? Well… there’s a workaround for it and, in this blog, I’ll show you how to do it.

What’s the approach?

In a nutshell, we will use a pretty old technology to achieve this; we will make use of the linked sources in Access in conjunction with an ODBC connection, the following diagram depicts the approach:

Without further intro, this is how you move from this:

to

Before start

• Use the same SQL Server Login ID and Password through out the whole process
• Stick to the DSN Name you choose, keep it all the way through.

Setting up the SQL Server DB

Depending on where you set your MDB, you might need to repeat this step twice: one in the computer you use to create the MDB and another time in the final SQL Server.

Gotcha: If you are doing these steps in a different SQL Server, for example, a SQL Express in your laptop rather than the final SQL Server; make sure the SQL Login ID and its Password match the one you plan to use later.

1. Create an empty database; in my case I called it “DSC” (pretty original)
   
2. Create a SQL Server Login which will be used to connect to this DB and grant the required rights. This User ID must match the one you will use in the actual SQL Server.
3. Run the following T-SQL script to create the DSC Schema. If you want to create it yourself, just get a copy of Devices.mdb and import it into SQL Server using “Import Data” Wizard

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Devices](
[TargetName] [nvarchar](255) NOT NULL,
[ConfigurationID] [nvarchar](255) NOT NULL,
[ServerCheckSum] [nvarchar](255) NOT NULL,
[TargetCheckSum] [nvarchar](255) NOT NULL,
[NodeCompliant] [bit] NOT NULL,
[LastComplianceTime] [datetime] NULL,
[LastHeartbeatTime] [datetime] NULL,
[Dirty] [bit] NOT NULL,
[StatusCode] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RegistrationData](
[AgentId] [nvarchar](255) NOT NULL,
[LCMVersion] [nvarchar](255) NULL,
[NodeName] [nvarchar](255) NULL,
[IPAddress] [nvarchar](255) NULL,
[ConfigurationNames] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[StatusReport](
[JobId] [nvarchar](50) NOT NULL,
[Id] [nvarchar](50) NOT NULL,
[OperationType] [nvarchar](255) NULL,
[RefreshMode] [nvarchar](255) NULL,
[Status] [nvarchar](255) NULL,
[LCMVersion] [nvarchar](50) NULL,
[ReportFormatVersion] [nvarchar](255) NULL,
[ConfigurationVersion] [nvarchar](255) NULL,
[NodeName] [nvarchar](255) NULL,
[IPAddress] [nvarchar](255) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Errors] [nvarchar](max) NULL,
[StatusData] [nvarchar](max) NULL,
[RebootRequested] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 

Setting up the ODBC Connection

Your ODBC connection should match you Microsoft Access platform. In my case, since I don’t have Microsoft Access installed in the server and I’m running Access 64-bit in my laptop, I created a 64-bit ODBC.

Gotcha: The DSN Name must match the one you plan to use later on the Pull Server.

1. Go to “Control Panel” -> “Administrative Tools” -> “ODBC Data Sources (64-bit)”
2. Select “System DSN” tab and click “Add…”
3. Select “Select Server” as the provider
   
4. Configure the connection name and the SQL Server to connect to (remember, in my case I’m doing this in my laptop where I have a SQL Express installed; if you do this directly using the final server, just put that server name in there). The DSN name you choose must be the same one you will use later on.
   
5. Configure the DSN to use SQL Server authentication and use the previously created SQL Login
   
6. Set the default database to the DSC one
   
7. And complete the wizard till you are done

Creating the customized Devices.mdb

1. Create a new empty mdb called Devices.mdb
   
2. Close and discard the default created table
3. Go to “External Data” tab and select “ODBC Database”
   
4. Choose “Link to the data source….”
   
5. A dialog to choose the DSN will pop-up. Select the tab “Machine Data Source” and select our previously created “DSC”
   
6. A pop-up prompting for the SQL Login password will be shown; enter the SQL Login ID and the password and click OK
   
7. Select which tables you want to link to, obviously you should choose the three DSC tables only and check the option “Save password”.
   
8. Then click “OK” and “Save Password” in the following pop-ups and you will get this
   
9. Rename the tables to remove “dbo_” so DSC can find the same table names it’s used to. Just select the table, hit “F2” and rename it
   
10. Close Access

Putting all together in the Pull Server

1. If you performed the steps described in “Setting up the SQL Server DB” on a different SQL Server than the one you plan to use for your Pull Server; then you should follow them again but now against the final SQL Server. Make sure the SQL Login ID and its password match the ones you used to create the MDB.
   
2. Since the Pull Server Application Pool is set to run as a 32-bit one, you should create a 32-bit ODBC connection. So, follow the steps described in “Setting up the ODBC Connection” and named same as the one you used to create the MDB
   
3. Copy the Devices.mdb file you created in step “Creating the customized Devices.mdb” to C:\Program Files\WindowsPowershell\DSCService
   
4. Update the Pull Server web.config to use Devices.mdb
   
5. That’s it.