Assess your on-premises database Using Database Migration Assistant (DMA)

Before you can migrate data from a SQL Server instance to a single database or pooled database in Azure SQL Database, you need to assess the SQL Server database for any blocking issues that might prevent migration. Using the Data Migration Assistant v3.3 or later, follow the steps described in the article Performing a SQL Server migration assessment to complete the on-premises database assessment. A summary of the required steps follows:

  1. In the Data Migration Assistant, select the New (+) icon, and then select the Assessment project type.
  2. Specify a project name, in the Source server type text box, select SQL Server, in the Target server type text box, select Azure SQL Database, and then select Create to create the project.

When you’re assessing the source SQL Server database migrating to a single database or pooled database in Azure SQL Database, you can choose one or both of the following assessment report types:

Check database compatibility
Check feature parity
Both report types are selected by default.

3. In the Data Migration Assistant, on the Options screen, select Next.

4. On the Select sources screen, in the Connect to a server dialog box, provide the connection details to your SQL Server, and then select Connect.

5. In the Add sources dialog box, select AdventureWorks2012, select Add, and then select Start Assessment.

If you use SSIS, DMA does not currently support the assessment of the source SSISDB. However, SSIS projects/packages will be assessed/validated as they are redeployed to the destination SSISDB hosted by Azure SQL Database. 

When the assessment is complete, the results display as shown in the following graphic:

Assess data migration

For databases in Azure SQL Database, the assessments identify feature parity issues and migration blocking issues for deploying to a single database or pooled database.

  1. The SQL Server feature parity category provides a comprehensive set of recommendations, alternative approaches available in Azure, and mitigating steps to help you plan the effort into your migration projects.
  2. The Compatibility issues category identifies partially supported or unsupported features that reflect compatibility issues that might block migrating SQL Server database(s) to Azure SQL Database. Recommendations are also provided to help you address those issues.

6. Review the assessment results for migration blocking issues and feature parity issues by selecting the specific options.

SQL Server Database Migration(On-Premises) to Azure SQL Database

The primary methods for migrating a SQL Server 2005 or later database to Azure SQL Database.

Migrate to a single database or a pooled database :

There are primary two methods for migrating a SQL Server 2005 or later database to Azure SQL Database. The first method is simpler but requires some, possibly substantial, downtime during the migration. The second method is more complex, but substantially eliminates downtime during the migration.

In both cases, you need to ensure that the source database is compatible with Azure SQL Database using the Data Migration Assistant (DMA). SQL Database is approaching feature parity with SQL Server, other than issues related to server-level and cross-database operations. Databases and applications that rely on partially supported or unsupported functions need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated.

Method 1: Migration with downtime during the migration

Use this method to migrate to a single or a pooled database if you can afford some downtime or you’re performing a test migration of a production database for later migration.The following list contains the general workflow for a SQL Server database migration of a single or a pooled database using this method.

VSSSDT migration diagram
  1. Assess the database for compatibility by using the latest version of the Data Migration Assistant (DMA).
  2. Prepare any necessary fixes as Transact-SQL scripts.
  3. Make a transactionally consistent copy of the source database being migrated or halt new transactions from occurring in the source database while migration is occurring. Methods to accomplish this later option include disabling client connectivity or creating a database snapshot. After migration, you may be able to use transactional replication to update the migrated databases with changes that occur after the cutoff point for the migration.
  4. Deploy the Transact-SQL scripts to apply the fixes to the database copy.
  5. Migrate the database copy to a new database in Azure SQL Database by using the Data Migration Assistant.

Also we can Import a BACPAC file to a new database in Azure SQL Database.

Optimizing data transfer performance during migration :

