Author Posts

June 8, 2018 at 11:45 pm

I'm moving databases from 1 server to another. I've a table that hosts the source and destination servers, the database name and the date & time to move them, along with people/groups to alert.
As I loop through the list, I check if the Logins for the database is already at the destination, if not, create it, but it's taking forever.....I'm hoping to find out if there is a faster way of doing it....

    # Get the list of databases
$GetDBS = "SELECT [SourceSqlServerName],DestSqlServerName,[DatabaseName] FROM dbo.dbmigration ORDER BY [DatabaseName]"
$DbMigrationInfo = Invoke-Sqlcmd -Query $GetDBS -ServerInstance $Source -Database $database
Foreach($migration in $DbMigrationInfo)
{
    $Source = $migration.SourceSqlServerName  
    $Destination = $migration.DestSqlServerName                                
    $Database = $migration.DatabaseName   

    $SourceServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Source
    $DestServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Destination

    Foreach($login in $SourceServer.Logins | ?{ $_.LoginType -Eq "WindowsGroup" -And $_.Name -NotIn "domain\release_manager","domain\TEST_RW","domain\TEST_R","domain\TEST_DBO"})
    {
        if($SourceServer.Logins[$Login.Name].EnumDatabaseMappings() | ?{$_.DBName -eq $Database })
        { 
             if(!($DestServer.Logins.Item($Login.Name)))
             {
                "$($Login.Name) needs to be added"
                $Login = New-Object Microsoft.SqlServer.Management.Smo.Login $DestServer,$($Login.Name)
                $Login.LoginType  = "WindowsGroup"
                $Login.Create()
            } else { "$($Login.Name) was already there" }
        }       
    }
}

Any help or advice welcomed....

June 9, 2018 at 12:06 am

I've found a way of making it better, by adding those that already exists to an array and excluding those next loop. But still slooooow.


$Exclude = @()
Foreach($Source in $Sources) 
{

    $Destination = "myserver\myinstance"
    $database = "master"

        # Get the list of databases
    $GetDBS = "SELECT * FROM sys.databases Where name not like 'reporting_%' AND Database_id > 4 AND Name NOT IN ('DBARepository','ssisdb')"
    $DbMigrationInfo = (Invoke-Sqlcmd -Query $GetDBS -ServerInstance $Source -Database $database).Name

    Foreach($migration in $DbMigrationInfo)
    {
        $SourceServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Source
        $DestServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Destination
        $extype = [Microsoft.SQLServer.Management.Common.ExecutionTypes]::ContinueOnError

        Foreach($login in $SourceServer.Logins | ?{ $_.LoginType -Eq "WindowsGroup" -And $_.Name -NotIn $Exclude} ) #-And $_.Name -NotIn "domain\release_manager","domain\TEST_RW","domain\TEST_R","domain\TEST_DBO"}
        {
            if($SourceServer.Logins[$Login.Name].EnumDatabaseMappings() | ?{$_.DBName -eq $Database })
            { 
                 if(!($DestServer.Logins.Item($Login.Name)))
                 {
                    "$($Login.Name) needs to be added"
                    $Login = New-Object Microsoft.SqlServer.Management.Smo.Login $DestServer,$($Login.Name)
                    $Login.LoginType  = "WindowsGroup"
                    $Login.Create()
                } else { "$($Login.Name) was already there" ; $Exclude += $($Login.Name) ; $Exclude}
            }       
        }
    }
}

June 9, 2018 at 12:30 am

This performance concern is not specific to PoSH. It is all things inline. You code, the servers involved and their consumption / processing speeds, network consumption, etc.

Loops are generally slow in almost any language and the larger the recordset, well, you know.

Your are also using three of them, nested at that. So, in such a case, if there are 100 sources, 100 DB's and 100 logins per DB, that's approximately (100 * 100 * 100) 1 million passes that have to be done in that nested loop set.

Then there is the speed in which SQL will handle each connection request, TSQL command and commits (remember SQL is a double commit thing), then there is the resource limitations on the server where this is being done and the target SQL server resource consumption.

My speed up suggestion is to set this up as parallel jobs in their on runspaces. So, multi-threading this effort. There are a lot of samples of using parallel processing and runspaces all over the web.

Example:

Parallel processing with PowerShell
'blogs.technet.microsoft.com/uktechnet/2016/06/20/parallel-processing-with-powershell'

Use PowerShell Workflow to Ping Computers in Parallel
'blogs.technet.microsoft.com/heyscriptingguy/2012/11/20/use-powershell-workflow-to-ping-computers-in-parallel'

Ping multiple servers simultaneously with PowerShell: Test-ConnectionAsync
Ping hundreds of machines at a time using PowerShell using multithreading (without the added overhead of additional powershell.exe processes that would come from using the Start-Job cmdlet.)
'gallery.technet.microsoft.com/scriptcenter/Multithreaded-PowerShell-0bc3f59b'

Multithreading with Jobs in PowerShell
'get-blog.com/?p=22'

