Migrate SQL Server Database to Azure SQL Database using Data Migration Assistant – final steps

The first two steps can be found at http://thedataengineer.com/2018/08/29/migrate-sql-server-to-azure-sql-server/

Part 3: Migration Assistant

Open the Data Migration Assistant and click on +New to start a new Migration

Choose Migration

Fill in the project name and keep the defaults for the other options and click Create


On the first screen, fill in the Server name and make your choice connection properties and click connect. Reminder the credentials used to connect must have Control Server permission


After connecting, choose the Source database. Keep Assess the database before Migration checked and click next


On the select target screen you have the option to either use an existing server to connect to and choose the target database or you can click on Create a new Azure SQL Database, which will take you to the Azure portal to create the new database.

I went out to the Azure portal and created a new server and database for the migration. I then came back in to the DMA window and filled out the info requested and clicked connect. I am also using SQL Server Authentication to connect.


Choose the database that is the target and click next


Select the objects you want to migrate

Check the Assessment and determine if you can fix the issue or uncheck the issue if you wish to not migrate the blocks


I also had an issue that I could not migrate some of the users, as they were in the form domain\user I just left these unchecked and will manually add any users to the new Azure SQL Server Database


Click generate sql script. This may take a little time to run

Once completed, review the script. When you are satisfied with the script, click deploy schema.


The following screen will appear


On completion, check for errors and determine if you can move on. Once you are satisfied with the schema deploy, click Migrate Data


Check that all of your tables are marked as OK. Also, you can see at the top, that Microsoft strongly recommends that you temporarily change your Azure SQL database to performance level P15during the migration process. This database that I am migrating is so small, that I am going to leave it as is. Click Start data migration


The in-progress screen appears. You can also monitor some of the activity in the Azure portal. For instance, Resource Utilization and used space


Once the data migration has completed you will see at the bottom right that Migration is complete and the Duration


I then connected to the Azure SQL database using SQL Server Management Studio and ran some comparisons to the original source and detected no differences.

Reminder, add any users in to the Azure SQL Server Database that were not automatically migrated.

You have now completed the migration.

 

 

 

Migrate SQL Server Database to Azure SQL Database using Data Migration Assistant

We will be using the Data Migration Assistant to assess and then migrate an on-prem SQL Server 2012 SP2 database to an Azure SQL Database

Part 1: Download and install Data Migration Assistant

To install DMA, download the latest version of the tool from the Microsoft Download Center, and then run the DataMigrationAssistant.msi file.

Click next

Accept the license agreement

Click install

Wait for the install to complete

Check the box beside Launch Microsoft Data Migration Assistant and click Finish

Part 2: Open Data Migration Assistant and create assessment

We will now start with assessing our on-prem SQL Server

  1. Click the +New icon and select Assessment project type
  2. Give your assessment a name
  3. We will leave the Source server type and Target server type as is.
  4. Click create

I left the defaults checked and clicked next

Fill in the server name and authentication type. *Notice that the credentials used to connect must be a member of the sysadmin role

Click connect

Choose the sources and click add. I am only interested in the CentralDB database at this time

If you are satisfied with the sources that you have added, click Start Assessment

Obviously, the assessment run time depends on how many and what size of the sources

When the assessment completes

You can see the results on the screen or you can export the report as a json or csv file.

In this example, you can see that there are 3 Unsupported features and 1 Partially supported feature.

With each issue the details section will give the impact. Recommendation and more info

One last screenshot to show the assessment options

You can open, restart or delete an assessment

In part 3, we will look at Migrating a SQL Server database to Azure SQL Database.

 

 

 

 

Overview of Connecting to Azure Analysis Services

With Azure Analysis Services Firewall off.

As admin account – Log in to Azure

Deploy model

Connect with SQL Server Management Studio from laptop

With admin account – 3 options for connecting

Does not work

Does not work

Using MFA does work – screen pops up for password and then another for text message before gaining access. – we can see the model as well, since this account is an Analysis Services Admin

As my non-admin account same three options for connecting

Does not work

Works but am just typing password in

Works but connects just asking for password, not asking for mfa, however does not have access to any databases as they are not marked as an admin. They will need a role assigned


Connect from Power BI

Using non-admin account

Using admin account – asks for password and MFA text

As admin create a read role in the model

Add non admin to read role

Try again to connect from Power BI using non admin account

Connection works with no MFA

 

 

 

Geo-Replication in Azure

I was recently asked to look at creating a read-only copy of a SQL Server database in China, with the source being in the UK. . For on-premise, to get AlwaysOnAvailabilty with the replica being a read only copy we would need to be running Windows FailOver Clustering Server version and the database would need to be Enterprise Edition. However, this is easily set up in Azure, using Geo Replication and copies would only be seconds apart in replication.

