You are browsing the archive for sql.

Avatar of alkin

by alkin

SCSM The report server cannot process the report or shared dataset

7:33 am in SytemCenter by alkin

 

I encountered the following issue while trying to run a report from the Service Manager console:

scsm

This was a total new installation (done by the Power Depoloyment Toolkit) and everything was installed fine without any errors so I doubled check my SQL, my datawarehouse server, my SQL reporting Service and by digging deeper and deeper into the reports themself on the SQL report I discovered the following error:

 

image

So that was the culprit!  To solve this issue, open the configuration of each report, go to data sources and select the DWDataMart Data source:

image

 

click OK and now you will be able to run reports again!

 

Thanks,

Alexandre Verkinderen

Avatar of alkin

by alkin

Poweshell Deployment Toolkit fails for SCCM

6:05 am in Uncategorized by alkin

 

I kept getting errors on the automatic deployment of SCCM with the PDT toolkit. The thing is that SCCM cannot work with Dynamic ports as described here:

So to fix this just change the SQL port in the to a fix port like shown below:

image

Normally PDT is going to verify if all the SQL settings are defined correctly during the pre-install validation except for this one. So if you want PDT to verify that the SCCM instance is not set to a dynamic port you can add:

<SQL>
<Port>True</Port>
</SQL>

…to the <Validation> section of the SC DB role in the variable.xml file

Thanks for Rob Willis to help me with this.

Alex

Avatar of alkin

by alkin

Setting SQL permissions with Powershell

12:08 am in SytemCenter by alkin

 

In the next couple of posts I’m going to blog about automating a System Center post-configuration deployment with Powershell. After you have deployed a typical SC environment you still need to do quite a lot of configuration. The following posts will cover how to automate this with Powershell.

 

The first powershell script is to define a specific user account as a sysadmin on a bunch of SQL servers:


<sub>#Variables
$Servers = @("srv01.contoso.com",”srv02.contoso.com”)</sub>

<sub>
#Connect remotely to the server</sub>