The following list contains recommendations for best performance during the import process.

  1. Choose the highest service tier and compute size that your budget allows to maximize the transfer performance. You can scale down after the migration completes to save money.
  2. Minimize the distance between your BACPAC file and the destination data center.
  3. Disable autostatistics during migration
  4. Partition tables and indexes
  5. Drop indexed views, and recreate them once finished
  6. Remove rarely queried historical data to another database and migrate this historical data to a separate database in Azure SQL Database. You can then query this historical data using elastic queries.

Optimize Your Performance Once the migration has been completed by Updating statistics with full scan after the migration is completed.

Script to Pull All Databases (and Properties) on SQL Server Instance with PowerShell Function

It is often asked by support teams investigating issues. It can also be asked by developers checking the impact of other services on their DEV/UAT environments, by change managers investigating impact of changes, by service managers investigating the impact of downtime, when capacity planning for a new service and numerous other situations.

A simple quick and easy question made simpler with this function which can also be called when creating documentation

The Assemblies are getting Loaded by using below script :

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $Server
($srv.Databases| Get-Member -MemberType Property)

There are 192 Properties that you can examine and that is just for databases:-)

To get the count of Properties use below code :

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $Server
($srv.Databases| Get-Member -MemberType Property).count

Picking out a few properties you could do something like this

If you want aliases for your column headings you will need to add a bit of code to the select.

For Example, maybe you want to Database Name as a heading and the Size in Gb (Its in Mb in the example above) You would need to create a hash table with a Label element and an Expression element. The Label is the column heading and the Expression can just be the data or a calculation on data.

The Column Heading is Database Name and the data is the Name property and select Size becomes

select @{label=”Database Name”;Expression={$_.Name}}

Select @{label=”Size GB”;Expression={“{0:N3}” -f ($_.Size/1024)}}

The Column Heading is Size GB and the data is the Size property divided by 1024 to 3 decimal places then your code would look like this

