Mar 262013
 

Database mirroring has been around since SQL Server 2005, and it’s turned out to be an excellent step up from log shipping.  However, like log shipping, it is still only a database-level disaster recovery solution.  Meaning that any logins, server role memberships or server-level permissions will not be mirrored over to the mirror server.  This is where the DBA needs to plan ahead and create their own custom jobs to script and/or document these types of shortcomings.

My solution is to use Powershell.  In this example, I have setup database mirroring for the AdventureWorks2012 database.  For this demo, both instances, TEST1 and TEST2, are on the same physical server.
There are two logins on the principal server that currently do not exist on the mirror server.  One is a SQL login, AWLogin1, and the other is a Windows Authenticated login, TRON2AWLogin2.
The first step of our Powershell script will need to connect to the principal server to generate a CREATE LOGIN script for those two logins.  To generate the script, we need to grab the login name, the SID, and the hashed password if it’s a SQL login.  This is accomplished by running the following code.

SELECT ‘USE master; CREATE LOGIN ‘ + QUOTENAME(p.name) + ‘ ‘ +
CASE WHEN p.type in (‘U’,‘G’)
    THEN ‘FROM WINDOWS ‘
    ELSE
    END
+ ‘WITH ‘ +
CASE WHEN p.type = ‘S’
    THEN ‘PASSWORD = ‘ + master.sys.fn_varbintohexstr(l.password_hash) + ‘ HASHED, ‘ + ‘SID = ‘ + master.sys.fn_varbintohexstr(l.sid) +  ‘, CHECK_EXPIRATION = ‘ +
    CASE WHEN l.is_expiration_checked >0
        THEN ‘ON, ‘
        ELSE ‘OFF, ‘
        END
    + ‘CHECK_POLICY = ‘ +
    CASE WHEN l.is_policy_checked >0
        THEN ‘ON, ‘
        ELSE ‘OFF, ‘
        END +
    CASE WHEN l.credential_id > 0
        THEN ‘CREDENTIAL = ‘ + c.name + ‘, ‘
        ELSE
        END
ELSE
END
+ ‘DEFAULT_DATABASE = ‘ + p.default_database_name+
CASE WHEN LEN(p.default_language_name)> 0
    THEN ‘, DEFAULT_LANGUAGE = ‘+ p.default_language_name
    ELSE
    END
+ ‘;’ AS ‘LoginScript’
FROM master.sys.server_principals p LEFT JOIN master.sys.sql_logins l
    ON p.principal_id = l.principal_id LEFT JOIN master.sys.credentials c
    ON l.credential_id = c.credential_id
WHERE p.type IN (‘S’,‘U’,‘G’)
    AND p.name NOT IN (‘sa’, ‘NT AUTHORITYSYSTEM’)
    AND p.name NOT LIKE ‘##%##’
    AND p.name NOT LIKE ‘BUILTIN%’
    AND p.name NOT LIKE ‘NT SERVICE%’
ORDER BY p.name;
In this example, you can see we have one row for each of the two logins.

The next step of the Powershell script will need to write those two rows of data to a file on the mirror server.  This is done using the System.IO.StreamWriterclass.

foreach($row in $commandList.Tables[0].Rows)
{
    try
    {
            $output = $row[“LoginScript”].ToString()
            $stream.WriteLine($output)
      }
      catch
      {
            $stream.Close()
            CheckForErrors
      }
}
When there is a need to failover to the mirror server, the DBA can then open this script and run it.  All logins will be created and with their original SID value and password.
The second half of the Powershell script will use the same procedures to script out any server role memberships or server-level permissions these two logins may have on the principal server.  This is done using the following block of code.

— BUILD SERVER ROLE MEMBERSHIPS
SELECT ‘USE master; EXEC sp_addsrvrolemember @loginame = ‘+QUOTENAME(s.name)+‘, @rolename = ‘+QUOTENAME(s2.name)+‘;’  AS ‘ServerPermission’
FROM master.sys.server_role_membersr INNER JOIN master.sys.server_principals s
    ON s.principal_id = r.member_principal_id INNER JOIN master.sys.server_principals s2
    ON s2.principal_id = r.role_principal_id
WHERE s2.type = ‘R’
    AND s.is_disabled = 0
    AND s.name NOT IN (‘sa’,‘NT AUTHORITYSYSTEM’)
    AND s.name NOT LIKE ‘##%##’
    AND s.name NOT LIKE ‘NT SERVICE%’
UNION ALL
— BUILD SERVER-LEVEL PERMISSIONS
SELECT ‘USE master; ‘+sp.state_desc+‘ ‘+sp.permission_name+‘ TO ‘+QUOTENAME(s.name) COLLATE Latin1_General_CI_AS+‘;’  AS ‘ServerPermission’
FROM sys.server_permissions sp JOIN sys.server_principals s
    ON sp.grantee_principal_id= s.principal_id
WHERE s.type IN (‘S’,‘G’,‘U’)
    AND sp.type NOT IN (‘CO’,‘COSQ’)
    AND s.is_disabled = 0
    AND s.name NOT IN (‘sa’,‘NT AUTHORITYSYSTEM’)
    AND s.name NOT LIKE ‘##%##’
    AND s.name NOT LIKE ‘NT SERVICE%’;
From the output, you can see the TRONAWLogin2 is a member of the BULKADMIN server role and has the VIEW SERVER STATE permission.  These two rows will be written to a file in the same file share as the previous file.  
As before, once the database is failed over to the mirror server, the DBA can run this script to apply any missing permissions.
Finally, this Powershell script can be scheduled to run from any server; however, I choose to setup this job on the principal server.  I schedule it to run once a day through SQL Agent.  Each run of the script will overwrite the existing file, so if there are any logins or permissions that have been added or removed, it will show up in the latest version of the files.

Using this Powershell script can make it very easy to script out logins and permissions.  While this example was used with database mirroring, then same strategy will work for log shipping.  The entire Powershell script is below.

########################################################################################### 
#
#   File Name:    CopyLoginsPermissions.ps1
#
#   Applies to:   SQL Server 2008
#                 SQL Server 2008 R2
#                 SQL Server 2012
#
#   Purpose:      To copy logins and permissions from a PRIMARY server
#                 to it's corresponding MIRROR server.
#
#   Prerequisite: Powershell v2.0 must be installed.
#                 SQL Server components must be installed.
#
#   Parameters:   [string]$sourceInstance - Production SQL Server name (Ex: SERVER\INSTANCE)
#                 [string]$destinationPath - BCP backup share (UNC Path Ex: \\SERVER\backup$)
#
#   Author:       Patrick Keisler
#
#   Version:      1.0.0
#
#   Date:         03/24/2013
#
#   Help:         http://www.patrickkeisler.com/
#
###########################################################################################

#Enable Debug Messages
#$DebugPreference = "Continue"

#Disable Debug Messages
$DebugPreference = "SilentlyContinue"

#Terminate Code on All Errors
$ErrorActionPreference = "Stop"

#Clear screen
CLEAR

#Load Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

function CheckForErrors {
    $errorsReported = $False
    if($Error.Count -ne 0)
    {
  Write-Host
  Write-Host "******************************"
        Write-Host "Errors:" $Error.Count
        Write-Host "******************************"
        foreach($err in $Error)
        {
            $errorsReported  = $True
            if( $err.Exception.InnerException -ne $null)
            {
                Write-Host $err.Exception.InnerException.ToString()
            }
            else
            {
                Write-Host $err.Exception.ToString()
            }
            Write-Host
        }
        throw;
    }
}
function GetServer {
    Param([string]$serverInstance)

    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($serverInstance)
    $server.ConnectionContext.ApplicationName = "Copy Logins and Permissions"
 $server.ConnectionContext.ConnectTimeout = 5
    $server;
}
function GetServerLogins {
 Param([string]$serverInstance, [string]$destinationPath)
 
 $array = $serverInstance.Split("\")
 if($array.Length -eq 1)
 {
  $machineName = $array[0]
  $instanceName = "DEFAULT"
 }
 else
 {
  $machineName = $array[0]
  $instanceName = $array[1]
 }
 
 $fileName = $destinationPath + "\" + $machineName + "_" + $instanceName + "_ServerLogins.sql"
 $stream = New-Object System.IO.StreamWriter($fileName)

 $server = GetServer($serverInstance)
 $db = $server.Databases["master"]
 $commandList = $db.ExecuteWithResults(
 @"
SELECT 'CREATE LOGIN ' + QUOTENAME(p.name) + ' ' +
CASE WHEN p.type in ('U','G')
        THEN 'FROM WINDOWS '
        ELSE ''
        END
+ 'WITH ' +
CASE WHEN p.type = 'S'
        THEN 'PASSWORD = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' HASHED, ' + 'SID = ' + master.sys.fn_varbintohexstr(l.sid) +  ', CHECK_EXPIRATION = ' +
        CASE WHEN l.is_expiration_checked > 0
                THEN 'ON, '
                ELSE 'OFF, '
                END
        + 'CHECK_POLICY = ' +
        CASE WHEN l.is_policy_checked > 0
                THEN 'ON, '
                ELSE 'OFF, '
                END +
        CASE WHEN l.credential_id > 0
                THEN 'CREDENTIAL = ' + c.name + ', '
                ELSE ''
                END
ELSE ''
END
+ 'DEFAULT_DATABASE = ' + p.default_database_name +
CASE WHEN LEN(p.default_language_name) > 0
        THEN ', DEFAULT_LANGUAGE = ' + p.default_language_name
        ELSE ''
        END
+ ';' AS 'LoginScript'
FROM master.sys.server_principals p LEFT JOIN master.sys.sql_logins l
 ON p.principal_id = l.principal_id LEFT JOIN master.sys.credentials c
 ON l.credential_id = c.credential_id
WHERE p.type IN ('S','U','G')
 AND p.name NOT IN ('sa','NT AUTHORITY\SYSTEM')
 AND p.name NOT LIKE '##%##'
 AND p.name NOT LIKE 'BUILTIN\%'
 AND p.name NOT LIKE 'NT SERVICE\%'
ORDER BY p.name;
"@
 )
 
 Write-Host "Writing to server logins file..." -NoNewline
 $stream.WriteLine("/* This file should not be blank!!!*/")
 $stream.WriteLine("/* These commands were generated from: $serverInstance */")

 if ($commandList.Tables[0].Rows.Count -ge 1)
 {
  foreach($row in $commandList.Tables[0].Rows)
  {
      try
      {
    $debug = "Script: " + $row["LoginScript"]
    Write-Debug $debug
    $output = $row["LoginScript"].ToString()
    $stream.WriteLine($output)
   }
   catch
   {
    $stream.Close()
    CheckForErrors
   }
  }
 }
 $stream.close()
 Write-Host "OK"
}

function GetServerPermissions {
 Param([string]$serverInstance, [string]$destinationPath)
 
 $array = $serverInstance.Split("\")
 if($array.Length -eq 1)
 {
  $machineName = $array[0]
  $instanceName = "DEFAULT"
 }
 else
 {
  $machineName = $array[0]
  $instanceName = $array[1]
 }
 
 $fileName = $destinationPath + "\" + $machineName + "_" + $instanceName + "_ServerPermissions.sql"
 $stream = New-Object System.IO.StreamWriter($fileName)

 $server = GetServer($serverInstance)
 $db = $server.Databases["master"]
 $commandList = $db.ExecuteWithResults(
 @"
-- BUILD SERVER ROLE MEMBERSHIPS
SELECT 'USE master; EXEC sp_addsrvrolemember @loginame = '+QUOTENAME(s.name)+', @rolename = '+QUOTENAME(s2.name)+';'  AS 'ServerPermission'
FROM master.sys.server_role_members r INNER JOIN master.sys.server_principals s 
 ON s.principal_id = r.member_principal_id INNER JOIN master.sys.server_principals s2 
 ON s2.principal_id = r.role_principal_id 
WHERE s2.type = 'R' 
 AND s.is_disabled = 0 
 AND s.name NOT IN ('sa','NT AUTHORITY\SYSTEM') 
 AND s.name NOT LIKE '##%##' 
 AND s.name NOT LIKE 'NT SERVICE\%' 
UNION ALL
-- BUILD SERVER-LEVEL PERMISSIONS
SELECT 'USE master; '+sp.state_desc+' '+sp.permission_name+' TO '+QUOTENAME(s.name) COLLATE Latin1_General_CI_AS+';'  AS 'ServerPermission' 
FROM sys.server_permissions sp JOIN sys.server_principals s 
 ON sp.grantee_principal_id = s.principal_id 
WHERE s.type IN ('S','G','U') 
 AND sp.type NOT IN ('CO','COSQ') 
 AND s.is_disabled = 0 
 AND s.name NOT IN ('sa','NT AUTHORITY\SYSTEM') 
 AND s.name NOT LIKE '##%##' 
 AND s.name NOT LIKE 'NT SERVICE\%';
"@
 )
 
 Write-Host "Writing to server permissions file..." -NoNewline
 $stream.WriteLine("/* If this file is blank, then no server-level permissions (i.e Server Role Memberships) existed on the production server. */")
 $stream.WriteLine("/* These commands were generated from: $serverInstance */")
 
 if ($commandList.Tables[0].Rows.Count -ge 1)
 {
  foreach($row in $commandList.Tables[0].Rows)
  {
      try
      {
    $debug = "Script: " + $row["ServerPermission"]
    Write-Debug $debug
    $output = $row["ServerPermission"].ToString()
    $stream.WriteLine($output)
   }
   catch
   {
    $stream.Close()
    CheckForErrors
   }
  }
 }
 $stream.close()
 Write-Host "OK"
}

function PerformValidation {
    Param($sourceInstance, $destinationPath)
 
 Write-Host "Validating parameters..." -NoNewline
    if([String]::IsNullOrEmpty($sourceInstance))
    {
  Write-Host "ERROR"
     $errorMessage = "Source server name is not valid."
        throw $errorMessage
    }
    if([String]::IsNullOrEmpty($destinationPath))
    {
  Write-Host "ERROR"
        $errorMessage = "Destination path name is not valid."
     throw $errorMessage
    }
 else
    {
        if(-not($destinationPath.StartsWith("\\")))
        {
   Write-Host "ERROR"
            $errorMessage = "Destination path is not valid: " + $destinationPath
            throw $errorMessage
        }
    }
    
 Write-Host "OK"
 
 Write-Host "Verifying source SQL Server connectivity..." -NoNewline
 $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sourceInstance)
    $conn.ApplicationName = "Copy Logins and Permissions"
 $conn.NonPooledConnection = $true
 $conn.ConnectTimeout = 5
 try
 {
  $conn.Connect()
        $conn.Disconnect()
 }
 catch
 {
  CheckForErrors
 }
    Write-Host "OK"


    Write-Host "Verifying destination file share exists..." -NoNewline
    if((Test-Path -Path $destinationPath) -ne $True)
    {
  Write-Host "ERROR"
        $errorMessage = "File share: " + $destinationPath + " does not exists"
        throw $errorMessage
    }
    Write-Host "OK"
}

function Main{
 
 Param([string]$sourceInstance, [string]$destinationPath)
 
 $Error.Clear()

    Write-Host
    Write-Host "============================================================="
    Write-Host " 1: Perform Initial Checks & Validate Input Parameters"
    Write-Host "============================================================="
 
 PerformValidation $sourceInstance $destinationPath
 
    Write-Host
    Write-Host "============================================================="
    Write-Host " 3: Create Server Logins Script"
    Write-Host "============================================================="

 GetServerLogins $sourceInstance $destinationPath

    Write-Host
    Write-Host "============================================================="
    Write-Host " 4: Create Server Permissions Script"
    Write-Host "============================================================="

 GetServerPermissions $sourceInstance $destinationPath

    Write-Host
    Write-Host "============================================================="
    Write-Host "  Copy Logins and Permissions to DR Completed Successfully   "
    Write-Host "============================================================="
}

#Capture inputs from the command line.
$sourceInstance = $args[0]
$destinationPath = $args[1]

#Hard-coded values used only for development
#$sourceInstance = "TRON\TEST1"
#$destinationPath = "\\TRON\BACKUP1"

#Prompt for inputs for an interactive script
#$sourceInstance = $(Read-Host "Source SQL Server name (Ex: server\instance)")
#$destinationPath = $(Read-Host "Destination share to copy backup file to (UNC Path Ex: \\SERVER\backup$)")

$debug = "Source Instance Parameter: " + $sourceInstance
Write-Debug $debug
$debug = "Destination Path Parameter: " + $destinationPath
Write-Debug $debug

Main $sourceInstance $destinationPath
Mar 122013
 

T-SQL Tuesday #40 is underway, and this month’s host is Jennifer McCown (blog|twitter).  The topic is about File and Filegroup Wisdom.  Jennifer says she’s a big fan of the basics, so I thought I would talk about the basics of proportional fill within a filegroup.  This should be pretty common knowledge, but I still talk to a lot of DBAs that don’t know anything  about it, or if they have heard of it, they still don’t know how it works.
The proportional fill algorithm is used to keep the amount of free space within a filegroup evenly distributed across all files in a filegroup.  SQL Server’s proportional fill falls in line with the strategy of placing your files and filegroups across multiple disks, and thus, allowing for improved I/O performance.
Let’s say we need to add more storage space for our AdventureWorks2012 database that has outgrown the current drive D.  Because of storage limitations, we can’t add any more space to D, so our only choice is to add a completely new drive E.  
Once we add the new E drive to the server, we add a new data file to the PRIMARY filegroup of the AdventureWorks2012database using the following query.
USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE (
     NAME = N’AdventureWorks2012_Data2′
    ,FILENAME = N’E:MSSQL11.TEST1MSSQLDATAAdventureWorks2012_Data2.ndf’
    ,SIZE = 200MB
    ,FILEGROWTH = 1024KB
) TO FILEGROUP [PRIMARY];
GO

One might think we’re safe at this point; however, because of the proportional fill feature we’re not.  Once new data is written to the data files, SQL Server will create the new page allocations on the newly created AdventureWorks2012_Data2.ndf file because it has a higher percentage of free space compared to AdventureWorks2012_Data.mdf.  Drive E now suddenly becomes a new I/O hotspot on the server.
You can check the space used with the following query.
USE AdventureWorks2012;
GO
SELECT
     name AS ‘LogicalName’
    ,physical_name AS ‘PhysicalName’
    ,CONVERT(INT,ROUND(size/128,0)) AS ‘Size (MB)’
    ,CONVERT(INT,ROUND(FILEPROPERTY(name,‘SpaceUsed’)/128,0)) AS ‘SpaceUsed (MB)’
FROM sys.database_files
WHERE type = 0;
GO

To avoid this disk hotspot issue, we need to have the data more evenly balanced across both files in the filegroup in terms of data page allocations.  The quickest way to do this is to rebuild all of the clustered indexes within the database.
ALTER INDEX [PK_AWBuildVersion_SystemInformationID] ON [dbo].[AWBuildVersion] REBUILD;
ALTER INDEX [PK_ErrorLog_ErrorLogID] ON[dbo].[ErrorLog] REBUILD;
ALTER INDEX [PK_Department_DepartmentID] ON[HumanResources].[Department] REBUILD;
:
:
ALTER INDEX [PK_Store_BusinessEntityID] ON[Sales].[Store] REBUILD;
GO

SQL Server will do its best to automatically rebalance all of the page allocations across all files within the same filegroup.  In our case, both data files are still part of the PRIMARY filegroup. 
Check the space used again with the following query.
USE AdventureWorks2012;
GO
SELECT
     name AS ‘LogicalName’
    ,physical_name AS ‘PhysicalName’
    ,CONVERT(INT,ROUND(size/128,0)) AS ‘Size (MB)’
    ,CONVERT(INT,ROUND(FILEPROPERTY(name,‘SpaceUsed’)/128,0)) AS ‘SpaceUsed (MB)’
FROM sys.database_files
WHERE type = 0;
GO

Now what we have is much more evenly balanced allocation across both data files.  This will allow SQL Server to even distribute the write I/O across both disk drives.
By doing this one index maintenance step after adding a new file, you’ll help prevent a write hotspot on one of your disks and help SQL Server improve its I/O performance.  But keep in mind that proportional fill only affects all files in the same filegroup.  If we had added the second file to a new filegroup, then we would have to manually move tables to the new filegroup.
For more info on files and filegroups, check out BooksOnline.
Mar 052013
 

Just for the record, this happens to be one of my favorite interview questions to ask candidates.

At some point in time, there will be a database containing tables without clustered indexes (a heap) that you will be responsible for maintaining.  I personally believe that every table should have a clustered index, but sometimes my advice is not always followed.  Additionally there can be databases from a 3rd party vendor that have this same design.  Depending on the what those heap tables are used for, over time it’s possible they can become highly fragmented and degrade query performance.  A fragmented heap is just as bad as a fragmented index.  To resolve this issue, I’d like to cover four ways we can defragment a heap. 
To start with, we will need a sample database with a highly fragmented heap table.  You can download the FRAG database (SQL2012) from here.  Let’s use the sys.dm_db_index_physical_stats DMV to check the fragmentation level.
USE FRAG;
GO
SELECT
     index_id
    ,index_type_desc
    ,index_depth
    ,index_level
    ,avg_fragmentation_in_percent
    ,fragment_count
    ,page_count
    ,record_count
FROM sys.dm_db_index_physical_stats(
     DB_ID(‘FRAG’)
    ,OBJECT_ID(‘MyTable’)
    ,NULL
    ,NULL
    ,‘DETAILED’);
GO

As you can see, the heap is 93% fragmented, and both non-clustered indexes are 99% fragmented.  So now we know what we’re dealing with. 
Repair options in order of my preference:
  1. ALTER TABLE…REBUILD (SQL 2008+).
  2. CREATE CLUSTERED INDEX, then DROP INDEX.
  3. CREATE TABLE temp, INSERT INTO temp, DROP original table, sp_rename temp to original, recreate the non-clustered indexes.
  4. BCP out all data, drop the table, recreate the table, bcp data in, recreate the non-clustered indexes.

Option 1 is the easiest and the most optimal way to remove heap fragmentation; however, this option was only introduced in SQL Server 2008, so it’s not available for all versions.  This is a single command that will rebuild the table and any associated indexes; yes, even clustered indexes.  Keep in mind, this command will rebuild the heap as well as all of the non-clustered indexes.
ALTER TABLE dbo.MyTable REBUILD;
GO

Option 2 is almost as quick, but involves a little bit of planning.  You will need to select a column to create the clustered index on, keeping in mind this will reorder the entire table by that key.  Once the clustered index has been created, immediately drop it.
CREATE CLUSTERED INDEX cluIdx1 ON dbo.MyTable(col1);
GO
DROP INDEX cluIdx1 ON dbo.MYTable;
GO
Option 3 requires manually moving all data to a new temporary table.  This option is an offline operation and should be done during off-hours.  First you will need to create a new temporary table with the same structure as the heap, and then copy all rows to the new temporary table.

CREATE TABLE dbo.MyTable_Temp(col1 INT,col2 INT);
GO
INSERT dbo.MyTable_Temp
SELECT * FROM dbo.MyTable;
GO
Next, drop the old table, rename the temporary table to the original name, and then create the original non-clustered indexes.
DROP TABLE dbo.MyTable;
GO
EXEC sp_rename ‘MyTable_Temp’,‘MyTable’;
GO
CREATE NONCLUSTERED INDEX idx1 ON dbo.MyTable(col1);
GO
CREATE NONCLUSTERED INDEX idx2 ON dbo.MyTable(col2);
GO
Option 4 is by far to the least efficient way to complete this task.  Just like option 3, this option is an offline operation and should be done during off-hours.  First we need to use the BCP utility to bulk copy out all of the data to a data file.  Using BCP will require a format file to define the structure of what we’re bulk copying.  In this example, I am using an XML format file.  More information on format files can be found here.
BCP FRAG.dbo.MyTable OUT D:MyTable.dat -T -STRONTEST1 -fD:MyTableFormat.xml
Once that is complete, we need to drop and recreate the table.

DROP TABLE dbo.MyTable;
GO
CREATE TABLE dbo.MyTable(col1 INT,col2 INT);
GO
Next, we need to use the BCP utility to bulk copy all of the data back into the table.

BCP FRAG.dbo.MyTable IN D:MyTable.dat -T -STRONTEST1 -fD:MyTableFormat.xml
Finally, we can create the original non-clustered indexes.

CREATE NONCLUSTERED INDEX idx1 ON dbo.MyTable(col1);
GO
CREATE NONCLUSTERED INDEX idx2 ON dbo.MyTable(col2);
GO
Options 1 and 2 do not require any downtime for the table; however, they will cause blocking during the rebuild stage.  You can use the WITH ONLINE option but that will require enough free space in tempdb for the entire table.  Both options 3 and 4 will require downtime and will potentially impact any foreign key constraints or other dependent objects.  If you’re running SQL Server 2008 or higher, I highly recommend using option 1.
As you’ve seen, there are multiple ways of dealing with heap fragmentation.  However, the best way is to avoid heaps altogether in your database design.