<sub>
Foreach($Server in $Servers)
{ </sub>

<sub>Invoke-Command -ComputerName $Server -ScriptBlock { </sub>

<sub>
#Set the system account as sysadmin</sub>

<sub>[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null </sub>

<sub># Create SMO Connections to both SQL Instances
$Svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "$server"
$svrole = $svr.Roles | where {$_.Name -eq 'sysadmin'}
$svrole.AddMember("User account”)
}
}</sub>

Thanks,

Alex

Avatar of alkin

by alkin

Community Day 2010

9:10 am in Uncategorized by alkin

Community Day is back with the Fourth Edition of this annual community event on Microsoft technologies!

Fourteen Microsoft User Groups combine their efforts to organize this unique networking and knowledge sharing
event. With so many new releases from Microsoft, the theme of Community Day 2010 is of course 2010: a new wave of products and technologies. This means that most sessions will be looking at Visual Studio 2010, Silverlight 4, Office 2010, SharePoint 2010, SQL Server 2008 R2, OCSR2…

Microsoft Community Day will take place on Thursday 24th June 2010 in Utopolis, Mechelen, where we will
bring together over 300 IT Pro’s and developers.

The Microsoft Community Day 2010 is supported by:

  • AZUG
  • BESUG
  • BIWUG
  • IT-Talks
  • Pro-Exchange
  • SCUG
  • SQLUG
  • ACCB.net/VBIB
  • Visug
  • WinSec
  • XNA-BUG
  • CLUG
  • DotNetHub
  • MyTIC

Practical event details
Location:
Utopolis Mechelen (Map)
Address: Spuibeekstraat 5 2800 Mechelen
Event date: Thursday June 24th
Start & end time: 8.30 – 18.30

Social
Community Day on Twitter: #comday2010

Avatar of alkin

by alkin

Cleaning the Localizedtext Table after SCOM upgrade to SCOM 2007 R2

1:48 pm in Uncategorized by alkin

After upgrading your OpsMgr 2007 components and upgrading your SQL environment to SQL Server 2008, you will need to run some post installation steps. These post installation steps are only needed if you have done an upgrade from OpsMgr 2007 to OpsMgr 2007 R2.

OpsMgr 2007 SP 1 had an issue with the localized text table continuing to grow. The main cause of this was converted MOM 2005 management packs running a lot of backwards-compatibility scripts, such as the converted Exchange 2007 management pack. The issue was that each event wrote additional data to the localized text table, which is not groomed. Over time, the Operational database continued to grow, which had a tremendous impact on OpsMgr performance. Kevin Holman writes about this at http://blogs.technet.com/kevinholman/archive/2008/10/13/does-your-opsdb-keep-growing-is-your-localizedtext-table-using-all-the-space.aspx; it is also documented on the SCUG.BE blog at http://scug.be/blogs/scom/archive/2009/05/28/optimizing-the-performance-of-your-opsmgr-console-and-reducing-db-size.aspx .

 

I’ve just did an upgrade at a customer site and after completing the post-installation steps as mentioned here http://technet.microsoft.com/nl-nl/library/dd789073(en-us).aspx and I was very surprised by the results!!

 

 

I checked the database size and the localizedtext table before running the cleanup script.

  • Database size before the cleanup script

image

28 gig of space!

image

  • 12.700.000 records in the localizedtext table before running the cleanup script.

 

In OpsMgr 2007 R2, the localizedtext table issue is gone. The table still exists but Microsoft has resolved the growing issue. Even if the issue is gone you will need to run the following SQL query once after the upgade to initially clean up the localizedtext and publishermessage tables. You can copy/paste the script from here http://technet.microsoft.com/nl-nl/library/dd789073(en-us).aspx 

 

The script has run for about 4 hours!

image

Now the results! :-)

 

Disk space after the clean up script:

image

7 gig of disk space; So that’s a gain of 20 gig of disk space! That’s amazing!

image

780.000 records found in the localizedtext table after running the clean up script !! So that’s about 12 million records lesser!!

 

For completing the post-upgrade steps I did a reindex of the database.

image

 

 

 

 

 

 

 

And now my console is running very fast and I’ve less data to backup! So after upgrading to SCOM 2007 R2 you will definitely have to run the clean up script!!

 

Greetings,
Alexandre Verkinderen

Avatar of alkin

by alkin

Export SQL reports in PDF landscape

12:53 pm in Uncategorized by alkin

I had to create some reports for a customer this week and the layout of the report didn’t satisfied my client. The reports are exported in portrait format and not in landscape format.

 

First part of the report

image

and on the second page I got the rest of my report.

image

 

Very ugly……….

 

How to export a scom report in pdf landscape format?

 Only solution here is to render the pdf in landscape format, so I start googling and found this blog http://www.bictt.com/blogs/bictt.php/2009/03/17/sql-reporting-services-render-pdf-in-a4-1  and also some information msdn http://msdn.microsoft.com/en-us/library/ms156281.aspx 

 

So to resolve our problem we have to add some code in the Rsreportserver.config file that you can find under “Program Files\Microsoft SQL Server\MSSQL.5\Reporting Services\ReportServer\”

 

You can specify rendering extension parameters in the RSReportServer configuration file to override default report rendering behavior for reports that run on a Reporting Services report server. You can modify rendering extension parameters to achieve the following objectives:

  • Change how the rendering extension name appears in the Export list of the report toolbar (for example, to change “Web archive” to “MHTML”), or localize the name to a different language.
  • Create multiple instances of the same rendering extension to support different report presentation options (for example, a portrait and landscape mode version of the Image rendering extension).
  • Change the default rendering extension parameters to use different values (for example, the Image rendering extension uses TIFF as the default output format; you can modify the extension parameters to use EMF instead).

 

Locate the render section in the rsreportserver.config file

 Capture

 

We are going to add two extra entries to the export list.

Below the pdf entry paste the following code:

<Extension Name="PDF (A4 Landscape)" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering">

        <OverrideNames>

            <Name Language="en-US">PDF in A4 Landscape</Name>

        </OverrideNames>

        <Configuration>

            <DeviceInfo>

                <OutputFormat>PDF</OutputFormat>

                <PageHeight>8.27in</PageHeight>

                <PageWidth>11.69in</PageWidth>

            </DeviceInfo>

        </Configuration>

</Extension>

<Extension Name="PDF (A4 Portrait)" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering">

        <OverrideNames>

            <Name Language="en-US">PDF in A4 Portrait</Name>

        </OverrideNames>

        <Configuration>

            <DeviceInfo>

                <OutputFormat>PDF</OutputFormat>

                <PageHeight>11.69in</PageHeight>

                <PageWidth>8.27in</PageWidth>

            </DeviceInfo>

        </Configuration>

</Extension>

Save the file and close it. I also did a IIsreset and a restart of my sql reporting services just to be sure.

When I want to export a report I have two extra entries:

image

 

Now when I want to export a report in landscape format I just choose to export in pdf in A4 landscape format and this is the result:

image

 

Everything perfectly fits on one page and I have a happy client now :-) !!  Thanks to Bob Cornelissen for his blog.

 

