BIS 2012 con đường cho giải pháp Business Intelligence và SharePoint Web Application


Khái lược:

Sau 12 năm nghiên cứu và xây dựng hệ thống BI bằng các phương án cổ điển như: Visual Basic  để thiết lập môi trường AW Ad-Hoc Reporter bằng Windows Form

Mô hình sản phẩm nhằm thiết lập Hệ thống báo cáo báo biểu đã được Microsoft phát triển từ thập niên 90:

BIS_Production

Tham khảo bài viết: https://thangletoan.wordpress.com/2013/07/30/lap-trnh-aw-ad-hoc-reporter-lm-bo-co-rdl-trong-sql-server-2008-r2/

Các sản phẩm và công nghệ BI tôi đã nghiên cứu và thiết kế trải dài hơn 13 năm qua được thể hiện:

  1. 1995: VISData 1.0
  2. 1998: VISData 1.1
  3. 2001: VASEPData 2.0
  4. 2003: PSMS Data 3.0
  5. 2004: WCOO Customs DB 1.0
  6. 2005: VN Customs Data 3.1
  7. 2008: Spanish Customs Data 2.0
  8. 2010: VN Customs DB 4.0
  9. 2013: Global Customs BIS 2012

Và vừa qua tôi đã có cơ hội để thể hiện một quan điểm, một cách nhìn đổi mới, một con đường phát triển công nghệ ứng dụng giải pháp mới cho toàn bộ

mạng lưới:

–  WCOO – ECC (Hải quan Châu âu)

– FIGIS (Tổ chức liên minh các Chuyên gia Nghiên cứu phân tích Kinh tế liên hợp quốc )

– FAO (Tổ chức nông lương thế giới)

– FIIS (Phòng kinh tế Thuỷ sản)

BIS2012

Kết quả:

– Sự thành công là chắc chắn vì nó là sự nghiệp, đam mê nghiên cứu và ứng dụng thực tiễn của tôi, của cả một đội ngũ các chuyên gia phân tích, thống kê quốc tế.

– Trong đó vai trò: Công nghệ Microsoft BI góp một phần rất quan trọng trong đường lối tư duy “Đổi mới” để giải quyết các  bài toán về Big Data vô cùng cấp thiết, phức tạp và quan trọng.

– Thay đổi trên đây mang tính quyết định vì nó đột phá hẳn tư duy cũ, cách làm cũ, về việc muốn lập các báo cáo phân tích phải làm Windows Form, phải xây dựng từ người lập trình ứng dụng, cung cấp báo cáo phải được xây dựng và quản lý bởi người quản trị và phải có Website, Windows Form hoặc Portal để cung cấp dịch vụ… Tất cả đều trở nên cổ điển với cách tư duy và thiết kế hệ thống mới… đó chính là BIS 2012.

Advertisements

Lập trình AW Ad-Hoc Reporter làm báo cáo RDL trong SQL Server 2008 R2


I. Giới thiệu về BI:

Mô hình sản phẩm nhằm thiết lập Hệ thống báo cáo báo biểu đã được Microsoft phát triển từ thập niên 90:

BIS_Production

Các sản phẩm và công nghệ BI tôi đã nghiên cứu và thiết kế trải dài hơn 13 năm qua được thể hiện:

  1. 1995: VISData 1.0
  2. 1998: VISData 1.1
  3. 2001: VASEPData 2.0
  4. 2003: PSMS Data 3.0
  5. 2004:  WCOO Customs DB 1.0
  6. 2005: VN Customs Data 3.1
  7. 2008: Spanish Customs Data 2.0
  8. 2010: VN Customs DB 4.0
  9. 2013: Global Customs BIS 2012

II. Nhận xét:

Tất cả các sản phẩm trên thành công đều dựa trên nguyên tắc sau:

  1. Công nghệ mới của Microsoft chuyên về xử lý dữ liệu.
  2. Tập trung cải tiến và phát triển các hệ thống CSDL lớn (Big Data – Data Warehouse – OLAP – Ad-Hoc Report on Web – Report Services).
  3. Học hỏi các giải pháp phân tích và lập trình bằng các ngôn ngữ dễ tích hợp trên nền SQL Business Intelligence Development Solution như VB , C#.

Mô hình xử lý dữ liệu của Microsoft hiện nay đã thay đổi mạnh mẽ và con đường công nghệ cũng đã chọn theo hướng:

image

 

III. Thông tin tham khảo:

Truy cập các trang web để tải về các ứng dụng cần thiết cho việc thiết kế ứng dụng:

  • Windows Form hoặc Web Form làm theo mô hình AW Ad-Hoc Reporter.
  • Nên chọn ứng dụng chuẩn và phù hợp với phiên bản SQL Server 2008 R2.

http://msftrsprodsamples.codeplex.com/ 

http://sqlserversamples.codeplex.com/#utilities

http://technet.microsoft.com/en-us/sqlserver/ff660783.aspx

http://archive.msdn.microsoft.com/reportviewer/Release/ProjectReleases.aspx?ReleaseId=4940

 

Source code
The book’s source code can be downloaded from Manning’s web site at http://www.manning.com/lachev.

The next sections discuss the software requirements for executing the code and the steps to set it up. Instead of partitioning the source code on a per-chapter basis, we decided to consolidate most of it in two applications: a WinForm-based AWReporterWin application and a web-based AWReporterWeb application. This approach has several advantages, including the following:

  • Simplifies the setup—For example, you need only one virtual folder to host the AWReporterWeb web application.
  • Allows the reader to launch the samples conveniently from a single application menu.
  • Simulates real-world applications—For example, you can encapsulate the code logic in a set of common classes.
    The trade-off is that you may not have all the software dependencies required to compile the sample applications and you may run into compilation errors, as explained in the next section.

SOFTWARE REQUIREMENTS
Some samples have more involved setup requirements. For example, chapter 12 requires the Office Web Components Primary Interop Assemblies (PIAs) to be installed, while chapter 13 requires the Authorization Manager (available only on Windows 2003 and Windows 2000 as a separate download) to be installed. To prevent compilation errors because of missing external dependencies, we excluded the source code for these two chapters, the AWReporterWin and AWReporterWeb projects, respectively. Please follow the setup instructions found in the readme files in the sample folders to run these samples successfully.
In case you still experience compilation errors as a result of missing external dependencies, we suggest that you resolve the issue by excluding the samples. For example, let’s say you don’t have Office 2003 and you can’t compile AWReporterWin. To fix this, right-click on the corresponding folder that contains the sample code in the Visual Studio .NET 2003 Solution Explorer and choose the Exclude from Project menu item. Then, compile the project and fix the compilation errors (if any) by commenting out any references to the excluded code.
Microsoft SQL Server 2000 Required by Reporting Services. You will need to install the AdventureWorks2000 database from the RS Setup program.

SOURCE CODE
SETTING UP THE SOURCE CODE

At this point, you should be able to run most of the reports.
Some reports require a more involved setup process. For example, there are reports that require that additional assemblies, such as AWC.RS.Extensions.dll and AWC.RS.Library.dll, be configured properly. The readme files that accompany the sample code include specific step-by-step instructions about how to configure these assemblies. Deploying the reports to the Report Server To run most of the code samples successfully, you need to deploy the sample reports
to the Report Server. Assuming that you have Administrator rights to the report catalog, the easiest way to do this is to follow these steps:

  • Step 1: Copy the AWC.RS.Library.dll and OpenForecast.dll to the Report Server  binary folder, C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin. This step is needed because some reports reference these assemblies, and the deployment process will fail if these assemblies are not found in the Report Server binary folder.
  • Step 2: If you haven’t done this already, copy the AWC.RS.Library.dll and Open-Forecast.dll to the Report Designer binary folder C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer.
  • Step 3: Open the AWReporter.rptproj project (found under the Reports folder) in Visual Studio .NET 2003.
  • Step 4: Right-click the AWReporter project in the Visual Studio .NET Solution Explorer and choose Properties to open the project’s properties.
  • Step 5: Verify that the TargetFolder setting is set to AWReporter and the TargetServer-URL setting is set to http://<servername>/ReportServer, where <servername> is the computer name where the Report Server is installed. If RS is installed locally, the TargetServerURL setting should be http://localhost/ReportServer.
  • Step 6: Click the Configuration Manager button and verify that both the Build and Deploy check boxes are selected for Debug configuration. Click OK to dismiss the Property Pages dialog.
  • Step 7: Right-click the AWReporter project again and choose Deploy. This will build the reports and then deploy them to the report catalog.
  • Step 8: To verify the setup, open the Report Manager web portal. If RS is installed locally, the default Report Manager URL will be http://localhost/reports. Under the Home folder, verify that the AWReporter folder exists. Click its link and run the Sales By Territory report. If everything is okay, the report will render in the browser.

SOURCE CODE
Configuring the AWReporterWeb application

