Category Archives: Microsoft Excel và thống kê
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
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.
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.
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
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 firstname.lastname@example.org or use the Comment field at the bottom of this page (sign-in required).
Table of Contents
- Overview / Survival Guide
- Media Type/Task/Feature 1
- Key Features
- Typical Scenarios
- Architectural Overview
- Conclusion and Next Steps
- See Also
- Other Languages
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
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.
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.
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.
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.
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.
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.
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.
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
To see how these key features of SQL Database can benefit organizations, let’s consider some common business application scenarios.
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.
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.
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:
- Create a database in SQL Database to store product data and customer data.
- 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.
- 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
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.
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.
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
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
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.
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.
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:
- User ID=user@server;
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.
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.
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.
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:
- 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.
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.
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:
- SELECT SUM(reserved_page_count) * 8192
- FROM sys.dm_db_partition_stats
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.
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.
If you want to continue to preview upcoming features for SQL Azure, be sure to visit SQL Azure Labs at sqlazurelabs.com.
Chuyển đổi qua lại giữa các kiểu dữ liệu Text và Number
A. Chuyển dữ liệu Text thành Number:
1/ Giới thiệu về Text :
Excel phân biệt dữ liệu kiểu số và dữ liệu kiểu Text, mặc dù nhìn chúng có vẻ giống nhau.
Khi bạn nhập dữ liệu vào một Cell, Excel thực thi ngay lập tức và quyết định xem bạn đang nhập 1 công thức, một số (kể cả ngày tháng hoặc thời gian) hoặc bất cứ thứ gì khác. Bất cứ những gì khác đó được xem là Text. Một Cel có thể chứa đến 32.000 ký tự
Nếu bạn muốn một số đ ược xem là Text, bạn có thể thực hiện các điều sau đây :
– Sử dụng Format / Cells, nhấp Tab Number v à chọn Text từ danh sách Categories. Nếu bạn không áp dụng định dạng canh l ề tr ái ph ải, thì dạng Text sẽ xu ất hiện cạnh trái của Cell.
– Đặt một dấu apostrophe (‘) trước s ố. Nếu dấu (‘) không hiển thị, nhưng mục nhập trong ô sẽ đ ược xem như là Text
B. Chuyển dữ liệu Number thành Text :
1. Khi bạn có một Data bao gồm d ữ liệu kiểu Text và kiểu Number như hình 319, một cách thông thường để bạn chuyển đổi những dữ liệu kiểu số thành Text là :
– Vào Format Cell sẽ có hộp thoại như hình 320
– Trong hộp Category, chọn Text
– Các dữ liệu kiểu Number sẽ chuyển thành Text. Tuy vậy, chúng vẫn khác với những dữ liệu kiểu Text đã được định dạng từ trước. Khi bạn chuyển dãy dữ liệu kiểu Text vừa Format xong trở lại kiểu Number, thì những ô Text ban đầu vẫn giữ nguyên kiểu dữ liệu Text không thay đổi.
2. Bạn có thể ch ọn Cell chứa Text . Nhấn F2 để thực hiện chế độ Edit Cell . Khi nhấn F2 xong, bạn se thấy con trỏ n ằm ngay trong Cell. Nh ấn phím Home để đưa vị trí con trỏ về vị trí bắt đầu của Cell. Gõ dấu (‘) để chuyển dữ liệu số thành Text. Xong, nh ấn Enter để di chuyểnsang các Cel bên cạnh. Tuy nhiên, việc làm này không được sử dụng nếu phải thực hiện trên nhiều Cell cần chuyển đổi.
3. Đối với cột có quá nhiều dữ liệu kiểu Text trong đó có cả Value thì có thể dùng Text to Column là hay nhất:
Chọn tất cả Data trong cột. Từ Menu, chọn Data – Text to Column. Bước đầu tiên của hộp thoại Wizard, bạn chọn Fixed Width như hình vẽ:
Bước 2, có thể có đường vẽ dọc trong Data Preview như hình 322 . Nếu có, bạn có thể bỏ nó đi bằng cách Double Click vào đường kẻ này.
Bước 3 của Wizard, tại Column Data Format, bạn chọn Text như hình 323
Sau khi bạn kết thúc bằng nhấn Finish, tất cả dữ liệu trong cột sẽ được chuyển thành dạng Text
Bạn không thể sử dụng nút AZ để Sort dữ liệu trong trường hợp này, bạn phải vào Menu Data – Sort. Sauk hi bạn nhấp OK, sẽ có hộp cảnh báo như hình 324. Bạn chọn Sort numbers and numbers stored as text separately.
Tổng kết sơ bộ:
– Các cách làm trên đều không thoả mãn thực tế của vấn đề chuyển giữa Text sang number số hoặc ngược lại, đơn giản vì Microsoft Excel luôn ngầm hiểu Text chứa Number chứ number không chứa text.
– Trong Text chứa number bằng cách của Excel là chứa phân định bằng đánh dấu ký tự ‘ trước các con số.
– Theo cách của tôi muốn triệt tiêu vấn đề, nên áp dụng hàm Excel để phân định, xác định, ép kiểu hay nhận dạng ra các giái trị nào là Text, giá trị nào là Số thì mới là cách toàn vẹn, đúng đắn nhất.
Cách giải quyết của tôi:
Ví dụ: cho 2 bảng tính “Bảng Bán hàng” để tìm ra số tồn ở “bảng Tồn”, cần dùng hàm VLOOKUP theo mã Hàng hoá để tìm ra các số liệu đã bán rồi mới tính tồn theo cột Nhập hàng trong bảng tồn.
Bảng bán hàng, có mã hàng là kiểu Text.
Bảng tồn, có mã hàng là kiểu text, sử dụng hàm VLOOKUP để tìm mã hàng trong bảng bán hàng để tính ra hàng tồn.
Lỗi: cứ các mã hàng có kiểu Text “ví dụ: 0000006 … 090145 thì tìm ra đúng kết quả, còn từ 100000 … 999999 thì không tìm ra kết quả, chỉ báo là #N/A: không có giá”
Sau 1 giờ đồng hồ mò mẫm tìm hiểu, tôi hiểu ra là vì Excel định dạng Text cho các giá trị có đầu bằng chữ số 0, còn các số có đầu chữ số 1 ..9 thì nó sẽ hiểu là Value có giá trị số, còn cell vẫn định nghĩa là Text giúp cho người dùng Search theo kiểu text, còn Match, vlookup / hlookup là kiểu so sánh giá trị thì không thể áp dụng cách trên được.
Bước thực hiện:
Tôi dùng hàm If để xác định vị trí ký tự đầu tiên bên phải của Cell là số không “0” thì sẽ ép “xác định” được cell đó là Text, nếu bên trái là khác không “0” thì sẽ ép “xác định” được cho cell đó là kiểu Value.
ví dụ: IF(LEFT(A2,1) =”0″,TEXT(A2,”000000″),VALUE(A2))
Sau khi ép “xác định” được kiểu của Cell, chúng ta sẽ đơn giản đưa hàm đó vào trong hàm Vlookup là coi như mọi việc trở lại bình thường.
ví dụ: =VLOOKUP(IF(LEFT(A2,1) =”0″,TEXT(A2,”000000″),VALUE(A2)),’TH Ban’!$A$2:$G$8130,5,FALSE)
Trân trọng cảm ơn các bạn đã post bài tham khảo trên GiaiphapExcel.com để tôi và nhiều người tham khảo, tìm hiểu ra cái được, cái chưa được.
Tôi hy vọng bài này sẽ thể giúp các bạn vượt qua được cái khó khi sử dụng Excel và các kỹ năng xử lý trong kinh tế, thống kê.