You are browsing the archive for SQL.

Upgrading from ConfigMgr 1702 to 1706 gives you “Microsoft SQL Server reported SQL message 2627, severity 14” in your status messages

7:58 am in 1702, 1706, ConfigMgr, ConfigMgr CB, Software updates, SQL, System Center by Kenny Buntinx [MVP]

If you upgraded your ConfigMgr 1702 or earlier environment to Configmgr 1706 and in the status messages after the upgrade you get :

Microsoft SQL Server reported SQL message 2627, severity 14: [23000][2627][Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint ‘SUM_DriverUpdates_PK’. Cannot insert duplicate key in object ‘dbo.SUM_DriverUpdates’. The duplicate key value is (d8483f4f-0390-49db-b251-faf884dd8eaf

Be aware that the Product Group are aware of this issue and are on a working on a fix. The result of this problem is that admins cannot see new Surface driver updates.

Other than that, nothing else is impacted so it can be ignored.

Hope it helps ,

Kenny Buntinx

MVP Enterprise Mobility

KB2688247: SQL Server cumulative updates must be manually installed on secondary System Center 2012 Configuration Manager sites that use SQL Server Express

10:21 am in ConfigMgr, ConfigMgr 2012, ConfigMgr 2012 SP1, ConfigMgr V.next, SCCM 2012, SCCM 2012 SP1, SQL, SQL Cluster, SQL Reporting services by Kenny Buntinx [MVP]

 

System Center 2012 Configuration Manager: The original RTM version of Microsoft System Center 2012 Configuration Manager installs Microsoft SQL Server Express 2008 R2 Service Pack 1 (SP1) when you deploy a new secondary site. The minimum supported SQL Server version is Cumulative Update 4 for SQL Server 2008 R2 Service Pack 1 (SP1).

System Center 2012 Configuration Manager Service Pack 1:  System Center 2012 Configuration Manager SP1 requires Cumulative Update 6 for SQL Server 2008 R2 Service Pack 1 (or SQL Server 2008 R2 SP2).

You must manually install the Cumulative Update for SQL Server 2008 R2 SP1 on the new secondary site after the site is installed. More information here : http://support.microsoft.com/kb/2688247

Hope it Helps ,

Kenny Buntinx

Configmgr 2012 RTM / SP1 and remote management points not healthy when running Configmgr DB on a SQL cluster.

7:01 pm in ConfigMgr 2012, ConfigMgr 2012 SP1, SCCM 2012, SCCM 2012 SP1, SQL, SQL Cluster by Kenny Buntinx [MVP]

 

This week I needed to upgrade our TAP customer Configuration Manager 2012 infrastructure from RTM to SP1 beta. Our SQL Server 2008 R2 environment is clustered.

A bit of background information :

At the time I installed this customers Configuration Manager 2012 RTM’s infrastructure , I ran into this issue : http://scug.be/sccm/2012/05/24/issue-configmgr-2012-secondary-sites-or-remote-dp-s-failing-to-serve-as-pxe-service-point and the solution here was to removing the SQL Server 2008 R2 Native client and installing the SQL Server 2008 Native client as we have seen some issues with a SQL 2008 R2 Native Client talking to a cluster on the SQL technet forums.

My SQL Database server is a two-node SQL Cluster, with SC-SCCM-CLUSTER as the name. In addition to the primary site DB, SC-SCCM-CLUSTER also hosts the WSUS database (WSUS is set up on the primary site server as it will have a SUP). The SQL cluster is Windows 2008 R2 SP1, and the SQL Cluster is a 2-node SQL Cluster 2008 R2 SP1 with CU6 (required for SP1).

Now the upgrade and the MP issues :

A fairly simple task but you need to keep a couple of things in mind when upgrading from Configuration Manager 2012 RTM to SP1 . In my environment I have 1 Primary site server and 35 remote sites ( Remote DP with PXE ) where 7 of them have a Management Point installed .

After the upgrade suddenly the Remote Management Points became unhealthy and throwing errors with status code 500 in the mpcontrol.log file  .

*** [08001][-2146893019][Microsoft][SQL Server Native Client 10.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.
*** [08001][-2146893019][Microsoft][SQL Server Native Client 10.0]Client unable to establish connection
*** Failed to connect to the SQL Server, connection type: MP_CONTROL_ACCESS.
Failed to get connection to the configured SQL database.

image

The solution :

The way we resolved the issue was to make the SQL Server certificate trusted. Go to the SQL cluster server and pick a node .

Here are the screenshots of SQL server certificate which you need to export and import to “Trusted Root Certification Authorities” list. You may need to repeat the steps on both SQL nodes and all REMOTE Management Point servers

image

Export the certificate and import it over here . When successful you should see this :

image

When it is done , reboot your server or reboot your Configmgr services and go into the MPcontrol.log . You will see the server talking correctly

image

image

The management point finds it database and can connect to it thanks to the certificate import . You can also verify and look into Certmgr.log at the primary site .

Hope it Helps ,

Kenny Buntinx

Important SQL information for a Configmgr 2012 CAS and Primary site.

4:46 pm in ConfigMgr, ConfigMgr 2012, ConfigMgr V.next, sccm, SCCM 2012, SQL by Kenny Buntinx [MVP]

You are probably deploying your CAS if you are a larger organization with specific needs. One of those needs is if you need more than 1 primary site because of geological/political issues. The version ( standard or enterprise ) of SQL that you might want to use on the CAS becomes very important if you are a larger organization.

If you use SQL Server Standard for the CAS, you can support up to 50,000 clients. But, if you use SQL Server Enterprise on the CAS, you can support up to 400,000 clients.

However you need to very well look at your growth plan because if you install SQL Server Standard on the CAS and then your company grows to 70,000 clients , you will have an issue.

You would say : “Why do I have an issue?“ , I just upgrade the SQL version on your CAS to SQL Enterprise to support those extra clients. However it will not work.

When the CAS is installed , it will be configured based on which target version of SQL is installed (Std or Ent). Once installed on SQL Standard, the table is a single partition, and will not be switched to multiple partitions when upgrading to SQL enterprise.

A CAS wipe and re-install is required to support more than > 50K clients and that will not be so easy or at least it will generate some additional problems.

Now you are might stuck about the question: “What about the SQL version on the primary sites ?” . Primary sites support up to 100K clients , but do NOT require SQL enterprise . SQL enterprise is only needed for the CAS if you need to support up to more than 50 K clients .

Little recap:

*CAS*

– Less than 50K clients –> SQL server standard is sufficient as long as you are not going over the 50K limit (Upgrade to SQL enterprise is NOT working when crossing the 50K limit)

– More than 50K clients –> SQL server Enterprise is needed.

*Primary Site*

– SQL server standard is always sufficient !

Hope it helps ,

Kenny Buntinx

SQL 2008 R2 failover cluster for system center: Lessons Learned (the hard way)

12:22 pm in SQL, SQL Reporting services, System Center by Kenny Buntinx [MVP]

I had assigned a task to install 5 SQL 2008 R2 cluster instances and on one of them had Analysis Services installed on a two node SQL Server 2008 R2 cluster (along with db engine).

Now I discovered that Analysis Services was not needed on one of the instances.

I thought : “No Problem , let’s remove” .

How do I remove this service from the cluster?

I tried the add/remove programs and command line option and both end up with the message: "The selected instance is clustered and cannot be removed as specified. To remove the selected instance, select "Remove Node" on the Installation Center or specify /Action=RemoveNode from the command line."

I tried removing one node hoping I could then uninstall from the only remaining node but still could not remove Analysis Services.

I’ve searched Google  Bing and discovered this article that explained that “You cannot add or remove features to a SQL Server 2008 or SQL Server 2008 R2 failover cluster”: Http://support.microsoft.com/kb/2547273

So I learned it the hard way. Think before you install a feature on a SQL Cluster , because once it is installed , there is no way back !!

Hope it Helps ,

Kenny Buntinx

Softgrid 4.1 migration towards ConfigMgr with App-V 4.6 Integration : The story of automation , Part 4.

11:20 am in ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr SP2, configmgr2007, ConfigMgr2007 R3, Deployment, Installation, migration, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, sccm2007, SQL by Kenny Buntinx [MVP]

Today , I will continue explaining my little migration project to migrate away from a standalone Softgrid 4.1 infrastructure towards a fully integrated SCCM2007 SP R2 App-V 4.6 infrastructure . You can read my previous posts right here :

 

Below I will discuss the migration scenario once more in a few bullet points to migrate away from the Softgrid 4.1 standalone environment :

1. Deploy the new app-V 4.6 clients on all workstations and check for inconsistencies (luckily all packages where sequenced with 4.2) DONE in Part 1.

2. Pull all existing Softgrid 4.2 sequenced packages thru the new App-V 4.6 Sequencer to avoid any complications. DONE in Part 1.

3. Import all packages into ConfigMgr 2007 SP2 R2 DONE in Part 1.

4. Create all necessary collections and create a dynamic membership query with a AD group name. Partially explained in Part 2 , DONE in Part 3.

5. Create all the necessary advertisements.

6. Switch over from Softgrid standalone to Configmgr 2007 R2 SP2. (Think about network impact !)

 

So that will leave us to creating all the necessary advertisments (remember 400 Pieces) between the virtual Packages and the necessary collections.

As explained in Part 3, we have created a CSV file with all the necessary information as APP-V package Name , ResourceID & Collection Names. Now we need to add the PackageID , Advertisement Name and CollectionID to the CSV file  ( you can extract that kind of information out of your SQL DB (dbo.v_Package & dbo.v_collections)

image

Figure 1: The modified CSV file added with PackageID, Advertisement Name, CollectionID

Once the CSV file is filled up with the necessary data , we can start working on the script to create those advertisements based on that Csv file , as we need the PackageID , CollectionID and Advertisment Name tables to make it work .

Below I will post the VBS code to read out the csv file and create all advertisements :


 

Stay tuned for Part 5 very soon talking about the actual switch over from Softgrid standalone to Configmgr 2007 R2 SP2. ….

 

Hope it Helps ,

Kenny Buntinx

ConfigMgr Dashboards and Software Update General Compliance Gauge

11:25 am in AdminUi, ConfigMgr, ConfigMgr 2007, ConfigMgr 2007 R2, ConfigMgr Dashboards, ConfigMgr SP2, configmgr2007, Dashboards, Reporting, sccm, SCCM 2007, SCCM 2007 R2, SCCM 2007 R3, SCCM 2007 SP2, SCCM Dashboards, sccm2007, SQL, SQL Reporting services, SRS by Kenny Buntinx [MVP]

Microsoft released last year the Configuration Manager 2007 dashboards.A dashboard is basically a webpage containing multiple reports with graphs. The dashboard is a WSS 3.0 / MOSS 2007 solution that tries to give an high level graphical representation of the data contained in ConfigMgr. This enables ConfigMgr administrators to have multiple information in reports on a single page or in this case to help IT managers what is happening in their environment without all the report data .

A manager at a customer asked me if it would be possible to have a general percentage of Software Update Percent Compliance spread over all the Software deployment Management advertisements that are targeted to Pre-Production and Production.

To accomplish this written a SQL query to to this . As I don’t take all the credit for doing this , I will refer to them during my blog post .

I started with getting my initial report SQL code from Kent Agerlund (MVP configMgr) on http://blog.coretech.dk/confmgr07/config-mgr-inventory-and-reporting/status-report-for-software-update-deployments/

As soon as I used this SQL Query in My SCCM Dashboards , I quickly saw that I could not use the value “% Compliance” on the Y axis in combination with my “Deployment” name. It is shown below in the picture :

 

image

 

As I needed to have the Percentage of my total compliance spread over my SDM Lists ,  I needed to modify the query . I want to special thank Raghu Kethineni for his help understanding this query .This is how the SQL query should look like to use the value “% Compliance” on the Y axis in combination with my “Deployment” name :

 

Select Deploymentname, Available, Deadline,

cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) AS ‘% Compliant’,

  [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)]

From

(select

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

count(*) as NumberOfComputers

from v_CIAssignment a

join v_AssignmentState_Combined assc

on a.AssignmentID=assc.AssignmentID

join v_StateNames sn

on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,

      sn.StateName) as PivotData

PIVOT

(

SUM (NumberOfComputers)

FOR LastEnforcementState IN

( [Compliant],

  [Enforcement state unknown],

  [Successfully installed update(s)],

  [Failed to install update(s)],

  [Installing update(s)],

  [Waiting for another installation to complete],

  [Pending system restart],

  [Downloading update(s)])

) AS pvt

****** SQL Query end ******

After modifying the SQL query , the value appeared in the list as shown below in the attached picture :

image

 

If you need to select specific SDM advertisements , like I had to split up “Pre-Production” from “Production” , I had to modify my SQL query once again by adding a filter as show in the code below . In this case we want to only have the Production .

****** SQL Query ******

Select Deploymentname, Available, Deadline,
cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) AS ‘% Compliant’,
  [Compliant],
  [Enforcement state unknown],
  [Successfully installed update(s)],
  [Failed to install update(s)],
  [Installing update(s)],
  [Waiting for another installation to complete],
  [Pending system restart],
  [Downloading update(s)]
From
(select
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
count(*) as NumberOfComputers
from v_CIAssignment a
join v_AssignmentState_Combined assc
on a.AssignmentID=assc.AssignmentID
join v_StateNames sn
on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
where AssignmentName NOT like ‘%_PREPROD_%’
group by a.AssignmentName, a.StartTime, a.EnforcementDeadline,
      sn.StateName) as PivotData
PIVOT
(
SUM (NumberOfComputers)
FOR LastEnforcementState IN
( [Compliant],
  [Enforcement state unknown],
  [Successfully installed update(s)],
  [Failed to install update(s)],
  [Installing update(s)],
  [Waiting for another installation to complete],
  [Pending system restart],
  [Downloading update(s)])
) AS pvt

****** SQL Query end ******

 

If done and you saved your dashboard  query , It will look like this :

 

image

 

Hope It Helps  ,

 

Kenny Buntinx

New Configuration Manager Knowledge Base articles

8:25 pm in ConfigMgr, ConfigMgr 2007, sccm, SCCM 2007, SQL by Kenny Buntinx [MVP]

KB960065 – Updates are not deployed to some clients when you use WSUS or SUM to deploy updates in System Center Configuration Manager 2007 Service Pack 1
Sys Center Config Mnger 2007 AL EN-US

KB957255 – The wake-on-LAN (WOL) feature in System Center Configuration Manager 2007 Service Pack 1 wakes clients one hour earlier than expected
Sys Center Config Mnger 2007 AL  EN-US

KB960741 – The SMS_Executive service process crashes on a Configuration Manager 2007 Service Pack 1 site server when you use Intel WS-MAN Translator to provision computers that are equipped with AMT 3.2.1 chipsets
Sys Center Config Mnger 2007 AL EN-US

KB961105 – Error message when you try to import OSD drivers in System Center Configuration Manager 2007 Service Pack 1 systems: “The selected driver is not applicable to any supported platforms”
Sys Center Config Mnger 2007 AL EN-US

 

Hope it helps ,

Kenny Buntinx

Error message when you install SQL Server 2005: “There was an unexpected failure during the setup wizard” – Error 1603.

1:17 pm in SQL by Kenny Buntinx [MVP]

Today I ran into some serious trouble at a customer . I had to install SQL server 2005 for a System Center Essentials installation.Every time I installed the SQL server it just would quit with a funny error : “There was an unexpected failure during the setup wizard”. What the hell ?

Doing some investigation , it seems to be a know bug when your sources aren’t coming from the original CD’s.If you are downloading them from the MVLS site , you will run into this issue one day .

This problem occurs because the folders that contain the files that are required during the installation of SQL Server 2005 do not have a correct layout.
This problem is most likely to occur if you start the SQL Server 2005 installation from a folder on a network share or on a hard disk when the folder was copied from the SQL Server 2005 installation CDs.

Solution:To work around this problem, set the folders in the correct layout for the SQL Server 2005 installation. The SQL Server 2005 installation uses the following two folders:

  • Servers
  • Tools

These two folders must be under the same level of a folder or the root folder of a drive. The names of these folders must be exactly Servers and Tools. The Servers folder contains all the files that are required to install major SQL Server 2005 components, such as database engine. The Tools folder contains tools components and Books Online for SQL Server 2005.Then try installing sql from the path\servers\setup.exe

Hope it helps ,

Kenny Buntinx