To configure the web-based samples, you need to set up the AWReporterWeb virtual folder by following these steps:

  • Step 1 Right-click the AWReporterWeb folder in Windows Explorer and choose Properties.
  • Step 2 Select the Web Sharing tab.
  • Step 3 Click the Share This Folder radio button.
  • Step 4 In the Edit Alias dialog, enter AWReporterWeb as an alias.
  • Step 5 Make sure that the Read Access Permission check box is selected and the Scripts radio button is selected. Click OK to close the Edit Alias dialog.
  • Step 6 Open the Internet Information Manager (IIS) console. Right-click the AWReporterWeb folder, choose Properties, and then select the Directory
    Security tab. Click the Edit button in the Authentication and Access Control panel. Uncheck the Enable Anonymous Access check box. Make sure that
    the Integrated Windows Authentication check box is selected.

 

RSExplorer Sample Application

 

This sample works only with SQL Server 2008 R2. It will not work with any version of SQL Server earlier than SQL Server 2008 R2.
RSExplorer is a sample Microsoft Windows application developed in Microsoft Visual C# using Microsoft Visual Studio. The application is based on a real-world scenario, and it demonstrates how to develop a Windows application that uses the Reporting Services Web service. RSExplorer uses a Microsoft .NET Framework proxy class to call Web service methods exposed by the Reporting Services SOAP API. It also highlights new features in the area of enterprise reporting. This sample is not supported on Itanium-based operating systems.

Important:

Sample applications should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.

Requirements

You should be familiar with Visual Studio and Visual Basic or C#, and you should have installed the following to use the RSExplorer sample:

  • Microsoft Visual Studio 2010.
  • SQL Server 2008 R2, including Reporting Services.
  • Microsoft .NET Framework 2.0.
  • Reporting Services samples. You can choose to install the samples during SQL Server setup. For this release, when you choose to install samples, a separate installer is installed on your computer that you can launch to extract and install samples. You can run this installer from the Start menu in the Microsoft SQL Server program group.
  • A report server that you have permission to access on your network, if you plan to use the sample client application to view the contents of a report server and render reports.

Note:

RSExplorer may be converted to compile and run in previous versions of Visual Studio. However, it is designed specifically to be compatible with the version mentioned in this section.

Important:

SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

Location

This sample is located by default at:
C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Application Samples\Report Search Sample

Building the Sample

If you have not already created a strong name key file, generate the key file using the following instructions.

To generate a strong name key file
  1. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.– or –Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
  2. Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed. Note: To determine the folder where samples are located, click the Start* button, point to All Programs, point to Microsoft SQL Server, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\100\Samples.*
  3. At the command prompt, run the following command to generate the key file: sn -k SampleKey.snkImportant: For more information about the strong-name key pair, see “Security Briefs: Strong Names and Security in the .NET Framework” in the .NET Development Center on MSDN.
To build and run the RSExplorer sample within Visual Studio
  1. Open the solution (RSExplorerCS.sln or RSExplorerVB.sln).
  2. From the Debug menu, click Start Without Debugging.
  3. To exit the application, choose File and then Exit.
To build and run the RSExplorer sample from the Command Line
  1. Use the Change Directory command to change to the RSExplorer directory.
  2. Type the following to build both the C# and Visual Basic versions of the sample: * for /r %f in (*.sln) do C:\WINDOWS\Microsoft.NET\Framework\v2.0.40607\msbuild.exe “%f” * Note: The Windows system path and .NET Framework version number may differ on your system.
  3. In the application’s \Bin\Debug directory, double-click RSExplorer.exe to start the application.
  4. From the File menu, choose Exit to close the application.
Using RSExplorer

After installing the RSExplorer sample application, you can use the sample to view the contents of a report server, add new folders, import reports, edit item properties, and view reports.

Important:

Sample applications should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.

To start the RSExplorer sample application
  1. Open RSExplorer.sln in Visual Studio.
  2. On the Debug menu, click Start to run the sample application.
  3. In the Server Address box, enter the virtual root of the report server you want to access for example, http://&lt;localhost>/reportserver, and then click Go. A detailed list of report server items should appear in the Catalog Explorer list view.
  4. Navigate the report server database tree by clicking on folders. You can navigate back by clicking the Up arrow on the toolbar.
  5. Click on any report in the list and click the Show Properties link to view the description and path properties.
To add a folder to the report server database
  1. Navigate to a location in the tree to which you want to add a new folder.
  2. On the File menu, click New, and then click Folder. The Add Folder dialog box opens.
  3. Choose a name and description for the folder, and then click Add.
To import a report
  1. On the File menu, click Import Report. The Import Report File dialog box opens.
  2. Use the dialog box to browse for a Report Definition Language (RDL) file that you want to upload to your report server, and then click Open.
To delete an item from the report server database
  1. Browse to and select an item that you want to delete in the Catalog Explorer list.
  2. On the Edit menu, click Delete. Click Yes to confirm the action.
To copy items in the report server database
  1. Select the item that you want to copy. RSExplorer is limited to copying reports, resources, and data sources.
  2. On the Edit menu, click Copy.
  3. Browse to a folder into which you want to copy the selected item or items, and then, on the Edit menu, click Paste.
To view the properties of a selected item
  1. Select an item in the Catalog Explorer list.
  2. Click the Show Properties link above the right window pane. The properties are displayed.

Các thủ tục, hàm, quy tắc và thiết kế DB Warehouse trong BI và SQL 2008 R2 Enterprise


1. Các toán tử dùng để sửa dữ liệu Null:

– Dùng toán tử IS [NOT] NULL hoặc IS NULL để lọc dữ liệu Null

ví dụ:  

UPDATE [Spanish_CustomsDB].[dbo].[SNUOC] 

SET [Francophone] = ‘X’

WHERE [Francophone] IS NULL

GO

 

2. Chuyển dữ liệu từ font ABC, Vni, BK II hoặc Việt hoá dữ liệu sang phông Font Unicode tiếng việt:

– Dùng hàm thủ tục Procedure hoặc T-SQL viết query kết hợp toán tử Like ‘%’  để lọc

Ví dụ:

UPDATE [Vietnam_CustomsDB].[dbo].[SNUOC]

SET [CHAU] =  N’Châu Phi’

/***** N’Châu Phi’ là font đã được Tôi chuyển thành Unicode Tiếng Việt *******/

WHERE [CHAU] Like ‘%Ch©u Phi%’

/*****   Ch©u Phi là font đã được hải quan nhập bằng ABC  *******/

GO

Sưu tầm: Overview of Windows Azure SQL Database


Nội dung bài thi Cloud có bài này đây:

Windows Azure SQL Database is a cloud database service from Microsoft. SQL Database provides web-facing database functionality as a utility service. Cloud-based database solutions such as SQL Database can provide many benefits, including rapid provisioning, cost-effective scalability, high availability, and reduced management overhead. This paper provides an architectural overview of SQL Database, and describes how you can use SQL Database to augment your existing on-p

Overview / Survival Guide

An overview/survival guide topic provides a guided view of a technology, solution or technology-related concept or activity through links to internal (TechNet Wiki) and external Microsoft and community information. For example, an overview of hyper-v for beginners or SQL Server pivot table survival guide. Typically, this type of topic will grow over time as other users contribute useful links and descriptions so it is okay to start with a short topic!

Begin with a description of the problem using general terms and broad concepts. This will provide context for the solutions that follow as well as set expectations for the reader. In separate sections, provide scoped guidance using appropriate headers by media type (videos, documents, white papers, blogs), task (plan, design, develop, deploy, maintain), feature (join, insert, delete, update, select), etc.

Media Type/Task/Feature 1

Describe the media type, task or feature. Media type requires less description, other classifications may require more.

  • First Link. Brief description of how reference is useful.

Repeat as needed.

References

Increase the credibility of the article by including useful references. This includes references used for the solution as well as relevant links to the TechNet Library, your blog, and other sites in community.

  • First Reference. Brief description of how reference is useful.

Don’t forget to tag the article with appropriate keywords so other users can find and learn from your experiences!

remises data infrastructure or as your complete database solution.

Last Reviewed: 8/26/2011

 Note

If you wish to contribute to this page, use the Edit tab at the top (sign-in required). If you wish to provide feedback for this documentation please either send e-mail to azuredocs@microsoft.com or use the Comment field at the bottom of this page (sign-in required).

Table of Contents


Introduction

Companies that provide Internet-based applications are facing many challenges today. Users expect access to ever-increasing amounts of data from anywhere, at any time, and from any device. The size, scale of use, and variety of forms of data are expanding rapidly. Developers must build and deploy applications quickly to keep up with these growing demands. Using the traditional on-premise data management model, meeting these needs demands constant investment in and management of servers, operating systems, storage, and networking. IT and operational staff must constantly monitor the infrastructure to ensure that capacity, performance, and availability are maintained as data volumes and user loads increase. Cloud database services, such as Windows® Azure SQL Database, provide an improved way to respond to these challenges. SQL Database is built on three key tenets: manageability, scalability, and developer agility.

From a developer’s perspective, SQL Database offers the well-known rich relational programming model, and uses a familiar data access protocol and simple deployment options. SQL Database simplifies the process of creating, prototyping, and deploying applications that integrate data across the enterprise. SQL Database removes infrastructure obstacles, thereby giving developers more freedom to innovate and experiment with new ways of sharing data.

