Showing posts with label Tips. Show all posts
Showing posts with label Tips. Show all posts

Tuesday, February 4, 2014

Using sys.objects When Scripting Permissions? You're Missing Something!

I suspect most DBAs have a script in their T-SQL toolbelt which scripts out database permissions for a specific user (or all users). I do, too,  and for years I've used it and always assumed it did the job - until recently when I found that permissions on a certificate weren't in the output for a user I was looking at.

My script, like just about every other one you can find with your search engine of choice, joined sys.database_permissions against sys.objects to get specific permissions on objects (e.g. tables, views, and stored procedures). While sys.objects does contain quite a few things, there are quite a few securables that it does not - specifically:

  • Assemblies
  • Asymmetric Keys
  • Certificates
  • Database Principals (users, application roles, and database roles)
  • Fulltext Catalogs
  • Fulltext Stoplists
  • Schemas
  • Service Broker Message Types
  • Service Broker Remote Service Bindings
  • Service Broker Routes
  • Service Broker Services
  • Service Broker Service Contracts
  • Symmetric Keys
  • Types
  • XML Schema Collections

Fortunately, there are catalog views for everything listed above and all we have to do is join sys.database_permissions against each of them to ensure that we're including all securables.

SMO also has the same issue - the Database.EnumObjectPermissions method does not include permissions on any of the aforementioned securables. That means the only way to retrieve all database permissions with SMO is to iterate through each of the securables listed above and call the object's EnumObjectPermissions method in addition to calling the Database.EnumObjectPermissions method - not pretty or efficient. (For what it's worth, version 1.0.2.1 of SQL Power Doc does NOT do this, meaning some permissions could be excluded from the documentation. I'll have a new beta that fixes this posted within the next week or two)

Below is the updated version of my script, which you can also download from SkyDrive OneDrive.



/*********************************************************************************************
Security - Script Database Level Permissions (2005+)

Description:
   Scripts database permissions (including objects) for a specific user or all database users

   (C) 2014, Kendal Van Dyke (mailto:kendal.vandyke@gmail.com)

Version History:
   v1.00 (2014-02-03)

License:
   This query is free to download and use for personal, educational, and internal
   corporate purposes, provided that this header is preserved. Redistribution or sale
   of this query, in whole or in part, is prohibited without the author's express
   written consent.

Note:
   Execute this query in the database that you wish to script permissions for
  
*********************************************************************************************/
SET NOCOUNT ON

DECLARE    
@user SYSNAME;

-- Set this to NULL for all users
-- SET @user = 'DOMAIN\USER';
SET @user = NULL;

SELECT 'USE ' + QUOTENAME(DB_NAME()) + ';' AS '--Database Context';


-- Logins
SELECT 'CREATE USER [' + usr.name + '] FOR LOGIN [' + susr.name + ']'
      
+ CASE WHEN usr.TYPE = 'S'
              
THEN ' WITH DEFAULT_SCHEMA=[' + usr.default_schema_name + '];'
              
ELSE ';'
        
END AS ' --Logins'
FROM   sys.database_permissions AS PERM
       INNER JOIN
sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
      
INNER JOIN sys.server_principals AS susr ON usr.sid = susr.sid
WHERE  PERM.permission_name = 'CONNECT'
      
AND susr.sid != 0x01
      
AND usr.NAME = COALESCE(@user, usr.NAME);


