Install All Web Features Through PowerShell

To install all web features on Windows 2008, 2008 R2 & 2012, run the following two commands from within Powershell:

Import-Module ServerManager Get-WindowsFeature Web-* | Add-WindowsFeature

Enable/Disable Hyperthreading from Command Line on Dell Servers

To Enable Hyperthreading, open a command prompt, and navigate to the following directory:

For 32-bit Systems:  C:\Program Files\Dell\SysMgt\oma\bin
For 64-bit Systems: C:\Program Files (x86)\Dell\SysMgt\oma\bin

Run the following command to DISABLE Hyperthreading:

omconfig chassis biossetup attribute=cpuht setting=disable

Run the following command to ENABLE Hyperthreading:
omconfig chassis biossetup attribute=cpuht setting=enable

A reboot is then required for the setting to take effect.  To see if Hyperthreading is enabled, open up Task Manager, click on the Performance tab, and the number of cores should now be doubled under the CPU Usage section.  So, for a Single Hex-Core processor, you should now see a total of 12 CPU's if it's enabled.  If you only see 6, it's disabled.

How-To: Copy SQL Server User Accounts From One Instance to Another

This article from Microsoft explains how to script out the logins in SQL Server on one server that you can execute onto another SQL Server.  Basically, what you are doing on the source SQL Server is creating a Stored Procedure in the Master database that will return all the logins WITH passwords in script format so that you can execute it in a Query onto the destination SQL Server.  Here is a sample of the output of the stored procedure:

-- Login: testuser
CREATE LOGIN [testuser] WITH PASSWORD = 0x0200565C53D7AE099FF88B71F1A8497B62943DF662CFDB7868EE943F2DF75BFE6249C61DDC2F4E43197991D6E617ADC2335F2C65DFB9A2A667FAE2C40C03D2A0847F66A68E HASHED, SID = 0x85VEB3BBBF2D146B272FC4E4C917512, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = ON

The article to follow is here.

Features Supported by the Editions of SQL Server 2012

This is a handy guide by Microsoft for figuring out what features are available in which editions of SQL Server 2012:

http://msdn.microsoft.com/en-us/library/cc645993.aspx

How-To: Pause/Suspend a Cluster Node for Maintenance

When you need to Pause a node in a cluster for maintenance, you can use this Powershell script provided by Microsoft:

http://technet.microsoft.com/en-us/library/ee461051.aspx

How to list or find Microsoft Hotfixes that are installed

These can all be run from the command line.  To send the results to a text file instead, at the end of each line include the following:  > drive\path\filename

To list all Microsoft Hotfixes that are installed:
wmic qfe get Hotfixid

To find a specific Microsoft Hotfix to see if it's installed:
wmic qfe | find "{KB#}"

To get a table list of hotfixes installed:
wmic qfe list brief

Examples:

wmic qfe | find "KB123456"
wmic qfe list brief > C:\Results\Hotfixes.txt


To generate a cluster log on a Windows 2008/Windows 2008 R2 cluster, open a command prompt and enter the following:

cluster log /g /span:30

Note that the /span:30 switch specifies the number of minutes to go back in time for the log collection. You can either specify your own number of minutes, or leave it off completely for several days of history.

Query to find spids at head of a blocking chain, their input buffers, and the type of blocking locks they hold


declare @blocker_spid smallint
declare @i_buff_string char(30)
set nocount on

/* Get all blocked spids */

select spid, blocked, hostname=substring (hostname, 1, 10),
progname=substring(program_name, 1, 10), cmd=substring(cmd, 1, 10),
status, physical_io, waittype
into #blk from master..sysprocesses (nolock) where blocked != 0

/* delete all blocking spids except the 1st in each blocking chain */