From the IT management perspective, SQL Database offers a systematic and secure cloud-deployed solution that integrates with your on-premise assets and gives the IT organization oversight and control of distributed data assets. SQL Database is built on the same Microsoft SQL Server® technologies that have already been used and proven in on-premise deployments to provide high availability, reliability, and security.

From the business perspective, SQL Database offers a cost-effective approach for managing data, with a flexible consumption-based pricing plan, near-zero capital and operational expenditures, and the ability to quickly and easily scale up or down as your needs change.

If you are planning to build applications on large or shared data sets, provide on-demand scalable data storage, or augment your on-premise data infrastructure with low-cost, rapidly provisioned cloud-based storage, SQL Database can provide a robust and cost-effective solution.
Return to Top


Key Features

SQL Database is a key component of the Microsoft data platform offering flexibility and scalability; reliability and security; and developer agility. Let’s begin by looking at some of these features.

Manageability

Windows Azure SQL Database offers the high availability and functionality of an enterprise data center without the administrative overhead that is associated with an on-premise solution. This self-managing capability enables organizations to provision data services for applications throughout the enterprise without adding to the support burden of the central IT department or distracting technology-savvy employees from their core tasks to maintain a departmental database application.

Low-Friction Provisioning

When you use the traditional on-premise data infrastructure, the time that it takes to deploy and secure servers, network components, and software can slow your ability to prototype or roll out new data-driven solutions. However, by using a cloud based solution such as SQL Database, you can provision your data-storage needs in minutes and respond rapidly to changes in demand. This reduces the initial costs of data services by enabling you to provision only what you need, secure in the knowledge that you can easily extend your cloud-based data storage if required at a future time.

High Availability

SQL Database is built on robust and proven Windows Server® and SQL Server technologies, and is flexible enough to cope with any variations in usage and load. The service replicates multiple redundant copies of your data to multiple physical servers to ensure data availability and business continuity. In the case of a disaster, SQL Database provides automatic failover to ensure maximum availability for your application.

Published service level agreements (SLAs) guarantee a business-ready service. When you move to SQL Database, you no longer need to back up, store, and protect data yourself.

Scalability

A key advantage of the cloud computing model is the ease with which you can scale your solution. Using SQL Database, you can create solutions that meet your scalability requirements, whether your application is a small departmental application or the next global Web success story.

Global Scalability

A pay-as-you-grow pricing model allows you to quickly provision new databases as needed or scale down the services without the financial costs associated with unused capacity. With a database scale out strategy your application can utilize the processing power of hundreds of servers and store terabytes of data.
SQL Database runs in worldwide data centers, so you can reach new markets immediately. If you want to target a specific region, you can deploy your database at the closest data center. You can harness this global scalability to build the next generation of Internet-scale applications that have worldwide reach, but without the infrastructure costs and management overhead.

Multi-Tenant Support

Independent software vendors (ISVs) who develop Software+Services (S+S) offerings must provide adequate isolation for individual customers’ data. ISV’s must be able to charge each customer the right price for the data storage services that they have consumed. SQL Database provides the flexibility that ISVs need to segregate customer data and implement multi-tenant billing, which enables you to build a global S+S solution quickly and easily.

Developer Empowerment

One of the potential obstacles to building great cloud-based applications is the requirement for developers to learn new tools, programming platforms, and data models. However, SQL Database is built on top of the TSQL language and is designed to be compatible with SQL Server with a few changes, so developers can use their existing knowledge and skills. This reduces the cost and time that is usually associated with creating a cloud-based application.

Familiar Client Development Model

When developers create on-premise applications that use SQL Server as a data store, they employ client libraries that use the Tabular Data Stream (TDS) protocol to communicate between client and server. There is a large global community of developers who are familiar with SQL Server and have experience of using one of the many client access libraries that are available for SQL Server, such as Microsoft ADO.NET, Open Database Connectivity (ODBC), JDBC and the SQL Server driver for PHP. SQL Azure provides the same TDS interface as SQL Server, so developers can use the same tools and libraries to build client applications for data that is in the cloud.

Proven Relational Data Model

SQL Database data is stored in a way that is very familiar to developers and administrators who use SQL Server. You can create a SQL Database Server which is a group of databases that are spread across multiple physical machines. This SQL Database Server is in some ways conceptually analogous to a SQL Server instance and acts as an authorization boundary just as in SQL Server. You can also set geo-location at this level. Windows® Azure™ and SQL Database data centers are located worldwide; if your application is relevant to a specific region, you can increase performance by geo-locating it there.

Within each server, you can create multiple databases that have tables, views, stored procedures, indices, and other familiar database objects. This data model ensures that your database developers can use their existing relational database design and Transact-SQL programming skills, and easily migrate existing on-premise database applications to the cloud.

SQL Database servers and databases are logical concepts that do not correspond to physical servers and databases. This abstraction enables the flexible provisioning that was described earlier in this paper. Administrators and developers can concentrate on data model design because SQL Data Services (SDS) insulates them from the physical implementation and management.

Synchronization and Support for Offline Scenarios

SQL Database is part of the rich Microsoft data platform which integrates with the Microsoft Sync Framework to support occasionally connected synchronization scenarios. For example, by using SQL Database and the Sync Framework, on-premise applications and client devices can synchronize with each other via a common data hub in the cloud.
Return to Top


Typical Scenarios

To see how these key features of SQL Database can benefit organizations, let’s consider some common business application scenarios.

Web Application

Most Web sites require a database to store user input, e-commerce transactions, and content, or for other purposes. Traditionally, such a data-driven Web site is implemented with a database server in the same data center as the Web server.

Using SQL Database, Web developers can choose to place data in the cloud where it is highly available and fault tolerant. As with the departmental application scenario, you can host your Web application on your own server, or by using a third-party Web hosted, and access the data in SQL Database across the Internet. However, to avoid issues of performance and application complexity that latency causes, you should consider hosting the Web site itself in Windows Azure so that it can benefit from co-location with your SQL Database.

Departmental/Workgroup Application

In a large organization, qualified database administrators run the mission-critical, company-wide databases, which receive the benefits of their experience. These databases may also have fault-tolerant configurations that have uninterruptible power supplies, redundant array of independent disks (RAID) storage, and clustered servers.

In contrast, a typical large organization also has many smaller database applications that IT-literate employees in departments and groups across the organization have created. They may have built such applications by using Microsoft Office Access®, Microsoft SQL Server® Express, or third-party software. The databases may be hosted on a single, cheap server, or even on a desktop computer, and they are typically managed by staff whose primary role is not data administration. Although the impact of a database failure is usually limited to the department that uses it, such interruptions can hamper productivity for large groups. It is also difficult to keep track of all such databases throughout your organization.

SQL Database represents an excellent opportunity to reorganize such disparate database applications. By provisioning SQL Databases for small departmental applications, your users can benefit from the self-management capabilities and fault tolerance that SQL Database provides, without placing an additional burden on your IT staff. This centralized approach also makes it much easier to audit the databases in your organization. In addition, because SQL Database has a pay-as-you-grow pricing structure, you can run small database applications very inexpensively.

When you migrate an on-premise client-server application to SQL Database, you can choose to leave the client application on premise and migrate only the data tier, which you can access by using common data access libraries across the Internet. However, if you use this application design, you must consider the latency issues that are inherent in Internet-based connectivity, which may result in more complex code in the client application. A better solution is to move the data access logic to Windows Azure, so that the same data center hosts both the data access code and the data itself. You can create a Web-based user interface (UI) in Windows Azure to which users connect with a browser. Alternatively, you can create a service by using ADO.NET Data Services to write code that exposes a SOAP, REST, or JSON interface to a simple desktop UI.

Data Hub

In a data hub scenario, you typically want to enable various mobile and remote users to collaborate by using the same set of data. Consider an insurance company that has a large mobile sales force that consists of more than five thousand people who are scattered across North America. Keeping customer and pricing data synchronized across the entire sales force is a constant problem. The first part of the problem is getting new customer contact information from the sales force into the internal finance systems. The second part is getting new price-list information out to the sales force. The insurance company needs a solution that will:

  • Keep each salesperson’s portable computer up to date with the latest pricing information
  • Keep the corporate system up to date with new customer information from the portable computer of each salesperson, without the risk of exposing critical corporate data

Currently, product and customer data is stored in a central SQL Server database in the data center. In addition, employees in the sales force use an application that runs on their portable computers and stores data in SQL Server Express. The IT department does not want to open the firewall to the on-premise data center to provide possibly insecure access from each salesperson’s portable computer. The development team can provide a safe and fully synchronized solution that uses SQL Database, by completing the following three tasks:

  1. Create a database in SQL Database to store product data and customer data.
  2. Create a Sync Framework provider for the data center. This Sync Framework provider keeps product and customer data synchronized between the data center and the SQL Database data hub.
  3. Create a second Sync Framework provider for the sales force’s portable computers. This Sync Framework provider keeps product and customer data