-- Role Members
SELECT 'EXEC sp_addrolemember @rolename = ' + QUOTENAME(usr.name, '''')
       +
', @membername = ' + QUOTENAME(usr2.name, '''') + ';' AS '--Role Memberships'
FROM   sys.database_principals AS usr
      
INNER JOIN sys.database_role_members AS rm ON usr.principal_id = rm.role_principal_id
      
INNER JOIN sys.database_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
WHERE  /*usr2.is_fixed_role = 0*/
      
usr2.sid != 0x01
      
AND usr2.NAME = COALESCE(@user, usr2.NAME)
ORDER BY rm.role_principal_id ASC;


-- Object permissions
WITH   cteObject ( [major_id], [name], [class_desc], [class_name] )
        
AS ( SELECT   obj.[object_id] AS [major_id] ,
                      
QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.'
                      
+ QUOTENAME(obj.name) AS [major_name] ,
                      
'OBJECT_OR_COLUMN' AS [class_desc] ,
                      
'OBJECT' AS [class_name]
              
FROM        sys.all_objects AS obj
              
UNION ALL
              
SELECT  assembly_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ASSEMBLY' AS [class_desc] ,
                      
'ASSEMBLY' AS [class_name]
              
FROM        sys.assemblies
              
UNION ALL
              
SELECT  asymmetric_key_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ASYMMETRIC KEY' AS [class_desc] ,
                      
'ASYMMETRIC_KEY' AS [class_name]
              
FROM        sys.asymmetric_keys
              
UNION ALL
              
SELECT  certificate_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'CERTIFICATE' AS [class_desc] ,
                      
'CERTIFICATE' AS [class_name]
              
FROM        sys.certificates
              
UNION ALL
              
SELECT  principal_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'DATABASE_PRINCIPAL' AS [class_desc] ,
                      
CASE type_desc
                        
WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE'
                        
WHEN 'DATABASE_ROLE' THEN 'ROLE'
                        
ELSE 'USER'
                      
END AS [class_name]
              
FROM        sys.database_principals
              
UNION ALL
              
SELECT  fulltext_catalog_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'FULLTEXT_CATALOG' AS [class_desc] ,
                      
'FULLTEXT CATALOG' AS [class_name]
              
FROM        sys.fulltext_catalogs
              
UNION ALL
              
SELECT  stoplist_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'FULLTEXT_STOPLIST' AS [class_desc] ,
                      
'FULLTEXT STOPLIST' AS [class_name]
              
FROM        sys.fulltext_stoplists
              
UNION ALL
              
SELECT  message_type_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'MESSAGE_TYPE' AS [class_desc] ,
                      
'MESSAGE TYPE' AS [class_name]
              
FROM        sys.service_message_types
              
UNION ALL
              
SELECT  remote_service_binding_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'REMOTE_SERVICE_BINDING' AS [class_desc] ,
                      
'REMOTE SERVICE BINDING' AS [class_name]
              
FROM        sys.remote_service_bindings
              
UNION ALL
              
SELECT  route_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'ROUTE' AS [class_desc] ,
                      
'ROUTE' AS [class_name]
              
FROM        sys.routes
              
UNION ALL
              
SELECT  [schema_id] AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SCHEMA' AS [class_desc] ,
                      
'SCHEMA' AS [class_name]
              
FROM        sys.schemas
              
UNION ALL
              
SELECT  service_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SERVICE' AS [class_desc] ,
                      
'SERVICE' AS [class_name]
              
FROM        sys.services
              
UNION ALL
              
SELECT  service_contract_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SERVICE_CONTRACT' AS [class_desc] ,
                      
'CONTRACT' AS [class_name]
              
FROM        sys.service_contracts
              
UNION ALL
              
SELECT  symmetric_key_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'SYMMETRIC_KEY' AS [class_desc] ,
                      
'SYMMETRIC KEY' AS [class_name]
              
FROM        sys.symmetric_keys
              
UNION ALL
              
SELECT  user_type_id AS [major_id] ,
                      
QUOTENAME(name) COLLATE database_default ,
                      
'TYPE' AS [class_desc] ,
                      
'TYPE' AS [class_name]
              
FROM        sys.types
              
UNION ALL
              
SELECT  xml_collection_id AS [major_id] ,
                      
QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
                      
+ QUOTENAME(name) COLLATE database_default ,
                      
'XML_SCHEMA_COLLECTION' AS [class_desc] ,
                      
'XML SCHEMA COLLECTION' AS [class_name]
              
FROM        sys.xml_schema_collections
            
)
  
SELECT  CASE WHEN PERM.state <> 'W' THEN PERM.state_desc
                
ELSE 'GRANT'
          
END + SPACE(1) + PERM.permission_name + ' ON ' + obj.class_name
          
+ ' :: ' + obj.name + CASE WHEN cl.column_id IS NULL THEN SPACE(0)
                                      
ELSE ' (' + QUOTENAME(cl.name) + ')'
                                
END + ' TO ' + QUOTENAME(usr.name) COLLATE database_default
          
+ CASE WHEN PERM.state <> 'W' THEN ';'
                  
ELSE ' WITH GRANT OPTION;'
            
END AS '--Object Level Permissions'
  
FROM    sys.database_permissions AS PERM
           INNER JOIN
cteObject AS obj ON PERM.major_id = obj.major_id
                                          
AND PERM.class_desc = obj.class_desc
          
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
          
LEFT JOIN sys.all_columns AS cl ON PERM.minor_id = cl.column_id
                                              
AND PERM.major_id = cl.[object_id]
                                              
AND PERM.class_desc = 'OBJECT_OR_COLUMN'
  
WHERE   usr.is_fixed_role = 0
          
AND usr.sid != 0x01
          
AND usr.NAME = COALESCE(@user, usr.NAME)
  
ORDER BY PERM.permission_name ASC ,
          
PERM.state_desc ASC;


-- Database permissions (non-object specific)
SELECT CASE WHEN PERM.state <> 'W' THEN PERM.state_desc
            
ELSE 'GRANT'
      
END + SPACE(1) + PERM.permission_name + ' TO ' + QUOTENAME(usr.name) COLLATE database_default
      
+ CASE WHEN PERM.state <> 'W' THEN ';'
              
ELSE ' WITH GRANT OPTION;'
        
END AS ' --Database Level Permissions'
FROM   sys.database_permissions AS PERM
       INNER JOIN
sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
WHERE  PERM.major_id = 0
      
AND usr.NAME != 'dbo'
      
AND usr.NAME = COALESCE(@user, usr.NAME)
ORDER BY PERM.permission_name ASC ,
      
PERM.state_desc ASC;

Thursday, December 5, 2013

How To Get The TableDiff Utility

Tablediff is a command line utility that comes with SQL Server to compare the data and schema in two tables for differences and generate a T-SQL script to synchronize them. It's a rudimentary but useful tool that most people don't know is available and it gets the job done when you don't have a more sophisticated tool like Red Gate's SQL Compare at your disposal.

You can find the tool in a version-specific subfolder of your SQL Server shared features installation directory. For example, look in C:\Program Files\Microsoft SQL Server\110\COM if you've got SQL 2012 installed on the C: drive.

I've always assumed that it's included with Management Studio but recently I found out that's not the case. After testing various installation options I discovered that it's included when the SQL Server Replication features are installed, meaning you also need to install the Database Engine Services to get it:

image

Although not limited to replication scenarios, Microsoft's documentation hints that it was designed with replication in mind:

The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology.

Perhaps this is the reason most people don't know about it - replication isn't widely used and if you exclude the feature from your database install you won't get tablediff.

Now you know...

Tuesday, October 22, 2013

Windows 8.1 Upgrade Deletes VirtualBox Host-Only Network Adapter

Windows 8.1 was released to the Windows Store last week and aside from having to download the same 3.9 GB update on each machine I've installed it on it's been a relatively easy and smooth update. I did, however, run into one small problem related to Oracle VirtualBox. When you install VirtualBox a "Host-Only" network adapter is installed in Windows to allow guest VMs to communicate with the Windows host, as the screenshot below shows:

Network Connections Window

Unfortunately the Windows 8.1 upgrade process deletes the network adapter. After the upgrade you'll be faced with a warning message in the VM properties window and your VM won't start.

VirtualBox VM Settings Window

The fix is easy - just reinstall VirtualBox. It's not a bad idea, actually, as at the same time as Windows 8.1 went live so did VirtualBox Version 4.3. The latest version has a few nice improvements that make it worth the upgrade, including: Multi-touch support, virtual USB webcam, a new NAT adapter that enables hosts and guests to communicate without requiring a host-only adapter, and the ability to record the entire VM session to video.

As always, if you upgrade VirtualBox it's a good idea to upgrade the guest additions as well.

Thursday, September 19, 2013

Practical PowerShell: Merge SQLSaturday Speaker SpeedPASSes Into One PDF

To make things easier on speakers prior to a SQLSaturday organizers are encouraged to print & cut their SpeedPASSes ahead of time and distribute them at the speaker appreciation gathering before the main event. The current iteration of the admin tools makes that an arduous task (at best) – eating a couple of hours that could be better spent doing other things to get ready.

WorkFlow

Here's how it works today:

  1. Log into admin site & navigate to Event Settings > Manage SpeedPASS  (1 minute )
  2. Filter the list by typing "Comp" in the Lunch Status text box (1 minute)
  3. Right click the link for each SpeedPASS, choose Save target as..., and choose the folder where you want to save the PDF. Repeat for however many comps you have - sometimes over 50! (30 minutes)
  4. Open and print each PDF (30+ minutes)

Having been faced with doing this this last week for SQLSaturday #232 in Orlando I spent as much time as if I had done this manually figuring out how to automate this (as much as possible) and cut the task down to 10 minutes or less for the benefit of other SQLSaturday organizers. Here's what I came up with:

  1. Log into admin site & navigate to Event Settings > Manage SpeedPASS  (1 minute)
  2. Filter the list by typing "Comp" in the Lunch Status text box (1 minute)
  3. Copy and paste the list into Excel (5 minutes)
  4. Copy the column with the SpeedPASS URLs from Excel to a text file and save it to a text file (1 minute)
  5. Run a script to download the PDF files and merge them into a single PDF (1 minute)
  6. Print the merged PDF (2 minutes)

Enter PowerShell

Being a PowerShell guy I figured step #5 can be done in a few lines of code. PowerShell can handle downloading the PDFs natively but not the merge part. Bingle led me to PDFsharp, a .NET library for creating and modifying PDF documents, and since PowerShell can use .NET libraries we're in business!

Note: The PDFsharp assemblies are compiled against the .NET 4.0 framework. You will need to use PowerShell version 3 or higher for this to work!

Before you can run any PowerShell code you need to download PDFSharp from CodePlex. Make sure you get the assemblies and not the source code! Once downloaded, unblock the ZIP file or PowerShell gets cranky because it's considered untrusted since the file came from the internet.

To unblock a file, navigate to it in Windows Explorer, right click, and choose the Properties menu option. On the General tab, click the Unblock button, then click the OK button to close the Properties dialog.

Unblock File

Once the file is unblocked you can extract the contents; it doesn't matter where but keep your explorer window open so you can copy the path to a DLL you'll use in the PowerShell code.

Next, copy and paste the code below into the PowerShell ISE (you're using PowerShell version 3, right?) and modify the following variables:

  • $PdfSharpPath: The path to the GDI+ version of PdfSharp.dll that you extracted in the preceding instructions
  • $SpeedPASSUrlPath: The path to the TXT file containing the SpeedPASS URls

Now execute the code, go grab a cup of your favorite beverage, and when you get back you should have the individual PDFs downloaded and a single merged PDF file ready to print. The individual PDF files are downloaded into a temporary folder and the merged PDF file will have the same path and name as the TXT file but with a PDF extension instead of TXT.

# Change the following path to where you've put the PDFsharp DLL  
$PdfSharpPath = 'C:\Users\Kendal\Downloads\PDFsharp-MigraDocFoundation-Assemblies-1_32\GDI+\PdfSharp.dll'

# Change the following path to where the TXT file with SpeedPASS URLs is
$SpeedPASSUrlPath = 'C:\Users\Kendal\Documents\SpeedPASSlist.txt'


# Load the PdfSharp Assembly
Add-Type -Path $PdfSharpPath

# Create a temporary path for downloading the SpeedPASS PDF files
$TmpPath = [System.IO.Path]::GetFileNameWithoutExtension([System.IO.Path]::GetRandomFileName())
$TmpPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), $TmpPath)