Hope this helps,

Alexandre Verkinderen

Avatar of alkin

by alkin

Community Day Session: Setting up & Configuring SQL Server to support your SCCM & SCOM environment

2:41 pm in Uncategorized by alkin

Kim Oppalfens and I did a session at community day together:

Setting up & Configuring SQL Server to support your SCCM & SCOM environment

System Center Configuration Manager and Operations Manager both need SQL Server. In this session we will show you what the best practices are for having a smooth running SQL server for our System Center environment. Having a good initial setup and configuration is not the only important thing, doing regular health checks on your SQL server is even more important. In this session we will discuss just about anything you need to know about setting up SQL from the SCOM or SCCM admin perspective. In this session you will learn how to work with the SQL team and set up your scom or sccm to talk with their standard SQL setup. This includes setups with remote sql servers, sql server services running as domain users, and named instances. Additionally we will talk about some SQL related configuration topics, like network libraries, firewall configuration, SQL collations, database recovery models and authentication modes.

At the end of this session you will know everything you need to know about SQL setup to support your scom or sccm environment.

Speaker: Kim Oppalfens & Alexandre Verkinderen

 

Slide Deck: http://scug.be/media/p/1136.aspx 

Pictures:

CommunityDay 2009

CommunityDay 2009

CommunityDay 2009

See you next year!

Alexandre Verkinderen

Avatar of alkin

by alkin

Opsmgr DB Create wizard System.InvalidOperationException

7:19 pm in Uncategorized by alkin

Today I wanted to install my OperationsmanagerDB on my SQL cluster 2005 SP2 actif-passive.

So I started the DBCreatewizard.exe and I got a very strange error mentioning something about

 

Severity: Error Message: Database creation failed. The database might have been incompletly created or modified. System.InvalidOperationException: An error occurred while trying to create the database on your SQL Server. Check your logs for more information. at Microsoft.EnterpriseManagement.Setup.DBCreateWizard.Program.LaunchDBCreation() at Microsoft.EnterpriseManagement.Setup.DBCreateWizard.SummaryPage.BackgroundThread()

 

clip_image002

 

To make a long story short:

We had a lab environment and production environment. Both environments are exact replicas: ip, users, servers, everything is the same. I did the install in the lab environment on my sql cluster and didn’t encounter any problems.

Now in production I had this strange error and asked my dba (that installed the sql cluster in lab and production) to confirm that everything was ok and that everything was the exact replica of the lab….

Now here comes the catch….and also the moment I wanted to kill my DBA….

I analyzed the dbcreatewizard error log that you can find here:

 

C:\Documents and Settings\user\Local Settings\Temp\1

image

And found the following sentence

 

10:31: CreateDB:  Attempting to create OperationsManager on ELSQL3E\WSSCOM threw the following sql exception Cannot use file ‘J:\SCOMOPLOG\OperationsManager.ldf’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

 

image

I checked on my cluster resource and indeed: the Log disk was missing as a dependency!!

image

So my production environment was not the exact replica as my lab environment. I had to add the Log file disk as a dependency for my sql resource group:

image

 

And now I was able to run the dbcreatewizard.exe succesfully!

 

image

 

Hope this helps,

Alexandre Verkinderen

Avatar of alkin

by alkin

Optimizing the performance of your Opsmgr Console and reducing DB size

9:12 am in Uncategorized by alkin

I had troubles with the Opmsgr Console performance at serveral clients.

One of the most important things to boost your console performance is to reduce the Opsmgr DB size.

 

If you want to know which tabel is taking most of your DB size run the following query:

Simple query to display large tables, to determine what is taking up space in the database:

SELECT so.name,
8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb,
Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
WHERE ‘U’ = so.type GROUP BY so.name  ORDER BY data_kb DESC

clip_image001

 

In my case the localizedtable was taking most of the disk space. Kevin Holman already blogged about this: http://blogs.technet.com/kevinholman/archive/2008/10/13/does-your-opsdb-keep-growing-is-your-localizedtext-table-using-all-the-space.aspx 

 

I followed his procedure, I will describe all the steps I did and has to say I was really suprise by the results! :-)

 

  • Count the rows in the localizedtable

select count(*) from localizedtext

clip_image002

In a normal environment with the typical management packs imported the total count of rows should be around the million. In one of my environments it was 22 milion!! So I defenitely needed to clean up this localizedtable!

 

  • Look at the most common events in the localized table

select messageid, ltvalue, count(*) as Count from publishermessages with(nolock)
inner join localizedtext with(nolock)
on messagestringId = localizedtext.ltstringid
group by messageid, ltvalue
order by Count DESC

 

In my case it was the exchange management pack that produced the most ammount of events.

 

  • Run the clean up script

Kevin Holman has provided a script to clean up the localized table. You can download it from his blog DeletePublisherMessages.zip .

Notice that this script is NOT supported by Microsoft, as it has not been thoroughly tested!  I’ve run the script in several environments now and didn’t run into issues but as a good Sys admin be carefull and take a backup of your opsmgr DB before running the script. Also make sure that your TEMPDB has enough space. And with enough space I really mean ENOUGH SPACE! Also the transaction logs of the TEMPDB will need a huge amount of free space. Also don’t run this scripts during the day when you have several operators running the opsmgr console. My CPU went crazy to 100 % processor time so my advice is to run the query at night or during the weekend.

 

 

  • Reindex the DB

USE OperationsManager
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable “Print ‘Reindexing ‘+’?’ DBCC DBREINDEX (‘?’)”

 

 

Now the results! :-)

 

Before running the script:

clip_image002[4]

After running the script:

image

 

Gain in diskspace:

OperationsManager free space in %:

 

image

 

OperationsManager free space in MB:

clip_image002[10]

 

 

 

 

I was really suprised by the ammount of free space I had now. With cleaning up the localizetable I won about 60 % of space!

 

My Opsmgr console is running really smootly now!

 

Hope this helps,

Alexandre Verkinderen

Avatar of alkin

by alkin

Opsmgr Data Warehouse failed to enumerate database components to be deployed

9:04 am in Uncategorized by alkin

Today I ran into this issue:

image

 

Data Warehouse failed to enumerate database components to be deployed. Failed to enumerate Data Warehouse components for deployment. The operation will be retried. Exception ‘SqlException': Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. One or more workflows were affected by this. Workflow name: Microsoft.SystemCenter.DataWarehouse.Deployment.Component Instance name: scom.be Instance ID: {AC6FACD9-

 

Issue:This error appears immediately after a management pack is imported. The installaion of the reports in the MP is failing.

Resolution:Run exec sp_updatestats on the OperationsManagerDW db (the data warehouse).

 

When running the exec sp_updatestats doesn’t help follow these steps:

 

Download and extract the following hotfix:

 

http://support.microsoft.com/?kbid=954643

image

 

Run the SQL query Managementpackinstall.sp.sql

image

 

And directly after running the query I got the following event:

image

 

Everything is fine now and I have green management server:

 

image

 

Hope this help,

Alexandre Verkinderen

http://scug.be

Visit Us On TwitterVisit Us On Linkedin