TFS - Building Database Project - PART I

Visual Studio 2012

Building a Database Project with TFS

I have built DBA data warehouse with TFS Database Project. I documented steps that I need to follow to build a database using TFS database Project. I created this post to share my knowledge with others.

Check and verify that VS 2012 has SQL Server template installed. 

If not, please install SSDTSetup.exe

1. Open VS2012 Studio and Click on New Project

2. Choose SQL server and give database name that you want to create. Check Add to source control

3. A new project is created in Solution explorer and it is added in source control

4. Set Target server by clicking on Properties of solution.

5. Currently, Target platform is SQL server 2014. So, change it to SQL server 2012

6. Save

7. Now, right click on solution and create a folder

8. Name a folder Scripts

9. Next, add a table under Scripts folder

10. Name a table and Click on Add

11. Build a table using a script or GUI

12. Save .  A new table is added to Scripts folder under the solution.

13. Next, Click on Check in at Solution Level

14. Save the project

15. Write in comment and click on Check In

16. Click Yes

17. Project is check in successfully

18. In Solution Explorer, A locked is appeared beside a solution name after a solution is checked In.

19. Click on Build and then Build a Solution

 20. Upon Build is successful, Click on Publish a solution

 21.       Click on Edit

22. Type Server Name in Server name

23. As a result, Target database connection is filled.

24. Click on Save Profile As 

 25. Save the profile where the solution is located

 26. Save the project

 27.       Check In the solution again.

 28. Write a comment in Check In

29. Click Build and then click on publish solution. Edit Target database connection to the server where database will be created. Then click on Publish

 30. It is successfully published.

31. A database with a table is created. Check in Management Studio.

PowerShell - Get Maintenance Plan of SQL servers

Some of us know SQL server maintenance plan is encrypted in xml file. I believe you can find a lot resources how to query SQL server maintenance plan. The hardest part of the query is I need to know how to unwrap xml and retrieve information.
I found very useful sites http://sqlchad.com/?p=339 and http://www.sqlballs.com/2013/01/how-do-you-query-maintenance-plan.html. They gave me what I am really looking for. I enhanced the script from the site and combine with powershell script to retrieve full and tlog backup maintenance plan from all SQL servers which includes maintenance clean up task. The script works in 2005/2008R2 and 2012.

Enjoy Scripting...

$($ServerList = 'E:\PowerShell\SQLServerList\SQLSvrList.txt'
$exportcsv ='E:\PowerShell\GetMaintenancePlan\BackupInfo.csv'
Clear-Content $exportcsv
$servername = Get-Content $ServerList
$errorlog = 'E:\PowerShell\GetMaintenancePlan\Error.txt'
$RunTime = $(get-date -f MM-dd-yyyy_HH_mm_ss)
if(!(test-path $ServerList))
{
    Write-Error "SQLSvr.txt is not Found" -ea Stop
}
if(test-path $ServerList)
{
foreach($server in $servername){
Try{
$sqlconn = new-object System.Data.SqlClient.SqlConnection("server=$server;Trusted_Connection=true");
$query = "

IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0,CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0)) = 9
BEGIN

          WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
          , 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
          ,ssis AS (
              SELECT name
                  , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
              FROM [msdb].[dbo].[sysdtspackages90]
              WHERE packagetype = 6

             )
          SELECT '$server' as ServerName,s.name as MaintenancePlanName,
               CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
                    WHEN 0 THEN
                         CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
                              WHEN 1 THEN 'DIFFERENTIAL'
                              WHEN 0 THEN 'FULL'
                              ELSE 'Maintenance clean Up'
                         END
                    WHEN 1 THEN 'FILES'
                    WHEN 2 THEN 'LOG'
                    ELSE 'UNKNOWN'
               END as BackupType,
               CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupCompressionAction)[1]', 'int')
                    WHEN 0 THEN 'SERVER DEFAULT CONFIG'
                    WHEN 1 THEN 'YES'
                    WHEN 2 THEN 'NO'
               END as Compressed,
               c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'VARCHAR(MAX)') as BackupLocation
          FROM ssis s
              CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
          WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1
       

END


ELSE IF SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0,CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),0)) >= 10

