Befriending Dragons

Transform Tech with Anti-bullying Cultures


Leave a comment

Data Gathering – SQL Server Setup Problems

Data Gathering – SQL Server Setup Problems

Cindy Gross, Dedicated Support Engineer

 

Gathering the right data during a problem can get you a long ways towards resolving the problem. When you ask the right questions and clearly define the problem it changes the way you approach the remaining steps. Sometimes the answer to a seemingly simple question leads you right to the solution. In this series of data gathering blogs I am NOT going to tell you how to solve the problem, but I am going to tell you how to get a good set of troubleshooting data you can use to do your own troubleshooting.

Any time you have a SQL Server setup issue you will need to collect this information:

 

  • Date/time of the failed setup
  • Type of install and parameters used – GUI, unattended/command line, slipstream, sysprep
  • Number of attempts, any actions you took to cleanup or remove prior installs
  • Version/Edition/Exact build #/32bit or x64 or IA64 for
    • Windows
    • SQL Server
    • Virtualization software (Hyper-V, VMWare for example)
  • How many cluster nodes and SQL instances or state that it’s standalone
  • Whether the problem is with RTM, SP, and/or CU
  • Whether the problem is with a new install or an upgrade
  • What components you are installing (Client tools, SSIS, SQL, RS, AS, BOL, samples) and whether any succeed
  • Exact error message
  • Repro steps and/or failure scenario
  • All setup logs (i.e. %ProgramFiles%Microsoft SQL Serverx0Setup BootstrapLOG)
  • Which step in the build doc you are at when you see the setup failure
  • What permissions have been enabled/granted for MSDTC
  • Any steps you take to attempt to resolve the problem and the result
  • If on a cluster, have you re-run the cluster verification log and addressed all errors and warnings?
  • Did you run setup using “run as administrator”?
  • What account did you log into Windows with when you ran setup and what groups does it belong to?

Armed with this information you are now ready to solve the problem!


Leave a comment

SQL PowerShell Demo – Intro

SQL PowerShell Demo – Intro

Cindy Gross, Dedicated Support Engineer  

Audience: SQL Server DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs.

0.     Prerequisites

1.     Install Basics

  • Check what version of PowerShell you currently have on your desktop.
    • Run PowerShell.exe with the “run as administrator” option
    • Get the version by executing the following in the PowerShell window:

# Get the version

$host.version