synchronized between the field salespeople and the SQL Database data hub.

The diagram in Figure 2 illustrates this solution.

Fig 2: Conceptual overview of a data hub scenario

Product pricing data flows from the enterprise database, through SQL Database, to more than five thousand salespeople. Customer contact data flows from more than five thousand salespeople, through SQL Database, to the enterprise database. When a salesperson’s portable computer is offline, changes that occur to local data are tracked. When the portable computer’s Internet connection is restored, the Sync Framework provider enumerates these changes and sends them to SQL Database. The safety of the corporate data center is ensured.

ISV S+S Offering

ISVs can use SQL Database to offer Software+Services solutions without distracting themselves from their core software development competency so that they can develop a hosting infrastructure capability. Windows Azure provides an ideal environment to host software services without the overhead of maintaining the hosting infrastructure at the premises of the ISV or the customer.

Consider an S+S vendor that provides compliance support to businesses, including financial, government, health-care, real estate, and franchising companies. Such organizations need to store historical data, such as financial records, business transactions, or correspondence, either for future reference or for compliance with record-keeping regulations. The S+S vendor employs a document management system for archived data that provides full text search, together with workflow functionality and check processing. The S+S vendor must also track and report access to resources as required for audit purposes. To reduce data storage costs and help to ensure rapid and secure access to records, the vendor wants to migrate its customers’ archive data to the cloud.

To achieve this aim, the company can create an account with SQL Database, together with Windows Azure accounts within the company space for each of its customers. After customers have an account, they can upload any form of document, such as e-mail, scanned checks, and escrow documents. Some of the documents are stored as binary large objects (BLOBs) in the Windows Azure BLOB store, whereas other documents are stored as structured data in SQL Database with standardized data fields.

In this scenario, it is essential to ensure that each customer’s data is isolated and only available to the right users. The S+S vendor can use SQL Database to implement this isolation and prevent inappropriate access. SQL Database also makes it simple to audit usage so that it is possible to bill customers appropriately.
Developers who write this solution for Windows Azure will find that it is tightly integrated with SQL Database. For example, they can use the familiar SQL Server client libraries that they use on the desktop; they can use one Windows Azure account to authenticate a user across the cloud; and they can use the same geo-location properties for the Windows Azure code and the SQL Database data.
Return to Top


Architectural Overview

In this section, you will see how SQL Database uses databases, how it structures relational data, and how you can connect to data.

Provisioning Model SQL Database is designed to support extreme scale and low cost while providing a familiar environment to administrators and developers. It has the hierarchical provisioning model that is described below to achieve this.

Windows Azure Platform Accounts

To use SQL Database, you must begin by creating a Windows Azure platform account. Using this account, you can access all of the facilities within the Windows Azure platform. This account is also used to bill for usage for all Windows Azure platform services. For more information on getting an account, see Windows Azure Platform Offers .

SQL Database Servers

Each Windows Azure account can contain multiple SQL Database servers. These servers are not implemented as SQL Server instances; instead, you can view them as logical concept that is used to provide a central administrative point for multiple SQL Database servers. Each server includes logins, just as you find in on-premise SQL Server instances, and you can also specify the geographic region your server is located in at this level.

You use Windows Azure Platform Management Portal to create and manage your database server. This portal provides an easy-to-use interface where you can create logins and provision databases.

SQL Databases

Each SQL Database server can contain multiple databases. A new database server has a master database that is just like an on-premise SQL Server instance. In each database, you can create tables, views, indices, stored procedures, and other familiar database objects. You can use the Windows Azure Platform Management portal to create a new database. Alternatively, you can use the Transact-SQL CREATE DATABASE command, and SQL Database Management API .

SQL Databases are implemented as replicated data partitions across multiple physical computers in an SQL Database data center. This architecture provides automatic failover and load balancing. Customer data is spread across multiple physical servers within the geo-location that is specified for the SQL Database server that is hosting the database. In this way, SQL Database achieves high availability and stability for all applications from the smallest to the largest without requiring intensive administrative effort.

Relational Database Model

A key design aim for SQL Database is to provide a familiar environment for database programmers. Therefore, the objects that you can create in a SQL Database are the same as those that are available in a SQL Server database. These include:

  • Tables: store data in rows with a consistent and normalized structure.
  • Indexes: increase the speed of searches and maximize performance.
  • Views: provide alternative ways to look at data in one or more tables.
  • Stored procedures: store common Transact-SQL scripts for simple execution.
  • Triggers: ensure data integrity by executing checks when data is modified.

Both SQL Server and SQL Database use the Transact-SQL language for database creation and data manipulation. Database developers and administrators can therefore be productive immediately in SQL Database by using their existing expertise.

For more information about Transact-SQL, see http://msdn.microsoft.com/en-us/library/ms189826.aspx .

Data Access Architecture

SQL Database exposes a Tabular Data Stream (TDS) endpoint to databases that are hosted in the cloud. TDS is the same network protocol that on premise SQL Server uses, therefore, a desktop client application can connect to SQL Database in the same way it connects to an on-premise SQL Server instance. Such an application runs code that was built by using ADO.NET, ODBC, or whatever technology you prefer to work with. Queries are formulated in the Transact-SQL language. Secure Sockets Layer (SSL) is required when a client application connects to the SQL Database TDS endpoint to ensure security.

In this desktop client application/SQL Database scenario, you must consider the latency that may arise over the cloud and handle it in the client code. The latency is inevitably higher than it would be if the database was on your premises. One way to avoid this latency is to create a Web-based user interface for your database application and host it in Windows Azure. In this scenario, the client code and data is hosted in the same data center, so latency is low. Users connect to such an application by using a Web browser. You can also use this architecture for data-driven Web sites that are hosted in Windows Azure.

In the third architecture that SQL Database supports, you can create application logic by using ADO.NET and the Entity Framework, and host it in Windows Azure.

You can then use ADO.NET Data Services to publish this application as a service that uses a SOAP, REST, or JSON interface, and build a lightweight client application to consume data from the service.

In this way, you avoid latency between the application and SQL Database, but you still provide an Internet-based service interface for your data, which you can then use in rich Internet applications or desktop solutions.

Security Model

Many databases contain sensitive data, so it is essential to carefully control access. This is especially important in a multi-tenant application that involves users from different customers who must be isolated from each other. SQL Database provides the same set of security principals that are available in SQL Server with SQL Server Authentication. You can use these to authorize access and secure your data:

  • SQL Server Logins: Used to authenticate access to SQL Database at the server level.
  • Database Users: Used to grant access to SQL Database at the database level.
  • Database Roles: Used to group users and grant access to SQL Database at the database level.

Scaling Out Databases

You can store any amount of data, from kilobytes to terabytes, in SQL Database. However, individual databases are limited to 150 GB in size. To create solutions that store more than 150 GB of data, you must partition large data sets across multiple databases and use parallel queries to access the data.

Data sharding is a technique used by many applications to improve performance, scalability and cost. Some applications are well suited for partitioning because they use data models with natural partitioning boundaries. For example, applications that store and process sales data using date or time predicates. These applications can benefit from processing a subset of the data instead of the entire data set. Data sharding also enables parallel processing of data. Applications can place multiple data partitions on multiple sets of compute resources and processes the data simultaneously.

Although there is ample literature on the benefits of data sharding, little is said about the cost of managing a distributed database on tens or hundreds of servers. SQL Database provides the infrastructure for applications that require tens or hundreds of databases without the associated administrative cost. An application can partition large data sets into many databases without facing an exponential cost structure and up front capital investment. Provisioning and using three hundred databases is as simple as provisioning three databases. The management burden of keeping hundreds of servers functioning in a synchronized fashion and provide a highly available database is taken on by SQL Database.

In addition, SQL Database provides elasticity in the scale out offering as an application can increase the number of databases when needed and decrease when the requirements change. The ability to scale down without cost penalties prevents the customer from being stuck paying for unused resources when they do not need them.

Even applications with small data sets that require large processing capacity (CPU and IO) can also benefit from partitioning by gaining access to parallel resources.

The decision to use a scale out database strategy is something that should be approached carefully as it can have an impact on the complexity of the application code and is not appropriate for every application, although the benefits described above may make it well worth the effort.
SQL Database provides a built in mechanism to handle sharding of data in the form of Federations in SQL Database – a way to achieve greater scalability and performance from the database tier of your application through horizontal partitioning where one or more tables within a database are split by row and portioned across multiple databases (Federation members); to learn more about Federations in SQL Database see http://msdn.microsoft.com/en-us/library/windowsazure/hh597452.aspx

Deployment

It is possible to create and populate a database entirely in SQL Database by using Transact-SQL. However, in most cases, developers or administrators will design and populate the database on the premises on a development computer or server. When the application is complete, the database must be deployed to the cloud.

To deploy a database to SQL Database, you can create a Transact-SQL CREATE DATABASE script in Microsoft SQL Server® Management Studio using the Generate Script Wizard. You can then run the Transact-SQL script in SQL Database to create the database.

