How to: SQL 2012 Performance Dashboard Reports

By Prett Sons, 2013/06/11

SQL Server Performance Dashboard comprises a set of custom reports that give you nitty gritty details about the performance of your SQL Server instance. Using these reports, you can keep track of the performance issues confronted by SQL Server and get significant help to resolve these issues without having to run T-SQL queries. SQL Server 2012 has introduced new enhancements to the Performance Dashboard toolset.

In order to use SQL Server 2012 Performance Dashboard Reports, you do not require installing Reporting Services. You can access these report files through the Custom Reports feature of SQL Server Management Studio. The data fetched from these reports can be used to diagnose performance issues, including CPU bottlenecks, IO bottlenecks, blocking, and latch contention. You may also get recommendations about creating and modifying indexes (from query optimizer).

You can download SQL Server 2012 Performance Dashboard Reports from the Microsoft Download Center and get started. The below given tips will guide you how to install and use SQL Server 2012 Performance Dashboard Reports in SQL Server 2012. In the later section, you will see what are the key changes made to the Performance Dashboard in SQL Server 2012.

Installing SQL Server 2012 Performance Dashboard Reports

The following steps will install the Performance Dashboard Reports.

• Open the below link in your web browser and download SQL Server 2012 Performance Dashboard Reports: http://www.microsoft.com/en-us/download/details.aspx?id=29063&WT.mc_id=aff-n-in-loc--pd

• Navigate to the location where you saved the SQLServer2012_PerformanceDashboard.MSI file and double-click it. You will receive a welcome screen. Click 'Next'.

• The next screen will present you with the license agreement. Read the agreement, select 'I accept the terms in the license agreement', and click 'Next'.

• The next screen will ask you the required registration information. Enter the details and click 'Next'.

• You will be asked to choose the program features for installation. Go with the default selection and click 'Next'.

• On the next screen, click 'Install' to begin the installation process. You can choose to click 'Back' button to make any other changes to your installation settings.

• Click 'Finish' to exit the installation wizard. Now, you have successfully installed SQL Server 2012 Performance Dashboard Reports.

Configuring SQL Server 2012 Performance Dashboard Reports

After finishing the installation, it is necessary to configure the SQL Server to use the Performance Dashboard toolset you have just installed. The default installation directory for SQL Server 2012 Performance Dashboard Reports is 'C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard\'. Navigate to this location, search for the 'setup.sql' file, double-click it to open the Performance Dashboard Reports in SQL Server Management Studio. Next, establish connection to the SQL Server Database Engine instance where you need to install and access the reports. Hit 'F5' to start configuring the Performance Dashboard Reports.

Using SQL Server 2012 Performance Dashboard Reports

After you have successfully installed Performance Dashboard Reports, you need to consider the following:

• You need to ensure that all functions and procedures accessed by the queries in reports must be present in all instances of SQL Server for which you need to carry out performance analysis. You need to search for the 'setup.sql' file after navigating to the installation directory and run the script. Close the window after the execution is finished.

• Go to Object Explorer in SQL Server Management Studio, right-click on the server instance, and select 'Reports' → 'Custom Reports'. In the 'Open File' dialog box, select the 'performance_dashboard_main' report file and click 'Open'. You will be displayed a warning dialog box stating that you are about to run a custom report. Click 'Run' in this dialog box to open the 'performance_dashboard_main.rdl' file. You will be presented with various charts and hyperlinks in the report for analyzing the performance of the server.

• You can click links provided in the main report to access the remaining reports. In order to better know how to use these reports, you can view the help file, i.e. PerformanceDashboardHelp.chm.

• On the dashboard, you will also find the extended events ('XEvent') sessions that the instance is currently running. XEvents is a feature in SQL Server that enables you to get information about various events that occur inside the database engine. The XEvents sessions report displays the configuration of all the currently active XEvents sessions on the SQL Server instance and the events captured by these sessions.

• By default, the system_health session is enabled by default in SQL Server 2012. The two sessions (i.e. the system_health session and the sp_server_diagnostics session) are always active on a default installation of SQL Server 2012.