2.     Hints

  • To paste into a PowerShell window (or a dos-prompt), copy your source then right click in the PowerShell window to paste. If you paste something that has a bullet point you will need to delete the o: or hidden character/space at the beginning before you hit enter.
  • To copy from a PowerShell window (or a dos-prompt), right click in the menu bar at the top. Choose edit.mark and highlight a “box” of data. You’re basically drawing from one corner to another, not picking individual words. Then without clicking anywhere else within the window, right click on the menu bar again and choose edit.copy. Then paste wherever you need to.
  • The pound sign (#) indicates a comment. You can cut/paste the demo steps including the lines with #. However, be careful about pasting an entire section at once, it will execute them all and the results may scroll off the screen.
  • For my SQL Server connections in this demo, I have used localhost to indicate the default instance on my local box. If you want to connect to a named instance, to an instance on another box, or to any clustered instance, replace localhost with the instance name (i.e. SERENITYHOMEInstance 1 or SomeRemoteSQL or localhostInstance1). Note that localhost is equivalent to a period (.) or (local).
  • Within SQLPS even a default instance needs an “instance” name specified and for a default instance the name is DEFAULT. For example this is the default instance on my local box: SQLSERVER:SQLlocalhostDEFAULTDatabasesAdventureWorks2008R2TablesPurchasing.Vendor
  • To change the dimensions of the PowerShell window (current and future), right click in the menu bar and choose properties. Here you can change the height, width, font, etc.
  • Many basic dos commands still work, such as cls to clear the screen.

3.     Explore Basic PowerShell Functionality

  • Run PowerShell.exe from the start menu with the “run as administrator” option. PowerShell should be in your path, but if you need to find the source location, look in a path similar to C:WindowsSystem32WindowsPowerShellv1.0.
  • Check your security level. I have put the # sign (comment) on the lines that are not PowerShell commands so that you can cut/paste the entire section into PowerShell or a script. See the hints section on how to cut/paste.

# Run this command to see what your security level is:

Get-ExecutionPolicy

# For information about execution policies:

get-help about_execution_policies

# If the execution policy is restricted then change it to unrestricted, RemoteSigned, or whatever level

# is most appropriate http://technet.microsoft.com/en-us/library/ee176961.aspx

# Example of changing the execution policy: 

Set-ExecutionPolicy unrestricted

  • In the PowerShell window run these commands and review the output. This is just a small sampling of the information available via Windows cmdlets.

# List of available commands and info about each:

get-help

# “Dir” is an alias for get-childitem so we see the help info for get-children when we pass in dir:

get-help dir

# “Ls” is also an alias for get-childitem:

get-help ls

# The “full” option includes examples, parameters, etc.:

get-help dir –full

# Returns one row per running instance of sqlservr.exe:

get-process sqlservr

# Returns one row per running instance of sqlservr.exe with different output:

get-process sqlservr -fileversioninfo

# one row per service with SQL in the name:

get-service *sql*

# system path from environment variables/set:

$env:path

# All environment variables that start with proc:

Dir env:proc*

# List of all PowerShell “drives” including basic regkey hives:

Get-psdrive

# Look at a registry key, in this case we can see which SQL instances are installed:

# Note that you can leave out HKEY_LOCAL_MACHINE since that is covered by the switch to hklm

# It appears that the HKEY_LOCAL_MACHINE part of the name is case sensitive

#32bit instances on an x64 box have keys in the Wow6432Node key

CD hklm:
dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names”

dir “HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftMicrosoft SQL ServerInstance Names”

4.     Formatting

# Look at some of the output format options

# Switch to the registry

CD hklm:

dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list

dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list –property name, property

dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list –property *

5.     Run a PowerShell Script

# Change to the directory where the PowerShell script resides

cd c:installsPowerShelltest

# Specify the current directory with a period (.) and call the PowerShell script

.getsysproperties.ps1

  • You can also run the PowerShell script from a dos-prompt
    • From start, execute cmd with “execute as administrator”. # is not a valid comment indicator in dos, so use REM instead.

REM Execute the script

cd c:installsPowerShelltest

PowerShell.exe -noexit .getsysproperties.ps1

  • (Optional) Save one or more of the earlier PowerShell cmdlet calls in a .ps1 file and test executing them.
  • Close your PowerShell window and CMD window.

6.     Run a SQL PowerShell cmdlet

  • On a box with the SQL 2008 client tools installed, run sqlps.exe with the “run as administrator” option which opens a PowerShell mini-shell window (it will look like a normal dos-prompt but have “PS SQLSERVER:>” at the prompt). Run these commands and explore the output (where you see localhost you can enter your own SQL Server serverinstance name)

# Look at the syntax and options for the cmdlet that lets you execute any SQL query

Get-help invoke-sqlcmd

# Connect to SQL and return the version information

invoke-sqlcmd -ServerInstance localhost -Query “select @@version”

# The output was cut off, let’s try a different format

invoke-sqlcmd -ServerInstance localhost -Query “select @@version” | format-list

# Close the sqlps window

  • From SSMS 2008+, right click on any SQL Server instance name and choose “start PowerShell”. You will see a dos-prompt open with something like PS SQLSERVER:SQLSERENITYHOMEDEFAULT (my instance =is a default instance on the server SERENITYHOME). That line shows the context, I no longer have to specify the instance name for invoke-sqlcmd. I do still have to specify a database if I want a context other than my default database.

# No instance name is specified, and it puts me in the default database for my Windows auth login

invoke-sqlcmd -Query “select @@version, db_name()”

# I can specify a specific database as a parameter

invoke-sqlcmd -Query “select @@version, db_name()” –database tempdb

# Close the “SQL Server PowerShell” window

  • From SSMS 2008+, right click on any SQL database or SQL database object and choose “start PowerShell”. When I do this for AdventureWorks2008.HumanResources.Department I see a dos-prompt open with PS SQLSERVER:SQLSERENITYHOMEDEFAULTDatabasesAdventureWorks2008TablesHumanResources.Department>). That’s the context, I no longer have to specify the instance name or database name for invoke-sqlcmd. I can then change to another database and/or another SQL Server (in this case I switched to the Kaylee instance and the AdventureWorks database).

# The database context is the database I clicked on in SSMS

invoke-sqlcmd -Query “select @@servername, db_name()”

