Copying logins from 1 sql server to another

Welcome Forums General PowerShell Q&A Copying logins from 1 sql server to another

This topic contains 10 replies, has 4 voices, and was last updated by

 
Participant
4 months, 2 weeks ago.

  • Author
    Posts
  • #102155

    Participant
    Points: 123
    Rank: Participant

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

  • #102158

    Participant
    Points: 123
    Rank: Participant

    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}
                }       
            }
        }
    }
  • #102161

    Participant
    Points: 322
    Helping Hand
    Rank: Contributor

    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'

    • #102163

      Participant
      Points: 123
      Rank: Participant

      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

  • #102164

    Participant
    Points: 322
    Helping Hand
    Rank: Contributor

    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
    }
    
    • #102169

      Participant
      Points: 123
      Rank: Participant

      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.

  • #102191

    Participant
    Points: 17
    Rank: Member

    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.

  • #105535

    Participant
    Points: 123
    Rank: Participant

    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)
        }
    }
    
  • #105547

    Participant
    Points: 52
    Rank: Member

    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.

    • #105550

      Participant
      Points: 123
      Rank: Participant

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

    • #105553

      Participant
      Points: 52
      Rank: Member

      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.

The topic ‘Copying logins from 1 sql server to another’ is closed to new replies.