Tag Archives: SQL2012

Moving a database from on-premise to the cloud – Step by Step Walkthrough

Posted on by

Cloud is the future and moving there is now as easy as few mouse clicks.

Today we will see how an On-premise database can be moved to a SQL Server instance running up on a Windows Azure Virtual Machine.There are multiple ways to accomplish this and in this post we will see how this can be achieved using SSMS wizards.

Step 1

I have a database named “MoveMeToCloud”,and this is one who is ready to take the flight and move to the cloud. This is a database which is deployed on my on-premises SQL Server Instance.

The first process is to export this database. For that purpose we will choose Tasks —

Export Data-tier Application

Cloudmove1

As always an introduction wizard is opened up, and you can proceed by choosing next.(Also can say do not show this again !)

Cloudmove2

Step 2

In this step we will connect Windows Azure Storage Account and we will export the BACPAC directly to the cloud storage.

Cloudmove3

When we connect the Azure storage account we will need to provide the account name and the key. Manage Access Keys under the Azure portal(Storage section) will get this info for us.

Cloudmove33

Cloudmove4

Step 3

Once the storage account connection is established we can proceed and finish this export activity.

Cloudmove5

The wizard will do multiple checks, and the process will complete eventually(Provided all the conditions are met).

I will talk about the conditions in a different post.

Cloudmove6

Step 4

Once the export is completed successfully we can check and verify that the BACPAC is indeed available under Windows Azure Storage Account

Cloudmove7

Step 5

As the BACPAC is now local to the cloud we can now proceed and do an Import of that file.

We will connect to a Windows Azure Virtual Machine which is running SQL Server 2012 up in Azure to do the import.

We will choose Import Data Tier Application to initiate the import.

Cloudmove8

We will need to provide the storage account details under the import settings

Cloudmove9

Once the connection is established correctly we can proceed and choose the database settings

Cloudmove10

Database settings will allow us to place the data file and log file according to our requirements.

We can proceed and finish the wizard to complete the import activity.

Cloudmove11

Cloudmove12

Step 6

Once the import is completed we are good to access the database up in the cloud.

SSMS Cloud

Thanks for reading and keep watching this space for more.

Advertisement

Linked Server created using a script is missing Product name and Data source fields !

Posted on by

Today I will discuss about an issue which was observed while creating a linked server via script.

The existing linked server was scripted out from a SQL2005 instance and the same script was used to create a linked server under a SQL2012(SP1/CU2) instance.

This a common procedure to follow if you are doing a side by side upgrade of SQLServer and ensure you have all objects in place under the new server.

After running the script the linked server was created successfully, however important details like Product name, Data source fields were found missing.

Here is the view which will explain the problem –

Linked Sever Issue Missing

The newly created linked server will work just fine, and users wont be facing any issues other than the missing details.

This looked like a bug to me right away and I opened a case with Microsoft.

Connect Item details – https://connect.microsoft.com/SQLServer/feedback/details/782793/linked-server-created-using-a-script-is-missing-product-name-or-data-source-fields

This is not a critical bug which will impact anyone badly, so I have a strong feeling that Microsoft might set low priority for this item and a fix might come only during the next release.

Conclusion

This is not an issue which is major, however details like Product name, Data source available to view comes handy if you need to trouble shoot issues related to linked server.

Apply CU2 for SQL2012 SP1 – Fix for Windows Installer starting repeatedly and causing CPU spikes !

Posted on by

CU2 for SQLServer 2012 SP1 is one key update which you should review and deploy if found necessary. I have found this very much needed for all my SQL2012 SP1 deployments. This CU contains a very important fix(KB2793634).

Note – The choice to deploy CU2 or just the fix (KB2793634) depends on your environment needs.

Here is why you need this fix –

As soon as you deploy SP1 for SQL2012 the Windows Installer (Msiexec.exe) process is repeatedly started to repair certain assemblies. Additionally, the following events are logged in the Application log:

EventId: 1004 Source: MsiInstaller Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’, Component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource”does not exist.

EventId: 1001 Source: MsiInstaller Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’

Source  – http://support.microsoft.com/kb/2793634

Here are the screenshots of the application log. These are shown as warnings, and could easily be missed.

InstallerIssue2

InstallerIssue1

When this occurs there is a spike in CPU.

This process is repeated every 2 minutes and can cause heavy registry usage issues.

Registry issues can cause multiple failures and a sample one is

InstallerIssue3

Note – Again, this varies from environment to environment.

Conclusion

Deploying the fix or the CU will resolve the issues related to the installer process.

Thanks for reading and keep watching this space for more.

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

Posted on by

The all new Windows Azure platform was announced yesterday by Scott Gu(CVP,Windows Azure Application Platform) and an online event was hosted here at http://www.meetwindowsazure.com/

You can watch the recorded keynote on demand here at http://www.meetwindowsazure.com/Conversations

There were lot of new features announced yesterday, and the whole event was packed with heavy and exciting demos.

You can refer the blog post written by ScottGu to get a feel of what Windows Azure is going to offer during the coming days.

Another good blog post written by Bob Kelly explains the new services and changes for the existing services can be read here

The new portal looks really cool. It’s all HTML 5 and metro style enabled. Let’s see what Paras Doshi(B/T) has to say about the portal here.

I decided to start playing with the portal to understand more about the offerings, and as always my first attention was towards SQL Database.

Windows Azure SQL Database is a relational database service offered on the Windows Azure platform.Its based on SQL Server 2012.

Here is my experience creating the first ever SQL Database on Azure –

Portal View

The interface is really neat with metro style layouts.

SQL Database Creation(Initial Steps)

There is a wizard to guide you to start creating the database, and there are two options Quick Create and Custom Create

I decided to go with Custom Create as that option will give me better control on what I am doing.

The wizard comes again with cool interface asking me to enter the Name, Edition, Max Size, Collation, Server details.

There are 2 edition selection options  WEB and BUSINESS.Web edition will allow max size of the database as 5 GB and Business edition allows max size of the database as 150 GB.

I am using the evaluation subscription, so I decided to choose the web edition with 1GB limit.

I also entered the New SQL Database Server option as this was the first time I am creating a database, and I had setup no server for this subscription.

The next option was to add a Login (Only SQL login option is available), password, confirmation and region where you want the server to be deployed.

Confirmation for SQL Database Availability

In a matter of seconds (3 or 5 !) the database was up and running on cloud.

Conclusion


The new Windows Azure platform is really powerful, flexible and friendly and over the next few days I will write more about my experience working with SQL Databases and much more.

Keep checking this space for more !

Thanks for reading.