I decided to do some testing. Geo replication is available at all price points in Azure, so for testing, I spun up a database in the Basic Pricing tier in the West US region. Once the database was available, I created a simple table with 4 rows of data and then confirmed the data existed. The next step was to go back in to Azure and under the newly created database resource, I clicked Geo-Replication. This opened another window that allowed me to choose a location. The picture above, shows all of the available locations. The blue hexagon is your primary database and the green hexagons are the available replication locations. For this one, I chose East Asia. The cost for this Geo-Replicated readable copy is the same as the primary copy. So in my case, each database was $5 per month.

 

The Geo-Replicated database will have the same database instance name, but will have a different server name.

As part of creating the new readable database, Azure will copy all of the data that is in the primary to the readable. Since I didn’t have much data, this process only took a minute or so. Once I got the notification that the readable was created, I connected via SSMS and was able to see the data. I then did some basic testing of deletes, inserts and updates. Obviously, the amount of data will make a difference, but as for speed, as soon as I hit execute in the primary database window and refreshed my select on the readable copy, the changes were there.

If you are looking for a read only copy of a SQL Server database, I definitely recommend Azure Geo-Replication. The whole process took less than 10 minutes to set up.

 

 

Creating a RunAsAccount in Azure for Azure Automation

Warning: We have three different people with three different levels of security in our Azure tenant and only one of us could create this account.

Person 1: Is the owner of a Resource group and can create items in the resource group, including the Automation Account and Runbooks, but can not create the RunAsAccount

Person 2: Is the tenant admin and can do what Person 1 can do and a lot more, but does not have permissions to create a user in Azure AD, so can not create the RunAsAccount either.

Person 3: Has all of the permissions as person 2, but is also able to create users in Azure AD, so he was the only one that was able to create the RunAsAccount.

Below are the steps that he followed to create the account

1. In the Azure portal, click All resources. In the list of resources, select the Automation account from the list of Automation accounts.

2. In the left-hand pane, select Run As Accounts under the section Account Settings.

3. Select Azure Run As Account. After selecting the Add Azure Run As Account, a pane appears and after reviewing the overview information, click Create to proceed with Run As account creation.

4. While Azure creates the Run As account, you can track the progress under Notifications from the menu. A banner is also displayed stating the account is being created. This process can take a few minutes to complete.

Since we only wanted the RunAsAccount to have permissions to certain Resource groups and not the whole subscription, we followed the steps below.

Limiting Run As account permissions – To restrict what the RunAs service principal can do, you can remove the account from the contributor role to the subscription and add it as a contributor to the resource groups you want to specify.

1. In the Azure portal, select Subscriptions and choose the subscription of your Automation Account. Select Access control (IAM) and search for the service principal for your Automation Account (it looks like _unique identifier). Select the account and click Remove to remove it from the subscription.

2. To add the service principal to a resource group, select the resource group in the Azure portal and select Access control (IAM). Select Add, this opens the Add permissions page. For Role, select Contributor. In the Select text box type in the name of the service principal for your Run As account, and select it from the list. Click Save to save the changes. Do this for the resources groups you want to give your Azure Automation Run As service principal access to.

 

 

 

2018’s list of goals

Many items peak my interest and that can sometimes make it difficult to choose a few that I would like to gain a deeper learning of, but I think my current role will guide me in 2018.

In no particular order:

SQL Server on Linux: This is a no brainer. As long as I can remember, I have preferred to work with command line over gui based tools. I’ve always felt like I have more control over what I am doing. And now that SQL Server is on Linux, I want to dig in and learn whatever I can. I think I also like the idea, that there aren’t a lot of SQL Server DBA’s that are familiar with Linux.

Cloud: I’ve played around in the cloud for years, but with little reason. Between Azure options and the Oracle Cloud, it’s time to start looking at the differences and see where each can help me.

Power BI: With Power BI Premium, I think this is a game changer for Corporate adoption.

Make it to PASS Summit: I’ve been to Oracle Open World 3 times, but do to job responsibilities or other co-workers training needs, I have unfortunately never made it to PASS. I don’t mind Open World, but more and more I can see PASS being more beneficial.

Combination of Both Worlds

I just recently came back to my blog and noticed that my last post stated that I was going back to Oracle. I did, but the fit with that employer was not the best and I moved on to another company at the beginning of 2016. I couldn’t be happier with that move in 2016. I get to work with SQL Server and Oracle along with other software packages. Some items that I am working on or researching and would like to blog about in the future, Automic Scheduling software, SQL Server on Linux, Azure, Azure Analysis Services, Business Intelligence, Power BI, Oracle Data Guard, Oracle Cloud and other various items.