delete from #blk
where blocked in (select spid from #blk)

/* get each spid from sysprocesses which is referenced in */
/* the "blocked" column of #blk. This should be the head */
/* of each blocking chain */

select "Blocking spid" = spid, loginame=substring(suser_name(sid),1,10),
hostname=substring (hostname, 1, 10), progname=substring(program_name, 1,10),
cmd=substring(cmd, 1, 10), status, physical_io, waittype
from master..sysprocesses (nolock)
where spid in
(select blocked from #blk)

/* For each spid at the head of a blocking chain */
/* print its input buffer to show what query it's running */

declare blk_cursor CURSOR FOR SELECT blocked from #blk
open blk_cursor
fetch next from blk_cursor into @blocker_spid
while (@@fetch_status <> -1)
begin
select @i_buff_string = ('dbcc inputbuffer (' +
convert(char(6),@blocker_spid) +')')
select 'Below is input buffer for this blocking spid: ', @blocker_spid
select ' '
exec (@i_buff_string)
fetch next from blk_cursor into @blocker_spid
end
deallocate blk_cursor


drop table #blk

This is a pretty good article on DNS Doctoring:

This is a pretty good article on DNS Doctoring:

http://secret-epedemiology-statistic.org.ua/1587052091/ch05lev1sec7.html

How-to Add a SQL Server SPN

From a command prompt, enter the following:

To create an SPN for the NetBIOS name of the SQL Server use the following command:
setspn -A MSSQLSvc/{SQL Server computer name}:1433 {Domain\Account} 

To create an SPN for the FQDN of the SQL Server use the following command:
setspn -A MSSQLSvc/{SQL Server FQDN}:1433 <Domain\Account>

Testing for AD Replication Issues

Run this command from a command prompt to find replication issues in a formatted table form.

repadmin /replsum /bysrc /bydest /sort:delta

Find System Uptime from Command Line

To find the system uptime, you can use this simple command from a command prompt.  It uses SystemInfo to find the last System Boot Time.

systeminfo | find "System Boot Time:"

How do I find the Domain Controllers for a domain a server is joined to?

1. Start -> Run -> nslookup   
2. set type=all
3. _ldap._tcp.dc._msdcs.DOMAIN_NAME

Replace DOMAIN_NAME with actual domain name

Quickly Determine Which Domain Controller(s) Hold Which FSMO Role(s)

1. On any Domain Controller, open up a command prompt
2. Type the following and hit Enter:

netdom query /domain:<domain> fsmo (where <domain> is the name of YOUR domain)

The results will return the server hosting each of the 5 FSMO roles

Free Date Calculator with Windows 7

If you are looking for a quick and simple Date Calculator (finding the number of years, months & weeks or days), the calculator application for Windows 7 now includes one.   Load up calculator (Start à Run à type calc and hit Enter).   You should see the usual calculator app appear:

Go to the File menu, and select Date Calculation toward the bottom of the screen.  Now, the calculator program will expand into a date calculator, allowing you to select 2 dates and calculate the total days in between them or the Years, Months and Weeks between.

Windows 8 Community Preview Product Key

To install the Windows 8 Consumer Preview, for all versions of Windows 8, you will need the following Product Key:

DNJXJ-7XBW8-2378T-X22TX-BKG7J

To download Windows 8 Consumer Preview, click here

Generating Strong, Random Passwords

While there are many sites out there that generate a random password for you, the one listed below has an added benefit in that it will provide you with the phonetic pronunciation of the password as well.  Simply select your requirements from the following list, click the Generate Password(s) button, and a random password will be generated for you:

Password Length, Show Phonetics, Include Letters, Include Mixed Case, Include Numbers, Include Punctuation, No similar characters, Number of characters.

http://www.pctools.com/guides/password/

Free Monitoring Tool For SQL Server

http://sqlmonitor.codeplex.com/

 

If you are looking for a tool to monitor performance information for SQL Server in real-time, there is a free tool available on Codeplex called SQL Live Monitor that has a GUI interface and does not require installation.  From the site, these are the features that it supports:

• Realtime SQL and System performance data
• Colour coded alerts
• Capture data logging to CSV - sample interval configurable
• PAL Perfmon counter logged to .blg or .csv for offline analysis using PAL - sample interval configurable
• Detailed view of certain SQL Areas via DMVs
• List Top 20 Queries by CPU, IO and Execution Count
• View SQL Waits by category
• View SQL Scheduler and CPU worker threads
• Monitor Kernel Pools and System PTE's
• Supports SQL 2000,SQL 2005, SQL 2008
• Captures data from local and remote servers
• Captures data from default and named instances (stand alone & clustered)
• Supports both SQL and Windows Authetication
• Supports x86 and x64
• No installation required
• Application small in size with minimal overhead when running
• Run multiple instances on same PC

There is some light documentation for a Quick Start and how to monitor clusters, as well as export data to PAL (Performance Analysis of Logs), which I will cover in a separate post.

Save Even More Money with a TechNet Standard Subscription

Even though this is an old article from 2010, it's still may be of some use for some.

Save Even More Money with a TechNet Standard Subscription

Introducing Microsoft SQL Server 2012 Free eBook

Microsoft is offering a free eBook on SQL Server 2012 covering the new features. This is not a step by step guide but covers an overview of the new SQL 2012 features.

You can download it and get more information here.

Memory Use in SQL Server

This is an excellent article that explains Memory Usage in SQL Server

http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html

How-To: Disable Scrict Name Checking in Windows

Allow connection to the remote server share via any DNS alias by creating the DisableStrictNameChecking registry key, follow these steps on the server with the share.

1.Create the CNAME record for the file server on the appropriate DNS server, if the CNAME record is not already present.
2.On the server click Start, click Run, type regedit, and then click OK.
3.Locate and click the following registry subkey: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters
4.Right click Parameters, point to New, and then click DWORD Value.
5.Type DisableStrictNameChecking, and then press ENTER.
6.Right-click DisableStrictNameChecking, and then click Modify.
7.In the Value data box, type 1, and then click OK.
8.Exit Registry Editor.
9.Restart your computer.

SQL Server Script: List Indexes That Are Not Used


SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;


SQL Server Script: Index Read/Write Statistics


SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
i.name AS [IndexName] ,
i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;


Cluster & SQL Server is not supported on Windows Server 2003 or Windows Server 2008 Terminal Server application server

http://support.microsoft.com/kb/327270

This has come up at least 3 times lately where I someone wants to install Terminal Services on a SQL Cluster node. The above link explains why this can't and shouldn't be done.

How-To: Restore a Deleted Object Using the Windows 2008 R2 Active Directory Recycle Bin

If you are running Active Directory on a Windows 2008 R2 server with the Forest Functional Level at Windows 2008 R2, you can take advantage of the Active Directory Recycle Bin to totally restore deleted objects to their original state.  If you have not already enabled this feature yet, take a look at this to enable it:  How to enable Active Directory Recycle Bin in Windows 2008 R2 to restore deleted objects

In order to now restore a deleted object, you will need to use either ldp.exe or Powershell since there is no GUI built in by Microsoft.  There are free options out there, and the best and easiest to use is this one, by Overall Solutions, and it is free.

If you prefer to use the command line or Powershell, you can follow these instructions from the Microsoft MSDN site.

How to enable Active Directory Recycle Bin in Windows 2008 R2 to restore deleted objects

One of the new features of Windows 2008 R2 is the "Active Directory Recycle Bin", which allows you to restore deleted objects in AD.  In order to use this functionality, you will need to raise your Forest Functional Level to Windows 2008 R2.  Once that is done, you will need to enable the Active Directory Recycle Bin.  To do this, you can follow these steps in this Microsoft MSDN Article:

Enable Active Directory Recycle Bin

Find Free Space, File Location, File Size for All Databases, All Instances

From: http://www.mssqltips.com/sqlservertip/1426/collect-sql-server-database-usage-for-free-space-and-trending/


DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command

SELECT
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate
FROM @DBInfo
ORDER BY
ServerName,
DatabaseName


How-To: Recover the Private Key in IIS for an SSL Certificate

To assign the existing private key to a new certificate, you must use the Microsoft Windows Server 2003 version of Certutil.exe. To do this, follow these steps:

1. Log on to the computer that issued the certificate request by using an account that has administrative permissions.
2. Click Start, click Run, type mmc, and then click OK.
3. On the File menu, click Add/Remove Snap-in.
4. In the Add/Remove Snap-in dialog box, click Add.
5. Click Certificates, and then click Add.
6. In the Certificates snap-in dialog box, click Computer account, and then click Next.
7. In the Select Computer dialog box, click Local computer: (the computer this console is running on), and then click Finish.
8. Click Close, and then click OK.
9. In the Certificates snap-in, expand Certificates, right-click the Personal folder, point to All Tasks, and then click Import.
10. On the Welcome to the Certificate Import Wizard page, click Next.
11. On the File to Import page, click Browse.
12. In the Open dialog box, click the new certificate, click Open, and then click Next.
13. On the Certificate Store page, click Place all certificates in the following store, and then click Browse.
14. In the Select Certificate Store dialog box, click Personal, click OK, click Next, and then click Finish.
15. In the Certificates snap-in, double-click the imported certificate that is in the Personal folder.
16. In the Certificate dialog box, click the Details tab.
17. Click Serial Number in the Field column of the Details tab, highlight the serial number, and then write down the serial number.
18. Click Start, click Run, type cmd, and then click OK.
19. At the command prompt, type the following:
20. certutil -repairstore my "SerialNumber" SerialNumber is the serial number that you wrote down in step 17.  Also, you will need to include the quotes around the serial number in this command line.
21. In the Certificates snap-in, right-click Certificates, and then click Refresh.  The certificate now has an associated private key. 

How to point to new location when the drive TempDB is located on is no longer available

How to point to new location when the drive TempDB is located on is no longer available

SQL Server requires the TempDB database to be accessible in order to start up.  If it cannot find the TempDB file, you will get an error when starting the service and it will not start.  The most likely scenario for this is when you have a drive, let’s say the D: drive, where TempDB is located on becomes corrupted or fails.   The issue is not that the file is unavailable, but the path specified isn’t, because SQL Server will always recreate the tempdb.mdf and templog.ldf files on startup.

First thing you’ll need to do is start SQL Server in “maintenance mode”.  Open a command prompt, and change directories to the BINN folder for your SQL server install.  For example, the default path for SQL Server 2008 R2 would be:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Run this command at the command prompt:

sqlservr -c -f -T3608 -T4022

The -c “Indicates that an instance of SQL Server is started independently of the Windows Service Control Manager. This option is used when starting SQL Server from a command prompt, to shorten the amount of time it takes for SQL Server to start.

The –f “Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting.”

The -Ttrace#” Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior. For more information, see Trace Flags (Transact-SQL).

Once you have this started up in the command prompt, open a new command prompt window and run the following command from the same “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn” location:

sqlcmd –E

You will be presented with the 1> prompt.  Here you can enter in SQL queries, and using the GO command will execute them.  We will do the following, hitting enter after each line (remember to replace {New Location} in the queries below with the file path you wish to use, i.e. the default “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data” for SQL Server 2008 R2):


USE master;
GO
GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = '{New Location}\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = {New Location}\templog.ldf');
GO



Once this is done, close out of this command prompt, and go to the other command prompt with sqlservr.exe running.  Hit CTRL+C to break it, and hit Y to accept shutting SQL Server down.  Now, go to SQL Server Configuration Manager and start the SQL Server service.  SQL Server should now recreate the TempDB files and start up normally again!

Upgrading Windows 7 32-bit to 64-bit

Attempting to install SQL Server Reporting Services on Cluster Nodes with the same instance name fails

When you attempt to use the same instance name for installing SQL Server Reporting Services using the clustered instance, you will receive the following error when attempting to install it the second time:

StandaloneInstall_HasClusteredOrPreparedInstanceCheck

Checks if the selected instance name is already used by an existing cluster-prepared or clustered instance on any cluster node.

Failed

The instance selected for installation is already installed and clustered on computer {ComputerName}. To continue, select a different instance to cluster.

The issue is that SSRS must be two separate installations, one on each node, with two different instance names.  You can, however, share the same report server database using a scale-out deployment.  To set up a Scale-Out Deployment, you can use the following MSDN link:

http://msdn.microsoft.com/en-us/library/ms159114.aspx

How to: Add Dependencies to a SQL Server Resource

From the Microsoft MSDN Site: 

If you install SQL Server into a Windows cluster group with multiple disk drives and choose to place your data on one of the drives, the SQL Server resource will be set to be dependent only on that drive. To put data or logs on another disk, you must first add a dependency to the SQL Server resource for the additional disk.
It is important to note that if you add any other resources to the SQL Server group, those resources must always have their own unique SQL network name resources and their own SQL IP address resources.
Do not use the existing SQL network name resources and SQL IP address resources for anything other than SQL Server. If SQL Server resources are shared with other resources, the following problems may occur:

• Outages that are not expected may occur.
• Service pack installations may not be successful.
• The SQL Server Setup program may not be successful. If this problem occurs, you cannot install additional instances of SQL Server or perform routine maintenance.

Consider these additional issues:

• FTP with SQL Server replication: For instances of SQL Server that use FTP with SQL Server replication, your FTP service must use one of the same physical disks as the installation of SQL Server that is set up to use the FTP service.
• SQL Server resource dependencies: If you add a resource to a SQL Server group and you have a dependency on the SQL Server resource to make sure that SQL Server is available, Microsoft recommends that you add a dependency on the SQL Server Agent resource. Do not add a dependency on the SQL Server resource. To make sure that the computer that is running SQL Server remains highly available, configure the SQL Server Agent resource so that it does not affect the SQL Server group if the SQL Server Agent resource fails.
• File shares and printer resources: When you install File Share resources or Printer cluster resources, they should not be put on the same physical disk resources as the computer that is running SQL Server. If they are put on the same physical disk resources, you may experience performance degradation and loss of service to the computer that is running SQL Server.
• MS DTC considerations: After you install the operating system and configure your cluster, you must configure MS DTC to work in a cluster by using the Cluster Administrator. Failure to cluster MS DTC will not block SQL Server Setup, but SQL Server application functionality may be affected if MS DTC is not properly configured.
  If you install Microsoft Distributed Transaction Coordinator (MS DTC) in your SQL Server group and you have other resources that are dependent on MS DTC, MS DTC will not be available if this group is offline or during a failover. Microsoft recommends that you put MS DTC in its own group with its own physical disk resource, if it is possible.

To add a dependency to a SQL Server resource

1. Open the Cluster Administrator in Windows Administrative Tools.
2. Locate the group that contains the applicable SQL Server resource that you would like to make dependent.
3. If the resource for the disk is already in this group, go to step 4. Otherwise, locate the group that contains the disk. If that group and the group that contains SQL Server are not owned by the same node, move the group containing the resource for the disk to the node that owns the SQL Server group.
4. Bring the SQL Server resource offline. This step applies to Windows 2003 failover clusters. Windows 2008 clusters do not require this step.
5. Select the SQL Server resource, open the Properties dialog box, and use the Dependencies tab to add the disk to the set of SQL Server dependencies.

Memory Limits for Windows Releases

This is a must-have when working with many different flavors of Windows and Editions!

Memory Limits for Windows Releases

IIS SSL Certificate Installation - Microsoft IIS 7

IIS SSL Certificate Installation - Microsoft IIS 7

A Look at the SQL Server Browser Service | Bob Pusateri - The Outer Join

A Look at the SQL Server Browser Service | Bob Pusateri - The Outer Join