Troubleshoot And Migrate Ms Sql Computer Science Essay

Published Date: 02 Nov 2017

You have all the functionalties on the windows Azure Virtual machine Ms sql 2013 database that you would have on any physical server environment. The Ms SQL Server 2013 on the windows Azure virtual machine provides the functionalities like you will have the resources which are mentioned below:

Windows performance counter

Xperf

MS SQL Server 2013 DMVs

You can also have the functionality of the storage analytics. Storage analytics is a tool that is normally used for the logging and trobleshooting the performance of the network drivers against the windows azure storage.

How to Migrate the MS SQL Server 2013 to a windows Azure virtual machine?

If you have developed an Ms SQL Server 2013 application on the windows platform and now you want to use it on the Windows Azure virtual machine and you do not know how to do it. You can easily migrate an already developed MS SQL Server 2013 application to the windows Azure virtual machine and we will tell you how in this article. In this article we will tell you how you can easily migrate the on-premise MS SQL Server 2013 databases to the cloud using the MS SQL Server 2013 on the windows Azure virtual machine. We will also tell you the cases where it is most suitable to move the existing SQL Server 2013 application to the cloud or what is the best time to migrate the existing database application to the windows Azure SQL database.

Using MS SQL Server 2013 in the windows Azure virtual machine will help you in reducing the overall cost of the ownership of the development of the application. It also helps you in reducing the cost of the management and the maintenance of the enterprise applications by migrating these database applications to the windows Azure platform. One must be worried about the changes required after the migration of the database application to another platform but the migration of the MS SQL Server 2013 applications to the windows azure virtual machine requires no or minimal code changes. Using MS SQL Server 2013 in the windows Azure VM both the administrators and the developers can use the same development and the administrative tools that are provided by the on-premises platforms.

When to use the MS SQL Server 2013 in the windows Azure VM?

When you will be using the MS SQL Server 2013 on the windows Azure virtual machine csn give you many advantages over the MS SQL Server 2013 on the on-premises. The advantages of using the MS SQL Server 2013 on the colud are listed below:

Quick application development and testing

Virtualization platform total cost of ownership

Quick on-demand Scale

Data availability and mobility

We will discuss each of the above mentioned scenario in detail in the next article. This comparison will help you in clearly making the decision whether you will need to use the MS SQL Server 2013 in the windpws Azure or in the windows Azure virtual machine.

MS SQL Server 2013 in Windows Azure or in Virtual Machine

When to use the MS SQL Server 2013 in the windows Azure VM? Part 2

1.Quick application development and testing

You can easily develop an application that may require the validation in a production like environment. You will not need to purchase the new hardwares to develop the validation functionality and to test the new validation feature. You can simply use the MS SQL Server in the windows Azure VM. You will just need to create the virtual machine that can contain the MS SQL Server 2013 by using the platform image or either by uploading your own image. We have showed you how you can do so in the previous articles. After creating the new virtual machine you will have to connect to the new VM and then move your data and develop the new application, test it and fix the bugs if you found any.

2.Virtualization platform total cost of ownership

By moving the already developed application to the windows azure virtual machine you can cut down the cost of purchasing new hardwares. These hardwares might be required to run the increasing number of entriprise applications. When you have moved your application to the windows azure VM then you can leverage the MS SQL Server 2013 existing infrastruicture in the windows azure virtual machine.

3.Quick on-demand Scale

You will need the new computers, memory and network resources to handle the seasonal application usage peaks. You can save this extra expenditure that is for some specific time only by moving the MS SQL Server 2013 to the windows Azure virtual machine.

4.Data availability and mobility

By hosting the MS SQL Server 2013 databases in the windows Azure VM you can make them available for both the on-premise and the cloud applications.

How to choose between MS SQL Server 2013 in windows Azure Virtual machine and the windows Azure Ms SQL Database?

A list of the decision work flow is mentioned below which will help you in deciding when to choose the MS SQL Server 2013 in the VM or the Windows Azure MS SQL Database:

If you are developing new database application then make the decision of choosing the Windows Azure MS SQL Database or the MS SQL Server database in the Virtual machine after reading the following points:

If the MS SQL Server 2013 supports all the features then provision the new MS SQL Server 2013 database in the windows Azure. You can also develop the new MS SQL Server 2013 database application using the windows Azure SDK and plugins for the Visual studio, Java, Hypertext Preprocessor language (PHP) and Node js. You can deploy your database applications in the Windwos Azure and create the tables in the MS SQL Server database.

If the MS SQL Server does not support all the features and you do not want to do the re-design of your database application then provision a new database in the VM. You can deploy the database by using the MS SQL Server 2013 data tools. You can also manage, promote and monitor your database application by simply using the administration tools like the MS SQL Server Management Studio.

Choose Between MS SQL Server 2013 in Windows Azure or in Virtual Machine for the migration of the database

How to choose between MS SQL Server 2013 in windows Azure Virtual machine and the windows Azure Ms SQL Database? Part 2

In the previous article we discussed the solutions for the new databases however if you have the existing databases then you will need to identify the database which you want to migrate to the MS SQL Server in the Windwos Azure. After that you will have the following options:

You will need to convert the physical or the virtual machine to the Hyper V VHDs(Virtual hard disks). You can do so by using the virtual machine manager which can either be a physical to the virtual machine (P2V) or the virtual to the virtual (V2V) wizard. After that upload the virtual hard disk (VHD) files to the windows Azure memory by using the CS upload command line tool. After that you will be ready to create a new virtual machine by using the already uploaded VHD. You can now manage, upgrade and supervise the MS SQl Server database application by using the popluar adminsistartive tool like MS SQL Server management studio.