BEGIN

          WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS
          , 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
          ,ssis AS (
              SELECT name
                  , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package
              FROM [msdb].[dbo].[sysssispackages]
              WHERE packagetype = 6

             )
          SELECT '$server' as ServerName,s.name as MaintenancePlanName,
             CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupAction)[1]', 'INT')
                     WHEN 0 THEN
                         CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupIsIncremental)[1]', 'bit')
                              WHEN 1 THEN 'DIFFERENTIAL'
                              WHEN 0 THEN 'FULL'
                              ELSE 'Maintenance clean Up'
                         END
                    WHEN 1 THEN 'FILES'
                    WHEN 2 THEN 'LOG'
                    ELSE 'Maintenance clean Up'
               END as BackupType,
  COALESCE(c.value('(SQLTask:SqlTaskData/@SQLTask:BackupFileExtension)[1]','varchar(1000)'),
  c.value('(SQLTask:SqlTaskData/@SQLTask:FileExtension)[1]','varchar(1000)')) as Extension,
           COALESCE(c.value('(SQLTask:SqlTaskData/@SQLTask:BackupFileExtension)[1]','varchar(1000)'),
  c.value('(SQLTask:SqlTaskData/@SQLTask:FileExtension)[1]','varchar(1000)'))+'$server' as PK,

'DELETE backups older than ' + c.value('(SQLTask:SqlTaskData/@SQLTask:RemoveOlderThan)[1]','VARCHAR(3)') +
CASE c.value('(SQLTask:SqlTaskData/@SQLTask:TimeUnitsType)[1]','VARCHAR(10)') --0=Daily,1=Weekly,2=Monthly,3=Yearly,5=Hourly
WHEN 0 THEN ' Day(s)'
WHEN 1 THEN ' Week(s)'
WHEN 2 THEN ' Month(s)'
WHEN 3 THEN ' Year(s)'
WHEN 4 THEN ' Minute(s)'
WHEN 5 THEN ' Hour(s)'
END AS Del_Freqency,
       CASE c.value('(SQLTask:SqlTaskData/@SQLTask:BackupCompressionAction)[1]', 'int')
                    WHEN 0 THEN 'SERVER DEFAULT CONFIG'
                    WHEN 1 THEN 'YES'
                    WHEN 2 THEN 'NO'
               END as Compressed,
  c.value('(SQLTask:SqlTaskData/@SQLTask:BackupVerifyIntegrity)[1]', 'BIT') as BackupVerified,
               c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'VARCHAR(MAX)') as BackupLocation



          FROM ssis s
              CROSS APPLY package.nodes('//DTS:ObjectData') t(c)
 where s.name in('Full BackUp','Transaction Logs')
END

"
$sqlconn.Open()
$sqlcmd = new-object System.Data.SqlClient.SqlCommand ($query, $sqlconn);
$sqlcmd.CommandTimeout = 0;
#$dr = $sqlcmd.ExecuteReader();
$dr = New-Object System.Data.SqlClient.SqlDataAdapter
$dr.SelectCommand = $sqlcmd
$dt = New-Object System.Data.DataTable
$dr.fill($dt) | out-null
$dt
}
Catch
{    Clear-Content $errorlog
    "Fail to get information $server +' ' + $RunTime :$_" |Out-File $errorlog -Append
}
}
})|Export-csv $exportcsv -noType
Get-Job | Wait-Job | Out-Null
Remove-Job -State Completed


Thank you.

Powershell - Get Servers Information

The original script came from MSSQLTIPS.COM

http://www.mssqltips.com/sqlservertip/3045/script-to-get-cpu-and-cores-for-sql-server-2012-licensing/#comments

I enhanced the script from MSSQLTIPS and added additonal information such as SQL server maxmemory, Ip address and etc...There are two script files. SQLServerInfo.ps1 contains a function Get-ServerInfo which retrieves information from all the servers. SQLServerInfo_Call.ps1 is to call SQLServerInfo.ps1 script.
You saved two script files in E:\ or C:\ as powershell files. You just need to change the path of the $errorlog,$exportcvs variables if you are putting your files in different drives.

How to run the script. - Open SQLServerInfo_Call and Click Run.


Enjoy scripting..

SQLServerInfo.ps1

param([string]$SQLServerList=$(Throw
"Paramater missing: -SQLServerList ConfigGroup"))

$errorlog = 'E:\PowerShell\SQLServersInfo\Error.txt'
$RunTime = $(get-date -f MM-dd-yyyy_HH_mm_ss)

