locked
PS script to deploy SQL code RRS feed

  • Question

  • Hi All,

    I have a deployment to do on a lot of servers, the issue i am facing at the moment is that, I dont have access to all the SQL instances. I am trying to write the PS code such that if the SQL code was successfully deployed, a log is written on the file. The way I have it written, when it makes a successful connection, a record is written on the log, this method is flawed because being able to connect to the instance does not mean one has the permission to create the account, so i thought one way around it is to do a check to see if the account has been created after a deployment of the code, the issue is that I only want it to do this check if the connection to SQL was successful, otherwise there is no point as it would result in another failure.

    Below is the code.

    cls
    
    
    
    function Invoke-Sqlcmd2 
    { 
        [CmdletBinding()] 
        param( 
        [Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance, 
        [Parameter(Position=1, Mandatory=$false)] [string]$Database, 
        [Parameter(Position=2, Mandatory=$false)] [string]$Query, 
        [Parameter(Position=3, Mandatory=$false)] [string]$Username, 
        [Parameter(Position=4, Mandatory=$false)] [string]$Password, 
        [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600, 
        [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15, 
        [Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile, 
        [Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow" 
        ) 
     
        if ($InputFile) 
        { 
            $filePath = $(resolve-path $InputFile).path 
            $Query =  [System.IO.File]::ReadAllText("$filePath") 
        } 
     
        $conn=new-object System.Data.SqlClient.SQLConnection 
          
        if ($Username) 
        { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout } 
        else 
        { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout } 
     
        $conn.ConnectionString=$ConnectionString 
         
        #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller 
        if ($PSBoundParameters.Verbose) 
        { 
            $conn.FireInfoMessageEventOnUserErrors=$true 
            $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"} 
            $conn.add_InfoMessage($handler) 
        } 
         
        $conn.Open() 
        $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) 
        $cmd.CommandTimeout=$QueryTimeout 
        $ds=New-Object system.Data.DataSet 
        $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) 
        [void]$da.fill($ds) 
        $conn.Close() 
        switch ($As) 
        { 
            'DataSet'   { Write-Output ($ds) } 
            'DataTable' { Write-Output ($ds.Tables) } 
            'DataRow'   { Write-Output ($ds.Tables[0]) } 
        } 
     
    } #Invoke-Sqlcmd2
    
    
    
    
    
    
    $account_deploy = "USE [master]
    
    IF NOT EXISTS (select * from master..syslogins where name = 'DOMAIN\my_account')
    CREATE LOGIN [DOMAIN\my_account] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    
    "
    
    $account_check = "use master
    select count(*) from syslogins where name like '%my_account%'
    "
    
    
    $logFile = "C:\Temp\MyDeploy_ $(get-date -f dd-MM-yyyy-HHmmss).log"
    $server_names = Get-Content "C:\temp\prod_list_2.txt" 
    
    Foreach ($server in $server_names) 
    {
    
                "Deploying to | $server " | Out-File $logFile -Append
    
            try {
    
                Invoke-Sqlcmd2 -Query $account_deploy -ServerInstance $server -database "master" -QueryTimeout 120 -ErrorAction Stop 
    
            }
            Catch{
    
                $ErrorMessage = $_.Exception.Message
                $Time = Get-Date
                "$server |  $ErrorMEssage | This script failed at $Time and error message is $ErrorMEssage " | Out-File $logFile -Append
    
    
            }
    
    
    				
    	
    	}
    
    
    



    • Edited by MrFlinstone Friday, November 9, 2018 4:05 PM
    Friday, November 9, 2018 4:03 PM

All replies

  • You would do better using SQLDMO to create a login.  It will throw an exception if the creation fails or if the account already exists.  It will also take much less code to do this.

    You can also import the latest SQLServer module and use the "Add-SqlLogin" command which will also do wha tyou need in only one line as well as give clear output to use in a log file.


    \_(ツ)_/


    • Edited by jrv Friday, November 9, 2018 4:12 PM
    Friday, November 9, 2018 4:08 PM
  • The reason why I have done it this way is that the servers to be deployed onto are a different mix, some would have the modules required and some would not, using sqlcmd2, it means its self contained.

    Friday, November 9, 2018 4:32 PM
  • The modules are not required on the servers.  The module only needs to be installed on the client.

    Install-Module SQLServer

    That's it.


    \_(ツ)_/

    Friday, November 9, 2018 4:35 PM
  • The modules are not required on the servers.  The module only needs to be installed on the client.

    Install-Module SQLServer

    That's it.


    \_(ツ)_/

    Which client are you referring to ?

    You mean the server that i am deploying from ?

    Tuesday, November 20, 2018 10:47 AM
  • The one you are running PowerShell on.

    In computers and software the "client" is the one that executes code and the server is the computer that the code is executed to.


    \_(ツ)_/

    Tuesday, November 20, 2018 12:28 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Tuesday, November 27, 2018 2:25 AM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Tuesday, December 4, 2018 3:05 AM