$srv.databases|select @{label=”Server”;Expression={$_.Parent.name}}, @{label="Database Name";Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,
RecoveryModel, @{label=”Size GB”;Expression={“{0:N3}” -f ($_.Size/1024)}}|`
Format-Table -Wrap –AutoSize

Here is the Full Script below :

<#PSScriptInfo

.VERSION 1.0

.GUID 48bf0316-66c3-4253-9154-6fc5b28e482a

.DESCRIPTION Returns Database Name and Size in GB for databases on a SQL server
      
.COMPANYNAME 

.COPYRIGHT 

.TAGS SQL, Database, Databases, Size

.LICENSEURI 

.PROJECTURI 

.ICONURI 

.EXTERNALMODULEDEPENDENCIES 

.REQUIREDSCRIPTS 

.EXTERNALSCRIPTDEPENDENCIES 

.RELEASENOTES

#>
<#
    .Synopsis
    Returns the databases on a SQL Server and their size
    .DESCRIPTION
    Returns Database Name and Size in GB for databases on a SQL server
    .EXAMPLE
    Show-DatabasesOnInstance

    This will return the user database names and sizes on the local machine default instance
    .EXAMPLE
    Show-DatabasesOnInstance -Servers SERVER1

    This will return the database names and sizes on SERVER1
    .EXAMPLE
    Show-DatabasesOnInstance -Servers SERVER1 -IncludeSystemDatabases

    This will return all of the database names and sizes on SERVER1 including system databases
    .EXAMPLE
    Show-DatabasesOnInstance -Servers 'SERVER1','SERVER2\INSTANCE'

    This will return the user database names and sizes on SERVER1 and SERVER2\INSTANCE
    .EXAMPLE
    $Servers = 'SERVER1','SERVER2','SERVER3'
    Show-DatabasesOnInstance -Servers $servers|out-file c:\temp\dbsize.txt

    This will get the user database names and sizes on SERVER1, SERVER2 and SERVER3 and export to a text file c:\temp\dbsize.txt
       
#>

Function Show-DatabasesOnInstance
{
    [CmdletBinding()]
    param (
        # Server Name or array of Server Names - Defaults to $ENV:COMPUTERNAME
        [Parameter(Mandatory = $false, 
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true, 
            Position = 0)]
        $Servers = $Env:COMPUTERNAME,
        # Switch to include System Databases
        [Parameter(Mandatory = $false)]
        [switch]$IncludeSystemDatabases
    )
    [void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );
    foreach ($Server in $Servers) {
        if ($Server.Contains('\')) {
            $ServerName = $Server.Split('\')[0]
            $Instance = $Server.Split('\')[1]
        }
        else {
            $Servername = $Server
        } 

        ## Check for connectivity
        if ((Test-Connection $ServerName -count 1 -Quiet) -eq $false) {
            Write-Error "Could not connect to $ServerName - Server did not respond to ping"
            $_.Exception
            continue
        }
    
        $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server

        if ($IncludeSystemDatabases) {
            try {
                $Return = $srv.databases|select @{label="Server";Expression={$_.Parent.name}},` 
@{label="Database Name";Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,` 
RecoveryModel, @{label="Size GB";Expression={"{0:N3}" -f ($_.Size/1024)}}|` 
Format-Table -Wrap –AutoSize
            }
            catch {
                Write-Error "Failed to get database information from $Server"
                $_.Exception
                continue
            }
        }
        else {
            try {
                $Return = $srv.databases.Where{$_.IsSystemObject -eq $false}| select @{label="Server";Expression={$_.Parent.name}},` 
@{label="Database Name";Expression={$_.Name}}, Owner, Collation, CompatibilityLevel,` 
RecoveryModel, @{label="Size GB";Expression={"{0:N3}" -f ($_.Size/1024)}}|` 
Format-Table -Wrap –AutoSize
            }
            catch {
                Write-Error "Failed to get database information from $Server"
                $_.Exception
                continue
            }
        }
        Write-Output "`n The Databases on $Server and their Size in GB `n"
        $Return
    }
}

After creating the Function ‘ Show-DatabasesOnInstance ‘ , execute on powershell as shown below :

You can modify the script based on your requirement with the help of Database Properties as discussed in above (we have almost 192 Properties).

Script can be changed\ modified at 2 places in Function where ” $Return ” value is mentioned.

SPN Registration Of Windows Service Accounts and Permissions

Startup accounts used to start and run SQL Server can be domain user accounts, local user accounts, managed service accounts, virtual accounts, or built-in system accounts. To start and run, each service in SQL Server must have a startup account configured during installation.

This section describes the accounts that can be configured to start SQL Server services, the default values used by SQL Server Setup, the concept of per-service SID’s, the startup options, and configuring the firewall.

Default Service Accounts :

The following table lists the default service accounts used by setup when installing all components. The default accounts listed are the recommended accounts, except as noted.

Stand-alone Server or Domain Controller

ComponentWindows Server 2008Windows 7 and Windows Server 2008 R2 and higher
Database EngineNETWORK SERVICEVirtual Account*
SQL Server AgentNETWORK SERVICEVirtual Account*
SSASNETWORK SERVICEVirtual Account* **
SSISNETWORK SERVICEVirtual Account*
SSRSNETWORK SERVICEVirtual Account*
SQL Server Distributed Replay ControllerNETWORK SERVICEVirtual Account*
SQL Server Distributed Replay ClientNETWORK SERVICEVirtual Account*
FD Launcher (Full-text Search)LOCAL SERVICEVirtual Account
SQL Server BrowserLOCAL SERVICELOCAL SERVICE
SQL Server VSS WriterLOCAL SYSTEMLOCAL SYSTEM
Advanced Analytics ExtensionsNTSERVICE\
MSSQLLaunchpad
NTSERVICE\
MSSQLLaunchpad
PolyBase EngineNETWORK SERVICENETWORK SERVICE
PolyBase Data Movement ServiceNETWORK SERVICENETWORK SERVICE

*When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA), configured with the minimum privileges necessary. ** When installed on a Domain Controller, a virtual account as the service account is not supported.

SQL Server Failover Cluster Instance

ComponentWindows Server 2008Windows Server 2008 R2
Database EngineNone. Provide a domain user account.Provide a domain user account.
SQL Server AgentNone. Provide a domain user account.Provide a domain user account.
SSASNone. Provide a domain user account.Provide a domain user account.
SSISNETWORK SERVICEVirtual Account
SSRSNETWORK SERVICEVirtual Account
FD Launcher (Full-text Search)LOCAL SERVICEVirtual Account
SQL Server BrowserLOCAL SERVICELOCAL SERVICE
SQL Server VSS WriterLOCAL SYSTEMLOCAL SYSTEM

Managed Service Accounts, Group Managed Service Accounts, and Virtual Accounts

Managed service accounts, group managed service accounts, and virtual accounts are designed to provide crucial applications such as SQL Server with the isolation of their own accounts, while eliminating the need for an administrator to manually administer the Service Principal Name (SPN) and credentials for these accounts. These make long term management of service account users, passwords and SPNs much easier.

  • Managed Service AccountsA Managed Service Account (MSA) is a type of domain account created and managed by the domain controller. It is assigned to a single member computer for use running a service. The password is managed automatically by the domain controller. You cannot use a MSA to log into a computer, but a computer can use a MSA to start a Windows service. An MSA has the ability to register a Service Principal Name (SPN) within Active Directory when given read and write servicePrincipalName permissions. A MSA is named with a $ suffix, for example DOMAIN\ACCOUNTNAME$. When specifying a MSA, leave the password blank. Because a MSA is assigned to a single computer, it cannot be used on different nodes of a Windows cluster. NoteThe MSA must be created in the Active Directory by the domain administrator before SQL Server setup can use it for SQL Server services.
  • Group Managed Service AccountsA Group Managed Service Account is an MSA for multiple servers. Windows manages a service account for services running on a group of servers. Active Directory automatically updates the group managed service account password without restarting services. You can configure SQL Server services to use a group managed service account principal. Beginning with SQL Server 2014, SQL Server supports group managed service accounts for standalone instances, and SQL Server 2016 and later for failover cluster instances, and availability groups.To use a group managed service account for SQL Server 2014 or later, the operating system must be Windows Server 2012 R2 or later. Servers with Windows Server 2012 R2 require KB 2998082 applied so that the services can log in without disruption immediately after a password change.For more information, see Group Managed Service Accounts NoteThe group managed service account must be created in the Active Directory by the domain administrator before SQL Server setup can use it for SQL Server services.
  • Virtual AccountsVirtual accounts (beginning with Windows Server 2008 R2 and Windows 7) are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$. When specifying a virtual account to start SQL Server, leave the password blank. If the virtual account fails to register the Service Principal Name (SPN), register the SPN manually. For more information on registering a SPN manually, see Manual SPN Registration
  • Note : Virtual accounts cannot be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster.
  • The following table lists examples of virtual account names.
ServiceVirtual Account Name
Default instance of the Database Engine serviceNT SERVICE\MSSQLSERVER
Named instance of a Database Engine service named PAYROLLNT SERVICE\MSSQL$PAYROLL
SQL Server Agent service on the default instance of SQL ServerNT SERVICE\SQLSERVERAGENT
SQL Server Agent service on an instance of SQL Server named PAYROLLNT SERVICE\SQLAGENT$PAYROLL

For more information on Managed Service Accounts and Virtual Accounts, see the Managed service account and virtual account concepts section of Service Accounts Step-by-Step Guide and Managed Service Accounts Frequently Asked Questions (FAQ).

Security Note: Always run SQL Server services by using the lowest possible user rights. Use a MSA or virtual account when possible. When MSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported.

The below link provides more Information on SPN Registration :

https://help.deepsecurity.trendmicro.com/11_2/on-premise/kerberos-mssql.html