First summary of previous articles:
Part 1: We defined SQL Azure and discussed advantages of SQL Azure
Part 2: We created an Azure account and created our very first SQL Azure database
Part 3: We discussed about the provisioning and the billing model of SQL Azure
Part 4: We discussed the SQL Azure architecture
Part 5: We discussed the SQL Azure security model
Part 6: We discussed how to migrate Databases to SQL Azure
Part 7: We discussed how to improve performance of SQL Azure DB and options for planning backup and restore strategies.
Part 8: We discussed administrative tasks related to SQL Azure.
Part 9: we discussed about Developing SQL Azure applications.
Now in this module, we are going to discuss about two SQL Azure features namely SQL Azure Data SYNC and SQL Azure Reporting. Both of these features are currently in CTP (Community Technology Preview) as of this writing. So let’s get started. First, we will discuss about SQL Azure Reporting and then we will discuss SQL Azure Data SYNC.
SQL Azure Reporting
So, what is SQL Azure Reporting and how does it relate to SQL Azure? Well, SQL Azure is a cloud based database service and SQL Azure Reporting is a reporting service that let’s you extend reporting to the cloud. Moreover, analogically speaking, if SQL Azure is equivalent to SQL Server then, SQL Azure Reporting is equivalent to SQL Server Reporting Services (SSRS).
So what is the benefit of reporting service on the cloud? The benefit of a reporting service on the cloud is that it conceptually removes the management overhead of installing or maintaining reporting infrastructure. In addition, since it leverages the highly available and scalable Azure environment, SQL Azure Reporting can scale to meet the demands of business as and when required.
So which tools are supported by SQL Azure reporting?
Answer: Business Intelligence Development Studio (BIDS) and Report Viewer Control in Visual Studio.
The great thing about the SQL Azure team is that they have strived to keep the symmetry between SQL Server and SQL Azure. Therefore, we do not have to learn new tools and our existing skills are easily transferable.
Can we embed reports to the application?
Yes, we can.
A SQL Azure Reporting allows us to embed reports to application. In addition, with SQL Azure Reporting, we can allow URL access. That means if the URL of the report is known, then the report can be viewed in the browser.
For what data sources SQL Azure Reporting can consume data. As of now, in CTP, only one data source is supported which is SQL Azure.
Now, having got the basic knowledge of SQL Azure Reporting, let’s build a report in Business Intelligence Studio (BIDS 2008) that has Adventure Works LT for SQL Azure as it’s data source and then deploy it to a SQL Azure Reporting Server. Here is the step-by-step procedure.
I assume you have BIDS installed on your local computer and you are familiar with creating reports in SSRS. In this step-by-step tutorial, we will create a simple report as our main aim is to introduce you to SQL Azure Reporting and not go into details of art of creating reports.
Go to Windows Azure Management Portal and go to “reporting” section. If you have not done so before, then create a SQL Azure Reporting Server.
After agreeing to Terms and Conditions, you will be asked to select the subscription and the server region, please do so and click on Next.
Now, you will be asked to select the administrator user name and password, please do so and click on Finish.
Now you go to the SQL Azure Report Server home, which looks like:
Now, we have a server that can host our reports. So let us create a report now via BIDS 2008. Note that I would be creating a simple report for the demonstration purpose and I already have an Adventure Works in SQL Azure database. If you wish to install this sample database on your SQL Azure Server, please refer http://beyondrelational.com/blogs/parasdoshi/archive/2011/05/23/let-s-install-an-adventure-works-lt-database-on-sql-azure.aspx.
So Now, let’s open SQL Server Business Intelligence Development studio and go to File > New > Project > SQL Server Project Wizard.
Click on Ok. Now, you will get a ‘Welcome to report wizard’. Click on Next. Now select a data source. Here note that as of now, SQL Azure Reporting only supports SQL Azure as of now, so please select SQL Azure as your data source:
Now click on Edit. You will be asked to enter the credentials. Please do so and test connection:
Click on Ok and the next dialog box is the ‘query builder’. You can go into the ‘Query Builder’ and if you do so then BIDS will launch a “query designer” for you. After getting the query string, please press on Next.
We will go with the “tabular” report type and the leave the “group the data in the table” as default. Then I chose the table style as Ocean. Then you will get the summary page. Review the summary, name the report, and then click on Finish.
Now we wish to deploy it on SQL Azure Report Server. So right click on “SQLAzureReportDemo” and select properties. Here in the Target Server URL, you will need the SQL Azure Report Service URL, which is shown in step 4. It takes the form: https://<server name>.reporting.windows.net/reportserver
Then “Deploy” the project. Optionally, you deploy a single report if you wish.
Once you click on deploy, you will be asked to login to reporting server login.
On successful deploy, you will see a message in the output tab.
Now you can go to the URL mentioned in the URL and login using the reporting server credentials:
Once logged in, browse and select the project. Here, select the report that you may want to see:
And here is the report:
Therefore, that’s about it. The step-by-step tutorial ends here. We saw how to build a report in Business Intelligence Studio (BIDS 2008) that has Adventure Works LT for SQL Azure as its data source and then we deployed it to a SQL Azure Reporting Server.
Also, note that you can embed this report in an application. To know more about SQL Azure Reporting visit
SQL Azure Data SYNC
SQL Azure Data SYNC is a service that lets you synchronize SQL Server Database with SQL Azure Databases. It also enables us to synchronize SQL Azure Databases. It lets you schedule the synchronization. It also supports bi-directional as well as uni-directional synchronization. This feature is build on top of Microsoft Sync Framework. But having an out-of-the-box solution from SQL Azure makes our task of synchronization of databases a lot easier. Therefore, before we see how to go about using this technology – here are couple of scenario’s that will give you a taste of the usefulness of SQL Azure data sync.
- Extend local infrastructure to cloud that enables efficient data access from remote locations via leveraging multiple SQL Azure data centres.
- You can spread the workload over multiple databases which are synchronized using SQL Azure Data SYNC
Now in this module, we will see a cloud-to-cloud synchronization and after that, I would point to resources that would help you learn on-premise to cloud synchronization. So let us get started with cloud-to-cloud synchronization:
Go to Windows Azure Management Portal and go to “Data Sync” section. First, let us provision a Data Sync Server.
After agreeing to terms of use, you are asked to select the subscription under which you wish to create the server. Please select the subscription. Then you are asked to select the region – please select the region too. Right now, there are two options: North Central US and West Europe.
I would be synchronizing one database hosted in North Central US and other database hosted in South Central US for this module and so I selected the “North Central US” as the region for my Data SYNC server.
Click on Finish to create a SQL Azure Data SYNC server.
Now, in this module – I would demonstrate cloud-to-cloud synchronization and so I would go for “Sync between SQL Azure databases”.
Now, here the service will guide you while you setup your synchronization environment. So step 1 is to name the sync group.
Now, step 2 is to add the Hub.
I added a Hub database and tested the connection:
Now for the purpose of this module – I would add one member database. I have opted for “Bi-directional” sync direction.
After we have added the Hub and Member database, we will select the configuration of the sync group. I chose Hub Wins for the conflict resolution and 5 minutes as my sync schedule.
The next step is to select the tables that you want to sync. So click on Edit Dataset and then select all since for this module, I want all tables to get synchronized.
Note that can you do sophisticated tasks like filtering here.
Now we need to click on deploy to save the changes in sync group.
On successful deployment – you would see something like:
Now since we have sync interval of 5 minutes and the sync direction was bi-directional, every 5 minutes Hub and Member database would get synchronized in a bi-directional fashion.
If for some reason, you do not want to wait for the sync interval to trigger and want to instantly perform sync – then click on Sync Now.
Just note that the Hub and Member databases would have few objects (tables, stored procedures, etc) under the schema “Data SYNC”. Please do not edit/delete anything there. These objects are meant to be “meta-data” for the SQL Azure Data SYNC service.
So, that is it – we saw how to set up a cloud-to-cloud synchronization environment using SQL Azure Data SYNC. And to learn more about Data SYNC. please visit: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-data-sync-overview.aspx
Conclusion
In this module, we discussed about SQL Azure Reporting and SQL Azure Data Sync.