Cindy Gross | Befriending Dragons

Category: SQL & Big Data

  • SQL PASS: All the Magic Knobs – Tools

    SQL PASS: All the Magic Knobs – Tools

    In my All the Magic Knobs talk at #SQLPASS 2011 I discussed some easy ways to determine if you’re using some of the performance magic for SQL Server. When you have many consolidated, non-tier 1 databases you don’t have a lot of control over, the best way to tune is to provide a solid, performant infrastructure through low effort, high impact choices. The same steps help in your tier 1 environments as well. The quickest way to see how close you are to that standard is to run one of our automated health checks. They check the SQL instance itself and some of the most important Windows settings that help SQL Server operate optimally.

    SQL Best Practices Analyzer (BPA) is available for SQL Server 2000, 2005, and 2008/2008 R2. It is an add-in to the Microsoft Baseline Configuration Analyzer (MBCA). Both the MBCA and the SQL BPA are free. You can run the BPA locally or remotely and you can find plenty of sample scripts to run it against multiple instances. You choose your schedule for execution and you can either review the output after each execution manually or write your own program to alert you to what you consider the most serious items.

    The System Center Advisor (SCA) is at this point still in pre-release. Licensing details will be available after release, for now you can download a free trial. It works for SQL Server 2008 and newer on Windows 2008 and newer. SCA runs on a schedule and sends alerts when a registered instance is not configured as advised. What it checks can change dynamically as PSS finds new important items.

    Several companies, including Microsoft through our Premier Field Engineering (PFE) team, offer various health checks that include knowledge sharing and additional advice to help you decide if, how, when, and where to implement the recommendations.

    Of course, you have to actually implement the recommendations to get the benefit; the tools listed above don’t do any remediation on their own. While that should go without saying, in my experience known recommendations often go unimplemented until some problem they would have prevented pops up.

    For more SQL Server best practices see some of my other blogs: http://blogs.msdn.com/b/cindygross/archive/tags/best+practices/  

     

  • SQLPASS: Are You Smarter Than an MCM? VLF Demos

    Are You Smarter Than an MCM?

     I had a great time on Wednesday co-presenting with some great SQL peeps! We dressed up, had a lot of fun, and shared a few technical tips along the way. My demo was on VLFs, an often forgotten and/or misunderstood part of the transaction log technology. The demo files are attached below. Thanks to everyone who came to the talk! #SQLPASS

    The Team for DBA-414-M

    Our fabulous “Smarter” team is

    VLFs

    Question: Cindy has ADHD.  In the past she we able to keep up with multiple tasks at once, but lately she has slowed down.  Why? 

    Answer: Excessive VLFs

     A VLF is the boundary within your log file(s) for log operations such as scans (replication, recovery, mirroring, CDC, etc.) and log clearing/truncating.

    • 100s of VLFs might be ok, 1000s is probably bad, 10s of thousands is almost certainly bad. Bad means much slower operations for activities that operate within VLFs boundaries.
    • DBCC LOGINFO is undocumented and unsupported but is the only way to see your VLF distribution. It returns one row per VLF.
    • Often you get too many VLFs from autogrow/small growths.

     VLF Lessons Learned:

    • Avoid autogrow by pre-sizing your data and log files appropriately. This includes alerting when your free space starts to decrease significantly and planning for data growth over the lifecycle of the system. Make sure you check your system as well as your user databases.
    • Set autogrow values to reasonably large amounts to avoid a series of small autogrows in an unexpectedly high volume period, but not so big the growths themselves slow down the system.
    • Alert on autogrows and check (and possibly correct) VLFs afterwards.
    • Check your VLFs periodically and during performance troubleshooting and consider shrinking/regrowing properly (with the proper presizing) to reduce the total number of VLFs when the number gets “too high”.
    • Never grow (manually or via autogrow settings) in increments of exactly 4GB. Values such as 4000MB or 8000MB are good, but there is a bug with multiples of exactly 4GB.
    • Do NOT shrink your data or log files on a regular basis. Shrinking should be extremely rare and in response to a specific issue.
    • And because it can’t be said too often, pre-size your databases to avoid autogrow.
    • Revisit the sizes periodically.

     Thanks everyone for coming to the talk and for the great feedback afterwards! You did fill out your evaluation forms, right? 🙂

    Pictures:

    http://yfrog.com/z/h88chpvj

    http://flic.kr/p/awbV6h

    VLFDemo.zip

  • SQL PASS: All the Magic Knobs

    SQL PASS 2011 DBA-319-C #SQLPASS

    All the Magic Knobs – Low Effort, High Return Tuning

    Key points covered:

    • Power Savings = High Performance
    • Smart Virtualization
    • Enough Hardware
    • Control other apps, filter drivers
    • Optimize for ad hoc workloads = ON
    • Compression = ON
    • Set LPIM + Max Server Memory
    • Pre-size files, avoid shrink and autogrow
    • Fast Tempdb
    • Proper Maintenance

    My presentation from 10/13/11 is attached.

    AllTheMagicKnobs.pptx

  • 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!

  • 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

  • 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

  • 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

  • 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:

  • Invite a guest in, or not?

    The guest user in SQL Server is a bit of a funny object. While we’re taught to be polite to guests, sometimes we don’t want to invite them into our databases.

    If you have a login to an instance of SQL Server that doesn’t automatically give you access to any databases…. or does it? By default guest has been “enabled” by GRANTing CONNECT to guest in master, msdb, and tempdb. While guest exists in each database, unless you enable guest in the model database guest will not be enabled by default for user databases. If you do enable guest it, like every other user, is a member of the public and has some basic permissions in that database. This is mostly around metadata.

    Unfortunately this isn’t always clear in our current documents. There are some places in BOL where we say you can remove guest from any database other than master and tempdb and others where we say you can remove it from any user database. The real answer is that as of SQL Server 2008 R2 and earlier guest must be enabled in master, tempdb, AND msdb or some functionality will break. The details are documented in this new KB article: You should not disable the guest user in the msdb database in SQL Server http://support.microsoft.com/kb/2539091

    The below query shows you that each database has a user called guest but you can see that most (at least by default) do not have guest enabled/CONNECT permission granted to guest. Of course sp_msforeachdb is undocumented and unsupported with all the usual caveats. You may want to look at Bob Taylor’s SQL Iterators code on Codeplex, though Codeplex is still unsupported sample code.

    DECLARE @var1 varchar (1000);
    SELECT @var1 = ‘USE ?;SELECT ”=== ”+DB_NAME();
    SELECT name, type_desc, owning_principal_id
    FROM sys.database_principals
    WHERE name = ”guest”;
    SELECT prins.name AS grantee_name, perms.*
    FROM sys.database_permissions AS perms
    JOIN sys.database_principals AS prins
    ON perms.grantee_principal_id = prins.principal_id
    WHERE prins.name = ”guest” AND perms.permission_name = ”CONNECT”;’
    EXEC sp_msforeachdb @var1;

  • Taming the Tempdb Tempest – WI SQL Server Virtual User Group, 22 Apr 2011

    Thanks to the Wisconsin Virtual SQL Server User Group for letting me talk about tempdb today! The slides and demo queries are attached. Once the recording is available I will update this blog with a link to it.

    Taming the Tempdb Tempest

    Summary:

    ·         Multiple data files of the same size, one log file

    ·         Enough data files to avoid contention, not so many to cause problems.

    ·         Presize for peak periods of next X months, re-evaluate

    ·         Set autogrow to be rare but “big enough”

    ·         Instant File Initialization on (small security risk)

    ·         Fast IO subsystem

    ·         Change size/settings if you add new features that use tempdb

    ·         Monitor for approaching full, change in activity/size

    ·         Performance tune user databases and applications

    ·         Limit use of versioning or temp objects

     

    The demo queries are:

    ·         sys.dm_db_file_space_usage.sql: How space is used inside tempdb

    ·         sys.dm_db_file_space_usage_companion1.sql: Show how different activities cause space to be used in tempdb

    ·         Autogrow.sql: Find autogrow settings for all dbs on an instance

    ·         TempdbContention.sql: Find contention on tempdb metadata

     

    Also see my previous blog post with the same basic data in a different format: Compilation of SQL Server TempDB IO Best Practices http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

    I also delivered the talk to the Boise SQL Server User Group on 13 Sep 2011 and the updated queries and slide deck are attached below.

    TempdbBoiseSQLSep2011.zip