• In SQL Server 2012, the databases overview report provides more information about the databases than that given by any previous version of Performance Dashboard Report. The additional options that you can view in the report are 'Log Reuse Wait Description' and the 'Page Verify Option'. You can view the databases overview report by clicking the database option in the main report.

• You can also view details of the System Sessions using the Sessions option (a drill-down option available on the main dashboard). If you take a sneak-peek at the Sessions details, you will see all the sessions that the server is currently running. It will also display information about the background tasks including other details, such as memory usage, logical reads, physical reads, and more.

You can also use SQL Server 2012 Performance Dashboard Reports to diagnose performance issues for SQL Server 2008 and 2008 R2 instances.

AngularJS in Visual Studio 2013

Video here

Creating Web Test and Load Test using Microsoft Visual Studio

Video Here

K2 SmartForms: Restriction for the file type in file attachment control

Follow this link

SharePoint Service account creation with PowerShell from CSV

Quick easy way to create Service Accounts for SharePoint.

Users.csv (layout):

FirstName, LastName, SamAccountName, -acc password-

sp_cacheSuperUser, sp_cacheSuperUser, sp_cacheSuperUser, -acc password-

sp_cacheSuperReader,sp_cacheSuperReader,sp_cacheSuperReader, -acc password-

sp_farm,sp_farm,sp_farm, -acc password-

sp_portal_mysite,sp_portal_mysite,sp_portal_mysite, -acc password-

sp_portal_intranet,sp_portal_intranet,sp_portal_intranet, -acc password-

sp_searchContent,sp_searchContent,sp_searchContent, -acc password-

sp_searchService,sp_searchService,sp_searchService, -acc password-

sp_services,sp_services,sp_services, -acc password-

sp_userProfiles,sp_userProfiles,sp_userProfiles, -acc password-

PowerShell Script:

# Created this script using Windows Server 2012# Import Active Directory module

Import-Module ActiveDirectory -ErrorAction SilentlyContinue

# Set OU for the user accounts

$OU = “OU=SharePoint,OU=Service Accounts,DC=dev,DC=local”

# Get domain name

$dnsroot = ‘@’ + (Get-ADDomain).dnsroot

# Import the file with the users.

$users = Import-Csv .\users.csv #-Delimiter “;”

Write-Output $users

foreach ($user in $users)

{

    # Get password from CSV File

    $Password = ConvertTo-SecureString $user.Password -asPlaintext -Force

    # Create Accounts

    #$FullName = ($user.FirstName + ” ” + $user.LastName)

    $FullName = ($user.LastName)

    $UserPrincipalName = ($user.SamAccountName + $dnsroot)

    try

    {

        New-ADUser -SamAccountName $user.SamAccountName –Name $FullName –DisplayName $FullName -GivenName $user.FirstName -Surname $user.LastName –UserPrincipalName $UserPrincipalName -Enabled $true -ChangePasswordAtLogon $false -PasswordNeverExpires $false -Path $OU -AccountPassword $Password -PassThru | Out-Null

        Write-Output “Created user $($user.SamAccountName)”

    }

    catch [System.Object]

    {

        Write-Output “Could not create user $($user.SamAccountName), $_”

    }

} 

MSMQ workgroup flag reverts back to 1

Manage to fix this issue on my VM by following these next steps:

Uninstall MSMQ from windows features completely

Setting Permissions in Active Directory Domain Services Before Installing the Routing Service or the Directory Service Integration Features of Message Queuing

The successful installation of the Routing Service feature on a Windows Server 2008 R2 computer that is not a domain controller, or the Directory Service Integration feature of Message Queuing on a Windows Server 2008 R2 computer that is a domain controller requires that specific permissions are set in Active Directory Domain Services. Follow these steps to grant the appropriate permissions in Active Directory Domain Services before installing these features.

To grant permissions for a computer object to the Servers object in Active Directory Domain Services before installing the Routing Service feature on a computer that is not a domain controller