# We can change to another db (if you do not have AdventureWorks, choose another database)

cd SQLSERVER:sqlserenityhomekayleedatabasesAdventureWorks

# Now look at the current database context

 invoke-sqlcmd “select @@servername, db_name()”

# Close the “SQL Server PowerShell” window

7.     Execute from a SQL Agent Job

  • In SSMS, go to SQL Server Agent.Jobs and right click to create a new job called Test PS.
  • On the Steps page create a new job step and set the “type” = “PowerShell”.
  • I used “Run as” = “SQL Server Agent Service Account”, make sure whatever account you use has sufficient permissions.
  • Command = invoke-sqlcmd -serverinstance localhost -database TempDB -query “select getdate() AS RunTime, @@servername AS ServerInstance, db_name() AS DBName” > c:temppstest.txt
  • Note that if you don’t specify the ServerInstance parameter it will try to connect to the local default instance even if you run from a job on a named instance. The above command will connect to the local default instance, change it to another instance if needed.
  • Save and run the job.
  • Run notepad c:temppstest.txt and see that it outputs the expected ServerInstance and DBName.
  • Note that each job step opens its own sqlps.exe which takes about 20-40MB of memory.

8.     SQL from Windows PowerShell

  • Close any existing PowerShell windows.
  • Run PowerShell.exe.
  • Try to switch to the SQL mini-shell and it will fail

# Change to the SQL mini-shell

Cd sqlserver:

  • In PowerShell, run sqlps to load the SQL provider and cmdlets.

# Load SQLPS

sqlps

  • Try to switch to the SQL mini-shell and it now works

# Change to the SQL mini-shell

Cd sqlserver:

  • You can now do things like get-help invoke-sqlcmd because loading sqlps loaded the SQL cmdlets.

# Test a SQL cmdlet

invoke-sqlcmd -ServerInstance localhost -Query “select @@version”

  • BOL topic “Running SQL Server PowerShell” has other ways to load the SQL provider/snap-ins and SMO to Windows PowerShell.

9.     Run a SQL PowerShell Script

Open notepad and create c:tempTestSQL.ps1 (make sure .txt is not added to the end). Paste in this text:

# get a list of instances on this server

cd hklm:

dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names”

# see what we can admin

cd sqlserver:

ls

# see what the machine name is

cd sql

ls

# issue a query

invoke-sqlcmd -ServerInstance localhost -query “SELECT top 5 name FROM sys.databases ORDER BY name”

  • In Windows PowerShell load the SQL cmdlets and then execute the SQL PowerShell script

# Load the SQL cmdlets

Sqlps

# Change to the C:temp folder

Cd c:temp

# Execute the PowerShell script

.testsql.ps1

  • By default the format is slightly different for some of the output when executed from a script.

10.     Cleanup

  • Close the PowerShell window.
  • Delete c:temptestsql.ps1 and c:temppstest.txt
  • Delete the SQL Agent job you created.

My other PowerShell blogs:

Powershell for SQL – Getting Started http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-getting-started.aspx

PowerShell for SQL Server – Basics http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-server-basics.aspx


2 Comments

PowerShell for SQL Server – Basics

PowerShell for SQL Server – Basics

Cindy Gross, Dedicated Support Engineer

 Audience: SQL DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs.

Basics

  • PowerShell processes objects based on .NET.
  • Cmdlets are named in a Verb-Noun format such as Start-Service and Get-Help.
  • For help on any Cmdlet, use: get-help <cmdlet-name> -detailed.
  • Scripting is good for things like automating system administration tasks. Scripting languages include VBScript, CScript, and PowerShell.
  • Basic script editing can be done in notepad.exe.
  • SQL08+ installs sqlps.exe which is SQL’s PowerShell interface.
    • If you run sqlps.exe on its own it is a “mini-shell” with all the SQL functionality but not all of the Windows functionality.
    • If you open Windows PowerShell (PowerShell.exe) and then run sqlps to load the SQL provider and cmdlets you have both SQL and Windows functionality.
  • For the most part scripting is NOT case sensitive (though I found one registry key that is case sensitive – HKLM).
  • PowerShell scripts have the .ps1 suffix and you always specify the full path (or . for the current dir) when you call the script.

