Getting Started With SQL Azure series:
1. What is SQL Azure?
2. Creating First SQL Azure Database
3. Provisioning and Billing Model
4. SQL Azure Architecture
5. SQL Azure's security model
6. Migrating Databases to SQL Azure
7. Administration Tasks - I
8. Administration Tasks - II
9. Developing SQL Azure applications
10A. Getting started with SQL Azure reporting and SQL Azure Data SYNC
10B Conclusion
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.
1) 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.
2. 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”:
3. Now, you will be asked to select the administrator user name and password – please do so and click on Finish.
4. Now you go to the SQL Azure report server home which looks like:
5. Now, we have a server that can host our reports so let’s create a report now via BIDS 2008. Note that I would be creating a simple report for the demonstration purpose and I already have a adventure works lt 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
6. 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:
7. Now click on Edit And you will be asked to enter the credentials. please do so and test connection:
8. 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.
9. 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. And then you will get the summary page. review the summary and name the report and then click on Finish.
10. 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
And then “Deploy” the project. Optionally, you deploy a single report if you wish.
11. Once you click on deploy, you will be asked to login to reporting server
reporting server login:
And on successful deploy, you will see a message in the output tab:
12. Now you can go to the URL mentioned in the URL and login using the reporting server credentials:
13. Once logged in, browse and select the project. And here select the report that you may want to see:
And here is the report:
So 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 it’s 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
1. http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-reporting-overview.aspx
2. BOL: http://msdn.microsoft.com/en-us/library/gg430130.aspx
Now, let’s shift our focus to SQL Azure Data SYNC
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:
1. Go to Windows Azure management portal. And go to “Data Sync” section. First, let’s provision a Data Sync server.
2. 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. And then you are asked to select the region – please select the region too. Right now, there are two options here: 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.
And click on Finish to create a SQL Azure Data SYNC server.
3. Now, in this module – I would demonstrate cloud to cloud synchronization and so I would go for “Sync between SQL Azure databases”.
4. Now, Here the service will guide you while you setup your synchronization environment. So step 1 is to name the sync group.
5. Now, step 2 is to add the Hub.
I added a Hub database and tested the connection:
6. Now for the purpose of this module – I would add one member database. I have opted for “Bi-drectional” sync direction.
7. 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 mins as my sync schedule.
8. The next step is to select the tables that you want to sync. So I clicked 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.
9. Now we need to click on deploy to save the changes in sync group.
10. On successful deployment – you would see something like:
Now since we have sync interval of 5 mins and the sync direction was bi-directional – every 5 mins Hub and Member database would get synchronized in a bi-directional fashion.
11. 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”:
12. And 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’s 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 and Thank you for following the series.
please read the final part here: Getting started with SQL Azure - Part 10 B: Conclusion << Paras Doshi