Geo-location for SQL Database is set at SQL Database Server level. Therefore, to deploy a database for a specific region, you should create a new SQL Database Server in that geo-location and connect to it to run the deployment script.
Return to Top


Conclusion and Next Steps

In this paper, we have introduced Windows Azure SQL Database and described its key capabilities and benefits. SQL Database is a cloud-based database service that offers developer agility, application flexibility, and virtually unlimited scalability, with a flexible, cost-effective delivery model. The robust underlying architecture provides reliability, high availability, and security. In addition, support for the most prevalent Internet communication protocols ensures ease of deployment and use. We have examined some scenarios where SQL Database can offer real business value to customers, such as data hub solutions and archival and compliance systems. We’ve also taken a look “under the hood” at the architectural and programming models that provide the core functionality of SQL Database.

Sưu tầm: Getting Started with SQL Azure Development


Trích bài viết của Lynn Langit is a developer evangelist for Microsoft in Southern California. She’s published two books on SQL Server Business Intelligence and has created a set of courseware to introduce children to programming at TeachingKidsProgramming.org. Read her blog at blogs.msdn.com/b/SoCalDevGal.

Bài viết này cũng có trong bài thi cơ đấy.

Microsoft Windows Azure offers several choices for data storage. These include Windows Azure storage and SQL Azure. You may choose to use one or both in your particular project. Windows Azure storage currently contains three types of storage structures: tables, queues and blobs.

SQL Azure is a relational data storage service in the cloud. Some of the benefits of this offering are the ability to use a familiar relational development model that includes much of the standard SQL Server language (T-SQL), tools and utilities. Of course, working with well-understood relational structures in the cloud, such as tables, views and stored procedures, also results in increased developer productivity when working in this new platform. Other benefits include a reduced need for physical database-administration tasks to perform server setup, maintenance and security, as well as built-in support for reliability, high availability and scalability.

I won’t cover Windows Azure storage or make a comparison between the two storage modes here. You can read more about these storage options in Julie Lerman’s July 2010 Data Points column (msdn.microsoft.com/magazine/ff796231). It’s important to note that Windows Azure tables are not relational tables. The focus of this is on understanding the capabilities included in SQL Azure.

This article will explain the differences between SQL Server and SQL Azure. You need to understand the differences in detail so that you can appropriately leverage your current knowledge of SQL Server as you work on projects that use SQL Azure as a data source.

If you’re new to cloud computing you’ll want to do some background reading on Windows Azure before continuing with this article. A good place to start is the MSDN Developer Cloud Center at msdn.microsoft.com/ff380142.

Getting Started with SQL Azure

To start working with SQL Azure, you’ll first need to set up an account. If you’re an MSDN subscriber, then you can use up to three SQL Azure databases (maximum size 1GB each) for up to 16 months (details at msdn.microsoft.com/subscriptions/ee461076) as a developer sandbox. To sign up for a regular SQL Azure account (storage and data transfer fees apply) go to microsoft.com/windowsazure/offers/.

After you’ve signed up for your SQL Azure account, the simplest way to initially access it is via the Web portal at sql.azure.com. You must sign in with the Windows Live ID that you’ve associated to your Windows Azure account. After you sign in, you can create your server installation and get started developing your application.

An example of the SQL Azure Web management portal is shown in Figure 1. Here you can see a server and its associated databases. You’ll notice that there’s also a tab on the Web portal for managing the Firewall Settings for your particular SQL Azure installation.

image: Summary Information for a SQL Azure Database

Figure 1 Summary Information for a SQL Azure Database

As you initially create your SQL Azure server installation, it will be assigned a random string for the server name. You’ll generally also set the administrator username, password, geographic server location and firewall rules at the time of server creation. You can select the location for your SQL Azure installation at the time of server creation. You will be presented with a list of locations (datacenters) from which to choose. If your application front end is built in Windows Azure, you have the option to locate both that installation and your SQL Azure installation in the same geographic location by associating the two installations.

By default there’s no access to your server, so you’ll have to create firewall rules for all client IPs. SQL Azure uses port 1433, so make sure that port is open for your client application as well. When connecting to SQL Azure you’ll use the username@servernameformat for your username. SQL Azure supports SQL Server Authentication only; Windows Authentication is not supported. Multiple Active Result Set (MARS) connections are supported.

Open connections will time out after 30 minutes of inactivity. Also, connections can be dropped for long-running queries and transactions or excessive resource usage. Development best practices in your applications around connections are to open, use and then close those connections manually, to include retry connection logic for dropped connections and to avoid caching connections because of these behaviors. For more details about supported client protocols for SQL Azure, see Steve Hale’s blog post at blogs.msdn.com/b/sqlnativeclient/archive/2010/02/12/using-sql-server-client-apis-with-sql-azure-vversion-1-0.aspx.

Another best practice is to encrypt your connection string to prevent man-in-the-middle attacks.

You’ll be connected to the master database by default if you don’t specify a database name in the connection string. In SQL Azure the T-SQL statement USE is not supported for changing databases, so you’ll generally specify the database you want to connect to in the connection string (assuming you want to connect to a database other than master). Here’s an example of an ADO.NET connection:

  1. Server=tcp:server.ctp.database.windows.net;
  2. Database=<databasename>;
  3. User ID=user@server;
  4. Password=password;
  5. Trusted_Connection=False;
  6. Encrypt=true;

Setting up Databases

After you’ve successfully connected to your installation you’ll want to create one or more databases. Although you can create databases using the SQL Azure portal, you may prefer to do so using some of the other tools, such as SQL Server Management Studio 2008 R2. By default, you can create up to 149 databases for each SQL Azure server installation. If you need more databases than that, you must call the Windows Azure business desk to have this limit increased.

When creating a database you must select the maximum size. The current options for sizing (and billing) are Web or Business Edition. Web Edition, the default, supports databases of 1GB or 5GB total. Business Edition supports databases of up to 50GB, sized in increments of 10GB—in other words, 10GB, 20GB, 30GB, 40GB and 50GB.

You set the size limit for your database when you create it by using the MAXSIZE keyword. You can change the size limit or the edition (Web or Business) after the initial creation using the ALTER DATABASE statement. If you reach your size or capacity limit for the edition you’ve selected, then you’ll see the error code 40544. The database size measurement doesn’t include the master database, or any database logs. For more details about sizing and pricing, see microsoft.com/windowsazure/pricing/#sql.

It’s important to realize that when you create a new SQL Azure database, you’re actually creating three replicas of that database. This is done to ensure high availability. These replicas are completely transparent to you. The new database appears as a single unit for your purposes.

Once you’ve created a database, you can quickly get the connection string information for it by selecting the database in the list on the portal and then clicking the Connection Strings button. You can also quickly test connectivity via the portal by clicking the Test Connectivity button for the selected database. For this test to succeed you must enable the Allow Microsoft Services to Connect to this Server option on the Firewall Rules tab of the SQL Azure portal.

Creating Your Application

After you’ve set up your account, created your server, created at least one database and set a firewall rule so that you can connect to the database, you can start developing your application using this data source.

Unlike Windows Azure data storage options such as tables, queues or blobs, when you’re using SQL Azure as a data source for your project, there’s nothing to install in your development environment. If you’re using Visual Studio 2010, you can just get started—no additional SDKs, tools or anything else are needed.

Although many developers will choose to use a Windows Azure front end with a SQL Azure back end, this configuration is not required. You can use any front-end client with a supported connection library such as ADO.NET or ODBC. This could include, for example, an application written in Java or PHP. Connecting to SQL Azure via OLE DB is currently not supported.

If you’re using Visual Studio 2010 to develop your application, you can take advantage of the included ability to view or create many types of objects in your selected SQL Azure database installation directly from the Visual Studio Server Explorer. These objects are Tables, Views, Stored Procedures, Functions and Synonyms. You can also see the data associated with these objects using this viewer. For many developers, using Visual Studio 2010 as the primary tool to view and manage SQL Azure data will be sufficient. The Server Explorer View window is shown in Figure 2. Both a local installation of a database and a cloud-based instance are shown. You’ll see that the tree nodes differ slightly in the two views. For example, there’s no Assemblies node in the cloud installation because custom assemblies are not supported in SQL Azure.

image: Viewing Data Connections in Visual Studio Server Explorer

Figure 2 Viewing Data Connections in Visual Studio Server Explorer

As I mentioned earlier, another tool you may want to use to work with SQL Azure is SQL Server Management Studio (SSMS) 2008 R2. With SSMS 2008 R2, you actually have access to a fuller set of operations for SQL Azure databases than in Visual Studio 2010. I find that I use both tools, depending on which operation I’m trying to complete. An example of an operation available in SSMS 2008 R2 (and not in Visual Studio 2010) is creating a new database using a T-SQL script. Another example is the ability to easily perform index operations (create, maintain, delete and so on). An example is shown in Figure 3.

image: Using SQL Server Management Studio 2008 R2 to Manage SQL Azure

Figure 3 Using SQL Server Management Studio 2008 R2 to Manage SQL Azure