SQL

  • You must use the SQL08+ components to use PowerShell, but you can connect to SQL 2005 SP2+ and SQL 2000 SP4+ though the functionality may be limited.
  • Sqlps.exe and SSMS (right click, start PowerShell) start PowerShell with the SQL Server PowerShell provider and cmdlets loaded.
  • Within Windows PowerShell you can run sqlps to load the SQL cmdlets into Windows PowerShell.
  • The main cmdlet for SQL is Invoke-SQLCmd which lets you run any SQL query. The other cmdlets in SQL Server 2008/2008R2 are invoke-PolicyEvaluation (PBM), Encode-SQLName/Decode-SQLName (format SQL identifiers), and Convert-UrnToPath (navigation). You can also load the SQL Server Management Object (SMO) DLLs for additional functionality.
  • Read gettingStarted.rtf in the “Windows PowerShell 1.0 Documentation Pack” and complete the demo steps.

Learning

Syntax

  • Compare with –eq –ne –gt –like
  • $_ is the current object
  • The main difference between the script hosts cscript and wscript (the default) is that echo for cscript is to the command window and echo for wscript is to a message box.
  • If a path has a space you need to add & “…”. For example: & “C:tempmy PowerShellmytest.ps1”.
  • The current path is . as in .mytest.ps1

References

SQL Powershell

Windows Powershell

Downloads

My other PowerShell blogs:

Powershell for SQL – Getting Started http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-getting-started.aspx

SQL PowerShell Demo – Intro http://blogs.msdn.com/b/cindygross/archive/2011/09/23/sql-powershell-demo-intro.aspx


Leave a comment

PowerShell for SQL – Getting Started

PowerShell for SQL – Getting Started

Cindy Gross, Dedicated Support Engineer

