To ensure your SQL Server or Azure databases remain safe, you should backup your databases periodically. And it is important to store the backups in a reliable location. Microsoft SkyDrive currently offers 7GB free, Box offers 5GB free – both are reliable and it is simple to send your backups there. SQLBackupAndFTP in it’s latest version 9 added the option to backup to SkyDrive and Box ( in addition to local/network folder, NAS drive, FTP, Dropbox, Google Drive and Amazon S3). Just select the databases that you’d like to backup and select to store the backups in SkyDrive or Box. Below I will show you how to do it in details
Select databases to backup
First connect to your SQL Server or Azure Sql Database. Then select the databases you’d like to backup.
Connect to SkyDrive or Box cloud
If you have a free version of SQLBackupAndFTP Box destination is included, but SkyDrive destination will be disabled as it is available in the Standard version or above. Click “Try now” to get 30 days trial on all options
On the “SkyDrive Settings” form you’ll need to authorize SQLBackupAndFTP to access your SkyDrive. Click “Authorize…” to open SkyDrive authorization page in your browser, sign in your to SkyDrive account and click at “Allow” . On the next page you will see the field with an authorization code. Copy it to the clipboard. Box operation is just the same.
After that return to SQLBackupAndFTP, paste the authorization code and click “OK” .
After you are authorized, you can enter the path to a backup folder. SQLBackupAndFTP will create the folder if it does not exist.
That’s all what has to be done to backup to SkyDrive or Box cloud. You can now click on “Run Now” button to test this job.
Whatever is your preference for storing SQL backups, it is easy with SQLBackupAndFTP. Note that at the time of this writing they are running a very rare promotion on volume licenses:
5–9 licenses: 20% off
10–19 licenses: 35% off
More than 20 licenses: 50% off
Please let me know your favorite options for storing the backups
Microsoft has recently released the updated to Windows Azure Training Kit. Earlier this month they have updated the kit and included quite a lot of things. Now the training kit contains 47 hands-on labs, 24 demos and 38 presentations. The best part is that the kit is now available to download in two different formats 1) Full Package (324.5 MB) and 2) Web Installer (2.4 MB). The full package enables you to download all of the hands-on labs and presentations to your local machine. The Web Installer allows you to select and download just the specific hands-on labs and presentations that you need.
This Windows Azure Training Kit contains Hands on Labs, Presentations and Videos and Demos. I encourage all of you to try this out as well. The Kit also contains details about Samples and Tools. The training kit is the most authoritative learning resource on Windows Azure.
Microsoft SQL Azure labs has recently released Data Explorer client. I was looking forward to visualizing tool for quite a while and I am delighted to see this tool. I will be trying out this tool in coming week and will post here my experience. I have listed few of the resources which are related to Data Explorer at the end. Please let me know if I have missed any and I will add the same.
With “Data Explorer” you can:
Identify the data you care about from the sources you work with (e.g. Excel spreadsheets, files, SQL Server databases).
Discover relevant data and services via automatic recommendations from the Windows Azure Marketplace.
Enrich your data by combining it and visualizing the results.
Collaborate with your colleagues to refine the data.
Publish the results to share them with others or power solutions.
The Data Explorer Client package contains the Data Explorer workspace as well as an Office plugin that integrates Data Explorer into Excel.
Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction.
[TIP: Remember Key words On Demand, Scalable, Self-service, and Measurable. Now take the first word from each key word which gives us OSSM which can be pronounced as Awesome. Thus remember cloud computing is Awesome! Tip courtesy: Dave Nielsen]
3.How is SQL Azure different than SQL server?
SQL Azure is a cloud based service and so it has own set of pros and cons when compared to SQL server. SQL Azure service benefits includes on demand provisioning, high availability, reduced management overhead and scalability. But SQL Azure abstracts some details from the subscriber which can be good or bad which depends on the context of the need.
4.How many replicas are maintained for each SQL Azure database?
For each database, three replicas are maintained for each database that one provisions. One of them is primary replica. All read/write happen on primary replica and other replicas are kept in sync with primary replica. If for some reason, primary goes down, other replica is promoted to primary. All this happens under the hood.
5.How can we migrate from SQL server to SQL Azure?
For Data Migration, we can use BCP or SSIS. And for schema Migration we can use Generate Script Wizard. Also, we could use a Tool called SQL Azure migration wizard available on codeplex.
6.Which tools are available to manage SQL Azure databases and servers?
We can manage SQL Azure database using SQL server management server 2008 R2. Also, we can manage SQL Azure databases and servers through a Silverlight app integrated in Azure management portal.
7.Tell me something about security and SQL Azure.
SQL Azure service allows blocking a request based on its IP address through SQL Azure firewall. It uses SQL server Authentication mechanism to authenticate connections. Also connections to SQL Azure are SSL-encrypted by default.
8.What is SQL Azure Firewall?
SQL Azure firewall is a security mechanism that blocks requests based on its IP address.
9.What is the difference between web edition and business edition?
SQL Azure Web edition database Max Size is 5 GB whereas the business edition supports Max Size up to 50 GB. The size of a web edition database can be increased (/decreased) in the increments (/decrements) of 1 GB whereas the size of a business edition can be increased in the increments of 10 GB.
10.How do we synchronize On Premise SQL server with SQL Azure?
We could use a No code solution called DATA SYNC (currently in community technology preview) to synchronize on-premise SQL server with SQL Azure. We can also develop custom solutions using SYNC framework.
11.How do we Backup SQL Azure Data?
SQL Azure keeps three replicas of a database to tackle hardware level issues. To tackle user level errors, we can use COPY command that allows us to create a replica of a SQL Azure database. We can also backup SQL Azure data to local SQL server using BCP, SSIS, etc. but as of now, point in time recovery is not supported.
12.What is the current pricing model of SQL Azure?
Charges for SQL Azure consumption is based on 1) Size 2) Data Transfer.
13.What is the current limitation of the size of SQL Azure DB?
Maximum size of a SQL Azure database is 50 GB.
14.How do you handle datasets larger than 50 GB?
As of now, we have to build custom solution at application level that can handle scale out of underlying SQL Azure databases. But Microsoft has announced, SQL Azure Federations that will assist scaling out of SQL Azure databases. And scale out means that we are splitting the data into smaller subsets spread across multiple databases.
15.What happens when the SQL Azure database reaches Max Size?
Read operations continue to work but create/insert/update operations are throttled. You can drop/delete/truncate data.
16.How many databases can we create in a single server?
150 databases (including master database) can be created in a single SQL Azure server.
17.How many servers can we create in a single subscription?
As of now, we can create six servers under a single subscription.
18.How do you improve the performance of a SQL Azure Database?
We can tune a SQL Azure database using information available from execution plan and statistics of a query. We could use SQL Azure’s Dynamic Management views to monitor and manage SQL Azure database.
Also, SQL Azure performance is affected by network latency and bandwidth. Considering this, code near application topology gives the best performance.
19.What is code near application topology?
Code near application topology means that the SQL Azure database and the windows azure hosted service consuming the data are hosted in the same Azure datacenter.
[FYI: in the code far application topology, the app connects to SQL Azure from outside the Microsoft data center]
20.What were the latest updates to SQL Azure service?
Latest SQL Azure updates include multiple servers per subscription, SQL Azure co administrator support, creating Firewall rules for servers with IP detect.
21.When does a workload on SQL Azure get throttled?
When database reaches its maximum size update/insert/create operations get throttled. Also there are policies in place that does not allow to a workload to exploit a shared physical server. In other words, the policies make sure that all workload get a fair share of shared physical server. Now, a workload can get soft throttled that means that the workload has crossed the safety threshold. A workload can also got hard throttled which means that a SQL Azure machine is out of resources and it does not accept new connections. We can know more about what happened by decoding reason codes.
These were SQL Azure specific interview questions but remember that SQL server specific interview questions that we have seen so far can also appear in SQL Azure context since SQL Azure is “SQL server in the cloud”. Also questions specific to supported/unsupported features can be asked. I have not included the questions specific to unsupported features because it keeps on changing. Read general guidelines and limitations. Also note that SQL Azure is evolving very fast and it is important to keep a tab on the features that are added in service releases.
This is guest post by Paras Doshi. Paras Doshi is a research Intern at SolidQ.com and a Microsoft student partner. He is currently working in the domain of SQL Azure.
SQL Azure is nothing but a SQL server in the cloud. SQL Azure provides benefits such as on demand rapid provisioning, cost-effective scalability, high availability and reduced management overhead. To see an introduction on SQL Azure, check out the post by Pinal here
In this article, we are going to discuss how to extend SQL Azure with the Azure worker role. In other words, we will attempt to write a custom code and host it in the Azure worker role; the aim is to add some features that are not available with SQL Azure currently or features that need to be customized for flexibility. This way we extend the SQL Azure capability by building some solutions that run on Azure as worker roles. To understand Azure worker role, think of it as a windows service in cloud. Azure worker role can perform background processes, and to handle processes such as synchronization and backup, it becomes our ideal tool.
First, we will focus on writing a worker role code that synchronizes SQL Azure databases.
Before we do so, let’s see some scenarios in which synchronization between SQL Azure databases is beneficial:
scaling out access over multiple databases enables us to handle workload efficiently
As of now, SQL Azure database can be hosted in one of any six datacenters. By synchronizing databases located in different data centers, one can extend the data by enabling access to geographically distributed data
Let us see some scenarios in which SQL server to SQL Azure database synchronization is beneficial
To backup SQL Azure database on local infrastructure
Rather than investing in local infrastructure for increased workloads, such workloads could be handled by cloud
Ability to extend data to different datacenters located across the world to enable efficient data access from remote locations
Now, let us develop cloud-based app that synchronizes SQL Azure databases. For an Introduction to developing cloud based apps, click here
Now, in this article, I aim to provide a bird’s eye view of how a code that synchronizes SQL Azure databases look like and then list resources that can help you develop the solution from scratch.
Now, if you newly add a worker role to the cloud-based project, this is how the code will look like.
(Note: I have added comments to the skeleton code to point out the modifications that will be required in the code to carry out the SQL Azure synchronization. Note the placement of Setup() and Sync() function.)
Enabling SQL Azure databases synchronization through sync framework is a two-step process. In the first step, the database is provisioned and sync framework creates tracking tables, stored procedures, triggers, and tables to store metadata to enable synchronization. This is one time step. The code for the same is put in the setup() function which is called once when the worker role starts.
Now, the second step is continuous (or on demand) synchronization of SQL Azure databases by propagating changes between databases. This is done on a continuous basis by calling the sync() function in the while loop. The code logic to synchronize changes between SQL Azure databases should be put in the sync() function.
Discussing the coding part step by step is out of the scope of this article. Therefore, let me suggest you a resource, which is given here. Also, note that before you start developing the code, you will need to install SYNC framework 2.1 SDK (download here). Further, you will reference some libraries before you start coding. Details regarding the same are available in the article that I just pointed to.
You will be charged for data transfers if the databases are not in the same datacenter. For pricing information, go here
Currently, a tool named DATA SYNC, which is built on top of sync framework, is available in CTP that allows SQL Azure <-> SQL server and SQL Azure <-> SQL Azure synchronization (without writing single line of code); however, in some cases, the custom code shown in this blogpost provides flexibility that is not available with Data SYNC. For instance, filtering is not supported in the SQL Azure DATA SYNC CTP2; if you wish to have such a functionality now, then you have the option of developing a custom code using SYNC Framework.
Now, this code can be easily extended to synchronize at some schedule. Let us say we want the databases to get synchronized every day at 10:00 pm. This is what the code will look like now:
Don’t you think that by writing such a code, we are imitating the functionality provided by the SQL server agent for a SQL server? Think about it. We are scheduling our administrative task by writing custom code – in other words, we have developed a “Light weight SQL server agent for SQL Azure!” Since the SQL server agent is not currently available in cloud, we have developed a solution that enables us to schedule tasks, and thus we have extended SQL Azure with the Azure worker role!
Now if you wish to track jobs, you can do so by storing this data in SQL Azure (or Azure tables). The reason is that Windows Azure is a stateless platform, and we will need to store the state of the job ourselves and the choice that you have is SQL Azure or Azure tables.
Note that this solution requires custom code and also it is not UI driven; however, for now, it can act as a temporary solution until SQL server agent is made available in the cloud. Moreover, this solution does not encompass functionalities that a SQL server agent provides, but it does open up an interesting avenue to schedule some of the tasks such as backup and synchronization of SQL Azure databases by writing some custom code in the Azure worker role.
Now, let us see one more possibility – i.e., running BCP through a worker role in Azure-hosted services and then uploading the backup files either locally or on blobs. If you upload it locally, then consider the data transfer cost. If you upload it to blobs residing in the same datacenter, then no transfer cost applies but the cost on blob size applies. So, before choosing the option, you need to evaluate your preferences keeping the cost associated with each option in mind.
In this article, I have shown that Azure worker role solution could be developed to synchronize SQL Azure databases. Moreover, a light-weight SQL server agent for SQL Azure can be developed. Also we discussed the possibility of running BCP through a worker role in Azure-hosted services for backing up our precious SQL Azure data.
Thus, we can extend SQL Azure with the Azure worker role.
But remember: you will be charged for running Azure worker roles. So at the end of the day, you need to ask – am I willing to build a custom code and pay money to achieve this functionality?
I hope you found this blog post interesting. If you have any questions/feedback, you can comment below or you can mail me at Paras[at]student-partners[dot]com
I was recently reading on the subject SQL Azure Throttling and Decoding Reason Codes and end up reading the article over here. What I really liked is the explanation of the subject with Graphic. I have never seen any better explanation of this subject.
I really liked this diagram. However, based on reason code one has to adjust their resource usages. I now wonder do we have any tool available which can directly analysis the reason codes and based on it gives output that what kind of the throttling is happening. One of the idea I immediately got that I can make a Stored Procedure or Function where I pass this error code and it gives me back right away the throttling mode and resource type based on above algorithm.
Any one has the T-SQL code available for the same?
SQL Server has feature which copy database from one database to another database and it can be automated as well using SSIS.
Make sure you have SQL Server Agent Turned on as this feature will create a job. The same job will execute the task. Make sure that SSIS is properly configured as well with necessary security permissions. You can automate this process as well control error logging.
Following are the steps to copy database from one instance to another instance.
Specify Source Server
Specify Destination Server
Here you can select option if you want to keep the database ONLINE when it is being copied.
You can also select option of MOVE or COPY database as well.
Give appropriate database name.
On this screen you can select additional options to copy as well.
You create the package over here.
You can schedule the package using SQL Server Agent.
When this process is over it will show the success message and database will be copied to another server.
You can see how easy is the process to copy the database to another server.
Watch SQL in Sixty Seconds Episode on same subject.
When talking about SQL Azure the common complain I hear is that the script generated from stand-along SQL Server database is not compatible with SQL Azure. This was true for some time for sure but not any more. If you have SQL Server 2008 R2 installed you can follow the guideline below to generate script which is compatible with SQL Azure.
As above images are very clear I will not write more about them. SQL Azure does not support filegroups. Let us generate script for any table created on PRIMARY filegroup for standalong SQL Server and compare it with the script generated for SQL Azure.
You can clearly see that there is no filegroup in the code generated for SQL Azure. Give it a try and please your comment here about what do you think about the same.
SQL Server and SQL Azure are two Microsoft Products which goes almost together. There are plenty of misconceptions about SQL Azure. I have seen enough developers not planning for SQL Azure because they are not sure what exactly they are getting into. Some are confused thinking Azure is not powerful enough. I disagree and strongly urge all of you to read following white paper written and published by Microsoft.
SQL Azure Database is a cloud-based relational database service from Microsoft. SQL Azure provides relational database functionality as a utility service. Cloud-based database solutions such as SQL Azure can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. This paper compares SQL Azure Database with SQL Server in terms of logical administration vs. physical administration, provisioning, Transact-SQL support, data storage, SSIS, along with other features and capabilities.
The content of this white paper is as following:
Similarities and Differences
Logical Administration vs. Physical Administration
Features and Types
Key Benefits of the Service
Familiar Development Model
Relational Data Model
The above summary text is taken from white paper itself.