if (-not $(Test-Path -Path $TmpPath)) {
New-Item -Path $TmpPath -ItemType 'Directory' | Out-Null
}

$OutputPath = [System.IO.Path]::ChangeExtension($SpeedPASSUrlPath, 'PDF')
$WebClient = New-Object System.Net.WebClient

Function Merge-PDF {
Param($path, $filename)

# Delete $filename if it exists
if (Test-Path $filename) {
Remove-Item $filename
}

$output = New-Object PdfSharp.Pdf.PdfDocument
$PdfReader = [PdfSharp.Pdf.IO.PdfReader]
$PdfDocumentOpenMode = [PdfSharp.Pdf.IO.PdfDocumentOpenMode]

foreach($i in (gci $path *.pdf -Recurse)) {
$input = New-Object PdfSharp.Pdf.PdfDocument
$input = $PdfReader::Open($i.fullname, $PdfDocumentOpenMode::Import)
$input.Pages | %{$output.AddPage($_)} | Out-Null
}

$output.Save($filename)
}

try {
$WebClient = New-Object System.Net.WebClient

Write-Host 'Downloading SpeedPASS PDF files'

# Download the individual SpeedPASS files
Get-Content -Path $SpeedPASSUrlPath | Where-Object {
-not [String]::IsNullOrEmpty($_)
}
| ForEach-Object {
$SourceURL = $_

$Filename = Join-Path -Path $TmpPath -ChildPath $(Split-Path -Path $_ -Leaf)

try {
$WebClient.DownloadFile($SourceURL, $Filename)
}
catch {
Write-Host "Unable to download $SourceURL to $Filename"
}
}

# Call the merge function
Merge-PDF -path $TmpPath -filename $OutputPath

Write-Host "Merge Complete!"
Write-Host "SpeedPASS files are located in $TmpPath"
Write-Host "The merged SpeedPASS file is located at $OutputPath"

}
catch {
Write-Host "Uh-oh, something went wrong..."
Write-Host 'Review $Error to figure out what happened'
}
finally {
$WebClient.Dispose()
}

Remove-Variable -Name WebClient, TmpPath, OutputPath



Admittedly, this is unpolished code - good enough to accomplish what it needs to. If you're PowerShell savvy (or if you're learning PowerShell and want practice in writing & running scripts) feel free to turn this into a script that you can run year after year.



Enjoy!

Wednesday, September 5, 2012

PowerShell Split-Path Bug And A Workaround

Split-Path is a cmdlet built into Windows PowerShell that returns a specific part of a path, e.g.  a parent directory or a filename. Here's a simple example of how it works:

PS C:\Users\Kendal> $Path = Get-Location
PS C:\Users\Kendal> Split-Path $Path
C:\Users

But try it on a Path that doesn't exist on the machine running the command and you get an error:

PS C:\Users\Kendal> $Path = 'Q:\Users\Kendal\somefile.txt'
PS C:\Users\Kendal> Split-Path -Path $Path
Split-Path : Cannot find drive. A drive with the name 'Q' does not exist.
At line:1 char:11
+ Split-Path <<<<  -Path $Path
    + CategoryInfo          : ObjectNotFound: (Q:String) [Split-Path], DriveNotFoundException
    + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.SplitPathCommand

One way you'd see this problem is using SMO to connect to a remote SQL Server and retrieve the paths of each database file, then try to use Split-Path to get the filenames or their parent paths. This Connect Bug indicates that the issue is fixed, but only in PowerShell V3; I can reproduce the error consistently in PowerShell V2.

The workaround is pretty simple: Use the static methods in the System.IO.Path .NET class to get the directory and file names. Using the same path in the previous example, here's how it looks:

PS C:\Users\Kendal> $Path = 'Q:\Users\Kendal\somefile.txt'
PS C:\Users\Kendal> [System.IO.Path]::GetDirectoryName($Path)
Q:\Users\Kendal
PS C:\Users\Kendal> [System.IO.Path]::GetFileName($Path)
somefile.txt

A bonus is that using static .NET methods tend to be faster than their equivalent PowerShell cmdlet - in this example 3-4 times faster. With most scripts speed probably isn't as much of an issue as flexibility but if you're looking to eek out every bit of performance possible (and avoid this particular bug) using static .NET methods is the way to go.

Thursday, December 1, 2011

Database Mirroring In A Windows Workgroup

Database Mirroring is a popular high availability solution and low-cost alternative to clustering, especially for smaller shops who can't afford the costs associated with SANs and Windows Server Enterprise or for smaller installations where clustering is simply overkill.

I recently set up mirroring for servers in a Windows workgroup and I had a few struggles getting it to work correctly. I did eventually get it running and wanted to share two things in particular that you might find helpful if you find yourself in a similar situation.

SQL Service Accounts

Because a workgroup isn't part of a domain any account that SQL is run as on a machine in a workgroup is a non-domain account. Books Online states "If you run SQL Server under a non-domain account, you must use certificates" for configuring mirroring. However, that's not entirely accurate - unless you have a specific need for certificates you don't have to use them.

The solution is simple - use local machine accounts with the same username and password for each SQL instance that will participate in mirroring.

Let's pretend you have two servers named serverA and serverB that will participate in mirroring and the SQL Service on each server uses a local machine account named sqlservice. When you configure mirroring and use the Database Mirroring Security Wizard you'll want to make sure to pay attention to the part where you specify the service accounts. If serverA is going to be the Principal and serverB the Mirror you'll want to put serverB in the Principal and serverA in the Mirror on this screen:

image

Although this seems backwards from what you might think at first don't worry - when the wizard completes it will actually create the login for serverA on serverA and the login for serverB on serverB. This is because the wizard creates mirroring endpoints on each server and grants CONNECT access to the service account from the other partner. In this example since serverA is the Principal the wizard will use the credentials you entered for the Mirror (serverA\sqlservice) to create a login on the Principal (serverA) and the credentials you entered for the Principal (serverB\sqlservice) to create a login on the Mirror (serverB).

The simplified reason this approach works is because when a connection is established from serverA to serverB the credentials supplied from serverA match the local machine account on serverB and access is granted - No certificates required!

Fully Qualified Domain Names

Books Online indicates that while you can use a fully qualified domain name (FQDN) for server network addresses you can also use IP addresses. Windows workgroups don't have the concept of a FQDN so I went with IP addresses and everything appeared to work…until I tested failover. Failover to the mirror worked great except that the apps on the web servers using the SQL native client (configured with the right connection string parameters) wouldn't talk to the mirror. On top of that, after failover the mirror would lose connectivity to the witness and if I rebooted the principal all the databases on the mirror would go into recovery mode. Talk about a demo that didn't go over so well!

Digging around in the Windows event log on the mirror turned up this error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://[SERVERNAME]:5022'.

where [SERVERNAME] is the name of the mirror. It turns out Adam Machanic (Blog | Twitter) had the same issue and fixed it by switching to FQDN. The problem? In a workgroup there are no FQDNs and trying to enter just a machine name in the mirroring wizard results in an error.

The solution? Add "fake" FQDN entries to the Windows hosts file (usually located in c:\windows\system32\drivers\etc\) on the principal, mirror, witness, and web servers, then rebuild mirroring using the "fake" FQDNs.

Conclusion

I hope my pain becomes your gain should you ever find yourself having to set up database mirroring in a Windows Workgroup!

Thursday, September 8, 2011

Configuring Windows 7 For Paired Storage

In recent years solid state drives (SSD for short) have dropped in price enough to be an affordable option in most mid-to-high end laptops & desktops. However, despite their insanely fast IO speeds their limited capacity compared to traditional spindle based storage (HDD) remains one of the biggest drawbacks. This has given rise to the concept of paired storage - using an SSD and HDD in the same machine to achieve a balance between speed and capacity.

Storage guru Stephen Foskett (Blog | Twitter) recently blogged about how he's implemented paired storage on his MacBook Pro. I'm using paired storage on a Windows 7 PC so I thought I'd share how I set it up to work for me.

Hardware

My laptop (Lenovo T410) shipped with an ultrabay slot on the side occupied by a DVD-RW drive. I ordered the laptop with a 500GB HDD to max out capacity, and knowing I was adding an SSD I also purchased an ultrabay adapter that allowed me to remove the DVD-RW drive and replace it with the HDD. For the occasional times I needed an optical drive I picked up an adapter on Ebay ($17!) that turned the DVD-RW into an external drive that connects via USB. I found a decent price on an 80 GB Intel X-25M SSD which completed everything needed to run paired storage.

Installation

My goal was to run the OS on the SSD to take advantage of its speed but the laptop shipped with the OS on the HDD so I needed a way to move it over to the SSD. Most Windows laptops ship these days without any physical media; instead they include a recovery partition which contains a factory image that can be restored to the boot partition. Fortunately Lenovo included a utility to make recovery DVDs from that image, so the first step was to create recovery media. Once that was done I physically moved the HDD to the ultrabay slot, installed the SSD in its place, then plugged in the USB optical drive and used the recovery media to lay down Windows on the SSD.

A nice "feature" of the the recovery media is that it self propagates a recovery partition on the SSD. Because space is a premium and I have physical recovery media there's no sense in keeping the recovery partition around so it was promptly deleted and the OS partition expanded out to make full use of the SSD space.

With Windows installed on the SSD I formatted the HDD to prepare it for use.

OS Changes

All of the OS changes I applied were motivated by two reasons - minimizing SSD space used and reducing SSD writes. SSDs have a limited write life cycle and manufacturers figure that an average user will write less than 100 GB per day to them when they calculate how long the drive will last. Wear leveling algorithms help spread the writes around to maximize life, and it also helps to keep as much free space as possible on the disk so there are more bits to spread all those writes around on.

The paging file will typically be as large as the amount of RAM but because it's used infrequently it's a perfect candidate for moving to the HDD. Changing it involves more than just a few clicks, but it's not terribly hard - here's step by step instructions for how. I set the file size to 400 MB on the SSD to allow Windows to write a dump file if something catastrophic occurs and set the file size to System managed size on the HDD.

Hibernation is a feature I never use and keeping it enabled lets a multi-GB file called hiberfile.sys hang around taking up space. Even if I did use hibernation there's no way to move this file to the HDD. The easiest fix for this is disable hibernation and delete the file. Follow this link for how to do it.

User Profiles

User profiles in Windows 7 are located in C:\Users and contain system files to support a user's session while logged in along with a user's documents, music, photos, etc.. Some people suggest moving the entire user profile to the HDD; I prefer a more limited approach that involves specific folders so that system files within the profile remain on the SSD. There's two ways to go about doing this: move the folder to a location on the HDD or use libraries to include the additional folders from the HDD.

In either approach you'll first want to set up folders on the HDD with the same path and permissions as they have on the SSD. An easy way to do this is by using xcopy from a command prompt running as an administrator. Here's an example of the command looks if my username is Kendal, my HDD is drive letter Z:, and I want to create a new folder for documents:

xcopy /T /O /I C:\Users\Kendal\Documents Z:\Users\Kendal\Documents

The /T switch creates a directory structure but doesn't copy any files. /O copies permissions, and /I tells xcopy that it's dealing with directories (folders) instead of files.

Moving specific folders to the HDD is relatively easy. For example, if you want to move your My Documents folder then in Windows Explorer navigate to the folder's parent directory (e.g. using the previous example for xcopy syntax it would be C:\users\Kendal) , then right click on the Documents folder and choose Properties. On the Location tab you can change the directory to the new directory you created on the HDD.

image

When you click the OK button you'll be prompted to move any files that existed in the old folder to the new folder. If you choose No they won't be moved and the folder will stick around on the SSD. If you choose Yes then Windows will move the files to the new folder, present the appropriate prompts  to deal with any conflicts as needed, then delete the old folder.

Libraries are another approach that work just as well, and in some cases even better if the folders you're including in a library aren't part of the standard set that can be moved using the technique I just described (e.g. folders for Virtual Machines). Microsoft has a good overview of what libraries are and how to use them that you should read up on first if you're not familiar with the feature.

Before setting up a library I use xcopy to mirror folder paths and permissions to the HDD. Once folders are set up for documents, music, etc. I add them to libraries and mark the folder on the HDD as the default location for saving files. If you choose to use libraries you'll also want to configure applications to use the folders on the HDD by default, e.g. iTunes, Windows Media Player, and Picasa. (This is an extra step that you don't have to do if you move the folders as previously described)

There is also a visual quirk that happens when you add the new folder to a library where the view shows files grouped by each folder in the library instead of as one unified folder like this:

image

If you prefer to see all the files in a single view simply right click on empty whitespace, choose Group By > (None)...

image

...and viola!

image

Other Folders

Over time other folders on the SSD start to accumulate files that take up space but are infrequently used. One in particular is C:\Windows\SoftwareDistribution\Download. Windows Update uses this folder to store downloaded patches and updates; the entire contents of this folder is safe to delete. No worries if you delete a downloaded update before you've applied it - Windows Update will just download it again for you.

I recommend grabbing a copy of the free utility WinDirStat to find other large files and folders that are taking up space on the SSD. Some applications will create really large (several GB large) files nested somewhere within the AppData folder in your user profile path. Outlook puts .OST and .PST files there and if you've got an iPod\iPhone\iPad you'll see 4-5 GB device backups there as well. Another folder that consumes a lot of space over time is C:\Windows\Installer. WinDirStat will help you find them easily.

Once you've found them you can use xcopy to move their folders to the HDD, then use junction, a free tool from Microsoft Sysinternals, to create junction points to the folders. A junction point is essentially an alias which allows an application to access a file or folder as if it existed on a particular path but the actual path is somewhere else. If you're not familiar with junction points or how to create them I'd hold off from trying this option for now - otherwise you could create more problems than you're trying to solve.

Conclusion

Even though I'm not running on the SSD exclusively I still get fantastic performance. I can get from power on to logged in and read to use in less than 60 seconds and on average applications take no more than a few seconds to load. Because the HDD only contains files that are used infrequently I get all the speed of the SSD plus the storage benefits of the HDD.

If you've already got Windows installed and want to make the move to paired storage but loathe the thought of reinstalling Windows Lifehacker posted a very helpful guide for how to do it that's worth checking out.

If you're thinking about putting an SSD into your machine but worried about the lack of space then paired storage may be exactly what you're looking for.

Friday, May 6, 2011

Orlando 101 For SQLRally Attendees: Weather

The inaugural SQLRally in Orlando is less than a week away (May 11-13)! Maybe you're coming to Orlando for the first time, or maybe you've been here before but just aren't familiar with the area. I live a few miles away from the Marriott so I thought I'd share some "insider" tips. After posting my tips for travel & hotel, food, shopping, and entertainment yesterday it hit me that I forgot to mention one of the best things about coming to Florida this time of year - the weather!

Sunshine…And Rain
Mid-May temperatures in Orlando peak in the low-90's (F) in the afternoons and drop into the low 70's (F) at night. What gets most people when they come here, though, is the humidity. It's not uncommon for the humidity to reach 60-75% on an average day, and when it rains it'll get close to 100%.

Speaking of rain, occasionally there are isolated showers in the afternoon that last 20-30 minutes - long enough to get the ground wet - but an hour later you'd never know it even rained because the heat evaporates all the water again. The biggest problem it causes is on the roads because for some reason people forget how to drive when it starts to rain.

If you have allergies bring your medication of choice - pollen levels in the area are very high this time of year.

Mosquitos
The Everglades (massive wetlands that cover 1\3 of Florida) start in Orlando and that means there are a lot of places with standing water. Standing water is breeding grounds for mosquitos and they come out en masse when the sun goes down! You may want to pick up some bug spray for the outdoors after hours events we have planned on Wednesday and Thursday.

Forecast
Here's the forecast for May 11-13, courtesy of weather.com:

Forecast - 2011.05.11

Forecast - 2011.05.12

Forecast - 2011.05.13

Don't forget your bathing suits, sandals, and sunscreen…and see you next week at SQLRally!

 

SQLRally_Banner_728x90