Copying logins from 1 sql server to another

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…

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}
            }       
        }
    }
}

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

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

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
}

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.

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.

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)
    }
}

I’d recommend looking at the DBATools module.

Https://dbatools.io/functions/copy-dbalogin/

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

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

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.