Category Archives: Migration

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.

SSMS Report Error – Index(zero based) must be greater than or equal to zero and less than the size of argument list !

Posted on by

SSMS reports are one of the best ways to pull some great deal of Server level or Database level information within SQLServer.

Today I was running the ‘Disk Usage’ report (This one is my favorite) for one of the database and immediately got this error!

Error – Index (zero based) must be greater than or equal to zero and less than the size of argument list!

Well that’s one really cool error message to get when you are seriously working on something important!

 

Immediately I checked to see if the same report is causing issues for other databases which were available on the same instance.

The report was working great for all other databases except one which gave the error.

All the 4 ‘Disk Usage’ reports where having issues for that particular database, and I decided to compare this database with one for which the report was running fine.

Eventually the culprit was found. The database was having compatibility level 80 (SQL 2000) and was residing on a SQL Server 2005 SP4 instance. SSMS which I used to run reports was of version 10.50.2500 (2008 R2 SP1).

There are multiple T-SQL statements which are ran to pull this report,and this can be tracked by running a SQL-Profiler(Again, with caution for a production environment)

Changing the compatibility to 90 fixed the issue and the reports were running as expected.

Note – Changing the compatibility needs to be reviewed properly with the application owners as it can bring unexpected results. Proper testing and approval needs to be taken.

Conclusion –

Yet another reason to find all the databases which are running with lower compatibility and push application owners to fix their code where ever its required and move forward with latest compatibility levels.

For SQL Server 2012 compatibility level 80 is not supported, hence you won’t face these type of compatibility related issues anymore.

Know the dates,Product life cycle dates !

Posted on by

With SQL Server 2012 RTM GA on April 2012 some of you might have already started planning to migrate your database environments to 2012.

Migrating to SQL Server 2012 is a great idea from various angles and a right budget will ease your efforts too(Keeping in mind the increase in licensing costs for 2012)

Migration is one of key activities which will be triggered soon after any product launch,and one of the key information which you should be aware as a DBA or as a decision maker is to understand when the mainstream support is going to be over for the existing versions of SQL Server.

You should know the dates,and the most important part of knowing this information is that it will enable you to take the right decisions.

When is the mainstream support for SQL Server 2005 SP4 is going to be over? What is extend support phase?

You can get all these information right under your finger tips by reading the below post from CSS Engineers post

http://blogs.msdn.com/b/psssql/archive/2010/02/17/mainstream-vs-extended-support-and-sql-server-2005-sp4-can-someone-explain-all-of-this.aspx

Microsoft has provided a great portal where you can get information on product life cycle

http://support.microsoft.com/lifecycle/search/

Conclusion

Know the exact dates which will help you to drive forward with correct decisions.

Thanks for reading.