Newly released in SQL Server 2008 R2 is a data-tier application, or DAC. DAC pacs are objects that combine SQL Server or SQL Azure database schemas and objects into a single entity. You can use either Visual Studio 2010 (to build) or SQL Server 2008 R2 SSMS (to extract) to create a DAC from an existing database.

If you wish to use Visual Studio 2010 to work with a DAC, then you’d start by selecting the SQL Server Data-Tier Application project type in Visual Studio 2010. Then, on the Solution Explorer, right-click your project name and click Import Data-Tier Application. A wizard opens to guide you through the import process. If you’re using SSMS, start by right-clicking on the database you want to use in the Object Explorer, click Tasks, then click Extract Data-Tier Application to create the DAC.

The generated DAC is a compressed file that contains multiple T-SQL and XML files. You can work with the contents by right-clicking the .dacpac file and then clicking Unpack. SQL Azure supports deleting, deploying, extracting and registering DAC pacs, but does not support upgrading them.

Another tool you can use to connect to SQL Azure is the latest community technology preview (CTP) release of the tool code-named “Houston.” Houston is a zero-install, Silverlight-based management tool for SQL Azure installations. When you connect to a SQL Azure installation using Houston, you specify the datacenter location (as of this writing North Central U.S., South Central U.S., North Europe, Central Europe, Asia Pacific or Southeast Asia).

Houston is in early beta and the current release (shown in Figure 4) looks somewhat like SSMS. Houston supports working with Tables, Views, Queries and Stored Procedures in a SQL Azure database installation. You can access Houston from the SQL Azure Labs site at sqlazurelabs.com/houston.aspx.

image: Using Houston to Manage SQL Azure

Figure 4 Using Houston to Manage SQL Azure

Another tool you can use to connect to a SQL Azure database is SQLCMD (msdn.microsoft.com/library/ee336280). Even though SQLCMD is supported, the OSQL command-line tool is not supported by SQL Azure.

Using SQL Azure

So now you’ve connected to your SQL Azure installation and have created a new, empty database. What exactly can you do with SQL Azure? Specifically, you may be wondering what the limits are on creating objects. And after those objects have been created, how do you populate those objects with data?

As I mentioned at the beginning of this article, SQL Azure provides relational cloud data storage, but it does have some subtle feature differences to an on-premises SQL Server installation. Starting with object creation, let’s look at some of the key differences between the two.

You can create the most commonly used objects in your SQL Azure database using familiar methods. The most commonly used relational objects (which include tables, views, stored procedures, indices and functions) are all available. There are some differences around object creation, though. Here’s a summary of those differences:

  • SQL Azure tables must contain a clustered index. Non-clustered indices can be subsequently created on selected tables. You can create spatial indices, but you cannot create XML indices.
  • Heap tables are not supported.
  • CLR geo-spatial types (such as Geography and Geometry) are supported, as is the HierachyID data type. Other CLR types are not supported.
  • View creation must be the first statement in a batch. Also, view (or stored procedure) creation with encryption is not supported.
  • Functions can be scalar, inline or multi-statement table-valued functions, but cannot be any type of CLR function.

There’s a complete reference of partially supported T-SQL statements for SQL Azure on MSDN at msdn.microsoft.com/library/ee336267.

Before you get started creating your objects, remember that you’ll connect to the master database if you don’t specify a different one in your connection string. In SQL Azure, the USE (database) statement is not supported for changing databases, so if you need to connect to a database other than the master database, then you must explicitly specify that database in your connection string, as shown earlier.

Data Migration and Loading

If you plan to create SQL Azure objects using an existing, on-premises database as your source data and structures, then you can simply use SSMS to script an appropriate DDL to create those objects on SQL Azure. Use the Generate Scripts Wizard and set the “Script for the database engine type” option to “for SQL Azure.”

An even easier way to generate a script is to use the SQL Azure Migration Wizard, available as a download from CodePlex at sqlazuremw.codeplex.com. With this handy tool you can generate a script to create the objects and can also load the data via bulk copy using bcp.exe.

You could also design a SQL Server Integration Services (SSIS) package to extract and run a DDM or DDL script. If you’re using SSIS, you’d most commonly design a package that extracts the DDL from the source database, scripts that DDL for SQL Azure and then executes that script on one or more SQL Azure installations. You might also choose to load the associated data as part of the package’s execution path. For more information about working with SSIS, see msdn.microsoft.com/library/ms141026.

Also of note regarding DDL creation and data migration is the CTP release of SQL Azure Data Sync Services (sqlazurelabs.com). You can see this service in action in a Channel 9 video, “Using SQL Azure Data Sync Service to provide Geo-Replication of SQL Azure Databases,” at tinyurl.com/2we4d6q. Currently, SQL Azure Data Sync services works via Synchronization Groups (HUB and MEMBER servers) and then via scheduled synchronization at the level of individual tables in the databases selected for synchronization.

You can use the Microsoft Sync Framework Power Pack for SQL Azure to synchronize data between a data source and a SQL Azure installation. As of this writing, this tool is in CTP release and is available from tinyurl.com/2ecjwku. If you use this framework to perform subsequent or ongoing data synchronization for your application, you may also wish to download the associated SDK.

What if your source database is larger than the maximum size for the SQL Azure database installation? This could be greater than the absolute maximum of 50GB for the Business Edition or some smaller limit based on the other program options.

Currently, customers must partition (or shard) their data manually if their database size exceeds the program limits. Microsoft has announced that it will be providing an auto-partitioning utility for SQL Azure in the future. In the meantime, it’s important to note that T-SQL table partitioning is not supported in SQL Azure. There’s a free utility called Enzo SQL Shard (enzosqlshard.codeplex.com) that you can use for partitioning your data source.

You’ll want to take note of some other differences between SQL Server and SQL Azure regarding data loading and data access.

Added recently is the ability to copy a SQL Azure database via the Database copy command. The syntax for a cross-server copy is as follows:

  1. CREATE DATABASE DB2A AS COPY OF Server1.DB1A

The T-SQL INSERT statement is supported (with the exceptions of updating with views or providing a locking hint inside of an INSERT statement).

Related further to data migration, T-SQL DROP DATABASE and other DDL commands have additional limits when executed against a SQL Azure installation. In addition, the T-SQL RESTORE and ATTACH DATABASE commands are not supported. Finally, the T-SQL statement EXECUTE AS (login) is not supported.

Data Access and Programmability

Now let’s take a look at common programming concerns when working with cloud data. First, you’ll want to consider where to set up your development environment. If you’re an MSDN subscriber and can work with a database that’s less than 1GB, then it may well make sense to 
develop using only a cloud installation (sandbox). In this way there will be no issue with migration from local to cloud. Using a regular (non-MSDN subscriber) SQL Azure account, you could develop directly against your cloud instance (most probably using a cloud-located copy of your production database). Of course, developing directly from the cloud is not practical for all situations.

If you choose to work with an on-premises SQL Server database as your development data source, then you must develop a mechanism for synchronizing your local installation with the cloud installation. You could do that using any of the methods discussed earlier, and tools like Data Sync Services and Sync Framework are being developed with this scenario in mind.

As long as you use only the supported features, the method for having your application switch from an on-premises SQL Server installation to a SQL Azure database is simple—you need only to change the connection string in your application.

Regardless of whether you set up your development installation locally or in the cloud, you’ll need to understand some programmability differences between SQL Server and SQL Azure. I’ve already covered the T-SQL and connection string differences. In addition, all tables must have a clustered index at minimum (heap tables are not supported).

As previously mentioned, the USE statement for changing databases isn’t supported. This also means that there’s no support for distributed (cross-database) transactions or queries, and linked servers are not supported.

Other options not available when working with a SQL Azure database include:

  • Full-text indexing
  • CLR custom types (however, the built-in Geometry and Geography CLR types are supported)
  • RowGUIDs (use the uniqueidentifier type with the NEWID function instead)
  • XML column indices
  • Filestream datatype
  • Sparse columns

Default collation is always used for the database. To make collation adjustments, set the column-level collation to the desired value using the T-SQL COLLATE statement.

And finally, you cannot currently use SQL Profiler or the Database Tuning Wizard on your SQL Azure database.

Some important tools that you can use with SQL Azure for tuning and monitoring include:

  • SSMS Query Optimizer to view estimated or actual query execution plan details and client statistics
  • Select Dynamic Management views to monitor health and status
  • Entity Framework to connect to SQL Azure after the initial model and mapping files have been created by connecting to a local copy of your SQL Azure database.

Depending on what type of application you’re developing, you may be using SSAS, SSRS, SSIS or PowerPivot. You can also use any of these products as consumers of SQL Azure database data. Simply connect to your SQL Azure server and selected database using the methods already described in this article.

It’s also important to fully understand the behavior of transactions in SQL Azure. As mentioned, only local (within the same database) transactions are supported. In addition, the only transaction-isolation level available for a database hosted on SQL Azure is READ COMMITTED SNAPSHOT. Using this isolation level, readers get the latest consistent version of data that was available when the statement STARTED.

