Windows Sys Admin Notes

How-To: Install SSMS in a Cluster After SQL Server is Installed in a Clustered Instance

2014-09-23T15:26:00.000-05:00

Here's a quick way of installing SSMS on cluster nodes after SQL Server has been installed in a clustered instance.

For Basic Tools:
setup.exe /q /ACTION=INSTALL /FEATURES=SSMS /IAcceptSQLServerLicenseTerms=TRUE

For Advanced Tools:
setup.exe /q /ACTION=INSTALL /FEATURES=ADV_SSMS /IAcceptSQLServerLicenseTerms=TRUE

Slipstreaming SP's and CU's into the SQL Server 2012 Installer

2014-03-26T13:33:00.002-05:00

You can now easily slipstream SQL 2012 installer with Service Packs and CU's.

1. Create a folder under the installation folder called "Patches"
2. Download all related files required for the hotfix/CU's into this folder
3. Go into the Installation folder, go to x64, and update the DefaultSetup.ini folder to include the following line at the end
  1. UpdateSource=".\Patches" (It's the relative path to the update files)
  2. Save it, copy it and overwrite the DefaultSetup.ini file in x86
  3. Now, run the normal Setup.exe executable in the root of the install folder, and when you run the install, it will present you with a screen showing all the hotfixes it found in that folder, and allow you to include or exclude them to slipstream into the install.

What is My SQL Server Collation Set To?

2013-07-24T16:26:00.003-05:00

To find out what the collation settings are in an instance of SQL Server, open a New Query window and execute the following command:


SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

It will return the collation setting, for example: SQL_Latin1_General_CP1_CI_AS

What DC is my Logon Server?

2013-06-12T08:43:00.000-05:00

To find out which Domain Controller your server/workstation is authenticating against, open up a command prompt (Start --> Run --> cmd --> Enter) and copy/paste the following:


echo %logonserver%

This will return the DC that has authenticated your login.

Printing a Selection of a Page in Adobe Acrobat

2013-05-23T08:49:00.000-05:00

If you've ever wondered how to print a portion of a single page in a PDF document in Acrobat, here is what you need to do:

Open the document, and go to the page you would like to print.

In Acrobat X/Reader X, Choose Edit > Take A Snapshot.
In Acrobat 9/Reader 9, Choose Tools > Select & Zoom > Snapshot Tool.

Drag a rectangle around the area you want to print. It will highlight the selection that will be printed

Go to File --> Print

Make any changes to the way it will print as you usually would (Fit to Page, Custom Scaling, etc.)

Click the Print button!

This works in both Reader and Acrobat.

Changing Host Name of Server w/ SQL Installed

2013-02-13T13:24:00.001-06:00

If you need to change the host name of a Windows Server with SQL Server already installed on it, there are two Stored Procedures that need to be run on SQL Server to change the server name within SQL.

After renaming the host and rebooting run the following two procedures with the appropriate host names and the restart SQL.


sp_dropserver OldServerName

go

sp_addserver NewServerName, local

go

Install All Web Features Through PowerShell

2012-09-19T15:26:00.000-05:00

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

2012-09-18T11:25:00.001-05:00

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

2012-08-23T12:39:00.000-05:00

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

2012-08-06T16:49:00.001-05:00

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

2012-08-06T16:14:00.000-05:00

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

2012-06-25T10:58:00.000-05:00

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

2012-06-25T09:56:00.001-05:00

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

2012-06-25T09:37:00.000-05:00



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:

2012-05-29T16:34:00.000-05:00

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

2012-05-29T15:13:00.000-05:00

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

2012-05-29T10:41:00.004-05:00

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

2012-05-29T10:40:00.000-05:00

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?

2012-05-29T10:34:00.000-05:00


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

Replace DOMAIN_NAME with actual domain name

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

2012-04-25T16:48:00.000-05:00

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

2012-04-19T09:35:00.001-05:00

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

2012-04-07T14:46:00.000-05:00

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

2012-04-02T09:27:00.000-05:00

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

2012-03-30T10:57:00.000-05:00

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

2012-03-22T14:35:00.001-05:00

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