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.

 

 

 

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.

 

 

Loading Linux command line logs in to Windows SQL Server

We recently decided, for auditing reasons, that we would like to keep a record of all of the commands that were run on our linux machines and because of separation of duties, we would load those logs in to a SQL Server instance on Windows. Funny how I feel the need to avoid confusion and make sure I say were the SQL Server instance is.

1. First, to be able to get the files I needed to be able to ssh to the linux servers. The easiest way to do this was using Putty

I have separated out the two scripts. First one being the script to download the logs to Windows server and the second script is to load logs to SQL Server

2. Download logs to Windows Server

Since I am connecting to numerous linux servers, I am using a file to store the user names, passwords and server names and will iterate through each line in the file to connect and get the logs.

The files manager.txt and others.txt only contain the password used by that user.

Example server name file

 3. Script to get server names, user names and passwords

 

4. Create SQL Server table with the following columns – filename, command, workingDirectory, server, username and date

5. Load logs in to SQL Server

Example log file from Linux – As you can see, all of the commands came in as comma separated, but they were all on one line.

 

Using the Get-Content command changed the file to a usable format, we used the replace command to remove the space and the command number and we used Set-Content command to save this as a txt file

 

6. SQL Server table

 

 

 

Using alias to upgrade old servers

One trick that I have used numerous times and really like is to use a server alias when moving to new servers. For instance, we were moving numerous databases from a 2000 server named serversql0001. Since this server was so old, we had no real idea how many places or connections people may have used the alias, so we decided during the downtime to remove this alias and point it to the new server that we were moving the databases to. After we brought the new server up, we checked some of the software that was connecting to the databases, and as expected, everything was fine, with no need for connection changes.

Connect to SQL Server when you don’t know SA account or don’t have sysadmin access

Below are the steps you need to perform to grant SYSADMIN access to a user in SQL Server in case you are completely locked out.

1. Download PSexec to connect using SQL Server Management Studio using the NT Authority\System

Download PsTools from https://download.sysinternals.com/files/PSTools.zip
Unzip the content and copy PsExec.exe to C:\Windows\System32

2. Stop the SQL Server and SQL Server Agent services on the server.

3. Open a cmd prompt window as administrator and navigate to SQL Server’s Binn directory. You may need to adjust your path based on your install location.

ex. C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn

4. Once you are in SQL Server’s Binn directory run the ‘sqlservr -m’ command to start SQL Server in single user mode as shown below. Had to add the location of the ERRORLOG

sqlservr -m -e C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Log\ERRORLOG;

If it’s a named instance:
sqlservr -m -s <instancename> -e C:\Program Files\Microsoft SQL Server\MSSQL11.SERVICECORE\MSSQL\Log\ERRORLOG;

After the SQL Server instance was started in single user mode, I was receiving logon errors form other users that were trying to logon from the application, when it was restarted in single user mode. I ignored the errors and moved on.

5. Execute PsExec – may need to adjust the ” marks and file locations

run cmd as administrator

PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”

The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will be pre-populated with NT AUTHORITY\SYSTEM

6. Click Connect and then go in to Security > Logins and add your account as a sysadmin

7. restart the sql server services

 

 

 

Oracle OEM details in PowerBI


Instead of filling out excel files with all of our Oracle database details, I decided to use Power BI to organize that information.
Prerequisites

  •  Must have Oracle client installed on computer
  • Must have a tnsnames.ora file to connect to Oracle database
  • Connecting to Oracle Database


The server entry is actually the entry in your tnsnames. Example below

Also fill in the select statement for the view that you want



In the next window choose database and then supply database credentials

A few of the OEM tables that I find interesting
mgmt.$hw_nic
mgmt.$jobs
mgmt.$os_hw_summary
mgmt.$target
mgmt.$target_properties

Going back to Oracle

I am thrilled to say that after 4 years of working with Microsoft SQL Server and the BI Tool Stack, I am going back to working with Oracle. I enjoyed working with Microsoft and getting to add another tool in my tool belt, but I am very excited about this new opportunity.

Hopefully this opportunity will lend itself to more blog posts.

Stay tuned for more details.

Being lucky enough to go from DBA to Microsoft Business Intelligence

So far in my career I have been lucky enough to work with some really hot products. I started out of school as an Oracle DBA, while also performing SAP Basis duties. I did this for about 3 years, when I decided it was time to try something new. What I didn’t know at the time was that my Director did not want to lose me as a resource.

Our company was also starting to migrate our current Oracle and Cognos Data Warehouses to a Microsoft Data Warehouse. With my background as a DBA, they felt it was the next logical step in my career and they were right. I started in the group taking on SQL Server DBA tasks, while gaining the necessary skills with the Microsoft BI Stack. Looking back this was such an amazing opportunity. I was able to be an integral part of building the new warehouse from scratch. I had some brilliant co-workers, that were so willing to help me learn. I also had the opportunity to work with some really knowledgeable consultants from Pragmatic Works. A side effect to our company moving from the previous data warehouse to the new data warehouse was adopting Agile development. I had the opportunity to attend many BI related and Agile related trainings.

And now after leaving that company to join my current employer, I have continued to expand my BI skills. I am once again back to doing a lot of the SQL Server DBA tasks, but also learning more aspects of the process. I am still using Agile and the BI stack, but I’ve had the ability to bring my ideas to another warehouse conversion while also learning new approaches to migrations,  development and architecture.

My future goals are to become MCSE Certified and work towards an Architect position. At some point, I would also like to become a Microsoft MVP.