SQL Azure doesn’t detect update conflicts. This is also called an optimistic concurrency model, because lost updates, non-repeatable reads and phantoms can occur. Of course, dirty reads cannot occur.

Database Administration

Generally, when using SQL Azure, the administrator role becomes one of logical installation management. Physical management is handled by the platform. From a practical standpoint this means there are no physical servers to buy, install, patch, maintain or secure. There’s no ability to physically place files, logs, tempdb and so on in specific physical locations. Because of this, there’s no support for the T-SQL commands USE <database>, FILEGROUP, BACKUP, RESTORE or SNAPSHOT.

There’s no support for the SQL Agent on SQL Azure. Also, there is no ability (or need) to configure replication, log shipping, database mirroring or clustering. If you need to maintain a local, synchronized copy of SQL Azure schemas and data, then you can use any of the tools discussed earlier for data migration and synchronization—they work both ways. You can also use the DATABASE COPY command.

Other than keeping data synchronized, what are some other tasks that administrators may need to perform on a SQL Azure installation?

Most commonly, there will still be a need to perform logical administration. This includes tasks related to security and performance management. Additionally, you may be involved in monitoring for capacity usage and associated costs. To help you with these tasks, SQL Azure provides a public Status History dashboard that shows current service status and recent history (an example of history is shown in Figure 5) at microsoft.com/windowsazure/support/status/servicedashboard.aspx.

image: SQL Azure Status History

Figure 5 SQL Azure Status History

SQL Azure provides a high-security bar by default. It forces SSL encryption with all permitted (via firewall rules) client connections. Server-level logins and database-level users and roles are also secured. There are no server-level roles in SQL Azure. Encrypting the connection string is a best practice. Also, you may want to use Windows Azure certificates for additional security. For more details, see blogs.msdn.com/b/sqlazure/archive/2010/09/07/10058942.aspx.

In the area of performance, SQL Azure includes features such as automatically killing long-running transactions and idle connections (more than 30 minutes). Although you can’t use SQL Profiler or trace flags for performance tuning, you can use SQL Query Optimizer to view query execution plans and client statistics. You can also perform statistics management and index tuning using the standard T-SQL methods.

There’s a select list of dynamic management views (covering database, execution or transaction information) available for database administration as well. These include sys.dm_exec_connections , _requests, _sessions, _tran_database_transactions, _active_transactions and _partition_stats. For a complete list of supported dynamic management views for SQL Azure, see msdn.microsoft.com/library/ee336238.aspx#dmv.

There are also some new views such as sys.database_usage and sys.bandwidth_usage. These show the number, type and size of the databases and the bandwidth usage for each database so that administrators can understand SQL Azure billing. A sample is shown in Figure 6. In this view, quantity is listed in KB. You can monitor space used via this command:

  1. SELECT SUM(reserved_page_count) * 8192
  2. FROM sys.dm_db_partition_stats

image: Bandwidth Usage in SQL Query

Figure 6 Bandwidth Usage in SQL Query

You can also access the current charges for the SQL Azure installation via the SQL Azure portal by clicking on the Billing link at the top-right corner of the screen.

Learn More

To learn more about SQL Azure, I suggest you download the Windows Azure Training Kit. This includes SQL Azure hands-on learning, white papers, videos and more. The training kit is available from microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78.

Also, you’ll want to read the SQL Azure Team Blog at blogs.msdn.com/b/sqlazure/ and check out the MSDN SQL Azure Developer Center at msdn.microsoft.com/windowsazure/sqlazure.

If you want to continue to preview upcoming features for SQL Azure, be sure to visit SQL Azure Labs at sqlazurelabs.com.

Sửa lỗi khi không truy cập được CSDL của MS SQL Server Express 2008


Thật bực bội khi 1 website đang chạy bằng IIS7.5 .ASPX và có dùng SQL Express bổng không chạy được nữa, lỗi

Don’t have permission to access SQL Server Express 2008?

Tìm nguyên nhân: Lúc đầu nhìn có vẻ không hiểu tại sao lại lỗi, nguyên nhân không biết nó phát xuất từ đâu.

Bình tĩnh dò vết các báo lỗi thì mới mường tượng ra là do 2 nguyên nhân cơ bản sau:

Xác định lỗi:

1. Do vá lỗi update của SQL 2008 theo Windows Server Update từ bản vá SQL lên SP1, SP2, Sp3…

2. Vá lỗi cấu hình từ các hướng dẫn của BaseLine Security 2.2 và các hướng dẫn của Secunia và Windows Server Perfomance Security.

Chính những nguyên nhân trên đã dẫn lỗi của SQL Express càng ngày càng xa và khó có thể giúp chúng ta (người quản trị Web Hosting) dễ dàng phát hiện ra, dẫn tới người lập trình Web và người quản trị Hosting có thể ping nhau không biết ddi tới đâu.

Phương án sửa:

Lỗi nằm ở quyền của Account đã login và cài bản SQL Express đã bị rút thông công, mất quyền truy cập các SQL PipeLine và quyền truy cập databases với quyền “SysAdmin”.

Các bước sửa lỗi:

  1. Tắt  SQL Server Express từ  màn control Administrator tool\ Services

  2. Mở  cmd window (Run as Admin) và chạy ở chế độ single-user mode as local admin và gõ lệnh sau:

    “c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS

  3. Mở tiếp 1 cửa sổ cmd mới (Run as Admin)

  4. gõ lệnh sqlcmd

    sqlcmd -S .\SQLEXPRESS

  5. Bây giờ hãy nhập user theo kiểu ‘domain\user’ vào nhóm sysadmin role:

    1. sp_addsrvrolemember ‘domain\user’, ‘sysadmin’

    2. GO

  6. Ctrl+C  bấm tổ hợp phím này để đóng  cửa sổ đầu tiên bạn đã mở single-user mode bằng lệnh  cmd để ngừng chế độ SQL Server cmd.

  7. Khởi động lại dịch vụ của SQL Server Express.

  8. Đăng nhập trở lại SQL Express bằng  Management Studio và truy cập bằng account bạn vừa thêm ở bước 5 thuộc nhóm “sysadmin.”

Kinh nghiệm migrate SQL Data Warehouse, OLAP 2000 và xây dựng hệ thống Business Intelligence Solution 2008


Phần 1. Nhận xét:

Business Intelligence thực tế cái tên này đã có từ hơn 16 năm qua, nhưng những thập niên 90 vẫn là cái gì đó ước muốn của giới quản lý và Công nghệ

Chức năng chính: Thu thập dữ liệu, Xử lý kho dữ liệu, lọc và báo cáo phân tích.

Năm 2000 xuất hiện Microsoft SQL 2000 mới đúng là cuộc cách mạng công nghệ ứng dụng trong quản lý kinh tế, vẫn có một chút thay đổi cho cái tên:

– SQL Server 2000 Analysis Services database objects

MSSQLServerOLAPService service

– Database Mining & Cube

– Database WareHouse…

Quan điểm: Vậy là đã hơn 12 năm cho công nghệ này, khá cũ kỹ nhưng vẫn còn rất nhiều ngành kinh tế Việt nam không biết đến, biết đến rất ít, áp dụng không đáng kể

hoặc vẫn bám vào nó. Trong khi giới Công nghệ ngày nay đã dùng đến SQL Server 2008, SQL Server 2012 với rất nhiều công nghệ gọn nhẹ, tốc độ xử lý cao, độ ổn định và đáp ứng kho dữ liệu khổng lồ. Những ngành kinh tế Việt nam giờ vẫn rất lỗi thời trong Hệ thống phân tích số liệu phải kể đến: TCTK, TCCNTT-HQVN, TC Thuế VN…

Vấn đề của chúng ta không phải là chạy đua với Công nghệ mới, v/đ là áp dụng triệt để giải pháp Business Intelligence Solution (BIS) một cách hệ thống, hiệu quả, an toàn và ổn định cho xu thế phát triển Thông tin.

Phần 2.

Nâng cấp hệ thống BIS 2012: Nói về hệ thống BIS, FAO là tổ chức lớn nhất và đi đầu trong áp dụng giải pháp triển khai cho các chương trình nghiên cứu phân tích kho CSDL thế giới về kinh tế, quản lý, dự báo Nông, Lâm, Thuỷ sản Trên toàn bộ Thế giới với hơn 70 năm quan.

Bước 1. Nâng cấp CSDL

image

Đưa hệ thống dữ liệu HQVN từ năm 2000 sang hệ thống SQL Server 2008 R2

image

Khôi phục các dữ liệu cũ và mới hơn:

image

Lựa chọn đè dữ liệu:

image

 

Bước 2: Nâng cấp quy trình đồng bộ dữ liệu DTS

image

Lỗi không tương thích 2000:

image

Như vậy phải cài thêm trong bộ cài: SQLSererv2008_Ent\1033_ENU_LP\x86\Setup\x86\SQLServer2005_BC.msi

image

Tiếp tục lỗi thiết công cụ Design DTS 2000:

image

Hãy vào trang sau để Download: http://www.microsoft.com/en-us/download/details.aspx?id=11988