1. Click Start , point to Programs , point to Administrative Tools , and then click Active Directory Sites and Services to open Active Directory Sites and Services .
2. Click to expand Active Directory Sites and Services , click to expand Sites , and then click to expand the site which this computer will be a member of.
3. Right-click Servers and select Properties to display the Servers Properties dialog box.
4. Click the Security tab of the Servers Properties dialog box.
5. Click the Add button to display the Select Users, Computer, or Groups dialog box.
6. Click the Object Types button to display the Object Types dialog box, click to enable Computers , and then click OK .
7. Enter the name of the computer for which the Routing Service or Directory Service Integration feature will be installed, click Check Names , and then click OK .
8. Enable the following permissions for this computer object:
• Allow Read
• Allow Write
• Allow Create all child objects
9. After enabling these permissions, click Advanced to display the Advanced Security Settings for Servers dialog box.
10. Select the computer object from the list of permission entries, and then click the Edit button.
11. Select Thisobject and all descendant objects from the Apply to drop-down list, and then click OK .
12. Click OK to close the Advanced Security Settings for Servers dialog box.
13. Click OK to close the Server Properties dialog box.

To grant the Network Service account the Create MSMQ Configuration Objects permission to the computer object in Active Directory Domain Services before installing the Directory Services Integration feature on a computer that is a domain controller

1. Click Start , point to Programs , point to Administrative Tools , and then click Active Directory Users and Computers to open Active Directory Users and Computers.
2. Click the View menu and click to enable the options for Users, Groups, and Computers as containers and Advanced Features .
3. Click to expand the Domain container for the domain, click to expand the Computers container, right-click the computer object on which the Directory Services Integration feature is being installed, and then click Properties to display the computer properties dialog box.
4. Click to select the Security tab of the computer properties dialog box.
5. Click the Advanced button to display the Advanced Security Settings for  dialog box.
6. Click the Add button to display the Select User, Computer, or Group dialog box.
7. Type Network Service into the Enter the object name to select edit box. Click Check Names , and then click OK .
8. Click to enable Allow for the Create MSMQ Configuration objects permission, and then click OK to close the Permissions Entry for  dialog box.
9. Click OK to close the Advanced Security Settings for  dialog box.
10. Click OK to close the computer properties dialog box.

Install MSMQ with Directory Service Integration

More SQL Pivot idea's

CREATE TABLE Test
(
Id INT,
Names VARCHAR(100)
)
GO
-- Load sample data
INSERT INTO Test SELECT
1,'A' UNION ALL SELECT
1,'B' UNION ALL SELECT
1,'C' UNION ALL SELECT
2,'A' UNION ALL SELECT
2,'B' UNION ALL SELECT
3,'X' UNION ALL SELECT
3,'Y' UNION ALL SELECT
3,'Z'
GO