Multithread Your PowerShell Commands Using Runspaces with PoshRSJob
'mcpmag.com/articles/2015/08/06/multithread-your-commands.aspx'

June 9, 2018 at 12:36 am

I use parallel for a lot of my other scripts
That there are many isnt the issue I'm trying address,it each one taking so long. Wondering f there is a faster way to copy each one

June 9, 2018 at 3:04 am

Understood, but, if this were me, I'd be testing to validate with code segment is the choke point vs trying to do this on the whole first.

So, Q&D (quick and dirty) I'd put timers in each loop segment to validate entry and exit.

$Exclude = @()
Foreach($Source in $Sources) 
{
$stopwatch1 = [diagnostics.stopwatch]::StartNew()
'Start time for Source check loop entry' + $stopwatch1.Elapsed

    $Destination = "myserver\myinstance"
    $database = "master"

        # Get the list of databases
    $GetDBS = "SELECT * FROM sys.databases Where name not like 'reporting_%' AND Database_id > 4 AND Name NOT IN ('DBARepository','ssisdb')"
    $DbMigrationInfo = (Invoke-Sqlcmd -Query $GetDBS -ServerInstance $Source -Database $database).Name

    Foreach($migration in $DbMigrationInfo)
    {
    $stopwatch2 = [diagnostics.stopwatch]::StartNew()
    'Start time for Migration check loop entry' + $stopwatch2.Elapsed

        $SourceServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Source
        $DestServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Destination
        $extype = [Microsoft.SQLServer.Management.Common.ExecutionTypes]::ContinueOnError

        Foreach($login in $SourceServer.Logins | ?{ $_.LoginType -Eq "WindowsGroup" -And $_.Name -NotIn $Exclude} ) #-And $_.Name -NotIn "domain\release_manager","domain\TEST_RW","domain\TEST_R","domain\TEST_DBO"}
        {
        $stopwatch3 = [diagnostics.stopwatch]::StartNew()
        'Start time for Login check loop entry' + $stopwatch3.Elapsed

            if($SourceServer.Logins[$Login.Name].EnumDatabaseMappings() | ?{$_.DBName -eq $Database })
            { 
                 if(!($DestServer.Logins.Item($Login.Name)))
                 {
                    "$($Login.Name) needs to be added"
                    $Login = New-Object Microsoft.SqlServer.Management.Smo.Login $DestServer,$($Login.Name)
                    $Login.LoginType  = "WindowsGroup"
                    $Login.Create()
                } else { "$($Login.Name) was already there" ; $Exclude += $($Login.Name) ; $Exclude}
            }
        'Elapsed time for Login check loop exit' + $stopwatch3.Elapsed      
        }
    'Elapsed time for Migration check loop exit' + $stopwatch2.Elapsed.TotalMilliseconds
    }
'Elapsed time for Source check loop exit' + $stopwatch1.Elapsed.TotalMilliseconds
}

June 9, 2018 at 1:41 pm

The intention of posting the question, was hoping for a "wow, no do it this way".
I don't need the stop watch to tell me which part is taking the time, this is the bottle neck: "if($SourceServer.Logins[$Login.Name].EnumDatabaseMappings() | ?{$_.DBName -eq $Database })".
But if I did need to time each step, I know just fine how to.
What I don't know, is a more efficient way of copying the logins, for the list of database(s) provided, from one sql server to another by automated process using Powershell – I say that as I can do it by manually by running a tsql script faster.

June 9, 2018 at 3:56 pm

I can't say if it will be faster, but instead of “rolling your own” consider giving Copy-DbaLogin from the DbaTools module a try.

July 28, 2018 at 3:38 pm

I found a faster way of doing this, thought I'd share....

foreach($DbInfo in $DbInfos)
{
   
   [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $($DbInfo.SourceSqlServerName)
    $Dest = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $($DbInfo.DestSqlServerName)
    $extype = [Microsoft.SQLServer.Management.Common.ExecutionTypes]::ContinueOnError
    $Db = $srv.Databases.Item($($DbInfo.DatabaseName))
    $Logins = $Db.Users | Select -ExpandProperty Login
    foreach($login in $logins | ? { $_ }) 
    {
        $lscript = $srv.Logins[$($Login)].Script()
                    Write-Host $lscript
        $Dest.Databases['tempdb'].ExecuteNonQuery($lscript,$extype)
    }
}

July 29, 2018 at 9:36 pm

I'd recommend looking at the DBATools module.

Copy-DbaLogin

It's a fantastic module, open source and created by some prominent SQL admins who contribute a lot to the community.

July 29, 2018 at 10:27 pm

Yeah, thanks, but the point was trying to learn for myself and understand the process, and not just copy others.

July 30, 2018 at 12:52 am

Perfectly fair. Have a look at the DBATools GitHub repo then and see how they've implemented their tool. Compare it to your own.

https://github.com/sqlcollaborative/dbatools/blob/development/functions/Copy-DbaLogin.ps1

In the context of being a DBA I can't recommend these tools enough to help with tasks you may be assigned at work.