Lưu ý chúng ta cần bản: SQLServer2005_DTS.msi

image

To install SQL Server 2000 DTS Designer Components
  1. Start Internet Explorer, and go to the http://go.microsoft.com/fwlink/?linkid=66593.

  2. On the Integration Services Downloads page, click Microsoft SQL Server 2000 DTS Designer Components.

  3. In the File Download dialog box, click Save.

  4. In the Save As dialog box, specify a folder for saving the components package, and then click Save.

  5. Click Start, click Run, type Explorer, and then click OK.

  6. In Windows Explorer, locate the folder (specified in step 4) where you saved the components package, and then double-click sqlserver2005_dts.msi.

  7. In the Open File dialog box, click Run.

  8. On the Welcome to the Install Wizard for SQL Server 2000 DTS Designer Components page, click Next.

  9. On the License Agreement page, accept the terms of the license, and then click Next.

  10. On the Registration Information page, type your name and company name, and then click Next.

  11. On the Ready to Install the Program page, click Install.

    The wizard installs the components.

  12. On the Completing the SQL Server 2000 DTS Designer Components Setup page, click Finish.

  13. In the SQL Server 2000 DTS Designer Components Setup dialog box asking you to restart the computer, click Yes

Vấn đề đồng bộ DTS từ 2000 sang SQL 2008 vẫn cần sự tương thích  trên công cụ thiết kế Business Intelligence Development Studio 2008

Run-time Support for DTS Packages

SQL Server 2008 does not install run-time support for DTS packages. Before you can run and manage DTS packages, you have to install this run-time support as described in the following procedure.

To install run-time support for Data Transformation Services packages
  • In an Internet browser, open the Microsoft SQL Server 2008 Feature Pack page, and then download and install the Microsoft SQL Server 2005 Backward Compatibility Components.

    NoteNote

    This download has not been updated for SQL Server 2008 R2.

và công cụ thiết kế DTS tương thích 2000

Trích dẫn:

There is no 64-bit run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. To run packages in 32-bit mode outside BI Development Studio on a 64-bit computer, you have to install the following components:

  • The 32-bit DTS runtime, as described in the previous section.

  • The 32-bit version of the Integration Services runtime and command prompt utilities.

To install the 32-bit version of the Integration Services runtime and command prompt utilities on a 64-bit x86 computer

  • During Setup, select Business Intelligence Development Studio or Management Tools – Complete.

    Selecting either of these options installs the 32-bit version of the Integration Services runtime, the dtexec utility (dtexec.exe), and other Integration Services tools.

To install the 32-bit version of the Integration Services runtime and command prompt utilities on a 64-bit Itanium computer

  • During Setup, select Management Tools – Complete.

    Selecting this option installs the 32-bit version of the Integration Services runtime, the dtexec utility (dtexec.exe), and other Integration Services tools.

SQL Server 2008 does not install design-time support for DTS packages. Before you can use the SQL Server 2008 tools to open and view DTS packages, you have to download and install this design-time support as described in the following procedure.

NoteNote

The DTS package designer that you can download works with the SQL Server 2008 tools, but the designer’s features have not been updated for SQL Server 2008.

To install design-time support for Data Transformation Services packages

By default, on a 32-bit computer, the binary files for SQL Server 2000 are in the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder, and the binary files for SQL Server 2008 are in the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn folder. These default locations might cause the path of the binary files for SQL Server 2000 to occur in the PATH environment variable before the path of the binary files for SQL Server 2008. If this happens, an error message can appear when you try to use the DTS Designer. To resolve this error, copy the files that the designer requires to a new location as described in the following procedures.

NoteNote

On a 64-bit computer, substitute %ProgramFiles(x86)% for %ProgramFiles% in the following procedures.

To ensure that the DTS designer can be used in SQL Server Management Studio

  1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.

  2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id% folder. For example, for U.S. English, the lang_id subfolder will be “1033”.

To ensure that the DTS designer can be used in Business Intelligence Development Studio

  1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE folder.

  2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% folder. For example, for U.S. English, the lang_id subfolder will be “1033”.

 

Bảng tổng kết các công cụ tương thích giữa SQL 2000 và SQL 2005:

The Data Warehouse and analysis server imports, transforms, and stores Web site data. You use the data that is stored in the Data Warehouse database to analyze user activity on your Web site.

Commerce Server 2007 does not support mixed deployments of SQL Server 2000 and SQL Server 2005. All SQL Server components that you install must be at the same version level as the SQL Server database that you installed.

Microsoft SQL Server Express does not support the Data Warehouse.

Component to install and configure

SQL Server 2000

SQL Server 2005

SQL Server 2000

X

SQL Server 2000 Service Pack 4

X

SQL Server 2005

X

SQL Server 2005 Service Pack 2

X

SQL Server 2000 Analysis Services

X

SQL Server 2005 Analysis Services

X

SQL Server 2000 Reporting Services

X

SQL Server 2005 Reporting Services

X

SQL Server 2000 DTS Designer Components

X

The Data Warehouse uses SQL Server Analysis Services to access the online analytical processing (OLAP) database. SQL Server Reporting Services is required for analysis reporting.

Commerce Server 2007 Data Warehouse does not support SQL Server or SQL Server Analysis Services installed on a named instance.

Trường hợp dùng SQL Server 2005 và dùng lại file .CAB được export từ OLAP Server 2000:

Tham khảo:

Trường hợp dùng SQL Server 2008 R2 Analysis Server và di chuyển OLAP 2000 Server

Tham khảo: http://msdn.microsoft.com/en-us/library/ms143409.aspx

 

Phần 3. Migration Database WareHouse từ MS SQL 2000 sang MS SQL Analysis Server 2008 R2.

1. Cài bàn MS SQL server 2008 R2 trên cùng máy chủ SQL 2000

2. Cài bổ sung bản SQL Server 2005 Backward Compatibility có trong SQLServer2005_BC.msi

và SQL Server 2000 DTS Designer Components có trong SQLServer2005_DTS.msi

Download: http://www.microsoft.com/en-us/download/details.aspx?id=11988

 

image

Ghi chú: Lỗi nếu không cài thêm bản SQL Server 2005 Backward Compatibility

Databases: SDATA_Demo
        Cubes:             Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Interop.Dso.IDbGroup90Ex’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{CE55E9E9-9597-4214-B21F-30C7683467B1}’ failed due

3. Khởi động hệ thống SQL Analysis Server 2008 R2

image

Chọn chế độ Migrate Database…

image

Bước cuối trong loạt động tác đồng bộ dữ liệu từ SQL OLAP 2000 sang SQL Analysis 2008 R2

image

Mọi thứ của SQL 2000 bây giờ sẽ là ở phía sau bạn !

 

Phần 4. Xác định v/đ nên hay không nên dùng Migrating SQL server để chuyển từ OLAP 2000 ?

Theo kinh nghiệm và các kết quả mà tôi sưu tầm được hơn 12 năm qua cho thấy:

– Trong các công cụ Migration Data giữa các phiên bản SQL server 6.5 , 7.0, 2000, 2005, 2008 đều luôn có vấn đề nảy sinh khi convert Database mining / Analysis, kết quả chỉ đạt 80 – 90%, và quan trọng là sự sai lệch chuẩn dữ liệu, sai lệnh các Method, không chuyển được hết dữ liệu …

– Tương tự v/đ trên, khi chuyển dữ liệu từ Lotus Notes, Oracle sang SQL / MySQL cũng vấp rất nhiều lỗi khi chuyển dạng dữ liệu…

– Tất cả đều phải dùng phương án giống nhau: là phải chạy cả OLAP 2000, SQL Server 2000 và cài SQL Server 2005 / 2008 trên cùng một máy để có thể Migration Convert Data Warehouse, Data mining, Cube…

Ở đây, tôi đề xuất phướng tối ưu và toàn vẹn dữ liệu theo cách làm bằng tay theo thiết kế của bản SQL 2000:

Bước 1: Chuyển dữ liệu từ SQL 2000 sang SQL Server 2008 R2 như cách thông thường ( Attachment file mdf & ldf)

Bước 2: Mở công cụ DTS của SQL 2000 và để sửa bản design DTS, tương tự bên SQL 2005 / 2008 cũng mở DTS để design manual sau đó chạy test thử các tiến trình có kiểm tra biên dịch.

Bước 3: Mở Analysis Server 2005 / 2008 và design các database Warehouse , Data mining, Data cube, Compact DB …

image

Sử dụng VS 2008 để chuyên thiết kế BIS 2008

image

Thiết kế các data mining và Cube khai báo các đơn vị tính 3 chiều và các kích thước Dimension

image

Sau khi thiết kế được toàn bộ Data Mining, Cube… hãy chạy Process để compile toàn bộ các tiến trình tính toán và tối ưu hoá Database Warehouse

Lưu lại thành Project trong VS 2008 Business Intelligence để có thể dùng lại về sau.

Chúc các bạn thành công trong triển khai hệ thống BIS 2008 hoặc BIS 2012 sắp tới !