SELECT T1.Id ,AllNames = SubString (
( SELECT ', ' + T2.Names
      FROM Test as T2
      WHERE T1.Id = T2.Id
      FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id
-sent by Johan S

BPM and ECM – The War Begins

Even though large tracts of BPM have fallen or may fall into the grip of ECM, we shall not flag or fail.
We shall go on to the end, we shall fight.

Read more here

Convert VDI to VHD

I recieved a Virtual Machine, that was build in Oracle Sun Virtual Box, and needed to be converted to run in Microsoft Virtual PC, and eventually on Microsoft Hyper-V;

I have found a number of articles, and this article/post was the only one that worked.

1. Convert the .vdi file to a raw disk image (.raw)
Perform a search on your system for existing .vdi files that you are going to convert.

a. Go to a cmd prompt and navigate to the VirtualBox folder (typically c:\program files\sun\VirtualBox).

b. Execute the following command against the .vdi file in question:

vboxmanage.exe internalcommands converttoraw "x\path-to-vdi\diskimage.vdi" "x:\path-to-output-folder\diskimage.raw"

Depending on the size of your .vdi file, the time for conversion may greatly vary.

Also, be sure you have around 2 times the available drive space
that your existing .vdi currently consumes on your logical volume.

2. Convert .raw disk image to .vmdk format using WinImage

a. Open WinImage, click on 'Disk'> 'Convert Virtual Hard Disk image...'

b. Next to the 'File name:' field, click on the file type drop-down and select 'All files (*.*)'.

c. Navigate to the location where you stored your outputted .raw disk file and double-click it.

d. Choose whether you wish to 'Create Fixed Size Virtual Hard Disk' or
'Create Dynamically Expanding Virtual Hard Disk' (I typically pick the latter) and click 'OK'.

e. Navigate to a folder where you wish to store the newly converted image to. Next to
'Save as type:' (for the sake of this How-to) choose 'VMWare VMDK (*.vmdk). and click 'Save'.

You should see a 'Reading disk' progress indicator giving you the status of the conversion process.
I've converted 30Gb images in about 10 minutes or less...but I have no firm numbers.

f. Once the conversion is complete, you'll see a dialog box that will ask you if you wish to connect to the partition. Click 'OK' if you wish to view the contents.

3. Import your disk images into your existing Virtual Infrastructure

Now that the files are converted, copy or move your converted disk image files to your virtualization software's datastore/disk storage folder.
Once moved/copied, you should now be able to create a new Virtual Machine and utilize the disks you just converted.
Note that you will need to install the proper guest additions/tools to the virtual machine when you get it booted, so you will likely not have
network access right off the bat.

Top 10 Mistakes When Building and Maintaining a Database

Building and maintain a SQL Server database environment takes a lot of work. There are many things to consider when you are designing, supporting and troubleshooting your environment. This article identifies a top ten list of mistakes, or things that sometimes are overlooked when supporting a database environment.

Read More...

Running Alfresco as Windows Service

Run Tomcat 6 as Windows service:

Info URL:

http://wiki.alfresco.com/wiki/Configuring_Alfresco_as_a_Windows_Service

Install Command Line:

:\> cd c:\alfresco\tomcat\bin
:\> service.bat install
:\> tomcat6 //US//Tomcat6 --JvmMs=128 --JvmMx=512 --JvmSs=96 ++JvmOptions "-XX:MaxPermSize=128m"

Uninstall Command Line:

:\> service.bat uninstall Tomcat6 (or the "name" used when installing the service)

TomCat6 Service Properties Link:

:\> tomcat6w.exe //ES//Tomcat6 (or the "name" used when installing the service)

ERRORS: TomCat6 SERVICE NOT STARTING

1. prunsrv.c Failed creating java
1. Solution:
• Copy msvcr71.dll from java’s bin directory to windows\system32 folder
2. Known issue
1. Solution:
• Make sure your tomcat’s pointing to correct jvm.dll folder.
• My tomcat pointing to C:\Program Files\Java\jre1.6.0_07\bin\client\jvm.dll, try change to C:\Program Files\Java\jre\bin\client\jvm.dll

Run MySQL as Windows Service:

These instructions assume that Alfresco is installed in C:\Alfresco. Before
you can install MySQL as a windows service you need to set the following directories
in the C:\alfresco\mysql\my.ini file

Edit my.ini:

basedir="C:\Alfresco\mysql"
datadir="C:\Alfresco\alf_data\mysql"

Install Command Line:

:\> cd C:\Alfresco\mysql\bin
:\> mysqld.exe --install AlfrescoMySQL --defaults-file=C:\Alfresco\mysql\my.ini

ERRORS: MySQL SERVICE NOT STARTING
None so far...

Cloning / Copying Virtual Hard Drives

I have a base virtual hard drive (VHD) which I use to create new virtual machines (VM) as needed. The base VHD has an operating system (OS) loaded with all latest updates, so when I need a new VM I just make a copy of my base VHD; create new VM with copied VHD. I’ve been using Sun Virtual Box to run my VM’s, it’s got a small memory foot print on the host machine and memory management is better so the precious megabytes on your demo / development pc can be used for fast running VM’s without having to kill each running server on you host machine and it’s free here.

But when cloning / copying VHD’s you sure will be running in to this error:

'Failed to open the hard disk J:\ISO\VPC\RTBase\Base_c.vdi, Cannot register the hard disk ‘J:\ISO\VPC\RTBase\Base_c.vdi’ with  UUID {d6dd48d6-5999-455a-8498-f6f1359ab971} because a hard disk ‘J:\ISO\VPC\RTBase\Base_c.vdi’ with  UUID {d6dd48d6-5999-455a-8498-f6f1359ab971} already exists in the media registry … ' bla-bla-bla

To fix this, Sun Virtual box has a cool command line tool include in its install folder [C:\Program Files\Sun\VirtualBox], VBoxManager. Executing the following command will change / assign a new UUID to the cloned / copied VHD.

Command line:

VBoxManage internalcommands sethduuid J:\ISO\VPC\SMDMOSSServer_c.vdi

Result:

SQL COALESCE

This is how it is done....

If you want to pivot the data you could run the following command.

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

and get the following result set.

- sent by Dirkie

Check disk space in C#

Sample 1: Using System.Diagnostics.PerformanceCounter
//Get disk space info from remote server
System.Diagnostics.PerformanceCounter _pc;
float _freemegabytes;
float _freespacepercentage;
float _capacity = 0;
string result = "";
//Get free space percentage
_pc = new System.Diagnostics.PerformanceCounter("LogicalDisk", "% Free Space", serverdrive, servername);
_freespacepercentage = _pc.NextValue();
//Get free space in megabytes
_pc = new System.Diagnostics.PerformanceCounter("LogicalDisk", "Free Megabytes", serverdrive, servername);
_freemegabytes = _pc.NextValue();
//Calculate the capacity in gigabytes
_capacity = ((_freemegabytes / _freespacepercentage) * 100) / 1024;
//Calculate free space in gigabytes
_freemegabytes = _freemegabytes / 1024;
result = "" + servername + " " + serverdrive + " TotalS:" + _capacity.ToString("##.00") + "gb FreeS:" + _freemegabytes.ToString("##.00") + "gb. ";
 
Sample 2: Using System.IO.DriveInfo
//Get disk space info from local server
System.IO.DriveInfo dinfo = new DriveInfo("x:");
//Get disk size
double dsize = double.Parse(dinfo.TotalSize.ToString()) / 1073741824;
//Get free space
double dspace = double.Parse(dinfo.TotalFreeSpace.ToString()) / 1073741824;
result = "Logical Disk Size = " + dsize.ToString("##.00") + " GB";
result += " Logical Disk FreeSpace = " + dspace.ToString("##.00") + " GB";

SysPrep base Virtual Machine to create multiple images

Overview
Creating new virtual machines is bit time consuming so to create a base virtual hard drive and just make copies of it and start-up new virtual machine is ideal, but cloning/coping vhd will lead to having virtual servers with same SID and CID on your network. There are tools like NewSID to fix it but I had problem with clone/ghost virtual machine which didn't want to be joined to a domain.

What is SysPrep?
SysPrep is a tool that allows you to prepare or “prep” a machine with the operating system along with any software you wish was pre-installed and pre-configured. Once a machine is SysPrep’d, you have a new virtual hard drive that has the Windows operating system along with any additional software or features you want, such as IIS, preinstalled and pre-configured. SysPrep allows you to create your perfect system configuration packaged so that you can have a new virtual machine up and running in just minutes. And, it is available for both Windows Server 2003 32bit/64bit and Windows XP.

Where to get SysPrep?
System Preparation tool for Windows Server 2003 Service Pack 2 32bit Deployment
System Preparation tool for Windows Server 2003 Service Pack 2 64bit Deployment

How to SysPrep?
Creating the base virtual machine image/vhd

1. Create new virtual machine, I did it on Hyper-V but should work for VPC 2007/Virtual Server and more.
2. Install your OS. Windows Server 2003 R2 (latest service pack) or Windows XP.
3. Do NOT join the virtual machine to any domain.
4. Leave the administrator password blank or reset it to blank.
5. Now get all latest windows updates and all.
6. Install antivirus software and latest updates.
7. Install the virtual additions, depending which virtualization you're using.
8. Install all and latest .Net frameworks.
9. Activate the OS license. Then you don't need to re-activate the OS for each new virtual machine you create.
10. When I create a base for virtual servers (MOSS, K2, SQL or WEB servers) I install BGInfo, part of Windows sysinternals package, get it here. It creates cool desktop background with various server information as a desktop background.

Create SysPrep.inf File

Before you can SysPrep you virtual machine, you need to create a SysPrep.inf configuration file. This file contains the information about your machine. It will also prevent you from having to enter you CD Key each time you create a new virtual machine from you SysPrep’d image. Below is a sample of the SysPrep.inf file that you need to create. This file configures the SysPrep process and automates boot up process.

1. On your virtual machine, create a folder SysPrep at the root of your C: drive (C:\SysPrep).
2. Copy the following text into a text file named SysPrep.inf.
3. Enter the correct values for the following keys:
1. TimeZone – the value of 140 is Harare, Pretoria (UCT +02:00). You may want to change this to your local time zone, but it is not required to do so, Index numbers for [GuiUnattended]/TimeZone.
2. OEMDuplicatorsting – this should contain the name of the operating system you have installed on your virtual machine.
3. FullName – your name, the name you would enter if you were installing Windows.
4. OrgName – the name of your company, or blank.
5. ProductKey – Your product key (CD key) license.

Sample SysPrep.inf file:

;SetupMgrTag
[GuiUnattended]
TimeZone=140
OEMSkipRegional=1
OemSkipWelcome=1
EncryptedAdminPassword=NO
OEMDuplicatorstring="Windows Server 2003 R2 64Bit"
[Identification]
JoinWorkgroup=WORKGROUP
[Networking]
InstallDefaultComponents=Yes
[LicenseFilePrintData]
AutoMode=PerServer
AutoUsers=50
[Unattended]
OemSkipEula=Yes
InstallFilesPath=C:\sysprep\i386
[UserData]
FullName="YOUR NAME HERE"
OrgName="YOUR COMPANY NAME HERE"
ProductKey=YOUR-PRODUCT-KEY-HERE
[SetupMgr]
DistFolder=C:\sysprep\i386
DistShare=windist

Your SysPrep.inf configuration file is now ready to be used.

SysPrep-ing your Virtual Machine

SysPrep-ing your virtual machine takes just a minute or two. Most of the time is simply shutting down your virtual machine. Important: do not start this virtual machine back up or it will un-SysPrep your machine. If this does happen, you can simply go through these steps below to SysPrep you virtual machine again.

1. Run the SysPrep install tool, it will install a deploy.cab file in this location, C:\WINDOWS\system32\deploy.cab.
2. Extract all the files in the deploy.cab to C:\SysPrep.
3. Then run SysPrep.exe
4. Check the “Don't reset grace period for activation” option.
5. Make sure Shutdown mode is Shut down.
6. Click the Reseal button to shutdown and package.
7. Click OK to generate new SID's.
8. Your virtual machine will now shut down and be SysPrep’d.
9. You now have a virtual image that is SysPrep’d, but not ready to be used.
10. Before you use this image, you will need to make a backup copy of your virtual machine image. This will allow you to always have a SysPrep’d virtual machine ready and waiting.
11. Backup your SysPrep’d virtual machine image (.vhd), and rename them to something you can easily understand and that describes what your image contains. For example:
    Base2003R2x64_SysPrep.vhd
12. You now have your virtual machine SysPrep’d. You can now use this image to quickly create a new virtual machine in minutes, with a new machine name and new unique System ID (SID) each time you use it.

The SSRS 2008 Minefield

One of the big advances in Microsoft's "2008 platform", with regard to Reporting Services, was that there would be a single, consistent Report Definition Language (RDL) across all the products. This means that reports developed in Report Builder can be shared with reports developed in BIDS, and vice-versa. While one can immediately appreciate the advantages of this, it's disappointing that it seems, on this occasion, to have been at the expense of compatibility efforts.

If you've developed reports in Visual Studio 2008 and expect to be able to deploy them to SSRS 2005, then think again. You can't.....Read More...