You can provision the new virtual machine with the MS SQL Server platform images or the application-tier cloud compute resource at the windows Azure management portal. You can deploy the database by using the MS SQL server data tools or the MS SQL Server management studio. Now migrate the existing application tier to the windows Azure project by using the windows azure SDK. You can also deploythe MS SQL Server database application to the windows Azure and then you can easily access your data on the cloud.

How to migrate the database schema and data to the MS SQL Server 2013 in the windows Azure virtual machine?

When you want to migrate your MS SQL Server 2013 database to the MS SQL Server in the windows Azure virtual machine. Make sure to follow the below mentioned steps completely and in the order they are specifed:

You will need to make the database schema and the data file on the on-premise by using the DAC, the backup or the detach. We will tell you in the next articles how you can make the database schema and data file. After you have created the file upload it to the instance of the MS SQL Server 2013 in the virtual machine.

You can also compress the file to reduce the size or encrypt it to make it safe before transmitting it to the windows Azure platform.

Now transmit your database schema, data and the log files to the windows Azure. If you use the CS upload you will need to place the files in the VHD; now upload the VHD to the windows Azure.

Now load the database schema to the MS SQL Server in the virtual machine.

Recreate any metadata that is not created by the migration tools on the MS SQL Server 2013 in the windows Azure virtual machine.

Prepare MS SQL Databae Schema and Data file

How to prepare the schema and the data on-premise and upload them to an instance of the MS SQL Server in the virtual machine?

In this article we will tell you how you can make the database schema and the data files on the on-premise. You will have several options that you may choose depending on your requirements:

Data-tier application. BACPAC or DACPAC files

Backup and restore

Detach and Attach

Other MS SQL Server techniques

1.Data tier application .BACPAC or .DACPAC files

To quickly and easily makethe database schema and data file you can use the Data-tier application that is also known as DAC. This database schema and data files are then transmitted on the on-premise windows Azure.

DACPAC file

A DACPAC file has the definations of all the MS SQL Server 2013 objects. These objects are the tables, views and other objects which are associated with the user’s database. This file focuses on the capturing and deploying the database schema which includes the upgradation to the existing schema. In the next article we will show you how to extract a DAC file from the database.

BACPAC file

A .bacpac file includes the database schema alongwith the data that is stored in the database. This file focuses on the capturing both schema and data. Logically it is same like the database backup and the bacpac cannot be used to upgrade the existing database. In the coming articles we will show you how to create a bacpac file.

A BACPAC file can be used to export the scheme and data to it. You can also import the schema and the data in the new MS SQL Server 2013 database on the host server. These importing and exporting of the data file and the schema is supported by the database management tools like the MS SQL Server management studio and the DACFx API. We will also show you how to import and export the BACPAC file to a new MS SQL Server 2013 database.

It is important to know that the DAC operations do not encrypt both the BACPAC and DACPAC files. To make the data transfer safe you should make sure that the communication between the on-premises and the windows azure is secure. To be on the safe side you can also encrypt the files separately so that the files get extra protected when the file is stored in the windows azure memory or on the the on-premise data drive. DAC file does not support full text catalogs.

MS SQL Server 2013 authentication logins are stored in the DAC file without a password. Whenevr the dac file is deployed or promoted the login is created as the disabled login with a password. If you want to enable the logins then you will need to lgin using the account that has the ‘Alter login’ permissions. Now use the ‘Alter login’ to enable the login account and assign password. This will not work with the windows authentication method because MS SQL Server does not have their passwords.

More Ways to create the MS SQL Server 2013 database Schema and data files

2. Backup and restore

You can use the backup and restore option to move the MS SQL Database 2013 to another instance of the MS SQL Server 2013. If both your MS SQL Server 2013 on-premise and the MS SQL Server 2013 in the VM have the same version (like in this case we have) you can simply copy the database backup file and restore it on the other server.

You should know that the backup and restore process is faster than the DAC process. You can also create the compressed backup file of the MS SQL Server 2013 data. You can use the existing popular adminsitartive tool for the backup process like MS SQL Server management studio alongwith other tools.

When you are about to move the MS SQL Server 2013 database to another MS SQL server instance then you will have to create the metadata of all the entities in the master and msdb on the destination server. We will explain this in our upcoming articles as how to manage the matadat while migrating the database on another server.

3. Detach and Attach

Detach and attch is another option that you can use formoving the MS SQL Server 2013 database to the server on the windows azure. You will need to copy the data which will be in the .mdf and the.ndf form and the log file which will be in the .ldf form to a folder in the VM. After copying the folder you will need to attach the database.

Some important things to know about detach and attach before migrating the database are:

When you are detaching a MS SQL Server database file it will remove the the file from the instance of the server but the database will be remained within its data and log files.

You will need to make the source database offline.

Detach and attach is suitable for upgrading databases and moving large databases.

It is important to know that you can not detach a MS SQL Server 2013 databse if any of the following is true:

There is the replica of the database which is also published.

A MS SQL Server database snapshot is present in the database.

The database mirroring session is mirroring the database that you want to detach.

The MS SQL Server database is suspect.

The required database is the system database.

It is appreciated if you take a complete new backup of the required database and restart the differential backups before detching the MS SQL Server 2013 database.

You can also use the data compression to compress the database before detaching and attaching the database to the new instance of the MS SQL Server 2013. This will help in reducing the size of the database.

When you attach the MS SQL Server 2013 database file on the new server you will need to create the metadata for the database to provide the consistent experience to the users and applications. This metadata can be the logins and the jobs on the new MS SQL Server 2013.