Function Get-ServerInfo{
    [CmdletBinding()]
    Param(
    [parameter(Mandatory = $TRUE,ValueFromPipeline = $TRUE)]   [String] $ComputerName
    # I have defined an input parameter - computer name, This can accept input from the pipleline.
    #This means you can call this function in two distinct ways
    #Get-Content names.txt | Get-ServerInfo
    #OR
    #Get-ServerInfo –computername SERVER1,SERVER2
    )

 
 
    Process{
 
                Try{

         
            $sqlconn = new-object System.Data.SqlClient.SqlConnection(`
            "server=$ComputerName;Trusted_Connection=true");
                     
            #SQL server Edition
            $query = "select SERVERPROPERTY('ProductVersion') As ProductVersion,SERVERPROPERTY('Edition') as Edition;"
         
            #SQL server Memory
            $query2 = "select value as MaxMemory from sys.configurations where name like 'max server memory (MB)';"
     
         
            #execute query Get Product Version and Edition
            $sqlconn.Open()
            $sqlcmd = new-object System.Data.SqlClient.SqlCommand ($query, $sqlconn);
            $sqlcmd.CommandTimeout = 0;
            $dr = $sqlcmd.ExecuteReader();        
                   
            while ($dr.Read()) {
             $SQLVersion=$dr.GetValue(0);
             $SQLEdition=$dr.GetValue(1);
            }
         
            $dr.Close()
            $sqlconn.Close()
           
            #execute query Get SQL Server Max Memory
            $sqlconn.Open()
            $sqlcmd2 = new-object System.Data.SqlClient.SqlCommand ($query2, $sqlconn);
            $sqlcmd2.CommandTimeout = 0;
            $dr2 = $sqlcmd2.ExecuteReader();
           
             while ($dr2.Read()) {
             $SQLMaxMemory=$dr2.GetValue(0);
           
            }
                 
            $dr2.Close()
            $sqlconn.Close()
                 
         
         
 
            #Get processors information          
            $CPU=Get-WmiObject -ComputerName $ComputerName -class Win32_Processor
         
            #Get Computer model information
            $OS_Info=Get-WmiObject -ComputerName $ComputerName -class Win32_ComputerSystem
         
            #Get Computer IP Address        
            $OS_IP =Get-WmiObject -ComputerName $ComputerName -class Win32_NetworkAdapterConfiguration -Filter IPEnabled=True                    
         
         
            #Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter IPEnabled=TRUE -ComputerName $ComputerName -Property IPAddress
            #Get OS Version
            $OS_Ver=Get-WmiObject -ComputerName $ComputerName -class Win32_OperatingSystem
   
           #Reset number of cores and use count for the CPUs counting
           $CPUs = 0
           $Cores = 0
         
           foreach($Processor in $CPU){

           $CPUs = $CPUs+1
         
           #count the total number of cores      
           $Cores = $Cores+$Processor.NumberOfCores
     
          }
       
          #Calculate Physical Memory
          $totalMemory = 0
       
           #Get Computer Physical Memory Information
           $OS_Mem=Get-WmiObject -ComputerName $ComputerName -class Win32_PhysicalMemory
       
          foreach($ram in $OS_Mem)
          {
           $totalMemory+= $ram.capacity
          }
       
         
           $InfoRecord = New-Object -TypeName PSObject -Property @{
                    Server = $ComputerName;
                    OS = $OS_Ver.Caption;                      
                    Model = $OS_Info.Model;
                    IpAddress = $OS_IP.IPAddress;
                    Domain = $OS_Info.Domain;
                    SQLEdition = $SQLEdition;
                    SQLVersion = $SQLVersion;
                    PhysicalMemory_MB = $totalMemory/(1024 * 1024);
                    SQLMaxMemory_MB = $SQLMaxMemory;
                    CPUNumber = $CPUs;
                    TotalCores = $Cores;
                    };

                    }

    Catch{

     "Fail to get information $ComputerName +' ' + $RunTime :$_" |Out-File $errorlog -Append
    }
 
    Write-Output $InfoRecord
     }
                         
   }
#loop through the server list and get information about CPUs, Cores and Default instance edition
   $exportcsv ='E:\PowerShell\SQLServersInfo\SQLServerInfo.csv'
   Get-Content $SQLServerList | Foreach-Object {Get-ServerInfo $_ }|Select Server, OS, Model,{$_.IpAddress},Domain,SQLEdition,SQLVersion,PhysicalMemory_MB,SQLMaxMemory_MB,CPUNumber,TotalCores|Export-csv $exportcsv  -noType -Append
   Get-Job | Wait-Job | Out-Null
   Remove-Job -State Completed

-------------End--------------
SQLSeverInfo_Call.ps1

SL "E:\PowerShell\SQLServersInfo"
.\SQLServerInfo.ps1 -SQLServerList "E:\PowerShell\SQLServerList\SQLSrvList.txt"

Powershell - Get ALL SQL services running Status in SQL server VMs

I created the following script to check any SQL services running on SQL server VMs. The script will read servers name in SQLSvrList.txt file and retrieve SQL services running status and startmode from WMI service module. It will export to SQLServerStatus.csv. If there is error in connection, the errors will be written to Error.txt.

Enjoy Scripting....

$($ServerList = 'E:\PowerShell\SQLServerList\SQLSvrList.txt'
if(!(test-path $ServerList))
{
    Write-Error "SQLSvrList.txt is not Found" -ea Stop
}
if(test-path $ServerList)
{
$errorlog = 'E:\PowerShell\SQLServerStatus\Error.txt'
$exportcsv ='E:\PowerShell\SQLServerStatus\SQLSeverStatus.csv'
$servername = Get-Content $ServerList
Clear-Content $exportcsv
foreach($server in $servername){
Try{

$WMI=Get-WmiObject win32_service -computer $server | Where-Object {$_.DisplayName -match 'SQL'}|

Select-Object @{Expression={$_.systemName};Label = "ServerName"},
                       @{Expression={$_.Name};Label = "SQLService"},
                       @{Expression={$_.StartName};Label = "Account"},
                       @{Expression={$_.StartMode};Label = "StartMode"},
                       @{Expression={$_.State};Label = "State"},
                       @{Expression={$_.DisplayName};Label = "ServiceName"}
                       $WMI|Export-csv $exportcsv -Append -NoTypeInformation
                                       

}
Catch
{
    Clear-Content $errorlog
     "Fail to get information $server +' ' + $RunTime :$_" |Out-File $errorlog -Append
}
}
})
Get-Job | Wait-Job | Out-Null
Remove-Job -State Completed

PowerShell - Get All SQL server VMs last reboot date/time

PowerShell ISE 3.0

The purpose of the following script is to make my DBA life easy when I manage 80 + SQL server VMs in the organization. The following script is to retrieve all SQL Server VM Last reboot date time without logging into each server and check eventlog.


Enjoy Scripting..

Step 1. Create a text file in which all sql server vm names. Save the file in your preferred. In my case the file is located at E:\PowerShell\SQLServerList\ folder
Step 2. run the script in powershell ISE or Schedule it in SQL Agent in your DBA test server instance


$($ServerList = 'E:\PowerShell\SQLServerList\SQLSvrList.txt'
$exportcsv ='E:\PowerShell\FindLastRebootDate\LastRebootDate.csv'
$servername = Get-Content $ServerList
$errorlog = 'E:\PowerShell\FindLastRebootDate\Error.txt'
if(!(test-path $ServerList))
{
    Write-Error "SQLSvrList.txt is not Found" -ea Stop
}
if(test-path $ServerList)
{
foreach($server in $servername){
Try{

$WMI=Get-WmiObject -ComputerName $server -Class win32_operatingsystem
$lastRebootTime = $WMI.ConvertToDateTime($WMI.LastBootUpTime)

$InfoRecord = New-Object -TypeName PSObject -Property @{
                    Server = $server;
                    LastRebootDateTime = $LastRebootTime;
                    };

$InfoRecord | Select Server,LastRebootDateTime


}
Catch
{
    Clear-Content $errorlog
    "Fail to get information $server +' ' + $RunTime :$_" |Out-File $errorlog -Append
}
}
})|Export-csv $exportcsv -noType
Get-Job | Wait-Job | Out-Null
Remove-Job -State Completed

PowerShell - Get All Scheduled Tasks in SQL Server VMs

 PowerShell ISE 3.0
The following script will collect tasks scheduled in Task Scheduler in windows server from all SQL server VMs. The purpose of my script is to know monthly reboot schedule of each server in the organization.

Enjoy Scripting..

Step 1. Create a text file in which all sql server vm names. Save the file in your preferred. In my case the file is located at E:\PowerShell\SQLServerList\ folder
Step 2. run the script in powershell ISE or Schedule it in SQL Agent in your DBA test server instance

$($ServerList = 'E:\PowerShell\SQLServerList\SQLSvrList.txt'
if(!(test-path $ServerList))
{
    Write-Error "SQLSvrList.txt is not Found" -ea Stop
}
if(test-path $ServerList)
{
$errorlog = 'E:\PowerShell\GetScheduleTask\Error.txt'
$exportcsv ='E:\PowerShell\GetScheduleTask\ScheduleList.csv'
$servername = Get-Content $ServerList
foreach($server in $servername){
Try{
Clear-Content $exportcsv
$schedule = new-object -com("Schedule.Service")
$schedule.connect($server)
$tasks = $schedule.getfolder("\").gettasks(0)
$tasks |select @{Name="Server";Expression={$server}},Name,LastRunTime,NextRunTime,@{Name="RunAs";Expression={[xml]$xml = $_.xml ; $xml.Task.Principals.principal.userID}}
}Catch
{
    Clear-Content $errorlog
    "Fail to get information $server +' ' + $RunTime :$_" |Out-File $errorlog -Append
}
}
})|Export-csv $exportcsv -noType
Get-Job | Wait-Job | Out-Null
Remove-Job -State Completed