You may want to use PowerShell to automate SQL activities such as backups. There are various ways to automate backups. For example, you can centralize the backup calls on an admin server which has some type of list of instances and databases and possibly varying schedules/types of backups for each. Another method is to use an admin server to push backup jobs to newly installed SQL instances. A variation of the 2nd is to periodically scan for new databases and/or databases without backups and/or databases that don’t meet some standard backup schedule and push out backup jobs. I’m not sure how automated or complex your backup plans are now. I’ve seen a wide range such as:

  • Manually schedule backups for each database as it is added (and hope you don’t miss one)
  • When you install the instance run a script to generate backup jobs for each database. Then periodically scan for new databases/databases not backed up and add backup jobs.
  • Schedule one backup job per instance to backup all local databases (either all on the same schedule or read a table to find the type/interval for each database on that instance)
  • Use single server or multi-server maintenance plans (not something I personally recommend in an enterprise – http://sqlserverpedia.com/blog/sql-server-management/maintenance-plans-vs-t-sql-scripts/)
  • Use a central scheduling system (master-target / multiserver administration via SQL jobs, UCP, 3rd party tools, etc.) with varying levels of complexity
  • Use a central Central Management Server (CMS) and/or a custom central admin databases to track which databases need which backup schedules and generate/check appropriate jobs

My next two blogs will have some details about the first steps with Powershell. Most of the SQL related operations you can do from Powershell are done by calling SMO http://msdn.microsoft.com/en-us/library/ms162169.aspx. Allen White does training on SMO, and I’m sure you can find others who do so as well.

Here are some backup samples that use PowerShell:

The Enterprise Policy Management (EPM) Framework is a classic example of how to use the new SQL Server 2008 Central Management Server (CMS) to automate tasks across multiple servers. In this case it is automating Policy Based Management (PBM) with PowerShell but the concept could apply to many types of automation.  http://epmframework.codeplex.com/releases/view/28621  

Next:

PowerShell for SQL Server – Basics http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-server-basics.aspx

SQL PowerShell Demo – Intro http://blogs.msdn.com/b/cindygross/archive/2011/09/23/sql-powershell-demo-intro.aspx


3 Comments

Using Mount Points with SQL Server

Using Mount Points with SQL Server

Author: Cindy Gross, Dedicated Support Engineer

Tech Reviewer: Min He, SQL Cluster PM

 

Summary: Microsoft does NOT support installing or putting databases on the “root” of a mount point in SQL Server 2005, 2008, 2008 R2 unless you manually manage the ACLs/permissions yourself (modified 1/13/12).

 The key points for mount points in combination with SQL Server 2005, 2008, 2008 R2 are:

  • A valid mount point that can be used by SQL Server databases is one that is mounted to a host volume (a shared drive in a cluster) that is associated with a drive letter. Multiple mount points can be hosted by a single drive meaning multiple mount points share a drive letter.
  • In a cluster, SQL Server must depend on each mount point it uses to avoid database corruption.
  • Do not put a SQL Server 2000 instance in a cluster where mount points exist, whether or not SQL Server 2000 is expected to use them (which it cannot). Do not add mount points to a cluster where SQL 2000 is already installed or future SQL Server 2000 patches will break.
  • Do not install SQL Server to the root directory of a mount point, always specify a subdirectory for all files. This has to do with how permissions are granted. If you must put files in the root of the mount point you must manually manage the ACLs/permissions.
  • Do not put DTC on a mount point.

For various reasons such as standardization, flexibility, space management, and just not enough letters in the alphabet many people use mount points on their servers. A mount point (aka mounted drive or volume junction) is a separate file system that is “mounted” onto a host drive so that it appears to be a subdirectory of the host drive. For example, say you have LUN volume A that is made visible to Windows as drive X:. You have a LUN volume B from another storage array and you want to present it to Windows. You might choose to mount it as X:SQL1. To SQL it looks like a subdirectory, but it’s really a whole different file system. Because it is a different file system, permissions are not inherited from the host system. So when you grant permissions to X: and say to propagate them to child folders, they are NOT applied to the mount point!

For testing purposes, try mounting a USB drive to your client box as C:USBMountPoint. You can move files to C:USBMountPoint and they will appear on the USB drive. Disconnect the USB drive and attach it to another box and those files are available on the new system but NOT on the old C:USBMountPoint location. That’s because the files aren’t really on C:, they’re on the USB drive that was mounted to C: for a while.

Example of creating a mount point on non-clustered disks:

  • Attach a USB drive formatted to NTFS
    • In my example it showed up as G:
    • It has a volume name of CGROSSFLASH
    • There is a file called BeforeMount.txt on the drive
  • Open “Disk Management” (Diskmgmt.msc)
  • Right click on CGROSSFLASH and choose “Change Drive Letter and Paths ”
  • Click “Remove” for G: then “Yes”
  • Right click on CGROSSFLASH  and choose “Change Drive Letter and Paths ”
  • Choose “Add” then “Mount in the following empty NTFS folder”
  • Click the “New Folder” button and give the directory a name, I’ll use USBMountPoint
  • Click “OK”
  • C:USBMountPoint is the root of my mount point. C: is the root of the host. You can see test.txt in C:USBMountPoint.
  • Try creating various files and directories in C:USBMountPoint.
  • Now remove the mount point then re-add it but create new folders first. You will end up mounting C:USBMountPointLevel2Level3. The files you created before now show up in the Level3 directory, not in the USBMountPoint directory.

 SQL Server 2000 and later standalone boxes and SQL Server 2005 and later clustered instances are supported on mount points mounted to a host volume (a shared drive in a cluster) that is associated with a drive letter. However, you must put all databases, including the system databases, on a subdirectory of the mount point, not in the root of the mount point, unless you manually manage the ACLs/permissions. The root of a mount point is the entire directory as defined when you mount it. For example, say I mount d:SQL1. That is the root of the mount point and I cannot install to it. If I mount d:SQL1ALL_DATA that is the root and I cannot install to d:SQLALL_DATA. I could install SQL to a subdirectory/subfolder such as d:SQL1ALL_DATADATA. If you install SQL to the root of a mount point setup makes the assumption that permissions are propogated up from the host root (d: in this example). However, that is not how Windows works with mount points. When you create a subdirectory setup creates permissions explicitly and therefore setup works when you install to a subdirectory of the mount point. The same logic applies to adding new databases or moving existing databases, they should not be in the root of a mount point.

In addition to putting the databases in subdirectories, you also have to make SQL depend on each individual mount point. When you make SQL depend on a mount point you force that mount point to come online before SQL does and therefore prevent certain types of potential corruption.

It is becoming more and more common in Windows 2008 and later clusters to have DTC depend on one of the lesser used/less important SQL Server drives. However, if you are using mount points for your SQL files you cannot do this as currently DTC is not supported on mount points.

 Mount points are a very useful tool. As long as you follow the rules for SQL Server you can increase your flexibility, spread your IO out over more IO paths, and/or add space to the system from multiple storage system with mount points.

 References: