Tuesday, 25 June 2013

Configure SSIS to SQL Server 2012 Cluster


SQL Server Integration services are not cluster aware by default, the following article shows a documented steps by Microsoft on how to configure SSIS to Cluster.

Install SSIS on all nodes of a cluster, If SSIS is installed after the SQL DB Cluster is built you need to select Stand-alone installation route(As shown below) as you cannot add SSIS to an existing SQL cluster during installation.


Open Failover Cluster Manager, connect to the cluster role to which SSIS need to be added as resource(usually SQL Server Role), Right Add Resource->Generic Service

 
Select SQL Server Integration Services and Click Next

Click Next


Click Finish, Now you should see SSIS added to as a resource of the Cluster Role.

Now Right Click on SQL Server Integration Services 11.0 Resource and select properties, In the Properties box go to Dependencies Tab and add SQL Server Resource and a disk resource where SSIS config file will be placed (We will look about config file in the next step)

 
Go to C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ copy MsDtsSrvr.ini.xml to the drive(Drive must be a part of SQL Server cluster role) and folder you have selected, I have chosen T:\SSIS.
 
Open this file in notepad modify <ServerName>.</ServerName> to <ServerName>SQLServer1\TEST2012</ServerName> with the SQL Server instance name in the cluster and <StorePath>..\Packages</StorePath> to some UNC path on the clustered drive if you store packages to file system and save the file. Sample file below
 
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>SQLServer1\TEST2012</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>T:\SSIS\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>
 
Go to Registry and Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile to the UNC path and file name of the service configuration file on the select clustered disk. In my case T:\SSIS\MsDtsSrvr.ini.xml
 
 
Once the registry is updated SSIS has been successfully updated to cluster. Try starting SSIS resource in the Cluster and try failing over to other nodes on the cluster.
 


 

Thursday, 13 June 2013

PowerShell to Script SQL Database Object and ReApply on a different Server


This week I had a requirement to move SQL 2012 database to a SQL server 2008 R2 server and the two servers reside in a different domain with no trust. Since SQL 2012 backups are not compatible with lower version servers the only way to move the databases was to script database with and apply the script on the destination, doing this manually took a lot of time so I decided to automate this process in PowerShell.
I took MSDN blog http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-database-objects.aspx as reference for scripting a database object using SMO in PowerShell and created my script the database using EnumScript() function instead of Script() function to accomplish my requirement. 

1.       ScriptDB.ps1 will script all the objects with data and will save it in a folder with ServerName->DBname. I have base path to save the script file is U:\, If you want to save It another location just change the following variable in the first line of the script $script:basepath = "U:\" to wherever you like or you can get the value as an argument.  The Script will create a folder in< ServerName>-><Dbname> and sub-folders Tables,Views,SP and Functions in the <dbname> Folder. 

2.       ReApply.ps1 will reapply the scripts saved in step one on the destination server. You have to pass the base folder path as Argument.

Script Database (ScriptDB.Ps1) -
# Script to Script a Database
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 12/06/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-d" to specify the database name
# Example1:- ScriptDB.ps1 -s sqlservername -d dbname
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb

Clear-Host
$script:basepath = "U:\"
<#*************************************************START:Main Program***************************************************************#>
<#Command Line Argument Verification#>
if($args.Length -ne 4)
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name and use -d to specify the database name" -ForegroundColor Red
$uParameterHelp = "
Help:-
******

# Parameter 1 :- -s to specify the SQL Server Name
# Parameter 2 :- -d to specify the database name
# Example1:- ScriptDB.ps1 -s sqlservername -d dbname
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-d") -or ($args[2] -eq "-D")))
{
$SQLInstance = $args[1]
$SQLSplit = $SQLInstance.Split("\")
$SQLcompname = $SQLSplit[0]
if($SQLSplit[1] -ne $null)
{
$instancename = $SQLSplit[1]
$sqlfolder = $SQLcompname+"_"+$instancename 
}else{$sqlfolder = $SQLcompname}

$dbname = $args[3]

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$SQLInstance"
$dbobj = $srv.databases[$dbname]

#$scriptdb = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
#$scriptdb.Options.AppendToFile = $True
#$scriptdb.Options.ToFileOnly = $True
$dbfolder = $basepath+$sqlfolder+"\"+$dbname
write-host $dbfolder
if((Test-Path -Path $dbfolder) -eq $false)
{
 New-Item -Path $dbfolder -ItemType Directory -ErrorAction Stop|out-null
}
#$scriptdb.Options.FileName = "$dbfolder\dbcreate.sql"
#$scriptdb.Script($dbobj)

$Objects = $null
$Objtbl = $dbobj.Tables | where {!($_.IsSystemObject)}
$Objviews = $dbobj.Views | where {!($_.IsSystemObject)}
$Objsp = $dbobj.StoredProcedures | where {!($_.IsSystemObject)}
$Objfn = $dbobj.UserDefinedFunctions | where {!($_.IsSystemObject)}
If($Objtbl -ne $null)
{
$Objects += $Objtbl
$tblfolder=$dbfolder+"\Tables"
 if((Test-Path -Path $tblfolder) -eq $false)
 {
  New-Item -Path $tblfolder -ItemType Directory -ErrorAction Stop|Out-Null
 }
}
if($Objviews -ne $null)
{
$Objects += $Objviews
$vwfolder=$dbfolder+"\Views"
 if ((Test-Path -Path $vwfolder) -eq $false)
 {
  New-Item -Path $vwfolder -ItemType Directory -ErrorAction Stop|Out-Null
 }
}
If($Objsp -ne $null)
{
$Objects += $Objsp
$spfolder=$dbfolder+"\SP"
 if ((Test-Path -Path $spfolder) -eq $false)
 {
  New-Item -Path $spfolder -ItemType Directory -ErrorAction Stop|Out-Null
 }
}
if($Objfn -ne $null)
{
$Objects += $Objfn
$fnfolder=$dbfolder+"\Functions"
 if ((Test-Path -Path $fnfolder) -eq $false)
 {
  New-Item -Path $fnfolder -ItemType Directory -ErrorAction Stop|Out-Null
 }
}

foreach ($ScriptThis in $Objects) 
 {
  $objname = $ScriptThis.name
  write-host $objname
  #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name 
  $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
  $scriptr.Options.AppendToFile = $True
  $scriptr.Options.AllowSystemObjects = $False
  $scriptr.Options.ClusteredIndexes = $True
  $scriptr.Options.DriAll = $True
  $scriptr.Options.ScriptDrops = $False
  $scriptr.Options.IncludeHeaders = $True
  $scriptr.Options.ToFileOnly = $True
  $scriptr.Options.Indexes = $True
  $scriptr.Options.Permissions = $True
  $scriptr.Options.WithDependencies = $False
  $scriptr.Options.ScriptData = $true
  $TypeFolder=$ScriptThis.GetType().Name
  switch($TypeFolder)
  {
  "Table"{$scriptr.Options.FileName = "$tblfolder\$objname.sql"}
  "View"{$scriptr.Options.FileName = "$vwfolder\$objname.sql"}
  "StoredProcedure"{$scriptr.Options.FileName = "$spfolder\$objname.sql"}
  "UserDefinedFunction"{$scriptr.Options.FileName = "$fnfolder\$objname.sql"}
  }
  #$scriptr.Script($ScriptThis)
  $Scriptr.EnumScript($ScriptThis) 
 }
}
else
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name and use -d to specify the database name" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter 2 :- -d to specify the database name
# Example1:- ScriptDB.ps1 -s sqlservername -d dbname
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb"
Write-Host $uParameterHelp -ForegroundColor Blue
}
ReApply Database (ReApply.Ps1) -
# Script to Reapply scripted database
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 12/06/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-d" to specify the database name
# Parameter 3 :- "-p" to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s SQLServerName -d dbname -p Folder Path
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb -P U:\SQLServer1
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb -P U:\SQLServer1

Clear-Host

<#*************************************************START:Main Program***************************************************************#>
<#Command Line Argument Verification#>
if($args.Length -ne 6)
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name;use -d to specify the database name and -p to specify folder path of the script files" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter 2 :- -d to specify the database name
# Parameter 3 :- -p to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s SQLServerName -d dbname -p Folder Path
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb -P U:\SQLServer1
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb -P U:\SQLServer1"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-d") -or ($args[2] -eq "-D")) -and (($args[4] -eq "-p") -or ($args[4] -eq "-P")))
{
$SQLInstance = $args[1]
$dbname = $args[3]
$basepath = $args[5]
Write-Host "ServerName :- "$SQLInstance
Write-Host "DatabaseName :- "$dbname
 if ((Test-Path $basepath) -eq $false)
 {
   Write-host -ForegroundColor Red "Script folder specified in the Argument cannot be found"
   EXIT;
 }Else
 {
  $tblfolder = $basepath+"\Tables"
  $vwfolder = $basepath+"\Views"
  $spfolder = $basepath+"\SP"
  $fnfolder = $basepath+"\Functions"
   if(Test-Path $tblfolder)
   {
    Write-Host "Creating Tables....."
   foreach($tblfile in Get-ChildItem $tblfolder)
   {
    $tblscriptfullpath = $tblfolder+"\"+$tblfile
    Write-Host "Executing SQLScript " $tblfile 
    Try{Invoke-Sqlcmd -InputFile $tblscriptfullpath -ServerInstance $SQLInstance -Database $dbname -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $tblfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
      }
   }
   }
     if(Test-Path $vwfolder)
   {
    Write-Host "Creating Views....."
   foreach($vwfile in Get-ChildItem $vwfolder)
   {
    $vwscriptfullpath = $vwfolder+"\"+$vwfile
    Write-Host "Executing SQLScript " $vwfile 
    Try{Invoke-Sqlcmd -InputFile $vwscriptfullpath -ServerInstance $SQLInstance -Database $dbname -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $vwfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
      }
   }
   }
  if(Test-Path $spfolder)
   {
    Write-Host "Creating Store Procedures....."
   foreach($spfile in Get-ChildItem $spfolder)
   {
    $spscriptfullpath = $spfolder+"\"+$spfile
    Write-Host "Executing SQLScript " $spfile 
    Try{Invoke-Sqlcmd -InputFile $spscriptfullpath -ServerInstance $SQLInstance -Database $dbname -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $spfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
      }
   }
   }
  if(Test-Path $fnfolder)
   {
    Write-Host "Creating Store Procedures....."
   foreach($fnfile in Get-ChildItem $fnfolder)
   {
    $fnscriptfullpath = $fnfolder+"\"+$fnfile
    Write-Host "Executing SQLScript " $fnfile 
    Try{Invoke-Sqlcmd -InputFile $fnscriptfullpath -ServerInstance $SQLInstance -Database $dbname -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $fnfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
      }
   }
   }
  
  
 }

}
else
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name;use -d to specify the database name and -p to specify folder path of the script files" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter 2 :- -d to specify the database name
# Parameter 3 :- -p to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s SQLServerName -d dbname -p Folder Path
# Example2:- ScriptDB.ps1 -s MyTestServer -d Testdb -P U:\SQLServer1
# Example3:- ScriptDB.ps1 -s MyTestServer\Instance1 -d Testdb -P U:\SQLServer1"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#Clean-up Variables#>
$SQLInstance = $null
$dbname = $null
$basepath = $null
$tblfolder = $null
$vwfolder = $null
$spfolder = $null
$fnfolder = $null
Remove-Variable -name tblfile
Remove-Variable -name vwfile
Remove-Variable -name spfile
Remove-Variable -name fnfile
$tblscriptfullpath = $null
$vwscriptfullpath = $null
$spscriptfullpath = $null
$fnscriptfullpath = $null
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Thursday, 6 June 2013

Create Availability Group in SQL Server 2012 Step by Step

The following are the steps needs to create an Always on Availability Group.

The SQL Instance used for this exercise are SQL Server1 and SQLServer2.
I going to use 3 user databases (ContainedDB, ReportServer and AGTest) in my SQLServer1 for creating Availability group



1.       Take a Full Backup followed by T-Log backup of all the databases on the primary server (SQLServer1) which needs to be included to availability group.

BACKUP DATABASE [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_full.bak' WITH INIT,STATS = 1
GO
BACKUP DATABASE [ReportServer] TO DISK = 'T:\Backup\ReportServer_full.bak' WITH INIT,STATS = 1
GO
BACKUP DATABASE [AGTest] TO DISK = 'T:\Backup\AGTest_full.bak' WITH INIT,STATS = 1
GO

BACKUP LOG [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_trn.bak' WITH INIT,STATS = 1
GO
BACKUP LOG [ReportServer] TO DISK = 'T:\Backup\ReportServer_trn.bak' WITH INIT,STATS = 1
GO
BACKUP LOG [AGTest] TO DISK = 'T:\EREISQLUS01_TEST2012\Backup\AGTest_trn.bak' WITH INIT,STATS = 1
GO
 
2.       Restore the Full backup and Log backup taken in step1 in NORECOVERY mode on the secondary server(SQLServer2), You can ignore Step 1 and 2 and choose Availability group wizard to take the backup and restore but I find this way quicker and efficient.

RESTORE DATABASE [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_full.bak'
WITH MOVE 'ContainedDB' TO 'G:\Data\ContainedDB.mdf',
MOVE 'ContainedDB_log' TO 'P:\TLog\ContainedDB_log.ldf',NORECOVERY,STATS = 1
GO
RESTORE LOG [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_trn.bak' WITH NORECOVERY,STATS = 1
GO

RESTORE DATABASE [AGTest] FROM DISK = 'H:\Backup\AGTest_full.bak'
WITH MOVE 'AGTest' TO 'G:\Data\AGTest.mdf',
MOVE 'AGTest_log' TO 'P:\TLog\AGTest_log.ldf',NORECOVERY,STATS = 1
GO

RESTORE LOG [AGTest] FROM DISK = 'H:\Backup\AGTest_trn.bak' WITH NORECOVERY,STATS = 1
GO

RESTORE DATABASE [ReportServer] FROM DISK = 'H:\Backup\ReportServer_full.bak'
WITH MOVE 'ReportServer' TO 'G:\Data\ReportServer.mdf',
MOVE 'ReportServer_log' TO 'P:\TLog\ReportServer_log.ldf',NORECOVERY,STATS = 1
GO

RESTORE LOG [ReportServer] FROM DISK = 'H:\Backup\ReportServer_trn.bak' WITH NORECOVERY,STATS = 1
 GO


3.   Connect  to SSMS of SQLServer1 expand AlwaysOn High Availability-> Right Click on the folder ->select New Availability Group Wizard

4.  Give a name to the Availability Group you want to create and Click Next, 



5.  Select the databases which need to be a part of Availability group and click next


6. Click Add Replica and connect to the secondary server, repeat the step if you want to add more than one replica.



7. I always keep the secondary databases read-only, select appropriately in Readable secondary column.

8. Select the Backup preferences menu on the page and select the appropriate backup preference, I leave it as default(Prefer Secondary)

9. Listeners can be created after the AG is set up.

10. Since we have restored the databases in secondary earlier I select Join option.

11. Click Next

12. Click Next


Availability group has been created now; let’s go back to the server to check it,
 

As shown in the picture above Availability group AGTest can be seen in both Primary and secondary server with corresponding Replica Details.
 
Now let’s see how to create a listener for the Availability group, Go to AlwaysOn High Availability -> AGtest->Availability Group Listeners; Right Click on the folder and select Add Listener.
 

Now fill the port number and Listener group Name(Note:- This Listener Group name and Port number are the details which will be used to connect to SQL Server by the users)

 




I usually chose Static IP Network mode to avoid any firewall and network issues, you can use DHCP if you like.
 
Click OK

Now that the Listener AGTestInstance has been created let’s see how users need to connect to AGTest.
 
In SSMS connect to the <ListenerName,Port Number> as the server name and connect to the AG.


 


Monday, 3 June 2013

PowerShell Script to Assign Static TCP Port to SQL Server Instance


I am designing a new SQL server Farm with more than 100 Instances for one of my clients and it would have been huge effort if I had to assign static TCP to each and every instance manually, but Thanks to Allen Whites Blog on PowerShell Managed Computer Objects in SMO http://sqlblog.com/blogs/allen_white/default.aspx ,This namespace pretty much gives us ability to do all the tasks which are done through Configuration Manager using PowerShell. I have written a wrapper over Allen Whites core script to assign Static port to SQL instances.
 
# Script to Set Static TCP port number for a SQL Server Instance
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 16/05/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-p" to TCP Port Number to be Set
# Example1:- SMOPort.ps1 -s SQLServerName-p tcpport
# Example2:- SMOPort.ps1 -s MyTestServer -p 1433
# Example3:- SMOPort.ps1 -s MyTestServer\Instance1 -p 1433

#Reference http://sqlblog.com/blogs/allen_white/default.aspx

Clear-Host
function IsNumeric { 
 
<#Reference http://gallery.technet.microsoft.com/scriptcenter/IsNumeric-c50ecf05    
.SYNOPSIS    
    Analyse whether input value is numeric or not 
   
.DESCRIPTION    
    Allows the administrator or programmer to analyse if the value is numeric value or  
    not. 
     
    By default, the return result value will be in 1 or 0. The binary of 1 means on and  
    0 means off is used as a straightforward implementation in electronic circuitry  
    using logic gates. Therefore, I have kept it this way. But this IsNumeric cmdlet  
    will return True or False boolean when user specified to return in boolean value  
    using the -Boolean parameter. 
 
.PARAMETER Value 
     
    Specify a value 
 
.PARAMETER Boolean 
     
    Specify to return result value using True or False 
 
.EXAMPLE 
    Get-ChildItem C:\Windows\Logs | where { $_.GetType().Name -eq "FileInfo" } | Select -ExpandProperty Name | IsNumeric -Verbose 
    DirectX.log 
    VERBOSE: False 
    0 
    IE9_NR_Setup.log 
    VERBOSE: False 
    0 
 
    The default return value is 0 when we attempt to get the files name through the  
    pipeline. You can see the Verbose output stating False when you specified the  
    -Verbose parameter 
 
.EXAMPLE 
    Get-ChildItem C:\Windows\Logs | where { $_.GetType().Name -eq "FileInfo" } | Select -ExpandProperty Length | IsNumeric -Verbose 
    119155 
    VERBOSE: True 
    1 
    2740 
    VERBOSE: True 
    1 
     
    The default return value is 1 when we attempt to get the files length through the  
    pipeline. You can see the Verbose output stating False when you specified the  
    -Verbose parameter 
         
.EXAMPLE 
    $IsThisNumbers? = ("1234567890" | IsNumeric -Boolean) ; $IsThisNumbers? 
    True 
     
    The return value is True for the input value 1234567890 because we specified the  
    -Boolean parameter 
     
.EXAMPLE     
    $IsThisNumbers? = ("ABCDEFGHIJ" | IsNumeric -Boolean) ; $IsThisNumbers? 
    False 
 
    The return value is False for the input value ABCDEFGHIJ because we specified the  
    -Boolean parameter 
 
.NOTES    
    Author  : Ryen Kia Zhi Tang 
    Date    : 20/07/2012 
    Blog    : ryentang.wordpress.com 
    Version : 1.0 
     
#> 
 
[CmdletBinding( 
    SupportsShouldProcess=$True, 
    ConfirmImpact='High')] 
 
param ( 
 
[Parameter( 
    Mandatory=$True, 
    ValueFromPipeline=$True, 
    ValueFromPipelineByPropertyName=$True)] 
     
    $Value, 
     
[Parameter( 
    Mandatory=$False, 
    ValueFromPipeline=$True, 
    ValueFromPipelineByPropertyName=$True)] 
    [alias('B')] 
    [Switch] $Boolean 
     
) 
     
BEGIN { 
 
    #clear variable 
    $IsNumeric = 0 
 
} 
 
PROCESS { 
 
    #verify input value is numeric data type 
    try { 0 + $Value | Out-Null 
    $IsNumeric = 1 }catch{ $IsNumeric = 0 } 
 
    if($IsNumeric){  
        $IsNumeric = 1 
        if($Boolean) { $Isnumeric = $True } 
    }else{  
        $IsNumeric = 0 
        if($Boolean) { $IsNumeric = $False } 
    } 
     
    if($PSBoundParameters['Verbose'] -and $IsNumeric) {  
    Write-Verbose "True" }else{ Write-Verbose "False" } 
     
    
    return $IsNumeric 
} 
 
END {} 
 
} #end of #function IsNumeric





<#*************************************************START:Main Program***************************************************************#>
<#Command Line Argument Verification#>
if($args.Length -ne 4)
{
Write-Host "Incorrect Paramenter Count use -c to specify the User Input File and use -a to specify the Action" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
 # Parameter 1 :- '-s' to specify the SQL Server Name
 # Parameter 2 :- '-p' to TCP Port Number to be Set
 # Example1:- SMOPort.ps1 -s SQLServerName-p tcpport
 # Example2:- SMOPort.ps1 -s MyTestServer -p 1433
 # Example3:- SMOPort.ps1 -s MyTestServer\Instance1 -p 1433"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-p") -or ($args[2] -eq "-P")))
{
$computer = $args[1]
$Error = 0
#Get the SQL and Instance name
$SQLInstance = $computer.Split("\")
$SQlcompname = $SQLInstance[0]
 if($SQLInstance[1] -eq $null)
 {
  $instname = "MSSQLSERVER"
  $SQLServicename = "MSSQLSERVER"
  $AgentServiceName = "SQLSERVERAGENT"
 }else
  {$instname = $SQLInstance[1];
   $SQLServicename = "MSSQL$"+$instname
   $AgentServiceName = "SQLAgent$"+$instname 
  }

$portnumber = $args[3]
#Check if Port number passed is Numeric 
 if (($portnumber | IsNumeric -Boolean))
 {
   Try
   {
   # Load the assemblies
   [system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null
   [system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")|Out-Null
   $mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $SQlcompname
   $i=$mc.ServerInstances[$instname]
   $p=$i.ServerProtocols['Tcp']
   $ip=$p.IPAddresses['IPAll']
   $ip.IPAddressProperties['TcpDynamicPorts'].Value = ''
   $ipa=$ip.IPAddressProperties['TcpPort']
   $ipa.Value = [string]$portnumber
   $p.Alter()
   #$ip.IPAddressProperties['TcpDynamicPorts'].Value = '1099'
   #$p.Alter()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR[Assign IP]:"$_.Exception.Message
    $Error = 1
   }
   Finally
   {
     if($Error -eq 1)
     {
      Write-host -ForegroundColor Red "ERROR[Assign IP]:FAILED!!!!"
      EXIT;
     }
     else
     {
      Write-host -ForegroundColor DarkGreen "[Assign IP]:SUCCESS-SQL Server TCP Port reconfigured to $portnumber, Restart the SQL Services for the Prot to be reconfigured!!!"
     }
   }
 }
 Else
 {
  Write-Host "ERROR : Incorrect Port Number, Port number for argument '-p' should be Numeric!" -ForegroundColor Red
  $uParameterHelp = "
Help:-
******
 # Parameter 1 :- '-s' to specify the SQL Server Name
 # Parameter 2 :- '-p' to TCP Port Number to be Set
 # Example1:- SMOPort.ps1 -s SQLServerName-p tcpport
 # Example2:- SMOPort.ps1 -s MyTestServer -p 1433
 # Example3:- SMOPort.ps1 -s MyTestServer\Instance1 -p 1433"
 Write-Host $uParameterHelp -ForegroundColor Blue
 }
}
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

TSQL to Attach a database with Rebuild New T-Log option


DBA’s often need to attach a database from SQL data file most of the cases provided by Vendor or during DR where T-Log does not have any significance, So we may well create a new log file instead of copying the log file over the network.

The TSQL used to attach a database with rebuild log option is given below,

CREATE DATABASE <dbname> ON (FILENAME = ‘<FilePath>) FOR ATTACH_REBUILD_LOG

Eg:-
CREATE DATABASE Testdb ON (Filename = ‘D:\Data\Testdb_data.mdf) FOR ATTACH_REBUILD_LOG