Virtual PC Presenter Script
Release 2 – CTP5 bits
Last Updated December 17, 2007
Prepared By
Table of Contents
Demo Summary ............................................................................................................................................................................................................. 5 Requirements................................................................................................................................................................................................................. 5 Setup and Preparation ................................................................................................................................................................................................... 5 Running the Demo ......................................................................................................................................................................................................... 6 Exiting the Demo ............................................................................................................................................................................................................ 6 Troubleshooting ............................................................................................................................................................................................................. 6 Trusted Platform ............................................................................................................................................................................................................ 7 Protect Your Information .......................................................................................................................................................................................... 7 All Actions Audited ............................................................................................................................................................................................... 7 Transparent Database Encryption........................................................................................................................................................................ 7 Extensible Key Management .............................................................................................................................................................................. 12 Spend Less Time on Ongoing Operations ............................................................................................................................................................... 14 Declarative Management Framework (DMF) .................................................................................................................................................... 14 PowerShell Integration....................................................................................................................................................................................... 23 Increase the Reliability of Applications ................................................................................................................................................................... 24 Enhanced Database Mirroring ........................................................................................................................................................................... 24 Page Recovery .................................................................................................................................................................................................... 27 Reduce Troubleshooting with Improved System Analysis ...................................................................................................................................... 28 Data Collection / Perf Warehouse ..................................................................................................................................................................... 28 Extended Events (XEvents)................................................................................................................................................................................. 28 Provide Predictable Response ................................................................................................................................................................................ 46 Resource Governor ............................................................................................................................................................................................ 46 Plan Guides / Plan Freezing ................................................................................................................................................................................ 51
Intelligent Platform ...................................................................................................................................................................................................... 52 Scale and Manage Growing Volumes of Data ......................................................................................................................................................... 52 Change Data Capture ......................................................................................................................................................................................... 52 Query Performance – Data Warehousing Queries ............................................................................................................................................ 54 Partitioning ......................................................................................................................................................................................................... 57 Backup Compression .......................................................................................................................................................................................... 57 Data Compression .............................................................................................................................................................................................. 58 Grouping Sets ..................................................................................................................................................................................................... 58 Build and Manage Sophisticated BI Solutions......................................................................................................................................................... 61 Integration with the 2007 Microsoft Office System .......................................................................................................................................... 61 Reporting Services Enhancements: Report Designer, Visualizations, and Tablix .............................................................................................. 62 Dimension Designer and Best Practice Alerts .................................................................................................................................................... 69 Named Sets ........................................................................................................................................................................................................ 76 Productive Platform ..................................................................................................................................................................................................... 77 Develop with Business Entities ............................................................................................................................................................................... 77 LINQ to SQL ........................................................................................................................................................................................................ 77 LINQ to Entities / ADO.NET Entity Framework................................................................................................................................................... 82 Database Applications ........................................................................................................................................................................................ 86 Project Codename “Astoria” .............................................................................................................................................................................. 86 Access Your Data from Anywhere........................................................................................................................................................................... 86 Building Occasionally Connected Systems ......................................................................................................................................................... 86 Store and Consume Any Type of Data .................................................................................................................................................................... 95 FILESTREAM Data Type ...................................................................................................................................................................................... 95 Sparse Columns / Filtered Index ...................................................................................................................................................................... 105
Integrated Full Text Search .............................................................................................................................................................................. 106 HierarchyID....................................................................................................................................................................................................... 106 Simplify Development of Data Applications ......................................................................................................................................................... 113 New Date/Time Data Types ............................................................................................................................................................................. 113 Table Value Parameters + MERGE statement .................................................................................................................................................. 119 XML Enhancements.......................................................................................................................................................................................... 122 Deliver Location Intelligence ................................................................................................................................................................................. 128 New GEOGRAPHY Data Type............................................................................................................................................................................ 129
Demo Summary
SQL Server 2008, the next release of Microsoft SQL Server, will provide a comprehensive data platform that is more secure, reliable, manageable and scalable for mission critical applications, while enabling developers to create new applications that can store and consume any type of data on any device, and enabling users to make informed decisions with relevant insights. The purpose of this demo is to showcase the new capabilities of SQL Server 2008 in a live, modular environment.
Requirements
To run this Virtual PC demonstration, you must have Microsoft Virtual PC 2007 installed on your computer, and your computer should meet the following minimum requirements: Microsoft Windows 2000 Professional, Windows XP Professional, Windows Server 2003, or Windows Vista operating system 2 GHz or faster CPU 2 GB of available RAM (4 GB of RAM strongly recommended) 25 GB of free hard drive space
Setup and Preparation
To prepare the Virtual PC image for demonstration follow these steps: 1. Copy the Virtual PC image to a folder on your computer. 2. Launch Microsoft Virtual PC. 3. Click New. 4. In the New Virtual Machine wizard, click Next. 5. Select the Create a Virtual Machine option (the default), and click Next. 6. Type a name for the demo, such as “SQL Server 2008 Demo,” and click Next. 7. Select Windows Server 2003 as the operating system, and click Next. 8. Choose the Adjusting the RAM option, and make sure you specify a minimum of 1400 megabytes of RAM. Click Next. 9. Select “An existing virtual hard disk,” and click Next. 10. Click Browse, and navigate to the directory containing the .vhd file for the demo. Select it, and click Open.
11. Check Enable undo disks. 12. Click Next. 13. Click Finish.
Running the Demo
1. Launch Microsoft Virtual PC, and select the demo image you just created. Click Start. 2. To log in to the demo, use the following account: Username: administrator Password: pass@word1
Exiting the Demo
After you’ve completed your product demonstration, it is recommended that you exit the demo in such a way that you do not save any changes you made to the environment during the presentation. To do this, select Shut Down from the Start menu when you are ready to end the presentation. While the Virtual PC image is shutting down, you are presented with a Virtual PC “Close” dialog box. From the “What do you want the Virtual Machine to do?” drop-down menu, simply select “Turn off and delete changes,” and then click OK.
Troubleshooting
If you are having issues with SharePoint Data Connection Library entries not showing up in Excel data connections, please run the following from the command prompt: net sans stop.
Trusted Platform
Protect Your Information
Building on the strengths of SQL Server 2005, SQL Server 2008 provides a more secure data platform enabling organizations to encrypt their valuable data within an entire database, data files or log files, without the need for application changes. SQL Server 2008 also simplifies compliance by providing more comprehensive data auditing enabling organizations to answer common questions like “What data was retrieved?” All Actions Audited SQL Server 2008 improves compliance and security by allowing you to audit activity on your data. Auditing can include information about when data has been read, in addition to any data modifications. SQL Server 2008 has features such as enhanced configuration and management of audits in the server, which enable organizations to meet varied compliance needs. SQL Server 2008 can also define audit specifications in each database, so audit configuration can be ported with databases. Filtering of audits to specific objects allows better performance in audit generation and flexibility in configuration.
Script
This section coming soon.
Click Steps
1. Step 1 2. Step 2 3. Step 3
Screenshots
Transparent Database Encryption SQL Server 2008 enables encryption of entire databases, data files, and log files, without the need for application changes. Encryption enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. Some of the benefits of transparent data encryption include searching encrypted data using either range or fuzzy searches, more secure data from unauthorized users, and data encryption. These can be enabled without changing existing applications.
Script
This warm-up step ensures the demo is correctly set back to the starting position.
Click Steps
1. Run C:\SQL Server 2008 Demo Files\Trusted Platform\Transparent Data Encryption\Cleanup.bat. From Windows Explorer, navigate to C:\SQL Server 2008 Demo Files\Trusted Platform\Transparent Data Encryption. Double-click on TDE Demo.ssmssln to open the demo solution file. [Leave the Windows Explorer window open in the background.]
Screenshots
With SQL Server 2005, database administrators gained the capability to encrypt individual pieces of data using cell-level encryption. This provided a very fine-grained solution to database encryption. Although encrypting data with this method can provide much greater security for confidential information, it can also cause substantial performance hits when querying or searching the data. To address these issues, SQL Server 2008 introduces Transparent Data Encryption (TDE) – a method for enabling data encryption without heavily impacting the performance of applications that consume the data. TDE is able to accomplish this task by offering roughergrained database level encryption. Instead of encrypting individual rows, columns, and cells, TDE provides a quick method of encrypting an entire database. Please note that TDE is not meant to replace cell-level encryption, and both methods are supported How does TDE work? Both the data and the log files of a database are encrypted on disk and get decrypted when they are read into memory. This makes encryption really transparent to applications. Because database applications attempt to minimize their I/O (a costly operation), tying encryption and decryption to I/O will also take advantage of existing
2.
3.
4. In the Object Explorer pane, expand the Connect drop-down menu and select Database Engine. 5. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. From the View menu, select Solution Explorer.
Step 4
6.
7. In the Solution Explorer pane, doubleclick on Encrypt DB.sql.
application design optimizations to minimize the performance impact of encryption. In fact, the performance hit on a database when encryption is enabled is negligible. The encryption of a database with TDE is done using a special key called, simply, database encryption key (or DEK, for short). For this demo, let’s take a look at the fictional Contoso Bank. This database contains all of the financial records of the bank, including customer and transaction information. As you can imagine, much of the data in this database is extremely sensitive, and needs to be kept as secure as possible. Because the whole database needs to be protected and not just individual pieces of data, Contoso Bank has chosen to encrypt the database using Transparent Data Encryption. 8. 9. In the Object Explorer pane, expand the Databases node. Expand the Contoso_Bank node.
10. Expand the Tables node. 11. Right-click on dbo.Customers and select Select Top 1000 Rows to view the confidential data we will be encrypting. 12. Close the query window. Steps 9-11
Before we can encrypt the database, we first need to create the service master key for this server, and then create the certificate we’ll use during the encryption process. We’ll quickly do both these tasks by running these SQL queries. Now, we’ll select the Contoso_Bank database, and bring up this new Manage Database Encryption dialog. Here, we can quickly associate the certificate we just created with this database, and then enable encryption. And with just a few short clicks, we have now encrypted the database. For larger databases, the server will launch a background process to perform the encryption. This process will be low impact on the server,
13. In the query pane, select the code labeled Step 1. 14. Click the Execute button. 15. In the Object Explorer, right-click the Contoso_Bank node, and select Tasks -> Manage Database Encryption. 16. Expand the blank drop-down selector box on the right side of the dialog, and select MyServerCert. 17. Check the Set Database Encryption On box. 18. Click OK. 19. In the Object Explorer pane, under the Steps 16-17
and the database will remain available as normal throughout the encryption process. When it completes the encryption, the encryption status will be updated in the DMV, as we’ll see in a minute. To verify that the database is now encrypted, we can use this System View to see what state the database is in. This encryption state of 3 indicates that encryption is indeed enabled on this database.
Contoso_Bank node, expand the Views node. 20. Expand the System Views node. 21. Scroll down to sys.dm_database_encryption_keys, right-click, and select Select Top 1000 Rows. 22. Close the query window. 23. Scroll back up and collapse the System Views node. 24. In the query pane, select the code labeled Step 2. 25. Click the Execute button. 26. Close the query window. 27. In the Object Explorer pane, right-click on Contoso_Bank, and select Tasks -> Detach. 28. Click the check box in the Drop Connections column. 29. Click OK. 30. Switch back to the Windows Explorer window. 31. Double-click on the SQL08DEMO Data shortcut. 32. Select Contoso_Bank.mdb and Contoso_Bank_log.LDF. Right-click and select Copy. 33. Select Back from the toolbar. 34. Double-click the SQL08DEMO Mirror
Even after the database is encrypted, we can still easily query the data. In fact, we can continue to perform the kinds of complex queries we’d like to be able to do, including using ranges, subselects, and searching. Now let’s see what happens if an unauthorized user manages to detach the encrypted database and get hold of the database files from the file system, and then tries to reattach them to another SQL Server Instance. To simulate this, we’ll go into the SQL Server Management Studio and detach the database. Next we’ll copy the database files, and move them over to this second instance of SQL Server we also have running on our demo machine. Note that when we attempt to run the SQL command to reattach the database, we receive an error. The database cannot even be reattached without the correct certificate.
Step 27
Data shortcut. 35. Right-click and select Paste. 36. Select Back from the toolbar. 37. Switch back to the SQL Server Management Studio window. 38. From the Solution Explorer, double-click the Attach DB Fails.sql item. 39. Click Execute. [Note the error message specifies failure due to not finding the server certificate with the correct thumbprint.] Let’s finish this scenario up by showing the proper way to migrate and reattach the database. Returning to our original instance of SQL Server, we’ll run this SQL query to export the certificate for Contoso Bank to a file. We can then import the certificate into the new database, and notice that now we’re finally able to reattach the database. This process provides an extra layer of security for your data, by preventing users that do not possess the correct certificate from attaching the database or accessing the data. Finally, as we would expect, once the database is attached again we’re able to access and query the database. 40. From the Solution Explorer, double-click the Export Cert.sql item. 41. Click Execute. 42. From the Solution Explorer, double-click the Import Cert and Attach DB.sql item. 43. In the query pane, select the code labeled Step 1. 44. Click the Execute button. 45. In the query pane, select the code labeled Step 2. 46. Click the Execute button. 47. In the query pane, select the six lines of code labeled Step 3. 48. Click the Execute button. 49. In the Object Explorer pane, expand the Connect drop-down menu and select Database Engine. 50. Ensure that the Server Type is set to
“Database Engine” and the Server Name is set to “SQL08DEMO\MIRROR”, and then click the Connect button. 51. In the Object Explorer, under SQL08DEMO\MIRROR, expand the Databases node. 52. Expand the Contoso_Bank node. 53. Expand the Tables node. 54. Right-click dbo.Customers and select Select Top 1000 Rows to view the confidential data.
Extensible Key Management SQL Server 2008 provides a comprehensive solution for encryption and key management. To meet the growing need for greater security of information within data centers, organizations have invested in vendors to manage security keys within the enterprise. SQL Server 2008 provides excellent support for this need by supporting third-party key management and hardware security module (HSM) products.
Script
With the growing demand for regulatory compliance of database security, and the overall concern for data privacy, more organizations are leveraging encryption as a method of protecting their data. As organizations increasingly use encryption and keys to secure their data, key management becomes more complex. Some high security databases use thousands of keys and must employ a system to store, retire, and regenerate these keys. Furthermore, these keys should be stored separately from the database to improve
Click Steps
This section is just talking points, since a Hardware Security Module cannot be demonstrated inside a virtual machine.
Screenshots
security. To address this issue, SQL Server 2008 features integrated support for Extensible Key Management, which exposes encryption functionality for use by third party vendors. These solutions work seamlessly with SQL Server 2008 databases and provide enterprisewide dedicated key management. This moves the key management workload from SQL Server to a dedicated external key management system. Extensible key management works by connecting an external device, known as a Hardware Security Module (HSM), to the network. This HSM is responsible for all key storage and management. All encryption and decryption is performed directly on the HSM. Employing a HSM for key storage and retrieval can increase security by creating a physical separation between keys and data, and providing an additional layer of authentication. HSM modules can also provide functionality not available in SQL Server natively, including key aging and rotation. To communicate with the HSM, there is a new SQL External Key Management (SQLEKM) interface provided for drivers. Since the EKM driver is loaded directly into the SQL Server application process, communication with the EKM should be fast and is primarily limited by the communication speed between the driver and the device. By using an Extensible Key Management system, organizations can greatly simplify key management and storage, and consolidate key
management in the enterprise.
Spend Less Time on Ongoing Operations
SQL Server 2008 introduces a new policy based management framework that will shift organizations from managing by scripts to managing by rules. This shift will enable organizations to reduce the time spent on daily maintenance operations by defining a common set of policies for database operations like Query Optimizations, Naming Conventions, Backup and Restore operations and Index Management. These policies will automatically be enforced and monitored and will enable you to publish these policies to thousands of servers, providing a consistent management framework across the enterprise. Declarative Management Framework (DMF) One of the most difficult tasks that administrators face in managing complex database environments is ensuring that all elements of the system conform to their company’s established security and usage policies. Administrators need to constantly monitor the security surface area of the entire database server, along with permissions and settings for every database, table, user, role, and schema. As part of an ongoing effort by Microsoft to reduce the total cost of ownership, SQL Server 2008 introduces the Declarative Management Framework (DMF), which is a new policy-based management framework for the SQL Server Database Engine. Declarative Management delivers the following benefits: Compliance with policies for system configuration Monitors and prevents changes to the system by authoring policies against the configuration Reduces total cost of ownership by simplifying administration tasks Detects compliance issues in SQL Server Management Studio
Declarative Management Framework (DMF) is a policy-based system for managing one or more instances of SQL Server 2008. To use the DMF, SQL Server policy administrators use SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects. Declarative Management Framework consists of three components: policy management, policy administrators who create policies, and explicit administration. Administrators select one or more managed targets and explicitly check that the targets comply with a specific policy, or explicitly force the targets to comply with a policy. Policy administrators enable automated policy execution by using one of the following execution modes: On Demand – Uses DDL triggers to prevent policy violations Changes are Attempted – Uses event notification to evaluate a policy when a relevant change occurs On Schedule – Uses a SQL Server Agent job to periodically evaluate a policy
Throughout this demo, we’ll see three examples of how DMF can help administrators effectively establish and monitor policies for their database environment.
Script
Scenario 1: Policy Enforcement In our first example, let’s take a quick look at how the Declarative Management Framework (DMF) works. Here, notice that we have a new database we’ve just created called DMF_Demo, which does not currently have any tables in it. This SQL script contains two queries, each of which will attempt to create a new table in the DMF_Demo database. Notice that when we run the first query, we get an error message and the table is not created. This error message tells us that the table we just tried to create was in violation of the “Table Naming Policy”. Running the second query gives us better results. This table was created successfully. When we right-click on the new table to inspect it, notice that we have a new menu option here for Policies. Each object in SQL Server 2008 now has this new option to help manage the policies that are associated with it. Viewing the policies here shows us that there is just one policy currently active on this object – the Table Naming Policy. When we manually run the policy, we get a new table that shows us the results. The green check marks tell us that the table passed the policy check and there are no issues we need to be concerned with. So where do these new policies live in the
Click Steps
1. 2. From the desktop, double-click on the SQL Server Management Studio icon. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. In the Object Explorer, expand the Database node. Expand the DMF_Demo node. Expand the Tables node. Right-click on the DMF_Demo node, and select Refresh. From the Standard toolbar, click the Open File button. Navigate to C:\SQL Server 2008 Demo Files\Business Critical\Declarative Management Framework and select Policy Enforcement Demo.sql. Click the Open button.
Screenshots
3. 4. 5. 6. 7. 8.
Steps 3-5
9.
10. Select the code under Query 1 and click the Execute button. 11. Select the code under Query 2 and click the Execute button. 12. Right-click on the DMF_Demo node, and select Refresh.
Management Studio? Looking under the Management node, notice there is a new folder called Policy Management. Drilling down here, we can see the Table Naming Policy that affected the DMF_Demo database. Let’s take a look and see what’s in this policy. This window lets us define the attributes of an individual policy. Notice that this policy is currently enabled, uses a condition called “disallow dbo schema for tables”, uses a filter to only apply to a database called DMF_Demo, and is set to Enforce for execution mode, which means it uses DDL triggers to check for policy violations. Now we’ll look at exactly what this condition does. Each condition belongs to an individual facet, which is a collection of attributes all associated with a logical entity. For example, this condition uses the Table Facet, which means this policy contains rules about database tables. As you’ll see later, SQL Server 2008 contains facets that can be used to create policies for all sorts of things, from schemas to users to views. This condition contains a single rule, which checks to make sure the schema field for a database table is not equal to dbo.
13. Expand the Tables node. 14. Right-click on goodschema.goodtable, and select Policies->View.
15. In the Table Naming Policy row, click the Run link. 16. Click the Close button. 17. Click the Close button. 18. In the Object Explorer pane, scroll down and expand the Management node. 19. Expand the Policy Management node. 20. Expand the Policies node. 21. Double-click on Table Naming Policy. 22. Click the Edit button next to the Check drop-down menu. 23. Click the Cancel button. 24. Click the Cancel button. Step 15 Steps 10-11
Step 22
Scenario 2: Monitoring Changes to the System We’ve just seen an example that shows us how polices can be used to monitor the name of an entity on the server, so now let’s look at an example where we’re actively monitoring the attributes of an entity. This Database AutoSettings policy is configured to monitor four attributes for databases. Looking at the conditions, we can see what those four conditions are. Note that since there are no filters set on this policy, it applies to every database on the server. Let’s create a new database. Now, consider what might happen if someone with access to this server changes a couple of the default options to settings we know will violate the Database AutoSettings policy. Let’s change the Auto Shrink property to True, and the Auto Update Statistics property to False. Notice when we refresh the Object Explorer that our database is listed, but it’s flagged with a little red warning icon, indicating that it is not in compliance with one or more policies. When we inspect the policies that apply to this database, we can see that the Database AutoSettings policy is effective. Looking at the history for this policy, we can see some new warning entries that were created just a minute ago. Expanding one of these entries, we can get more details about the individual entry. Here we see that although AutoClose and AutoCreateStatics passed the policy check, AutoShrink and AutoUpdateStatistics did not.
25. Under the Policies node, double-click on the Database AutoSettings policy. 26. Click the Edit button next to the Check drop-down menu. 27. Click the Cancel button. 28. Click the Cancel button. 29. In the Object Explorer pane, right-click on the Databases node and select New Database. 30. In the Database name textbox, type in a new name for the database, such as “PolicyTest”. 31. Click OK. 32. Right-click on the PolicyTest database, and select Properties. 33. Select the Options page. 34. Change the Auto Shrink property to “True”. 35. Change the Auto Update Statistics property to “False”. 36. Click OK. 37. In the Object Explorer pane, right-click on the SQL08DEMO machine and select Refresh. 38. Expand the Databases node. 39. Right-click on the PolicyTest database, and select Policies->View. 40. In the Database AutoSettings row, click on the History link. Steps 34-35 Step 26
If we choose, we can now run this policy against the database to automatically correct the attributes that are not in compliance. After we’ve clicked this Configure button, we can now view the details for this item, and see that it is now fully in compliance with the Database AutoSettings policy.
41. Click on the + sign to the left of the first row in the log file summary. 42. Click on the link that appears in the Details column. 43. Click the Close button. 44. Click the Close button. 45. In the Database AutoSettings row, click on the Run link. 46. Click the Configure button. 47. In the Details column, click on the View link. 48. Click the Close button. 49. Click the Close button. 50. Click the Close button. Steps 41-42
Scenario 3: Creating a Policy / Facets Now that we’ve seen how a couple existing policies work, it’s time for us to create a new policy from scratch. Before we do that, however, let’s take a quick detour and look in a little bit more detail at the facets we discussed earlier. Here is a list of all the Facets that are currently in the system. Each of these facets contains a logical grouping of properties for a particular entity, or aspect of SQL Server 2008. For example, here are all the attributes that can be used to define properties for a Database object. This Surface Area facet is interesting, because it contains a collection of attributes related to
51. In the Object Explorer pane, expand the Facets node (under Policy Management). 52. Double-click on the Database facet. 53. Click the Close button. 54. Double-click on the Surface Area Configuration facet. 55. Click the Close button. 56. Right-click on the DMF_Demo database, and select Policies->Facets. 57. Click the New Policy from Facet button. 58. Click the Cancel button. 59. Click the Close button.
securing this SQL Server instance. This can help ensure that only those components that are necessary for applications deployed on this instance are enabled. If we go back and look at the Policies options for our DMF_Demo database, we can see that we can view the properties of each facet that apply to this object. We can even create a new policy based on this facet, and either create it on the local system or export it to a file. Currently polices can be exported as XML, but soon support will be added for exporting in SML markup, which will be fully compatible with Microsoft System Center products. Now let’s create a new policy. This policy will contain a new condition for the ApplicationRole facet, which says that each Application Role must begin with “AR_”. Once the new condition has been added, we’ll make sure this policy applies to all Application Roles on the server, that the Execution mode is set to enforce, and finally that the policy is actually enabled. Now that our new policy has been created, we should perform a quick check to make sure the policy is valid. It looks like everything is fine, so let’s test this policy out by trying to create a new application role for the DMF_Demo database. We’ll call this new role “Bad_Role”, and assign it to the dbo schema. Just as we expected, when we tried to create this new application role, we received an error message warning us that the application role name violated the policy we just created. If we change the name of this application role, by adding an “AR_” in front of
60. Right-click on the Policies node (under Policy Management) and select New Policy. 61. In the Name textbox, type in “App Roles”. 62. Expand the Check drop-down menu, and select New Condition. 63. In the Name textbox, type in “App Role”. 64. Expand the Field drop-down menu, and select @Name. 65. Expand the Operator drop-down menu, and select LIKE. 66. In the Value text box, type in “AR_%”. 67. Click the OK button. 68. Click the checkbox next to Server/Database/ApplicationRole. 69. Click the Enabled checkbox. 70. Expand the When drop-down menu, and select Changes are attempted, prevent out-of-compliance. 71. Click the OK button. 72. Right-click on the App_Roles policy, and select Test Policy. 73. Click the Close button. 74. In the Object Explorer, expand the Security node under the DMF_Demo node. 75. Expand the Roles node. 76. Expand the Application Roles node. Steps 69-70 Steps 63-66 Step 62
the name, it passes the policy check and the application role is created.
77. Right-click on the Application Roles node, and select New Application Role. 78. In the Role name textbox, type in “Bad_Role”. 79. In the Default schema textbox, type in “dbo”. 80. In the Password and Confirm password textboxes, type in “pass@word1”. 81. Click the OK button. 82. Click the OK button. 83. Change the name in the Role name textbox to “AR_Good_Role”. 84. Click the OK button. Steps 78-80
Scenario 4: Best Practice Advisor Support Identifying and implementing database best practices to ensure a secure and reliable environment is one of the most important tasks for a database administrator. With SQL Server 2005, Microsoft introduced the SQL Server 2005 Best Practices Analyzer (BPA) tool to assist with this task. The BPA tool gathered data from Microsoft Windows and SQL Server configuration settings, and used a predefined list of SQL Server 2005 recommendations and best practices to determine if there were potential issues in the database environment. With SQL Server 2008, Microsoft has included best practice support right out of the box, in the form of pre-created DMF Best Practice policies. In this list of policies, you can see several dozen existing policies that end with “Best Practice”.
85. In the Object Explorer pane, ensure that Management -> Policy Management -> Policies is expanded. 86. Double-click the Guest Permissions Best Practice node. 87. Click the “…” button next to the Check drop-down menu. 88. Click the Cancel button. 89. Expand the Execution Mode drop-down box, and select On Schedule. 90. Click the New button next to the Schedule drop-down box. Steps 89-90
These are Microsoft-recommended best practices that can be implemented in your database environment. For example, let’s take a look at the Guest Permissions Best Practice. The Microsoft recommended best practice for guest users is that they do not have access to databases, so that access is limited to explicitly authorized users only. We can see that this best practice performs a check to make sure that a user does not have database access, and that the check is being performed against the Guest account for any database. Let’s configure this policy to run daily by creating a new schedule for this policy. Now, once we enable this policy, DMF will flag any database with a warning where the guest account has access. Best Practices enable administrators to monitor various aspects of the database environment based on Microsoft provided best practices. These best practices allow DBAs and IT Professionals to manage and monitor their databases by policies and rules rather than complicated scripts. Scenario 5: Customized Policies There are situations where policies cannot be expressed by existing facets exposed by SQL Server 2008. However, SQL Server 2008 includes support for evaluating conditions using T-SQL or WQL, allowing database administrators to create their own custom policies. For example, let’s take a look at this Windows Event Log System Failure policy. When we look
91. In the Name textbox, type in “Guest Permissions Job”. 92. Expand the Occurs drop-down menu, and select Daily. 93. Click OK. 94. Click the Enabled check box. 95. Click OK.
Steps 91-92
96. Double-click the Windows Event Log System Failure Error Best Practice. 97. Click the “…” button next to the Check drop-down menu. 98. Click the “…” button to the right of the Field expression. 99. Click the Cancel button. 100. Click the Cancel button. Step 98
at the condition being evaluated here, we can see that a custom expression has been written that checks to see whether a certain event code existing in the Windows System log file. Evaluating this expression confirms that an unexpected system shutdown error message does not exist in the log files, and warns you if it finds one, since this could affect database integrity.
101. Click the Cancel button. 102. Right-click on the Windows Event Log System Failure Error Best Practice and select Test Policy. 103. Click the Check button. 104. Click the View button. 105. Click the Close button. 106. Click the Close button.
Scenario 6: Policy Enforcement for Server Groups DMF policies can be applied not just to individual servers, but also to groups of servers. This allows a database administrator to define policies for how to configure a set of servers, and then quickly apply that policy to bring all servers in a group into compliance with that policy. To illustrate this, let’s create a new policy based on the Surface Area Configuration facet. We’re going to select this pre-existing condition that I already created, which checks to make sure that all aspects of the surface area are off by default. This can be useful in limited the database system’s overall surface area that is exposed to attacks, in order to reduce the chances of the system being compromised. Now that we have created this new policy, we’re going to export it to a simple XML file. Bringing up the registered servers pane, we can see that we actually have three servers in our group. We want to make sure that all three of
107. Ensure the Facets node is expanded. 108. Right-click on the Surface Area Configuration facet, and select New Policy. 109. In the Name textbox, type in “Surface Area Configuration – Off By Default”. 110. Expand the Check drop-down menu, and scroll down to the Surface Area Configuration category. Select the “Surface Area Configuration – Off By Default” condition. 111. Click the “…” button next to the Check drop-down menu. 112. Click the Cancel button. 113. Click OK. 114. Right-click on the Policies node and select Refresh. 115. Right-click on the Surface Area Configuration – Off By Default policy, and select Export Policy. 116. Click Save. Step 110
these servers is in compliance with the new policy we just created, so let’s see how we can do that. Here we’ll simply load the XML policy for the surface area policy, and by clicking this Configure button, we can apply this policy to all servers in the group. After the policy has been applied, we can then review the results of the action to see which changes occurred on which server. By using this method, administrators now have a simple method of managing the security policies for entire groups of servers, making it much easier to ensure that all systems are in compliance with established security policies.
117. From the View menu, select Registered Servers. 118. In the Registered Servers pane, expand the Database Engine node. 119. Expand the Local Server Groups node. 120. Right-click on the Local Server Groups node, and select Run Policy. 121. Click the “…” button to the right of the File text box. 122. Select Surface Area Configuration – Off By Default.xml and click Open. 123. Click the Configure button. 124. Click the top View link in the Details column. 125. Click the Close button. 126. Click the Close button. Steps 118-119
Step 124
PowerShell Integration Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2
3. Step 3
Increase the Reliability of Applications
SQL Server 2008 enables organizations to provide a more reliable platform for your mission critical applications. Using Database Mirroring, SQL Server 2008 enables organizations to seamlessly increase the reliability of applications using Database Mirroring. In addition, SQL Server simplifies the recovery of applications from storage failures along with providing the ability to add system resources like CPU and memory without affecting applications. Enhanced Database Mirroring Database Mirroring is a technology for increasing database availability. Database Mirroring transfers transaction log records from one server to another, allowing quick fail over to the standby server. In the event of a failover, client applications can automatically redirect their connection to the standby server. Fast failover with minimal data loss has traditionally involved higher hardware cost and greater software complexity. However, Database Mirroring can fail over quickly with no loss of committed data. In addition, Database Mirroring does not require proprietary hardware, and it is easy to set up and manage. The benefits of Database Mirroring are: Protecting data by providing complete or nearly complete redundancy. Increasing availability of a database in the event of a disaster Improving availability of production database during upgrades
SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring. New features include: Automatic page repair. SQL Server 2008 enables the principal and mirror computers to transparently recover from 823 and 824 errors on data pages by requesting a fresh copy of the corrupted page from the mirroring partner. Improved performance. SQL Server 2008 compresses the outgoing log stream in order to minimize the network bandwidth required by database mirroring. Enhanced supportability
SQL Server 2008 includes additional performance counters to enable more granular accounting of the time spent across the different stages of Database Management System (DBMS) log processing. SQL Server 2008 includes new Dynamic Management Views and extensions of existing views to expose additional information about mirroring sessions.
Script
Scenario 1: Establishing the Mirror Database availability is critical, especially for high volume customer-facing applications like ecommerce sites. SQL Server 2008 provides a number of methods to achieve and maintain high availability in different situations. These include failover clustering, database mirroring, and replication. With database mirroring, the primary database and a secondary mirror are kept synchronized. If the primary database fails for any reason, and the database instances have been configured for automatic failover, then the mirror will take over the duties of the primary. Users experience little or no disruption. A DBA can quickly configure mirroring by defining primary and secondary databases. Mirroring optionally allows configuration of a third database instance, called the witness. The witness monitors both databases and serves as the final arbiter in case of a failure and confirms the need for a failover operation. With the witness in place, failover is very fast, so customers are not affected if there is a problem. Notice that Object Explorer shows that the mirror has been established between the primary and mirror instances.
Click Steps
1. From the desktop, launch SQL Server Management Studio. 2. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button.
Screenshots
3. From the File menu, select Open -> Project/Solution. 4. Navigate to C:\SQL Server 2008 Demo Files\Trusted Platform\Database Mirroring\Establish Mirror, and select Mirroring.ssmsln. 5. Click OK. 6. Double-click on the “1 – PrimaryEndPointSetup.sql” script. 7. Click the Execute button. 8. Double-click on the “2 – MirrorEndPointSetup.sql” script. 9. Click the Execute button. 10. Double-click on the “3 – WitnessEndPointSetup.sql” script. 11. Click the Execute button. 12. Double-click on the “4 – MirrorSetupPartner.sql” script. 13. Click the Execute button. 14. Double-click on the “5 – PrimarySetupPartner.sql” script.
Steps 6,8,10,12, and 14
15. Click the Execute button. 16. Right-click on the SQL08DEMO server object in the object explorer pane, and select Refresh. 17. Expand the Databases node. 18. Highlight that the AdventureWorks node is now Primary, Synchronized. 19. From the Registered Servers pane, double-click sql08demo\mirror to add it to the Object pane. 20. From the Object Explorer pane, expand Databases under SQL08DEMO \MIRROR, and point out the AdventureWorks database is Mirror, Synchronized.
Step 18
Step 20 Scenario 2: Simulating a Failover To demonstrate database mirroring, we are going to use a sample application that performs an automatic database failover. Running this application simulates a situation where the primary database is working under peak load. During this time, the traffic between the primary database and the mirror increases as data is transferred to keep them synchronized. Notice that the queries are currently being executed on the primary database. 21. From the desktop, double-click on the Database Mirroring Demo icon. 22. Click the Begin Test button.
Step 22
Watch what happens when the primary database fails. After a brief delay, the mirror picks up the transactions that the primary was handling. This happens within a matter of seconds, because the witness instance configured earlier was able to detect the failure in the primary database server and switch the active server to the mirror. Using SQL Server 2008 database mirroring, you can help ensure that in the case of a database failure, customers can continue to use database applications without interruption.
23. Click the Fail Primary Instance button. 24. Click the End Test button. 25. Click the Restart Primary Instance button. 26. Close the Mirroring Application. 27. Return to SQL Server Management Studio. 28. In Object Explorer, right-click sql08demo\mirror, and select Disconnect. 29. Right-click the AdventureWorks database and select Properties. 30. Select the Mirroring page. 31. Click the Remove Mirroring button. 32. Click Yes. 33. Click OK. Step 23
Page Recovery Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
Reduce Troubleshooting with Improved System Analysis
Optimizing the performance of your data platform and delivering predictable response to end users are crucial for any organization. SQL Server is investing in dramatically improving the performance insight delivered to administrators by collecting performance data from within your system to a centralized data repository. Administrators are able to view management reports providing comparison and analysis of current performance versus prior history. Data Collection / Perf Warehouse Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 delivers more extensive performance data collection, a new centralized data repository for storing performance data, and new reporting and monitoring tools.
Script
This section coming soon.
Click Steps
4. Step 1 5. Step 2 6. Step 3
Screenshots
Extended Events (XEvents) SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure is a lightweight mechanism that supports capturing, filtering and acting upon events generated by the server process. This ability to act upon events allows users to quickly diagnose run time problems by adding contextual data, such as TSQL call stacks or query plan handles, to any event. Events can be captured into several different output types, including Event Tracing for Windows (ETW). When Extended Events are output to ETW, correlation with operating system and database applications are possible, allowing for a more holistic system tracing. The key benefits of XEvents are: Deep integration with Windows events. Administrators can track a request from the moment they enter SQL Server all the way into the Windows kernel. Very lightweight eventing mechanism. This makes XEvent the ideal solution for traces on large production systems. Lowered troubleshooting time with online event aggregation. Using the in memory event consumers allows a user to interrogate SQL Server directly for intelligence about behavior and bottlenecks, as opposed to manually analyzing trace files.
Precision data gathering via the ability to extend the information gathered when an event fires. User configurable always on event sessions. Users can create an event session to gather data specific to their environment, and have that session start every time SQL Server starts.
XEvent – Queryable Schema This demo highlights the key components of XEvent, including objects, events, and actions.
Script
All the metadata relevant to XEvents is stored in system tables. XEvent objects are stored in packages. Packages are binaries, but the objects within packages are described in the XEvent metadata. This top grid in the results pane shows the three XEvent packages, package0, sqlos and sqlserver. The next grid show the total count of objects by object type and the last grid shows how the objects are spread across the packages. When creating event sessions, you often need to specify the package name. The most important XEvent objects are events and targets. An event is, of course, an event. Targets are the consumers or where event data are directed. Actions allow you to include additional information with the data that is already available for an event. You are not restricted to a single package when creating an event session.
Click Steps
1. 2. From the desktop, launch SQL Server Management Studio. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. In Object Explorer, expand the Databases node. Select the SQL2008Demo node. In the Standard toolbar, click the New Query button. From the main SSMS menu, select File -> Open -> File and browse to the C:\SQL Server 2008 Demo Files\Trusted Platform\XEvent\XEvent Demos folder. Open XEvent Queryable Schema.sql. Click the Connect button. Select all the SQL text beneath Demo Step 1.
Screenshots
3. 4. 5. 6.
7. 8. 9.
10. Click the Execute button. 11. Scroll the results pane as necessary so Step 10
all three results sets can be viewed. There is a great deal of events - many more than those available to SQL Profiler and many at a much deeper level. Fortunately,the XEvent metadata groups these events into channels and keywords. This first grid shows all the available events, sorted by channel and keyword. The channel can help a user isolate events that would be of most interest to them. A DBA may be most interested in the Analytic channel. The Debug channel includes events that may be most useful for customer support. The keyword further categorizes the event. The second grid lists all the fields available for each event. This is data that can be received by a target and can also be used to filter events when creating or altering an event session. Targets are the consumers of an event session. It’s possible for a single event session to have multiple targets. The available targets include the asynchronous and synchronous bucketizer, which allows you to create buckets of events counts for a field available to the event. The pair matching targets allow you to record events that are missing a paired event, such as a resource lock without a subsequent release. The ETW (event tracing for windows) target allows you to send event data to a more traditional trace target. The second grid displays all the parameters for a target. These are values that could be specified when adding the target to the event session. 15. Select all the SQL text beneath Demo Step 3. 16. Click the Execute button. 17. Scroll the bottom grid in the results pane to show the parameters available for targets. 12. Select all the SQL text beneath Demo Step 2. 13. Click the Execute button. 14. Scroll the results pane to view the second result set as necessary to demonstrate the large number of available events.
Step 13
Step 16
Actions are performed when an event is fired and may be used to collect additional information that is available to a target. For example, the sqlserver.sql_text action adds the text of the executing SQL query.
18. Select all the SQL text beneath Demo Step 4. 19. Click the Execute button. 20. Close the query window.
Step 19
XEvent – Procedure & Kernel Analysis This demo captures a number of events that occur when a stored procedure is executed and sends them to an Event Tracing for Windows (ETW) session. In addition, an ETW session for the Windows kernel is run to disk activity. These two trace logs are then merged to that SQL Server events can be correlated to Windows events. This correlation is not possible with traces created with SQL Profiler.
Script
This code creates an event session to send SQL Server events to an ETW (Event Tracing for Windows) session. The events include sp_statement_starting and sp_statement_completed, which bracket a SQL statement executed within a stored procedure. The file_written and file_write_completed events are also captured. When adding the etw_classic_sync_target, we set the file path for the trace file to C:\demo\traces\sqletw2.etl. If this is not set, then the default target is xeetw.etl in the Windows temp directory. Because we want to filter the events to the
Click Steps
1. 2. From the desktop, double-click the SQL Server 2008 Demo Selector icon. From the displayed list of SQL Server 2008 demos, select “XEvent Procedure & Kernel Analysis Demo”. After the demo window has opened, click the Execute button on the toolbar at the top of the form.
Screenshots
3.
current database, we build the SQL command as a string and run it using the exec function. This is just so this correct database will be filtered on any SQL Server. We could have just specified the database id in the CREATE EVENT SESSION statement. The event session definition is persisted in system tables, so it takes just a simple TransactSQL statement to turn event sessions on or off. Now we start the Windows kernel trace using the logman utility from the command prompt. Here the trace output file is C:\demo\traces\kernel.etl. 4. 5. Click the Advance (forward arrow) button. Click the Execute button.
Step 5 First we start the trace using the ALTER EVENT SESSION command and then three stored procedures are executed. Since this target of this event session is a trace file instead of an in memory target, we can use ALTER EVENT SESSION again to stop the trace without losing our target data. The first two stored procedures perform exactly the same work, except that the first uses 200 transactions, while the second uses a single 6. 7. Click the Advance button. Click the Execute button.
transaction. Back at the command prompt, we use the logman utility again to flush the trace files and stop the traces. The XE_DEFAULT_ETW_SESSION is the name of the data collector set for the SQL Server ETW session. The tracerpt utility is then used to merge the SQL Server and Windows trace logs into a single report. This file is ordered by the timestamp of the events, so the SQL Server and Windows kernel events are intermingled. This intermingling of SQL Server and Windows kernel events is not possible using SQL Profiler. The output of tracerpt is a CSV (comma separated value) file, which may be opened in Microsoft Excel. In Windows Vista and 2008 Server, tracerpt can also produce an XML file. In the two bottom panes we have a grid summarizing some of the information available in the sp_statement_completed events, including the number of database writes, which is much higher in the sp_WorkLoad_1 procedure. The graph to the right of the grid shows the number of writes per stored procedure. 8. 9. Click the Advance button. Click the Execute button.
10. Wait for the step to complete executing and then click on the xeetw.csv hyperlink in the command window to open this file in Excel. Scroll down in the file to see that SQL Server events and intermingled with Windows kernel events, such as DiskIo.
Step 9
We can then examine the output to see that the first workload stored procedure produces a significant number of DiskIo events with a small amount of transferred data while the second workload stored procedure generates just a few DiskIo events with a larger amount of transferred data.
11. Double-click on the first row (not the header) on the grid in the lower left pane. This should be sp_WorkLoad_1. In the newly opened window, the top grid includes paired sp_statement_starting and sp_statement_completed events. The bottom grid displays the events that occurred within the selected event pair. Click on event pairs on the top grid with a value in the DiskIoEvents column greater than zero. 12. Close the window for sp_WorkLoad_1 and then double-click on the grid row for sp_WorkLoad_2. Now you can examine the DiskIo events for this second stored procedure. Step 11
XEvent – Procedure Usage This demo uses the asynchronous bucketizer target to bucket sp_statement_completed events for each stored procedure in a database. This event is fired whenever a stored procedure performs a piece of work. Administrators will then have some quick metrics for how often stored procedures are executing. Stored procedures with a high event count may need to be re-optimized or their results cached. “Deadwood” stored procedures can also be detected. Script
This first step simply cleans up any schema objects still left over from the last time the demo was run.
Click Steps
1. 2. From the desktop, launch SQL Server Management Studio. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. In Object Explorer, expand the
Screenshots
3.
Databases node. 4. 5. 6. Select the SQL2008Demo node. In the Standard toolbar, click the New Query button. From the main SSMS menu, select File -> Open -> File and browse to the C:\SQL Server 2008 Demo Files\Trusted Platform\XEvent\XEvent Demos folder. Open XEvent Procedure Usage.sql. Click the Connect button. Select all the SQL text beneath Demo Step 1.
7. 8. 9.
10. Click the Execute button. The next step is to create a new event session. This session captures the sp_statement_completed event for the current database. This event is fired whenever a statement is executed within a stored procedure. The event session is then altered to add the asynchronous bucketizer target. This will keep a counter of sp_statement_completed events for each stored procedure in the database. For purposes of this demo, we will set the MAX_DISPATH_LATENCY, or how often the event session is flushed to the buckets, to one second. This low setting is for purposes of this demo. A higher setting reduces the impact of the event session on the server. The asynchronous bucketizer is an in-memory target and thus extremely lightweight compared to SQL Profiler. Parameters available when adding the target (such as the number of 11. Select all the SQL text beneath Demo Step 2. 12. Click the Execute button.
buckets or “slots”) can further limit the resources consumed by the event session. The in-memory targets are appropriate for production systems. Here we’ve create the event session with the CREATE EVENT SESSION Transact-SQL statement and then added a target with the ALTER EVENT SESSION statement. The ALTER EVENT SESSION statement may be used to start and stop the event session and to modify the event session, such as adding a new event or a new target or dropping an event or a target. The event session definition is persisted in SQL Server system tables. They can be created ahead of time and then started and stopped on demand. They can also be configured to start automatically when the SQL Server starts. Now that we’ve defined our event session, let’s quickly create ten stored procedures we can use to run some sample queries against our test database with. The next step starts the event session and then executes a number of stored procedures, called usp_Demo_0 through usp_Demo_9. This creates sp_statement_completed events. The sequence skips usp_Demo_6 and usp_Demo_7. These procedures exist, but are not called. 13. Select all the SQL text beneath Demo Step 3. 14. Click the Execute button. 15. Select all the SQL text beneath Demo Step 4. 16. Click the Execute button.
Now we can view the event session results in the database. The event session needs to stay running, or else the results are cleared. In the results pane, the top grid shows the raw xml stored for the event session. The results for an asynchronous bucketizer target are stored in a single table cell. The middle grid displays the count of times the sp_statement_completed event has fired for each stored procedure. It may well be worth examining stored procedures with a large event count. Also, applications which call these high use procedures may need to be refactored. The bottom grid displays “deadwood” stored procedures that have not been called since the start of the event session. These queries demonstrate how easy it is to return results from an aggregate in-memory target. Using the in-memory event consumers allows a user to interrogate SQL Server directly for intelligence about behavior and bottlenecks, as opposed to manually analyzing trace files. Finally, the event session is stopped. However, the asynchronous bucketizer is such a lightweight target, that it could be left running indefinitely on a production system with negligible impact. Since the configuration for the event session is stored in system tables, an event session can be configured to automatically start with the server.
17. Select all the SQL text beneath Demo Step 5. 18. Click the Execute button. 19. Click on the cell in the first grid in the results pane underneath the Raw Xml column. 20. Close the XML Viewer window. 21. Close the query window.
Step 18
XEvent – Long Running Queries This demo uses a synchronous bucketizer target to store the SQL text of queries with a duration time over a particular threshold, in this case two seconds.
Script
First we need to delete the event session and stored procedures that may be left over from the last time we ran this demo.
Click Steps
1. 2. From the desktop, launch SQL Server Management Studio. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. In Object Explorer, expand the Databases node. Select the SQL2008Demo node. In the Standard toolbar, click the New Query button. From the main SSMS menu, select File -> Open -> File and browse to the C:\SQL Server 2008 Demo Files\Trusted Platform\XEvent\XEvent Demos folder. Open XEvent Long Running Queries.sql. Click the Connect button. Select all the SQL text beneath Demo Step 1.
Screenshots
3. 4. 5. 6.
7. 8. 9.
10. Click the Execute button. Now we’ll create an event session for the sql_statement_completed event. For the event, the sql_text action is added so that the text of the sql query is included. A predicate clause 11. Select all the SQL text beneath Demo Step 2. 12. Click the Execute button.
filters the events to those with a duration time greater than two seconds. The CPU time could have been used instead. Next, the session is altered to add the synchronous bucketizer target. The asynchronous bucketizer target could have been used just as easily. In this case, the value being bucketed is the sql_text. Since this is an action, the source_type parameter for the bucketizer must be 1. The CREATE EVENT SESSION and ALTER EVENT SESSION statements allows us to define very precise criteria for which events are considered and how they are filtered. We can also extend the data already collected with the event with actions, such as this example with the sql_text action. Other actions include collecting a stack dump or a query plan handle. Now we will create a stored procedure that hogs the CPU by adding records to a table variable and then incrementing them. The number of records and the number of increments are determined by the stored procedure parameters. We start the session and then run the usp_cpu_hog stored procedure several times. The two parameters control the amount of work that the stored procedure performs. The last two calls should exceed our threshold. 13. Select all the SQL text beneath Demo Step 3. 14. Click the Execute button.
15. Select all the SQL text beneath Demo Step 4. 16. Click the Execute button.
Now that we have run a few queries, we can see if any have exceeded our threshold of 2 seconds. First, we can query the raw XML that is stored for the synchronous bucketizer target. The second query parses the XML into tabular results. This displays the text for each query that exceeded the duration threshold plus a count of how many times the exact query has been executed. Finally, the event session is ended. Since the event session has only an in memory target, the session data is lost once the session is ended.
17. Select all the SQL text beneath Demo Step 5. 18. Click the Execute button. 19. In the first grid in the results pane, click on the row beneath the Raw Xml header. This opens a window so that it is easier to view the XML. Close the window.
Step 18
XEvent – Workload Statistics This script demonstrates how we can use the synchronous event counter target to capture workload statistics. This uses the extremely lightweight synchronous event counter target to track how many times particular events are fired.
Script
In this demo we will create an event session that uses the synchronous event counter target. This is an extremely lightweight in-memory target that keeps a counter for how many times a particular event fires. This is particularly appropriate for events for which the fact that they have fired is interesting, even without the additional data that may accompany events in the other targets. This script monitors three events: physical_page_read, file_read_completed and lock_acquired. We are going to run a query, with modifications, three times and each time view how often each
Click Steps
1. 2. From the desktop, launch SQL Server Management Studio. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. In Object Explorer, expand the Databases node. Select the SQL2008Demo node. In the Standard toolbar, click the New Query button.
Screenshots
3. 4. 5.
of these events fired.
6.
From the main SSMS menu, select File -> Open -> File and browse to the C:\SQL Server 2008 Demo Files\Trusted Platform\XEvent\XEvent Demos folder. Open XEvent Workload Statistics.sql. Click the Connect button. Select all the SQL text beneath Demo Step 1.
7. 8. First we’re just going to make sure that the event session isn’t hanging around from the last time I ran this demo. Next we will create the event session. This adds the three events: physical_page_read, file_read_completed and lock_acquired. The events are filtered so that they will only be captured for user logins. Adding the target is easy - we just have to name it. Now we will start the session and run a nontrivial query. This expands the entire AdventureWorks employee hierarchy. After the query is complete we will immediately drop the events from the session. If we stopped the event session, then the data in the in-memory targets would be lost, so we instead will drop the events to prevent capturing any more events. Here we query the in-memory targets. The first result set returns the xml in the target. As you can see this is highly readable. There’s hardly any need to parse the xml. But, the second query does that anyway. Here you can see the event counts in a grid. Note that the lock_acquired count is quite high even though 9.
10. Click the Execute button. 11. Select all the SQL text beneath Demo Step 2. 12. Click the Execute button.
13. Select all the SQL text beneath Demo Step 3. 14. Click the Execute button. 15. Scroll through the results to note the size of the result set.
16. Select all the SQL text beneath Demo Step 4. 17. Click the Execute button. 18. Click on the cell under targetXml to open the Xml viewer. 19. Close the Xml viewer. Step 17
we are returning less than 300 records. This is due to the recursion in the query. Now we are going to recreate the event session and rerun the query, but in this case, we are using the READ UNCOMMITTED Transaction Isolation Level. This of course would permit a dirty read, but may be appropriate in some circumstances. Looking at the event counters for this session, we can see that the count for the lock_aquired event has dropped dramatically. The counts for the other two events aren’t dramatically different. 20. Select all the SQL text beneath Demo Step 5. 21. Click the Execute button.
22. Select all the SQL text beneath Demo Step 6. 23. Click the Execute button.
Step 23 Let’s run the same original query again, but this time use index hints to force the optimizer to perform a table scan. This is easier than dropping the indexes. Normally, you would use index hints to try to improve performance. Note that it takes quite a bit longer to run the query. Finally, let’s look at the event counts when not using indexes. The counts for all three events have ballooned. 24. Select all the SQL text beneath Demo Step 7. 25. Click the Execute button.
26. Select all the SQL text beneath Demo Step 8. 27. Click the Execute button. 28. Close the query window.
Step 27
XEvent – Wait Info This script demonstrates how we can capture the wait_info and wait_info_external extended events. There are over 570 types of waits, which allow us to diagnose query performance at a level of detail that isn’t available in any other SQL Server diagnostic tool.
Script
In this demo we will be capturing the wait_info and wait_info_external events. The fields available with these events help us to diagnose the waits that occur when SQL queries are executing. For this demo, we will run the same query twice to compare which wait info events fire for each execution.
Click Steps
1. 2. From the desktop, launch SQL Server Management Studio. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. In Object Explorer, expand the Databases node. Select the SQL2008Demo node. In the Standard toolbar, click the New Query button. From the main SSMS menu, select File -> Open -> File and browse to the C:\SQL Server 2008 Demo Files\Trusted Platform\XEvent\XEvent Demos folder. Open XEvent Wait Info.sql. Click the Connect button. Select all the SQL text beneath Demo Step 1.
Screenshots
3. 4. 5. 6.
7. 8. First, we will make sure that the event sessions we will be creating are left behind from the last time I ran this demo. Now we will create two event sessions. For 9.
10. Click the Execute button. 11. Select all the SQL text beneath Demo
both of these sessions we are using a pairmatching target with purposely mismatched events. This will cause the pair_matching target to act like a ring buffer, collecting event data and then discarding older data as the memory allocated for the target fills. Since for the pair_matching target we can only capture a single event, we will create two event sessions, one for the sqlos.wait_info event and one for the sqlos.wait_info_external event. For each session, we include the event only of the session id > 50, which delineates user logins from system logins. Now we will start the event session, run a query, and then “pause” the event session by removing the events from the target. Since this is an in-memory target, it is necessary to keep the event session running to examine the target data. Before executing the query, we will execute DBCC DROPCLEANBUFFERS. This will make the query perform as if we have just recycled the SQL Server. This query creates a pivot table of AdventureWorks sales for each product by year. Now we can examine the wait info events for the query. The first query here shows the contents of the target_data field for the two pair_matching targets. Let’s open the XML file, and we can see that extensive information is captured in each wait_info or wait_info_external event. The wait_type will help us categorize the events. The next query parses the XML into a temp
Step 2. 12. Click the Execute button.
13. Select all the SQL text beneath Demo Step 3. 14. Click the Execute button.
Step 14 15. Select all the SQL text beneath Demo Step 4. 16. Click the Execute button. 17. In the first Results set, click on one of the targetXml cells to open the Xml viewer. 18. Close the Xml viewer. 19. Scroll if necessary to the second result Step 16
table and the following two queries select from this table. In these last two queries, we’ve joined to a WaitEvents table that has been added to this particular AdventureWorks database. This gives us a description for each wait_type value. Future CTPs will contain this information in a dynamic management table. The first grid sums the total_wait_time by wait_type. We can see that the PWAIT_PAGEIOLATCH_SH event takes account for most of the wait time. Now we will run the query again without the DBCC DROPCLEANBUFFERS command. Before this will we add the events back to the event sessions and the stop and restart the events. This clears the data in the targets. Now we will rerun the same query.
set. 20. Scroll through the third result set.
21. Select all the SQL text beneath Demo Step 5. 22. Click the Execute button.
23. Select all the SQL text beneath Demo Step 6. 24. Click the Execute button.
When we parse the target XML we can see that the wait times are significantly reduced and we have far fewer wait_info and wait_info_external events.
25. Select all the SQL text beneath Demo Step 7. 26. Click the Execute button. 27. Scroll through the result sets as necessary. Step 26
We are going to run the event sessions one more time, to see what wait events are generated running DBCC CHECKTABLE on one of the tables in the AdventureWorks database. First, we need to recycle the event sessions
28. Select all the SQL text beneath Demo Step 8. 29. Click the Execute button.
once again. Now let’s run the DBCC command. 30. Select all the SQL text beneath Demo Step 9. 31. Click the Execute button. Finally, we will examine the waits for the DBCC. There are many more wait events than for the prior queries, especially wait_info_external events. This is expected since the DBCC should cause some disk IO operations. There are more than 570 values for the wait_type. 32. Select all the SQL text beneath Demo Step 10. 33. Click the Execute button. 34. Scroll the result sets to demonstrate the large number of captured events. 35. Close the query window. Step 33
Provide Predictable Response
SQL Server 2008 also enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor will allow organizations to define resource limits and priorities for different workloads which enable concurrent workloads to provide consistent performance. Resource Governor SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. Resource Governor enables database administrators to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to end users.
Script
Click Steps
Screenshots
The new Resource Governor feature in SQL Server 2008 allows database administrators to prioritize resources, such as CPU and memory, across different groups of users. Resource Governor is flexible enough to allow administrators to change resource utilization dynamically, and quickly be able to adjust to changing conditions. In this demo we will throttle the CPU utilization for a group of users. This could be used to limit resources for lower priority operations, allowing higher priority operations to complete faster. Before SQL Server 2008, this would have required an external throttling mechanism, such as an import program that pauses itself between inserts. First we will launch SSMS. On this server we have added 3 logins, UserExecutive, UserMarketing and UserProduction.
1. 2.
From the desktop, launch SQL Server Management Studio. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. In the Object Explorer pane, expand the Security node. Expand the Logins node.
3. 4.
Steps 3-4
In Object Explorer, under the Management node, we now have this new Resource Governor object. Within Resource Governor we can manage different Resource Pools. These three existing resource pools are associated with the three logins we just looked at.
5. 6. 7.
In the Object Explorer pane, expand the Management node. Expand the Resource Governor node. Expand the Resource Pools node.
Steps 5-7
If we look at the PoolExecutive resource pool, we can see that it contains Workload Groups. Logins are categorized into workload groups and groups are categorized into pools.
8. 9.
Expand the PoolExecutive node. Expand the Workload Groups node.
Steps 8-9 SQL Server 2008 ships with two system resource pools and workload groups - default and internal. Internal SQL Server logins are assigned to the internal group. User logins are assigned to the default group unless they are placed into another group. 10. Under the Resource Pools node, expand the System Resource Pools node.
A classifier function is used to place logins into a workload group. This function is called when the user connects to the server. We’ve already created a classifier function called CLASSIFIER_V1 in the master database. If you look at this function, you can see that we have assigned users to workload groups based on their login name. But, you can use any classification scheme you wish.
11. In the Object Explorer pane, expand the Databases node. 12. Expand the System Databases node. 13. Expand the master node. 14. Expand the Programmability node. 15. Expand the Functions node. 16. Expand the Scalar-valued Functions node. 17. Under Scalar-valued Functions, right click on dbo.CLASSIFIER_V1 and select Script Function As | CREATE To | New Query Editor Window. 18. Close the newly opened query window. Steps 11-16
If we open the resource governor dialog, you can see that all the resource pools are listed and that for the selected resource pool, the workload groups are listed. For a pool we can assign a minimum and maximum CPU percentage and a minimum and maximum memory percentage. We can also modify groups to allocate resources, but in this demo we will be changing the PoolMarketing Maximum CPU %.
19. In the Object Explorer pane, right-click on Resource Governor and choose Properties. 20. Resize the window so all five resource pools are displayed in the Resource pools grid. 21. Click on the PoolMarketing resource pool. Verify that the Maximum CPU % for all three custom pools is 100%.
Step 20
Now, let’s launch a simple custom application that will stress the server. This application embeds a Performance Monitor window. This window displays the CPU utilization for each of the three pools. These new counters were added by the SQL Server 2008 installation. There is also a button for each of the users executive, marketing and production. Let’s start running some sample queries for all three users. Since all three users are cycling through the same query, the CPU utilization is approximately the same. To illustrate how Resource Governor can help prioritize workloads on the server, let’s decide that queries coming from the Marketing department should be given lower priority. We want to ensure that queries sent by the Executive and Production groups always receive precedence. To accomplish this, we’ll simply modify PoolMarketing to change the Maximum CPU % to 10%. Now you can see that the chart line for PoolMarketing is consistently below the CPU utilization lines for the two other pools.
22. Leaving the Resource Governor Properties window open, launch the Resource Governor Demo from the desktop. 23. Click the Start Executive User button. 24. Click the Start Marketing User button. 25. Click the Start Production User button.
Steps 23-25 26. Return to the Resource Governor Properties dialog. 27. Enter 10 in the Maximum CPU % for PoolMarketing and click the OK button. This will close the dialog. 28. Return to the Resource Governor Demo application.
Step 27 Finally, we’ll restore PoolMarketing to 100%. We can see that the CPU utilization for PoolMarketing is now similar to those for the other two pools. 29. Return to SSMS. 30. Open the Resource Governor Properties dialog again. 31. Change Maximum CPU % for Pool Marketing to 100 and click the OK button.
32. Return to the Resource Governor Demo application. 33. Click the Stop Executive User, Start Marketing User and Stop Production Users buttons. 34. Close the Resource Governor Demo application.
Plan Guides / Plan Freezing SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.
Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
Intelligent Platform
Scale and Manage Growing Volumes of Data
SQL Server 2008 enables enterprises to scale and manage large numbers of users and data with improved query performance on large tables, optimized queries for data warehousing scenarios, as well as increased I/O performance with efficient and cost effective data storage. SQL Server 2008 also enables users to integrate growing volumes of data by providing a highly scalable data integration platform with Integration Services, and consolidating real-time data into the data warehouse with Change Data Capture functionality. Change Data Capture To help track data changes and ensure data warehouse consistency, SQL Server 2008 introduces Change Data Capture (CDC) functionality to log updates in change tables, which makes it easy to identify rows that have been modified and to determine the details of the modification and its cause.
Script
CDC in Integration Services To visualize how Change Data Capture (CDC) works, let’s run an Integration Services package on the AdventureWorks database. CDC is targeted at applications which require visibility to changes in data in specific tables over specific periods of time. The most typical example of this would be an Incremental Load process on a Data Warehouse, which is what we’ll be simulating with this Integration Services package. Such an application first needs to identify the data in the source transactional database which has changed since the last time it ran, typically the previous night. SQL Server 2008’s Change Data Capture feature allows such an application
Click Steps
1. Navigate to C:\SQL Server 2008 Demo Files\Intelligent Platform\Change Data Capture\CDCSample2.sln.
Screenshots
2. In the Solution Explorer pane, doubleclick SetupCDCSample.dtsx. 3. In the toolbar, click the Start Debugging button.
Step 3
to identify this data directly by querying the CDC tables for all records within a given time period of interest. Before CDC, such an application would require querying some system or record of changes not integral to the database engine itself. Such records depend on custom-developed triggers on each table to record changed data in log or history tables, or timestamp columns which themselves depend on triggers to update, or status columns, or a combination of these. CDC not only provides a greater level of reliability by its integration with the database engine itself, but allows a centralized and consistent format and location in which to locate the changed data and metadata regarding those changes. Before we run this package, let’s quickly look at the different elements, and what they do. First, this top task enables CDC on the sample tables and makes sure the sample data is ready for the demo. These “View” tasks will display data at points throughout the demo, allowing us to see the data as it moves through the IS package. Next, these three Modify/Insert tasks modify data in CDC-enabled tables so change data is generated and can be seen. The “Determine Extraction Time” task ensures the correct time period of CDC data is requested. “Cycle Master” represents a periodic task to ETL from CDC data to a Data Warehouse target – this will only run once in this demo but would normally be scheduled at some regular interval. Now let’s run this IS package, and see what happens. This first data viewer shows us a subset of the original, unchanged Customer table data, on which CDC has been enabled.
4. 5.
Wait until first (“Original Customer Data…”) Data Viewer displays Click Detach on data viewer to resume package execution
6. Click to highlight row with CustomerID 696. 7. 8. 9. Wait until second (“Changed Customer Data…”) Data Viewer displays Click Detach on data viewer to resume package execution Double-click the right edge of the rightmost column, so ModifiedDate values are visible. Steps 4-6
10. Click to highlight row with CustomerID 696.
Steps 7-10
Note the column values on row 696, which we’ll observe throughout this demo. This second data viewer shows the Customer table after a number of Updates, as well as quite a few inserts, have been performed. Note that the ModifiedDate is more recent than the surrounding records, the Territory has been updated from 1 to 2 and the CustomerType has been updated from ‘S’ to ‘I’. There are also now rows visible below 701, indicating that additional records have been inserted in this table. Finally, this third data viewer is a view of the system table created when CDC is enabled on a table – one of these tables is created for each table on which CDC is enabled. __$start_lsn indicates the Log Sequence Number (LSN) from which this CDC record was generated, __$operation (the 4th column) indicates the DML operation which generated this CDC record (1 = Delete; 2 = Insert; 3 = Before Update; 4 = After Update). The 6th and following columns are the state of the changed data. Note the first and second rows, reflecting the CustomerID 696 data before and after the Update was performed, and the rows below reflecting all the other inserts to the Customer table.
11. Wait until third (“CDC Data…”) Data Viewer displays. 12. Click Detach on data viewer to resume package execution.
Steps 11-12
Query Performance – Data Warehousing Queries SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star join query optimizations reduce query response time by recognizing data warehouse join patterns.
Script
Important Note: Because star join queries require multiprocessor environments to take advantage of the new parallelism enhancements, this new feature is not able to be demonstrated within a VPC. This is because Virtual PC and Virtual Server are not able to emulate multiple processor cores for running virtual machines. You must take the SQL script used in this scenario and run it natively on a system with multiple processor cores. Alternatively, when the click steps at right call for viewing an execution plan (Step #11), the presenter can open a saved execution plan stored in the same directory as the sample query, and proceed through the rest of the click steps as normal. SQL Server 2008 introduces numerous enhancements to data warehousing query performance. Let’s take a quick look at one of the most significant improvements – optimizations to star join query performance. One of the most common types of queries performed against a data warehouse is the star join query. This type of query requires data from several dimensional tables joined with a fact table to return the desired information. Producing these types of queries can be resource intensive on the host server, which is why Microsoft has focused on improving performance for star join queries in SQL Server 2008. These optimizations in query performance are achieved by extending the query processor, enabling it to detect star schemas and
Click Steps
1. 2. From the desktop, launch SQL Server Management Studio. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. From the main menu, select File -> Open -> File and browse to the C:\SQL Server 2008 Demo Files\Intelligent Platform\Star Join Queries folder. Open Star Join Sample Query.sql. Highlight Query 1 and click Execute. Highlight Query 2 and click Execute. Highlight Query 3 and click Execute. Highlight Query 4 and click Execute. In the SQL Editor toolbar, click the Include Actual Execution Plan button.
Screenshots
3.
4. 5. 6. 7. 8. 9.
10. Highlight Query 4 and click Execute. 11. In the Results pane, switch to the Execution Plan tab. 12. Scroll all the way to the right and highlight the Table Scan node by hovering over it. 13. Highlight the little yellow icon with two arrows that all the nodes have. This tells you that the query is running in parallel. Step 11 Step 9
snowflake schemas, and automatically identify dimensions and fact tables. All of this is done automatically by the query processor – there are no configuration changes that need to be made, and you don’t need to change how you write your queries. Under the hood, the optimizations in SQL Server 2008 take place in how the query engine uses bitmap filters, which are a method of performing a semi-join reduction on data being merged into a query result. Although SQL Server has supported bitmap filters for several releases now, the new enhancements allow query plans to consider bitmap filters and push the filters deeper into the query tree. Query plans can also now arrange multiple bitmap filters over the same fact table, and re-order bitmaps dynamically based on selectivity. These optimizations lead to significant performance improvements for star join queries, and allow intuitively written queries to perform well. To demonstrate how these performance enhancements work, let’s consider the sample AdventureWorksDW sample database that ships with SQL Server. For this demo, we’re going to create a new fact table by copying the data from the FactInternetSales table. In fact, we’re going to copy this data into the new table four times, so we have a large amount of data for our query to process. As you can see, our sample query has been designed to perform several complicated joins over the time and product dimensions. Let’s run the query and see what results we get.
14. Right-click on one of the Bitmap Create operator nodes, and select Properties. 15. In the Properties pane, highlight the Defined Values item. If the value of this property starts with “Opt_”, then the new Star Join optimizations were used in this bitmap operator. Depending on your system configuration, not all Bitmap Operators might be optimized. 16. Right-click on the Table Scan node and select Properties. 17. In the Properties pane, select the Predicate item and click the “…” button to view the full text string. Highlight the probe operation against the bitmap. This illustrates how even down at the table scan level, we can look at the bitmaps to determine whether the current row of data will qualify for the overall query result, even if it’s several joins up in the query plan execution. If it doesn’t qualify, then we can prune it out at this level from further processing. The earlier you can rule out query rows from processing, the better performance you will be able to achieve. This illustrates how the new Star Join optimizations use bitmap filters earlier in the query execution process to achieve better performance.
Step 14
Step 16
Now let’s enable this option to include the execution plan in the results, and run the query again. Switching to the Execution Plan pane, we can see in great detail how the query was executed, and how resource intensive each part of the operation was. The little yellow arrows that show up by each node indicate that this query did indeed run in parallel.
Partitioning Partitions enable organizations to manage large, growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances on partitioning in SQL Server 2005 by improving performance on large, partitioned tables.
Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
Backup Compression Keeping disk-based backups online is expensive and time consuming. With SQL Server 2008 backup compression, less disk I/O is required, less storage is required to keep backups online, and backups run significantly faster.
Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2
3. Step 3
Data Compression Improved data compression enables data to be stored more effectively and reduces the storage requirements for your data. Data compression also provides significant performance improvements for large input/output-bound workloads such as data warehousing.
Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
Grouping Sets GROUPING SETS is an extension to the GROUP BY clause that enables users to define multiple groupings in the same query. GROUPING SETS produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
Script
GROUPING SETS is a new extension to the GROUP BY clause. It allows you to specify aggregation totals in a more precise manner than WITH ROLLUP or WITH CUBE, producing results that previously would have required union queries. First, we’ll connect to the SQL Server and open a script file.
Click Steps
1. 2. From the desktop, launch SQL Server Management Studio. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. In Object Explorer, expand the
Screenshots
3.
This first query returns sales data from the AdventureWorks database. It does a simple group by to summarize order totals by territory, sales person and order year. We are just displaying the sales person ID instead of their names to make the sample queries a bit simpler.
Databases node. 4. 5. 6. Select the AdventureWorks node. In the Standard toolbar, click the New Query button. From the main SSMS menu, select File -> Open -> File and browse to the C:\SQL Server 2008 Demo Files\Intelligent Platform\Grouping Sets Queries folder. Open Grouping Sets AdventureWorks Sales Demo.sql. Select all the SQL text beneath Demo Step 1. Click the Execute button.
7. 8. 9. With the GROUP BY, we only get subtotals for all of the grouping columns. If we want separate subtotals for some of the grouping columns, we can use WITH ROLLUP. This gives us a left to right rollup of the grouping columns, so that we get a subtotal for everything, then a subtotal for territory, then a subtotal for territory and sales person, then the original subtotals for all three grouping columns. Since WITH ROLLUP gives up the additional subtotals moving from left to right, we don’t get a subtotal by sales person and order year alone. To add these, we can use WITH CUBE. But then, we get all the possible combinations of group columns, which can be much more than we wanted, especially if we have many group by columns. If we want more precise control of the
10. Select all the SQL text beneath Demo Step 2. 11. Click the Execute button.
12. Select all the SQL text beneath Demo Step 3. 13. Click the Execute button.
14. Select all the SQL text beneath Demo
aggregations returned, we can create a union query that includes a select statement for each aggregation that we want to see. This means repeating the selection SQL in each select statement. In this example, we want to return a subtotal by territory, sales person and order year (the original group by statement without ROLLUP or CUBE), and then add subtotals by Territory, order year and Sales person, order year. In the latter two statements, we need to add a null placeholder column for the grouping column we are not including in the subtotal. Later, if we need to modify the query to change the selection, then we will probably need to modify all three select statements. With the new GROUPING SETS syntax included in SQL Server 2008, we can get rid of the UNION statements altogether. Now we just need one select statement, with a grouping set for each aggregation. This query is equivalent to the union query above. We have a grouping set for all three grouped by columns, then one for territory and order year, and finally one for sales person and order year. We get much more control over the aggregations returned by rollup and cube, but without needing to resort to union queries. Grouping set queries are much simpler to maintain than union queries. Here, we will add an additional grouping set to get an aggregation by territory, and another with empty parenthesis to get an aggregation for all records. In the union version we would need to either add more select statements or modify one of the statements to add WITH ROLLUP to
Step 4. 15. Click the Execute button.
16. Select all the SQL text beneath Demo Step 5. 17. Click the Execute button.
18. Select all the SQL text beneath Demo Step 6. 19. Click the Execute button.
one of the GROUP BY clauses, if it happens to work. It’s much more natural to just modify the grouping sets clause. If you have many GROUP BY columns, adding grouping sets to duplicate the results from a rollup or cube operation may get complicated. However, you can use the ROLLUP and CUBE keywords in grouping sets. Moreover, you can perform rollups and cubes on subsets of the group by columns. In this last example, we will perform a rollup by territory, sales person and order year, and then add an additional grouping set for sales person and order year. The rollup grouping set is equivalent to four separate grouping sets. 20. Select all the SQL text beneath Demo Step 7. 21. Click the Execute button.
Build and Manage Sophisticated BI Solutions
With SQL Server 2008, users are able to easily create and deliver reports throughout their organizations both internally and externally. SQL Server Reporting Services will also allow users to create reports of any size or complexity along with rich formatting. SQL Server Analysis Services provides a more comprehensive and scalable analysis platform enabling you to provide a consistent set of KPIs and business metrics to all users. SQL Server 2008 enables users to easily consume and provide reports through Microsoft Office. Users have the ability to author reports directly in Microsoft Word and Excel and publish and share them within their organization with integration with Microsoft Office SharePoint Server. Integration with the 2007 Microsoft Office System Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
Reporting Services Enhancements: Report Designer, Visualizations, and Tablix SQL Server 2008 provides a number of reporting enhancements that enable you to quickly and easily generate the reports that your organization needs, in the format that you want, and in a layout that makes sense of the data they contain. Report Builder has been enhanced extensively in SQL Server 2008 to enable users to easily build ad hoc reports with any structure. The intuitive design interface makes it easy for non-developers to create business documents, such as purchase orders, invoices, and contracts, based on report data. Report Designer has also been updated for SQL Server 2008, and is now a stand-alone application that can be run separate from the Business Intelligence Development Studio, enabling information workers to more easily craft complex, professional reports. Rich formatting can make business documents and reports more intuitive and significantly easier to understand. The rich text component of SQL Server 2008 enables mixed-formatting text boxes and importing of marked up text strings, and supports the new chart formats and the Tablix data region, so that users can generate reports with high standards of visual design to convey business information clearly and logically.
Script
Perform these steps to load the report template and set the correct deployment settings in Report Designer before giving the demo.
Click Steps
1. From the Start menu, point to Programs -> Microsoft SQL Server code name Katmai -> Reporting Services -> Report Designer Preview. 2. From the Office menu, select Open. 3. Browse to C:\SQL Server 2008 Demo Files\Intelligent Platform\Reporting Services Enhancements\Product Sales.rdl. 4. From the Office menu, select Publish>Settings. 5. Change the Report Server URL to http://localhost:81/reportserver. (You just need to add the :81 part) 6. Click OK. 7. Launch Internet Explorer. 8. From the Favorites menu, select Report
Screenshots
Step 5
Manager. Tablix Tables What we’re looking at here in the new interface for Report Designer. Previously, Report Designer used to be part of the Business Intelligence Development Studio, but customers asked for the ability to deploy a robust design surface for creating complex, professional reports without having to install Visual Studio. Now, the new Report Designer is a stand-alone application that makes it easier for information workers to create the reports that your organization relies on. Looking at our design surface, you can see we’ve already created a basic table showing total sales by region. Let’s see what this looks like when we preview it. Now, let’s say we want to enhance this table by adding total order quantity by year to the right of our current data. Previously, this was something that was difficult to create, but we can use the new Tablix table structure to easily support this type of report. Tablix is a new layout structure that combines Table and Matrix data regions into a new Tablix data region. Tablix enables developers to generate reports that combine fixed and dynamic rows. Previously, layouts of this kind had to be developed by using multiple matrix data regions and shrinking row headers. Support for Tablix data regions simplifies the inclusion of combined static and dynamic data in reports, and extends the formatting and layout capabilities of Reporting Services 9. Switch back to Report Designer. 10. Click the Preview button. 11. Click the Design button. 12. Select the table to enter Edit mode. 13. Right-click on the Total Sales cell, and select Add Group -> Adjacent Right.
Step 13 14. Expand the Group Expression dropdown menu, and select the third item, for OrderYear.Value. 15. Click OK.
Step 14 16. In the top row in the new column, hover over the empty cell and expand the smart tag when it appears. 17. Select OrderYear.
Steps 16-17
significantly. To show how Tablix works, we’ll go ahead and create a new group to the right of the existing region group. This OrderYear header is actually a dynamic row that will expand to show all the years we have sales data for. Now we’ll add the OrderQuantity data we’re interested in to the rest of the rows in this column. Next, we’ll add a new subtotal column to the right of our OrderYear column, which will be used for summing up the order quantity data separately from the region data. One of the things we might want to do here is widen this top title header so it spans all the new columns in this report. As we do this, notice how these blue grids lines show up and help us “snap” to other items in the report. This is a new feature in Report Designer that really helps make it simple to align all your report elements nicely. Finally, let’s spend a quick minute cleaning up this report and making sure all the new elements are formatted correctly. We can do this by adjusting the properties of each item in this right pane, just like we would with any object in Visual Studio. Now lets’ preview the new Tablix report and see how it looks. As you can see, we’ve quickly created a very complex report that shows not just sales by region, but also quantity of sales by year. This really lets us show a lot of complex data quickly, in a very new fashion. To make this report even more complex, one 18. Right-click on the new OrderYear item, and select Expression. 19. Edit the expression to remove the Sum component. It should read “=Fields!OrderYear.Value”. 20. Click OK. 21. In the middle row of the new column, hover over the empty cell and expand the smart tag when it appears. 22. Select OrderQuantity. 23. In the bottom row of the new column, hover over the empty cell and expand the smart tag when it appears. 24. Select OrderQuantity. Steps 21-22
Step 19
25. Right-click on the top cell in the new column, and select Add Total. 26. Right-click on the top cell in this new column, and select Expression. 27. Change the expression to “Total Quantity”. 28. Click OK. 29. In the middle row of the subtotal column, hover over the empty cell and expand the smart tag when it appears. 30. Select OrderQuantity. Step 25
last thing we might want to do is add subcategories. This is as simple as dragging and dropping, and now when we preview the report, we can see the sales by region and quantity by year data are both now broken down by subcategories in the rows.
31. In the bottom row of the subtotal column, hover over the empty cell and expand the smart tag when it appears. 32. Select OrderQuantity. 33. Select the Product Sales Report text item. 34. From the top toolbar, click the Center text button. 35. Grab the right handle on the Product Sales Report text box, and drag it right until it’s even with the end of the Total Quantity column. A blue bar will show up as a guide for where to stop dragging. 36. Select the top cell for the two new columns. 37. From the top toolbar, expand the Background Color drop-down menu, and select Teal. 38. From the top toolbar, expand the Text Color drop-down menu, and select White. 39. Click the Bold button. 40. Change the Font Size to 12. 41. Select the bottom two cells for each column. 42. From the top toolbar, expand the Background Color drop-down menu, and select Gray 25%. 43. Change the bottom cell in the OrderYear column and the two cells in the Total Quantity column to Bold.
Step 37
Steps 37-43
44. Click the Preview button. 45. Click the Design button. 46. Select the table to enter Edit mode. 47. From the left pane, grab the SubCategory item and drag it below ProductCategory in the Row Groups pane. 48. Highlight all the cells in the new Subcategory column. 49. From the top toolbar, expand the Background Color drop-down menu, and select Teal. 50. From the top toolbar, expand the Text Color drop-down menu, and select White. 51. Click the Preview button. 52. Click the Design button. Charts To enhance the data visualization capabilities of SQL Server 2008 Reporting Services, Microsoft will be incorporating a new, advanced data visualization engine. The products to be incorporated include new charting and gauge visualizations. The ability to design and format rich charts will be included directly in Report Designer, giving information workers the ability to easily create rich, useful data visualizations. To see how this works, let’s add a new chart to this report that we’ve been working on. To do this, we’ll just grab this chart item and drag it onto our report. Now we’ll add the 53. Select the Product Sales Report header and the Tablix table, and drag them lower down on the design surface, again using the blue snap-to lines to help with positioning. 54. In the top ribbon, switch to the Insert tab. 55. Click the Chart button. 56. Grab the edge of the chart and resize it, so it takes up more space on the design surface. 57. Double-click on the chart to put it in Edit mode. Step 55
Step 47
ProductCategory data to our category fields, and the SalesAmount data to the data category field. This gives us a nice simple report showing sales by product category, which we can see when we preview the report. Notice as we’re previewing this chart that the Bike sales category is much larger than the other categories. This makes it difficult to easily see the difference between some of the other categories, such as clothes and accessories. Now let’s enhance the look of this chart by using some of the formatting options provided by the new visualizations. First, let’s format the vertical axis for the report, and add an axis break. This will help us address the problem with the report we just discussed. Now when we preview it, notice that we have this “tear” going through the middle of the report, indicating where the axis break occurs. The numbers on the vertical axis above the break are much larger than below the break, allowing us to more easily make comparisons between all the product categories. Another nice advantage of the new charting capabilities is the ability to use some really nice formatting options to enhance the look of the report. For example, we can apply some shading to the bars in the report, and do the same thing for the report background. This level of detail and control over report elements simply wasn’t possible with previous versions of Reporting Services. Here’s another nice ability of the new charts – we can also add multiple vertical axes to the report, allowing us to see more information in a single chart. We’re going to take the
58. Right-click on the chart background and select Legend Properties. 59. Change the Title text to read “Product Sales”. 60. Change the Legend Position to bottom middle. 61. Click OK. 62. In the left pane, under DataSet1, grab the ProductCategory item, and drag it over the “Drop category fields here” area on the design surface. 63. In the left pane, under DataSet1, grab the SalesAmount item, and drag it over the “Drop data fields here” area on the design surface. 64. In the top ribbon, switch to the Home tab. 65. Click the Preview button. 66. Click the Design button. 67. Right-click on the vertical Sales axis, and select Axis Properties. 68. Select the Enable scale breaks checkbox. 69. Click OK. 70. Click the Preview button. 71. Click the Design button. Step 67
Steps 62-63
OrderQuantity data, and also add it to the data fields. However, it might be too confusing to have both pieces of data show up as bars in this report, so let’s change the type of visualization we’re using for the OrderQuantity data. In this dialog box, we can select the type and subtype we want to use to display this data with. There are over 30 different combinations that can be used to display data with the charts, providing many different options for creating charts. Here, we’re going to change the ReportQuantity to a Smooth Line. When we preview the report again, we can see how quickly we’ve taken a basic chart, and really enhanced it with the new capabilities of the new chart visualizations.
72. Right-click the Sales Amount data series item and select Series Properties. 73. Switch to the Fill tab. 74. Select the Gradient radio button. 75. Expand the Color drop-down menu, and select Light Turquoise. 76. Expand the Secondary Color drop-down menu, and select Teal. 77. Expand the Gradient style drop-down menu, and select TopBottom. 78. Click OK. 79. Right-click on the chart background, and select Chart Area Properties. 80. Switch to the Fill tab. 81. Select the Gradient radio button. 82. Expand the Color drop-down menu, and select Gray 25%. 83. Expand the Secondary Color drop-down menu, and select Gray 50%. 84. Expand the Gradient style drop-down menu, and select TopBottom. 85. Click OK. 86. In the left pane, under DataSet1, grab the OrderQuantity item, and drag it over the “Drop data fields here” area (next to Sales Amount) on the design surface. 87. Right-click the Order Quantity data series item and select Change Chart Steps 73-77
Steps 80-84
Type. 88. Select the first item in the Line row. 89. Click OK. 90. Right-click the Order Quantity data series item and select Series Properties. 91. Switch to the Axes and Chart Areas tab. 92. Change the Value Axis radio button to Secondary. 93. Switch to the Fill tab. 94. Expand the Color drop-down menu, and select Red. 95. Switch to the Border tab. 96. Change the Line Width to 2 pt. 97. Click OK. 98. Click the Preview button. 99. Click the Design button. 100. From the Office menu, select Publish->Deploy. 101. 102. 103. 104. Click OK. Switch back to Internet Explorer. Click Reload. Click the Product Sales link. Step 88
Dimension Designer and Best Practice Alerts Through a tight interoperability with Microsoft Visual Studio, developers can easily build and maintain robust, secure, scalable BI applications. SQL Server Business Intelligence Development Studio offers a single tool that covers multiple types of BI solutions and provides a single,
consistent environment for developing ETL, analysis and reporting solutions. Intuitive BI wizards that are delivered as part of the Business Intelligence Development Studio make it easy for even novice developers to build advanced Business Intelligence models and projects. However, enabling developers to build solutions more quickly is only effective if those solutions are optimally designed. To help ensure the best possible performance and correct functionality, SQL Server 2008 includes new enhancements to the development environment that promote best practices and help developers create effective analysis solutions.
Script
Updated Create New Dimension Wizard OLAP models can be quite complex, with many interdependencies between objects. Because of this, best practices and performance tuning tips are not generally well known and are difficult to uncover. Much of the focus with SQL Server 2008 Analysis Services has been placed on enhancing the ease-of-use of the solution designer, and reducing the time it takes to create solutions. Best practices and performance tuning tips have been embedded directly into the object model and the user interface. Also, key areas of the design interface have been modified so that an optimal design is the natural outcome. Let’s move through the workflow in the Business Intelligence Developer Studio, and see how we can take advantage of these new capabilities. Here, we’re working with the standard AdventureWorks data warehouse model that ships as an example with SQL Server 2008. First, let’s create a new dimension using the Dimension Wizard. This wizard has been greatly simplified from previous versions, with fewer
Click Steps
1. From the Start menu, point to All Programs->Microsoft SQL Server code name Katmai->SQL Server Business Intelligence Development Studio. 2. From the File menu, select Open>Project/Solution. 3. Navigate to C:\SQL Server 2008 Demo Files\Intelligent Platform\Dimension Designer\Adventure Works.sln. 4. Click the Open button. 5. In the Solution Explorer pane, doubleclick on AdventureWorks.cube. 6. Right-click on the Dimensions folder, and select New Dimension. 7. Click Next. 8. Ensure the Use an Existing Table radio button is selected, and click Next. 9. Expand the Main table drop-down menu, and select FactInternetSalesReason. Highlight the warning message that is displayed. 10. Expand the Main table drop-down menu, and select DimTime.
Screenshots
Step 6
Steps 10-11
paths and branches to navigate. As we move through this wizard, you can see that when certain selections are made, warning messages are displayed down in the bottom of the dialog box, to better inform you about the choices you are making. For example, when we select a table with a composite key, a warning message is displayed recommending the creation of a named calculation in the Data Source View. When we go to select dimension attributes, we can see another change that has been made to this wizard. It’s now much more conservative about the items that are selected by default. In fact, items are not selected unless the wizard can detect a parent-child relationship between the attributes. If we try and remove the primary key from this dimension, we’ll also get a warning about why we’re not able to remove it. Now let’s go ahead and define the attributes we want to include in this dimension. As we move through this process, note that there’s an improved interface for defining the attribute type. Attribute types are now grouped in a familiar tree structure, allowing for easy navigation and selection. Finally, we can give our new dimension a name, and finish the wizard.
11. Expand the Name column drop-down menu, and select Date. 12. Click Next. 13. Attempt to uncheck the box next to Time Key. Highlight the warning message that is displayed. 14. Click in the Attribute Name text box for Time Key, and change the name to “Date”. 15. Check the boxes next to the Day Number of Week, Day Number of Month, and Day Number of Year columns. 16. In the Enable Browsing column, uncheck the boxes for Day Number of Week, Day Number of Month, and Day Number of Year. 17. Check the boxes next to the Calendar Quarter, Calendar Year, Calendar Semester, and English Month Name columns. 18. Click in the Attribute Name text box for English Month Name, and change the name to “Months”. 19. For Months, click in the Attribute Type column and expand the drop-down menu. 20. Expand the Date node. 21. Expand the Calendar node. 22. Select Month and click OK. 23. For Calendar Semester, click in the Steps 14-18
Attribute Type column and expand the drop-down menu. 24. Expand the Date node. 25. Expand the Calendar node. 26. Select Half Year and click OK. 27. For Calendar Year, click in the Attribute Type column and expand the drop-down menu. 28. Expand the Date node. 29. Expand the Calendar node. 30. Select Year and click OK. 31. For Calendar Quarter, click in the Attribute Type column and expand the drop-down menu. 32. Expand the Date node. 33. Expand the Calendar node. 34. Select Quarter and click OK. 35. Click Next. 36. In the Name text box, change the name to “NewDate”. 37. Click Finish. Steps 19-34
Dimension Structure Now that we’ve created our new dimension, we’re presented with the standard dimension structure interface. One of the things you might notice, however, is the blue squiggly line above the NewDate attribute. Mousing over this warning provides a helpful message, recommending to the developer that he might want to create a user-defined hierarchy, since no parent-child hierarchies were detected. This is our first introduction to the new build time warnings. These warnings are intended to make it easier for developers to be aware of opportunities to improve cube design, optimize performance, and generally take advantage of best practices. They will not prevent you from building or deploying your model, and are intended as an unobtrusive addition to the interface. There are over 40 of these best practices integrated into real-time design checks throughout the solution designer. Later we’ll see how to manage these alerts. Now we’ll go ahead and create our user-defined hierarchy. As we do this, you’ll notice we get a new warning, stating that we don’t have attribute relationships established between the attributes used as levels in this hierarchy. Before we look at attribute relationships, let’s look at one more enhancement here. One of the things we should do is create composite keys, so we’ll select the Calendar Quarter attribute, and edit the key columns in the Properties pane. This brings up the new dialog box for editing key columns, which provides a simpler and more straightforward method of
38. In the Attributes pane, mouse over the NewDate node with the blue squiggly line. 39. In the Attributes pane, grab the Calendar Year item and drag it into the Hierarchies pane. 40. In the Attributes pane, grab the Calendar Semester item and drag it onto the box in the Hierarchies pane. 41. In the Attributes pane, grab the Calendar Quarter item and drag it onto the box in the Hierarchies pane. 42. In the Attributes pane, grab the Months item and drag it onto the box in the Hierarchies pane. 43. In the Attributes pane, grab the Date item and drag it onto the box in the Hierarchies pane. 44. In the Hierarchies pane, mouse over the warning that appears in the top left corner of the Hierarchy box. 45. In the Attributes pane, select the Calendar Quarter attribute. 46. In the Properties pane, scroll down to the KeyColumns entry. Click in the value column and then click the “…” button to open the Key Columns dialog box. 47. In the Available Columns list, doubleclick on Calendar Year. 48. Click OK. Step 46
Steps 39-43
adding key columns. Once we’ve created our composite key, notice that we now have a red squiggly line under Calendar Quarter. Unlike the blue design-time warnings, the red squiggly lines indicate realtime error notifications. These errors must be fixed before the cube can be deployed. In this case, adding the composite key means we also need to define the NameColumn for the attribute, so we’ll go ahead and do that now. Attribute Relationship Designer Now let’s look at the new attribute relationship designer window. This new designer is used for viewing and editing attribute relationships, and includes built-in validations to aid in creating optimal dimension designs. Using this visual design surface, it’s now quite easy for us to define the attribute relationships, which we can do by dragging and dropping. One important thing we can do here is edit the relationship type, which is done by editing the arrows between attributes. If possible, you should define the relationship type as rigid, to help maintain the data structures. Let’s create a non-optimal relationship structure, and see what happens. Just like in other parts of the solution designer, we get a warning, letting us know we have a redundant attribute relationship. This can cause issues in the aggregation of data and lead to unexpected results, and should be corrected. We’ll go ahead and delete this warning, and now create a new situation, by defining a loop
49. In the Attributes pane, mouse over the red squiggly line that appears under Calendar Quarter. 50. In the Properties pane, expand the dropdown selector for NameColumn, and select (new). 51. In the Source column list, scroll down and select CalendarQuarterOfYear. 52. Click OK. 53. Click on the Attribute Relationships tab. 54. Click the drop-down button on the Date node. 55. Select the Months node, and drag it onto the Calendar Quarter node. 56. Select the Calendar Quarter node, and drag it onto the Calendar Semester node. 57. Select the Calendar Semester node, and drag it onto the Calendar Year node. 58. Expand the Zoom drop-down menu, and select 75%. 59. Double-click on the arrow between the Date and Months node. 60. Expand the Relationship type dropdown menu, and select Rigid. 61. Click OK. 62. Select the Months node, and drag it onto the Calendar Semester node. 63. Mouse-over the blue arrow to highlight Step 63 Steps 55-57
in the attribute relationships. This is going to create issues with the cube structure, and again is flagged as an error. This relationship must be adjusted before the cube can be deployed. Since we’ve fixed all the errors with the cube design as they’ve come up, we can now go ahead and build the solution. Here we can see all the warnings that come up for the cube. None of these will stop deployment, but all indicate things we might want to look at in the future. One of the things we can do here is dismiss individual warnings. You can even specify a comment for each dismissal, so that other developers can later review your reasoning for ignoring them.
the warning. 64. Right-click on the blue arrow and select Delete. 65. Click OK. 66. Select the Calendar Quarter node, and drag it onto the Date node. 67. From the View menu, select Error List. 68. From the Build menu, select Build AdventureWorks DW-X. 69. In the Error List pane, click on the Errors button. 70. In the Attribute Relationships pane, right-click on the red arrow between Calendar Quarter and Date, and select Delete. 71. Click OK. 72. From the Build menu, select Build AdventureWorks DW-X. 73. In the Error List pane, click on the Errors button. Step 66
Database Design Now, when we go to edit the database, we can see this new tab for warnings. Here we can see a list of all the different design warning rules that the solution designer is currently monitoring, and we can choose to enable or disable specific warnings or groups of warnings. Each of these rules also is ranked by importance, to allow the developer to understand the possible implications of disabling a particular rule.
74. In the Error List pane, click on the Warnings button. 75. Right-click on the description for the first warning, and select Dismiss. 76. In the Comments text box, type in a brief comment. 77. Click OK. 78. Close the Error List pane. 79. In the Solution Explorer pane, right click
In the bottom portion of this window, we can view all the dismissed warnings, and read the comments that have been left for them. If we choose, we can enable a specific warning again.
on the main AdventureWorks DW-X node, and select Edit Database. 80. Select the Warnings tab. 81. In the Design Warning Rules list, expand the Cube Design node. 82. Select the warning in the Dismissed Warnings list. 83. Click the Re-enable button.
Named Sets Script
This section coming soon.
Click Steps
4. Step 1
Screenshots
5. Step 2 6. Step 3
Productive Platform
Develop with Business Entities
As part of the next generation of the ADO.NET data access framework, the new ADO.NET Entity Framework lets developers access their data by defining business entities, such as Customers, Orders and Products, instead of tables and columns. Query and retrieve these entities natively within any .NET language with the introduction of LINQ. These technologies enable developers to program the logical entity model while administrators can define the physical implementation of the model as tables and columns. LINQ to SQL Language Integrated Query (LINQ) enables developers to issue queries against data by using a managed programming language such as C# or Visual Basic.NET, instead of SQL statements. LINQ enables seamless, strongly typed, set-oriented queries written in .NET Framework languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping Provider. SQL Server 2008 features a new LINQ to SQL Provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.
Script
In order to demonstrate the benefits of LINQ to SQL, let’s consider how we can take an existing application and update it to use LINQ for some of the database logic. Here’s our sample application, built using Visual Studio 2008. It’s a product browser application with some simple functionality. It lists all the products in the AdventureWorks database by product model number and name. You can even perform some basic searches on the products with this search box.
Click Steps
1. 2. 3. From the desktop, double-click the Microsoft Visual Studio 2008 icon. From the File menu, select Open>Project/Solution. Browse to C:\SQL Server 2008 Demo Files\Productive Platform\LINQ to SQL\ProductBrowser.sln. Click the Open button. Click the Start Debugging button on the Visual Studio toolbar. Type ‘FR’ into the search box to demonstrate that search is functioning properly.
Screenshots
4. 5. 6.
7. Now we’ve received a request to update this application. The warehouse staff has requested that we add a SellEndDate column, so they can make sure they are not stocking discontinued inventory. First let’s update this application using our standard development methodology. Currently, the application uses a parameterized SQL query via ADO.NET to handle the data retrieval. Here’s the SQL code we’re using. Let’s modify it to support the updates in the new SellEndDate column. Using some pre-built code snippets we’ve attached to the toolbox, we’re going to speed things up a bit here by quickly filling in everything we need. What we’ve done here is add a new nullable SellEndDate field to the ProductResult class, and add the SellEndDate field to the SELECT in our query, along with the code to assign a value from the resulting DataTable DataRow. It’s a nullable column, so several lines of code are required to handle this properly. 8. 9.
Close the Product Browser application. From the View menu, select Toolbox. Place the cursor at the end of line 71 (…Description { get; set; } line).
10. In the Toolbox pane, double-click 1: ProductResult – end line 71. 11. Place the cursor at the end of Line 82 (…p.Name, pd.Description line). 12. Double-click the 1: GetResults end line 82 item in the Toolbox pane (SellEndDte is misspelled, but this is intentional and will be corrected later). 13. Place the cursor at the end of Line 110 (…(string)row*“Description”+ line) 14. In the Toolbox pane, double-click 1: GetResults – end line 110. 15. Highlight the several lines inserted that were required to properly assign a nullable column to a nullable field. 16. Place the cursor at the end of Line 119 (Description = description line) 17. In the Toolbox pane, double-click 1: GetResults – end line 119. Steps 10,12,14, and17
We’ve finished the code changes required to display the new SellEndDate column, so let’s test our application and see how it looks. It looks like our compiler has thrown as error, telling us that SellEndDte is invalid. Of course it is, since we mistyped it! This is a common problem when working with, and especially editing SQL in parameterized queries or dynamic SQL. The code compiles, but the SQL is wrong and an exception is thrown. This issue is greatly reduced with LINQ, as we will have IntelliSense to help us get it right, and the compiler will complain if we still get it wrong. Now, when we debug this application, we can see the new column for SellEndDate. Let’s do a quick search again and see if we have any items with a valid date.
18. Click the Start Debugging button on the Visual Studio toolbar. 19. Note error displayed by debugger, telling us SellEndDte is invalid. 20. Click the Stop Debugging button on the Visual Studio toolbar (or hit SHIFT-F5). 21. In line 82, change “SellEndDte” to “SellEndDate” to correct the typo. 22. Click the Start Debugging button on the Visual Studio toolbar. 23. Type ‘FR’ into the search box to demonstrate that search is still functioning properly. 24. Close the Product Browser application. Step 19
Steps 23
Let’s next see how we can make the same update using LINQ to SQL. We’re going to delete the entire block of code associated with the GetResults method, and replace it with this LINQ code from the Toolbox. There are several important things to notice about this new code: - There are much fewer lines of code needed in the new GetResults method. - The 11 lines of LINQ code replace the entirety of the old parameterized query method. - There are no SQL string literals required, reducing the chance of compile or runtime errors We can see that in this case using the LINQ code has several advantages. The code is much shorter and easier to follow. There is no longer the need for so much code just to construct, initialize, execute and read the ADO.NET objects. Just define the query in the LINQ query, and all the rest is handled for us by LINQ in the background. Also, we don’t have as many worries about typos in SQL literals, as there are none required in the rewritten code – make a mistake and you’ll get compile errors.
25. Select and delete the entire GetResults method (lines 75-125), leaving the cursor in its place. 26. In the Toolbox pane, double-click 2: GetResults – LINQ to SQL. 27. Click the Start Debugging button on the Visual Studio toolbar. 28. Type ‘FR’ into the search box to demonstrate that search is still functioning properly. 29. Close the Product Browser application. Step 26
Here’s another advantage of LINQ to SQL coding. Let’s say that we’ve deployed our new application, and everyone who uses it internally is thrilled with the results, and likes having access to the additional data. Pretty soon we receive another feature request to add more columns of data to the application, to show the Product Line, Class, and Style fields for each product. Fortunately, since we’re now managing the database queries via LINQ code, it’s quite simple for us to make the additional changes. There’s no need to go in and make further modifications to the stored procedure before we update our code. All we have to do is update our project code.
30. Place the cursor at the end of line 72 (…SellEndDate , get; set; - line). 31. Double-click the 3: ProductResult – end line 72 item in the Toolbox pane. 32. Place the cursor before the end brace after p.SellEndDate on Line 90 (‘SellEndDate = p.SellEndDate-;’ line in GetResults method). 33. Double-click the 3: GetResults: line 90 before brace item in the Toolbox pane. 34. Place the cursor at the end of Line 91 (Line =) 35. Type ‘*space+p.’ (without the singlequotes) and highlight the IntelliSense popping up to let you select which field you want, preventing typos because we’re no longer dealing with literals. 36. Scroll down and select ‘ProductLine’ and type ‘,’ (comma) to complete the line. 37. Highlight the three single lines inserted that were required to properly assign three nullable columns to three nullable fields. Step 36 Steps 31, 33
Once again, we’ll test our Product Browser and see how the new enhancements work. With minimal coding, we’re now able to quickly support adding the additional data columns to the application.
38. Click the Start Debugging button on the Visual Studio toolbar. 39. Show application working same as before, but with additional requested columns. 40. Close the Product Browser application.
LINQ to Entities / ADO.NET Entity Framework A trend among database developers is to define high-level business objects, or entities, that they then map to the tables and columns stored in a database. Rather than programming against tables and columns in a database, developers use high-level entities such as ‘Customer’ or ‘Order’ to represent the underlying data. The ADO.NET Entity Framework enables developers to program against relational data in terms of such entities. Programming at this level of abstraction is highly productive and allows developers to take full advantage of entity-relationship modeling. The object services layer of ADO.NET enables the materialization, change tracking, and persistence of data as Common Language Runtime (CLR) objects. Developers using the ADO.NET Entity Framework can program against a database by using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development. Microsoft Language Integrated Query (LINQ) enables developers to issue queries against data by using a managed programming language such as C# or Visual Basic.NET, instead of SQL statements. LINQ enables seamless, strongly typed, set-oriented queries written in .NET Framework languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping Provider. SQL Server 2008 features a new LINQ to SQL Provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.
Script
Developers often spend countless hours deciphering database schemas and writing complex queries to retrieve the data that they need in their applications. The ADO.NET Entity Framework simplifies these tasks and enables developers to focus on the business logic of their applications. Enterprise systems often use data from multiple disparate sources that use different schemas and naming conventions. In addition, these data sources often use varying levels of normalization, which results in information for a particular business item being spread across multiple tables and rows. This results in developers needing to write a large amount of application logic to manage these complex database relations.
Click Steps
1. Launch Windows Explorer and browse to C:\SQL Server 2008 Demo Files\Productive Platform\LINQ to Entities. 2. Double-click ProductBrowserLINQtoEntities.sln. 3. From the Visual Studio 2008 toolbar, select the Start Debugging button (green arrow). 4. Type ‘red’ in the ‘Search for Product Name’ box to show searching is functioning normally. 5. Close the Product Browser application.
Screenshots
Step 2
Step 4
The ADO.NET Entity Framework, which is based on the Entity Data Model, enables developers to transform the relational data in database schemas into conceptual entities that can be used directly in applications. For example, the customer data in your application may be stored across multiple tables in a database. By using the ADO.NET Entity Framework, architects and developers can define a single conceptual customer entity that neatly abstracts the complex relations that are required to access and update customer data from an application. This layer of abstraction isolates the data access logic into a set of well defined entities that can be used in an application and the abstraction helps developers to concentrate on developing the application logic. Because the Entity Data Model uses entities as opposed to tables and rows, developers need a query language that interacts with those objects. Entity SQL is a new language that enables the execution of set-oriented declarative queries and updates for the entities and relationships in the Entity Data Model. Entity SQL is virtually data provider agnostic, so you can reuse queries against different database providers, which saves you coding time. Most developers use object oriented programming languages such as C# and Visual Basic for writing new code in their business applications. These languages model entities as classes and their behaviors as code, in contrast to ADO.NET which exposes data as values. This introduces an impedance mismatch between the data and the application. The ADO.NET
Entity Framework provides an object services layer that reduces this mismatch. Developers can use Object Services to build typed queries and to return, manipulate, and update results as business objects. The ADO.NET Entity Framework generates .NET classes from the Entity Data Model entities in a schema. These classes are partial classes so developers can extend them with custom business logic without affecting the generated code. These business objects can be queried by using Entity SQL or Language Integrated Query (LINQ). To see how LINQ to Entities works, let’s return to the same Product Browser application we just saw in our LINQ to SQL demonstration. This time, we’ve rebuilt the same application using LINQ to Entities. Developers often don’t have control over DB schemas and are sometimes not even asked or informed of schema changes, which might affect informal or internal applications. Something as simple as changing a field name would require adjusting code, recompiling and redistributing such applications. With LINQ to SQL, these changes are easier and more reliable, but would have still required a recompile and redeployment. Let’s see what making a change like this means to our Product Browser application built with LINQ to Entities. We’ll show this by quickly modifying the name of one of the table columns in our database. After the schema change, the application gives us an error when we try and load it, which is exactly what we expected. 6. Launch SQL Server Management Studio. 7. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. 8. Expand the Databases -> AdventureWorksEntities ->Tables node. 9. Right-click the Production.Product table node and select Design. 10. Change the ProductNumber Column Name to say ‘Number’ and then press enter on your keyboard. 11. Close the Table Designer window and click Yes when prompted to save changes. 12. Switch back to Windows Explorer and Step 12 Step 10
double-click the ProductBrowser Debug Folder. 13. Double-click the ProductBrowser.exe file. 14. When the error dialog comes up, click Close. With LINQ to Entities, such schema changes may not require a recompile or redeployment at all, but only simple changes to the deployed application’s Entity Model mapping and storage XML files. 15. Ctrl-click to select both ProductsModel.msl and ProductsModel.ssdl. 16. Right-click one of the selected files and select Send to -> Visual Studio 2008. 17. Select the ProductsModel.msl tab if it’s not already open. 18. On line 18 change the ColumnName value from ProductNumber to Number. 19. Select the ProductsModel.ssdl tab. 20. On line 50 change the Name property value from ProductNumber to Number. 21. In the Standard Toolbar, click the Save All button. 22. Close Visual Studio. 23. Double-click the ProductBrowser.exe file to open the application. 24. Type ‘red’ in the ‘Search for Product Name’ box to show searching is functioning normally. 25. Close the Product Browser application. Step 15
Database Applications Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
Project Codename “Astoria” Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
Access Your Data from Anywhere
SQL Server 2008 enables the rapid creation of occasionally connected applications that can function, disconnected from the network. Developers can create next generation applications with local data stores that automatically synchronize data with a central data store. Building Occasionally Connected Systems With mobile devices and workers on-the-go, occasionally connected has become a way of life. SQL Server 2008 delivers a unified synchronization platform that enables consistent synchronization across applications, data stores, and data types. In a joint effort with Visual Studio, SQL Server 2008 enables the rapid creation of occasionally connected applications by way of new synchronization services in ADO.NET and offline designers in Visual Studio. SQL Server 2008 provides support for change tracking, enabling customers to develop caching-based, synchronizationbased, and notification-based applications using a robust implementation with minimal performance overhead.
Script
Click Steps
Screenshots
Visual Studio 2008 has been engineered from the ground up to support the easy and rapid development of occasionally connected applications. To see how this works, let’s create a simple application that uses a local data store to cache and browse product data for the Northwind Traders database. We’ll begin by creating a new Windows Forms Application project in Visual Studio, which we’ll name SyncServices. Once this project has been created, we’re going to add a new item to the project solution called a Local Database Cache. This new service will manage the local caching of data for the application.
1.
From the desktop, double-click on the Microsoft Visual Studio 2008 icon.
2. From the File menu, select New-> Project. 3. In the Project Type pane, select the Windows project type under the Visual C# node. 4. In the Templates pane, select the Windows Forms Application template. 5. Change the project name textbox to SyncServices. 6. Click OK. 7. Expand the Solution Explorer pane if it isn’t visible. 8. Right-click on the SyncServices project, and select Add->New Item. Steps 3-5
9. From the Add New Item dialog, select Local Database Cache. 10. Change the Name textbox to NorthwindCache.sync. 11. Click the Add button. Steps 9-10
After we’ve added the Local Database Cache to the solution, this Configure Data Synchronization dialog box will pop up. First, we’ll select the database we want this application to connect to. Once we’ve selected our database, we can specify which tables we want to be able to access locally through the cache. In this case, we’ll select the Customers, Employees, and Shippers tables. Notice as we select each table that Visual Studio applies smart defaults to a variety of options, including how to handle data, updates, inserts, and deleted items. Once the Configure Tables for Offline Use dialog box has been closed, Visual Studio 2008 generates and runs the server side SQL scripts to make the recommended changes to the database to accommodate the synchronization. The scripts both to replicate and undo those changes are added to the solution. Returning to our Data Synchronization dialog, we’re going to grab this code sample that shows how to refresh the local cache and synchronize it with the database, since we’ll need it later in the demo. Accepting our changes to the main Configure Data Synchronization dialog, Visual Studio will now create the SQL Data File (.sdf) that is used to store the local data cache, and then synchronize with the primary data source to populate the cache with data.
12. From the Configure Data Synchronization dialog box that will pop up, expand the Server Connection drop-down menu and select sql08demo\sqlexpress.Northwind.dbo. 13. Click the Add button on the bottom left of the dialog.
Steps 12-13 14. In the Configure Tables for Offline Use dialog box, select the Customers, Employees and Shippers tables. 15. Click OK. 16. From the Configure Data Synchronization dialog box, click the Show Code Example link. 17. Click the Copy Code to the Clipboard button. 18. Click the Close button. 19. Click OK. Step 14
Immediately after the previous dialog box closes, we’re presented with another dialog box. This Data Source Configuration Wizard is used to create and edit data sources in the application. In this case, it needs us to specify which database objects we want in our dataset. We’ll select the three tables we configured for offline use, and close this dialog. After clicking the Finish button, Visual Studio creates the typed dataset called NorthwindDataSet and adds it to the project solution.
20. From the Data Source Configuration Wizard, expand Tables and select the Customers, Employees, and Shippers tables. 21. Click the Finish button.
Step 20
Now we’re going to use the Server Explorer to browse through the structure for the Northwind database. In addition to the three cached tables we’ve already added to the project, we’re going to add the Orders and Order Detail tables. Since we have not configured these tables for data synchronization, they will require live access to the Northwind database to retrieve data.
22. In the Solution Explorer pane, right-click on NorthwindDataSet.xsd and select View Designer. 23. If the Server Explorer pane is not visible on the left side of the Visual Studio application window, select View -> Server Explorer from the menu. 24. From the Server Explorer pane, expand the sql08demo\sqlexpress.Northwind.dbo data connection. 25. Expand the Tables node. 26. Drag the Orders and Order Detail tables onto the NorthwindDataSet design pane. 27. From the File menu, select Close. 28. When the dialog prompts you to save changes, click Yes. Step 26
Next we need to make some changes to fields associated with the Orders table, to better define how they will be used as part of our sample application. What we want to do here is change the OrderID to be used as a Label field, and then change several of the other items to be used as drop-down combo boxes. After we have finished defining how we will use these data fields, we can simply grab the entire Orders entity and drag it onto our blank form design surface. You can see that Visual Studio has now automatically populated the form will all the correct data fields, and has even added a toolbar that will allow us to page through the order records. Behind the scenes, Visual Studio has created a significant amount of code to support this form, including all the code that binds the data fields to form controls. It also adds all the typed datasets, binding sources, table adapters, and adapter managers needed to support this application. To wrap our application up, we just need to finish binding our other data entities to their respective form controls. We’ll grab the Customer entity, and drag it onto the Customer ID field on the form, and then we’ll repeat that process for the Employees and Shippers entities.
29. Switch to the Data Sources tab in the Server Explorer pane. If the Data Sources tab is not visible at the bottom of the Server Explorer pane, select Data ->Show Data Sources from the menu. 30. Click on the Orders node, expand the dropdown menu, and select Details. 31. Expand the Orders node.
Step 30 32. Change the following fields’ types (by selecting the field and selecting items from the drop down): OrderID -> Label, CustomerID -> ComboBox, EmployeeID -> ComboBox, ShipVia -> ComboBox.
Step 32
33. From Data Sources, select Orders and drag it on to Form1 in the design pane. 34. From the Data Sources pane, select the Customers table and drag it onto the Customer ID ComboBox on Form1. 35. From the Data Sources pane, select the Employees table and drag it onto the Employee ID ComboBox on Form1. 36. From the Data Sources pane, select the Shippers table and drag it onto the Ship Via ComboBox on Form1.
Steps 33-36 Now we’re ready to test this application and see how it works. Before we run the application, however, let’s also pull up the SQL Profiler tool so we can watch what happens on the Northwind database while the application is running. Here in the Profiler, we’ll create a new trace to watch activity on the Northwind database, using this template we’ve already created. Now let’s switch back to Visual Studio and compile our application. Here’s our basic form, and as you can see, it looks like it is working fine. All of the fields are populated with data, which means that all of the form controls are correctly bound to their data sources. We can page through the records using the application toolbar, to further test that the controls are working correctly. 37. From the desktop, double-click on the SQL Server Profiler icon. 38. From the File menu, select New Trace. 39. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO\SQLEXPRESS”, and then click the Connect button. 40. Change the text in the Trace name textbox to read “SyncServicesTrace”. 41. From the Template drop-down menu, select the OCS template. 42. Click the Run button. 43. Switch back to Visual Studio 2008. 44. From the Visual Studio 2008 toolbar, select the Start Debugging button (green arrow). Step 45
Locally, the application is storing the three tables we configured for offline use in SQL Server Compact Edition 3.5. This lightweight version of SQL Server offers a maintenance free, compact embedded database for single-user client applications for all Windows platforms, including Tablet PCs, Pocket PCs, Smart Phones, and desktop computers. SQL Server Compact Edition 3.5 offers essential relational database functionality in a compact size. With an approximately 5 MB memory footprint and a less than 2 MB disk footprint, SQL Server Compact Edition can run concurrently with other applications, making it effectively invisible to the user of the application. Additionally, SQL Server Compact Edition fits within the resource constraints of today's mobile devices. Behind the scenes, let’s look at what impact this is having on our database. Switching back to the SQL Profiler, notice that the only table that was queried by our application was the Orders table. Because the Customer, Employee, and Shipper tables are all cached locally, it was not necessary to query the database for any records from those tables. To enhance our application, let’s add a new button to the toolbar that actually synchronizes the local cache with the remote database. We’ll select the ordersBindingNavigator control, which handles all the functionality
45. On the Form1 application, click the Move Next button several times to page through the data. 46. Switch back to the SQL Profiler application. 47. Switch back to the Form1 application. 48. Close the Form1 application. 49. Switch back to Visual Studio 2008.
50. In the Form1.cs design pane, click on the ordersBindingNavigator item to select it. Step 50
for the top toolbar in our application. From this drop-down menu, we’re going to choose to add a new button to this toolbar. Doubleclicking on this new button will take us to the actual code relating to the button, and here we’ll simply paste in the sample code we copied earlier to perform synchronization. This code is actually part of the new Microsoft Synchronization Services for ADO.NET 1.0, which provides a set of components to synchronize data between data services and a local store. The Synchronization Services API, which is modeled after the ADO.NET data access APIs, gives you an intuitive way to synchronize data. It makes building applications for occasionally connected environments a logical extension of building applications where you can count on a consistent network connection. Since the original code in the Form1_Load method was intended for loading, and not synchronization, we need to modify it to use the Merge method. We’ll do this quickly by inserting the new code from this text file. Next, we’re going to refactor these four lines of code from the Form1_Load method into a new method that can be called from both the old Form1_Load method and our new click method for the toolbar button. Finally, we’ll change the name of the button in the Properties pane and call it “Sync Now”.
51. With ordersBindingNavigator selected, expand the Add ToolStripButton item and select Button. 52. Double-click on the new button that was added to the toolbar to bring up code view. 53. From the Edit menu, select Paste.
Step 51 54. Scroll up, and select the first six lines of code from within the Form1_Load method, and select Edit -> Insert File as Text. 55. Browse to C:\SQL Server 2008 Demo Files\Productive Platform\OCS\LoadCode.cs and click Open. 56. Select the first six lines (three lines of code and three comments) from within this method that were just pasted in, right click and select Refactor -> Extract Method. 57. Change the New method name to LoadLocalData. 58. Click the OK button. 59. Copy the line of code that was just created to the clipboard. Step 54
60. Scroll back down to the new toolStripButton1_Click method, and paste the code over the top of the “// TODO” line. 61. Close the Form1.cs code pane. 62. Right-click on the new button, and select Properties. 63. Scroll down, and change the Text attribute to “Sync Now” and press Enter. Step 60
Before we run our application again, we’re going to actually open the Customers table for the Northwind database and make a change to the first record. Let’s change the Company Name associated with this record. Loading up our application again, notice that the old company name is still showing up. This is because the local cache has not yet been synchronized with the remote database. Fortunately, we’ve just added the functionality to handle this situation. Clicking on our new Sync Now button, we can see that the new company name is now showing up on this table. As you can see, getting off the ground with the new synchronization technologies in Visual Studio 2008 is fairly easy. The next step would be to enhance this application even further, by adding support for two-way synchronization, which would allow changes and deletions made in the local cache to be reflected in the remote database.
64. Switch back to the Server Explorer pane. 65. Expand sql08demo\sqlexpress.Northwind.dbo. 66. Expand Tables. 67. Right-click on the Customers table, and select Show Table Data. 68. In the Customers table, modify the CompanyName for the data record whose CustomerID is ALFKI (the first row of data). Change the record to read “Contoso” and hit Enter. 69. From the Visual Studio 2008 toolbar, select the Start Debugging button (green arrow). 70. From the SyncServices application, click the Sync Now button on the toolbar. 71. Switch back to SQL Server Profiler. 72. Select one of the “exec sp_executesql” rows and show that the update (Merge) was accomplished via T-SQL statements which can be formulated and used anywhere you have connectivity to the SQL Server 2008
Step 68
instance. 73. Switch back to Visual Studio. 74. Click the Stop Debugging button on the toolbar. 75. Switch to the Solution Explorer tab. 76. Right-click on NorthwindCache.sync and select View Code. 77. Click at the end of Line 6 after the open brace and type “this.Customers.SyncDirection = “ (excluding the quotes). IntelliSense should pop up when you hit space after the = and suggest “Microsoft.Synchronization.Data.SyncDirecti on”. 78. Type “.B” – IntelliSense should suggest “Bidirectional”. 79. Type “;” (semicolon) to complete the line of code. You have just enabled bidirectional syncing on your new Occasionally Connected System.
Store and Consume Any Type of Data
SQL Server has led the industry in providing support for non-relational data with the introduction of OLAP Services in SQL Server 7.0 and XML support in SQL Server 2005. SQL Server 2008 builds on the strong legacy by providing new data types that enable developers and administrators to store unstructured data like documents and images. FILESTREAM Data Type The new SQL Server 2008 FILESTREAM data type allows large binary data like documents and images to be stored directly in an NTFS file system; the document or image remains an integral part of the database and maintains transactional consistency. FILESTREAM enables the storage of large binary data, traditionally managed by the database, to be stored outside the database as individual files that can be accessed using an NTFS
streaming API. Using NTFS streaming APIs allows efficient performance of common file operations while providing all of the rich database services, including security and backup.
Script
The proliferation of digital content has significant implications for the way in which organizations store and access business data. Increasingly, databases that are at the core of business applications must be integrated with unstructured data in the form of documents, images, video content, and other multimedia formats. Organizations increasingly need to be able to store and manage digital data of all formats in order to manage the information lifecycle, meet compliance requirements, and implement content management solutions. To address this challenge, organizations are now seeking to manage both relational data and unstructured data at the enterprise scale, and require a solution that comprehensively meets the needs of relational and nonrelational data storage while reducing the cost of managing and building applications for that data. Applications that rely on relational and nonrelational data typically use one of three architectures. Relational data in the database and nonrelational binary large object (BLOB) data in file systems and file servers. Relational data in the database and nonrelational data in a dedicated BLOB store. Relational data and non-relational data in the database.
Click Steps
1. From Windows Explorer, navigate to C:\SQL Server 2008 Demo Files\Productive Platform\FILESTREAM.
Screenshots
2. Double-click on FILESTREAM.ssmssln. Leave the Windows Explorer window open in the background. 3. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. Step 2
Each approach has its own benefits and drawbacks. For example, storing unstructured data in file servers or dedicated BLOB stores can reduce costs in terms of dollars per gigabyte, but typically adds complexity for managing and building applications, because the application has to maintain integrity between records in the database and the separate system that contains the BLOBs to which those records are associated. On the other hand, storing BLOBs in the database conveniently centralizes data storage, but typically comes at higher cost and can result in reduced performance. Microsoft SQL Server™ 2008 provides a flexible solution for storing unstructured data and combining it with relational data to build comprehensive solutions that encompass the full range of data across an organization. SQL Server 2008 SQL Server 2008 does this by introducing new FILESTREAM capabilities for storing BLOB data, while continuing support for standard BLOB columns through the varbinary data type. FILESTREAM is an attribute you can set on a varbinary column so that the data is stored on the file system (and therefore benefits from its fast streaming capabilities and storage capabilities) but is managed and accessed directly within the context of the database. In SQL Server 2008, you can apply the FILESTREAM attribute to a varbinary column, and SQL Server then stores the data for that column on the local NTFS file system. Storing the data on the file system brings two key benefits:
1. 2.
Performance matches the streaming performance of the file system. BLOB size is limited only by the file system volume size.
However, the column can be managed just like any other BLOB column in SQL Server, so administrators can use the manageability and security capabilities of SQL Server to integrate BLOB data management with the rest of the data in the relational database—without needing to manage the file system data separately. Defining the data as a FILESTREAM column in SQL Server also ensures data-level consistency between the relational data in the database and the unstructured data that is physically stored on the file system. A FILESTREAM column behaves exactly the same as a BLOB column, which means full integration of maintenance operations such as backup and restore, complete integration with the SQL Server security model, and full-transaction support. Application developers can work with FILESTREAM data through one of two programming models; they can use TransactSQL to access and manipulate the data just like standard BLOB columns, or they can use the Win32 streaming APIs with Transact-SQL transactional semantics to ensure consistency, which means that they can use standard Win32 read/write calls to FILESTREAM BLOBs as they would if interacting with files on the file system. In SQL Server 2008, FILESTREAM columns can only store data on local disk volumes, and some features such as transparent encryption and
table-valued parameters are not supported for FILESTREAM columns. Additionally, you cannot use tables that contain FILESTREAM columns in database snapshots or database mirroring sessions, although log shipping is supported. To see how FILESTREAM works in practice, let’s look at a simple example where we use a database with FILESTREAM to store and manipulate a collection of Windows Media streaming media files. The folder we are looking at right now is the standard data folder for SQL Server 2008, which contains all the data and log files for the relational database engine. The important thing to note right now is that there are no subfolders here. Switching back to SSMS, we’re next going to execute some code to create a FILESTREAMenabled database. In this code, we’re able to directly specify the location on the local file system where we’ll create the FILESTREAM DataContainer object. Now, when we return to our data folder, notice that a new subfolder has been created for our FILESTREAM DataContainer. It’s important to note that the contents of this folder should never be manipulated directly from the file system. All operations need to be performed using Transact-SQL or Win32 streaming APIs, in order to ensure that the database maintains integrity. 4. Switch back to the Windows Explorer window and double-click the DB Data Folder. Switch back to SQL Server Management Studio. From the View menu, select Solution Explorer. In the Solution Explorer pane, doubleclick on CREATE DB.sql. Select the lines of T-SQL under STEP 1, and click Execute. Switch back to the Windows Explorer window.
5. 6. 7. 8. 9.
Step 4
10. Scroll and find the folder ‘filestream1’ to show that SQL Server 2008 has created a folder in the filesystem for the FILESTREAM DataContainer.
Step 10
We’ve configured our database server to use FILESTREAM, and created our database, so the next step will be to create the table we’ll to store our media files in. The ID uniqueidentifier column is required on any table with a FILESTREAM column. Note that the FILESTREAM column is defined just like a VARBINARY(MAX), just with the FILESTREAM attribute added. This next set of code is used to create an additional table and permissions required by our Media Library web application. Now that we’ve set up our tables for the media archive, we’ll use this batch file to upload some sample content to work with. Next, we’ll switch back to SSMS and run a query to see what this data looks like stored in the VideoList table. For the video column, we’re just going to return the length of each video in bytes, since there’s no point in having the database actually return all the videos as query results!
11. Switch back to the SQL Server Management Studio window. 12. In the Solution Explorer pane, doubleclick on CREATE TABLE.sql. 13. In the query window, select the T-SQL under STEP 2, and click Execute. 14. Select the T-SQL under STEP 3 and click Execute. Step 12
15. Switch back to the Windows Explorer window and click the Back button on the toolbar. 16. Double-click UploadVideos.bat. 17. Wait for the upload to finish (~25 seconds) and press any key to close the command window. 18. Switch back to the SQL Server Management Studio window. 19. Select the T-SQL under STEP 4 and click Execute.
Step 16
This web application provides a more interesting front end for our FILESTREAM database. Here, we can see a list of all the videos in the database, and stream a video using Windows Media Services. All of our interactions with the FILESTREAM table have so far been through T-SQL statements, but here we’re actually using the full Win32 streaming APIs to access and stream the videos.
20. Switch back to the Windows Explorer window. 21. Double-click the Media Library link. 22. Click the Play link on any of the items. 23. When finished playing the video, close the Internet Explorer window to return to the video list page. Step 22
We’re also storing some simple metadata about each video. Notice that when we refresh the page, we can see how many plays each video has received, and the date of the last play time. We can also use this web application to upload a new video to the database. Let’s upload this video and add our own comment on it that will be added to the table in the same transaction that stores the video.
24. Click the Refresh button on the toolbar.
25. Click the Browse button at the bottom of the Media Library web page. 26. Browse to My Documents -> My Videos and select PGR3_E32005_Xbox360_Video.wmv. 27. Click Open. 28. Type ‘I love this one!’ (or any random comment) in the Comment text box. 29. Click the Upload Media button. 30. Click the Play button for the new video. 31. Close the new Windows Media Player window. 32. Close the Media Library Internet Explorer window. Step 26
Optional Code Walkthrough Behind the scenes, these are the steps that are taken to upload a new video to our FILESTREAM database: 1. 2. 3. 4. Create a Connection and Transaction as normal. Get a new GUID value to use in inserting the new row. Insert a blank row in the FILESTREAM table to write to later. Get the PathName of the FILESTREAM column in the new row.
33. In Windows Explorer, double-click on MediaLibrary.sln. 34. If Default.aspx.cs is not open in the editor pane, then in the Solution Explorer pane right-click Default.aspx and select View Code. 35. Walk through the 9 steps in the StoreUploadedMediaFile method that should appear within numbered regions. These can be expanded and collapsed with the ‘+’ icons on the left margin of the code window.
5. 6. 7.
8. 9.
Get the TransactionContext for the Transaction. Get a SafeFileHandle from which to create the FileStream we’ll write to. Get the FileStream from the SafeFileHandle using OpenSqlFileStream, and write the bytes to the FileStream as with any Stream. Clean up the Streams and SafeFileHandle. Commit the Transaction so SQL Server persists the updated FILESTREAM data.
36. Switch back to the Windows Explorer window. 37. Double-click the SQLFSStoragePlugin.sln link to open the C++ FILESTREAM Windows Media Services plugin project. 38. Highlight the code that comes up. 39. Close Microsoft Visual Studio 2008.
This next code sample illustrates the C++ code required to read a FILESTREAM column, which is very similar to the C# code we saw earlier for writing the stream. The steps involved here are: 1. 2. 3. 4. Start a Transaction (even though we’re only reading). Get the TransactionContext for the Transaction. Get the PathName for the FILESTREAM column. Get the FileStream from OpenSQlFileStream with the PathName and TransactionContext. Read the File Stream as normal, closing the Streams and Handles and Committing the Transaction afterward. 40. Switch back to the SQL Server Management Studio window.
5.
To illustrate just how far the FILESTREAM integration with SQL Server 2008 extends, let’s perform another operation on the MediaArchive database – backup. Performing a backup operation on a FILESTREAM database is no different than any other SQL Server database, even though the files themselves are stored in a separate DataContainer. For this
backup, we’re going to disable compression, but we’ll circle back to this in a minute. Now that our backup is complete, we’re going to actually go ahead and delete the MediaArchive database from the server. When we return to our database files window, notice that the subfolder associated with the DataContainer is now gone. This illustrates how the DataContainer is logically linked to SQL Server, and again underscores the necessity of maintaining database integrity by not directly manipulating the container from the file system itself. We can just as easily restore the database to the server, which will also rebuild the DataContainer and all the media files it contained.
41. In the Object Explorer pane, expand the Connect drop-down menu and select Database Engine. 42. Ensure that the Server Type is set to “Database Engine” and the Server Name is set to “SQL08DEMO”, and then click the Connect button. 43. Expand the SQL08DEMO -> Databases nodes to locate the MediaArchive DB. 44. Right-click the MediaArchive node and select Tasks -> Backup. 45. Ensure Database is MediaArchive, Backup Type is Full and Backup component is Database. 46. Switch to the Options pane. 47. In the Set backup compression dropdown menu, select ‘Do not compress backup’. 48. Click OK. 49. Click OK when the process completes. 50. Right-click the MediaArchive node and select Delete. 51. Place a check in the ‘Close existing connections’ check box. 52. Click OK. 53. Switch back to the Windows Explorer window and double-click ‘DB Data Folder’. 54. Scroll down and show that SQL Server removed the ‘filestream1’ folder when Step 47 Step 41
the FILESTREAM DB was dropped. 55. Switch back to the SQL Server Management Studio window. 56. Right-click the Databases node and select Restore Database. 57. Select the ‘From device’ radio button. 58. Click the ‘…’ button next to the ‘From device’ drop-down menu. 59. Click the Add button. 60. In the tree view, navigate to MSSQL -> Backup and select MediaArchive.bak. 61. Click OK. 62. Click OK. 63. Expand the ‘To database’ drop-down menu and select MediaArchive. 64. Check the Restore check-box by ‘MediaArchive-Full Database Backup’ and click OK. 65. Click OK when the process completes. 66. Right-click the Databases node and select Refresh. 67. Select the T-SQL under Step 4 and click Execute to show the records have been restored to the table as well. SQL Server 2008 also introduces the ability to apply compression to a backup file, allowing you to save valuable storage space for your database backups. Although this compression is negligible for compressing media files (which are typically already compressed), it can have a 68. Switch back to the Windows Explorer window and scroll if necessary to show SQL Server 2008 has created a new folder for the FILESTREAM DataContainer again.
huge impact when compressing standard relational data. For example, in this demonstration we actually populated the Users table with a large number of additional data, in order to simulate a large relational database. When we compare the size of the compressed backup with the original, you’ll notice that the results are quite significant. This can be a very useful feature when backing up large databases, and save a significant amount of storage space on backup devices.
69. Click Back on the Windows Explorer toolbar. 70. Switch back to the SQL Server Management Studio window. 71. Right-click the MediaArchive node and select Tasks -> Backup. 72. Ensure Database is MediaArchive, Backup Type is Full and Backup component is Database. 73. Click the Remove button. 74. Click the Add button. 75. At the very end of the File name text box, add CompressedMediaArchive.bak to the path name. 76. Click OK. 77. Switch to the Options pane. 78. In the Set backup compression dropdown menu, select ‘Compress backup’. 79. Click OK. 80. Click OK when the process completes. 81. Switch back to the Windows Explorer window. 82. Double-click ‘DB Backup Folder’.
Sparse Columns / Filtered Index This feature provides a highly efficient way of managing empty data in a database by enabling NULL data to consume no physical space. For example, sparse columns allows object models that typically contain numerous null values to be stored in a SQL Server 2008 database without experiencing large space costs. Sparse Columns also enable administrators to create tables with more than 1,024 columns.
Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
Integrated Full Text Search Integrated Full-Text Search makes the transition between Full-Text Search and relational data seamless, while enabling the use of full-text indexes to perform high-speed text searches on large text columns.
Script
This section coming soon.
Click Steps
1. Step 1
Screenshots
2. Step 2 3. Step 3
HierarchyID SQL Server 2008 enables database applications to model tree structures in a more efficient way than currently possible. HierarchyId is a new system type that can store values that represent nodes in a hierarchy tree. This new type features a flexible programming model. It is implemented as a CLR UDT that exposes several efficient and useful built-in methods for creating and operating on hierarchy nodes.
Script
Click Steps
Screenshots
As part of the drive to move SQL Server 2008 “beyond relational”, a new HierarchyID data type has been added. The HierarchyID type logically stores information about a single node in a hierarchy tree by encoding the path from the root of the tree to the node. Examples of how this may be used include an organizational structure, a file system, a set of tasks in a project, or a taxonomy of language terms. In this demonstration, we’ll look at some code examples of how hierarchies are currently implemented in databases, and then compare that to how the same hierarchy can be implemented with HierarchyID. For both of these examples, we’ll consider the case of a standard corporate organization structure for departments. Let’s start by building our corporate org structure the old way. First we’ll create the schema for the department hierarchy, and then add an index on the ParentID column. Next we create the top level department, with a ParentID of null, since it’s the root node in the tree. Finally, we insert all of our additional departments. Here’s our entire org table. As you can see, to create the hierarchy we use the ParentID column to reference the parent node in the DepartmentID column. Notice that creating a new department in this org table is quite trivial – all we have to do is assign a ParentID to the department.
1.
From the desktop, double-click the SQL Server 2008 Demo Selector icon.
2. From the displayed list of SQL Server 2008 demos, select “HierarchyID Data Type Demo”. 3. After the demo window has opened, click the Execute button on the toolbar at the top of the form. 4. Click the Advance button.
Step 3
Now that we’ve populated the org chart, one of the most common things we might want to do with it is display the structure of all or part of the tree. This is actually a pretty complicated operation to perform on a standard org chart, requiring some pretty complex code to traverse and explode the tree. More importantly, since the entire hierarchy needs to be calculated and exploded each time this stored procedure is run, this is actually a pretty resource-intensive procedure to perform on the server.
5. 6.
Click the Execute button. Click the Advance button.
Step 5 Let’s see how we can create this same org tree using the new HierarchyID data type. The sample table, OrgNew, is used to structure the departments of a corporation. It includes a unique identifier for the department, the department name and a HierarchyID column that stores the position of the department in the corporate structure. The table also contains a computed column for the level of the node. This is so we can create a “breadth first” index for the hierarchy. This is more efficient for returning HierarchyID values on the same level, such as the direct descendants for a department or all top-level departments. A “depth-first” index, 7. 8. Click the Execute button. Click the Advance button. Step 7
which is created just on the HierarchyID value itself, is more efficient for returning nodes that are near each other, such as all descendants for a node. We will also add the root node to the table using the department “Entire Organization”. You can set a node to the root node by using the static GetRoot method of the HierarchyID type or by doing an implicit conversion from ‘/’. Before adding new departments, we will create a new stored procedure to make this simpler. This procedure, AddDepartmentNew, takes the id and name of the new department as parameters. The third parameter is the id of the parent department. Creating a new node in this tree is quite a bit more complicated than performing the same operation in a normal org tree. Remember that all we had to do there was create a new department and assign it a ParentID. However, the advantage of using HierarchyID is that the operation of creating or inserting a node only needs to be performed once, and then the node and level will always be available. We won’t need to constantly traverse the tree to calculate node levels when interacting with the tree, greatly reducing overhead for reporting operations when compared to a standard org tree structure. We now call AddDepartmentNew to populate this table with the identical org structure shown in our previous example. 9. Click the Execute button.
10. Click the Advance button.
Step 9
Let’s look at the actual structure of the HierarchyID data type for a minute. A HierachyID is represented as a path from the root node with forward slashes to delineate levels. For example, in our organization, “Entire Organization” is the top level, with Operations, Development, and Parking as sublevels. If Field Operations was the second child of the Operations node, it would hold a node value of “/1/2/”. A HierarchyID may store the node position in a very large hierarchy. Nodes above the HierarchyID are ancestors. So, for “/1/2/2/”, both “/1/2/” and “/1/” are ancestors. The root item, “/”, is the ancestor for every node. Nodes below a HierarchyID are descendants. To assist in reporting the organizational structure, we will create a ShowDepartmentChart stored procedure. This procedure returns all departments below and including the department passed to the stored procedure. Each department is indented based on its level relative to the level of the department supplied as a parameter. If Level were not a table value, the same logic could be implemented using the GetLevel method of the HiearchyID data type. We now call ShowDepartmentChart to view the Operations department and the Entire Organization. As we discussed previously, this method of reporting a tree structure takes considerably less system resources than the comparable method with a standard org tree. Typically, 11. Click the Execute button. 12. Click the Advance button.
Step 11
reporting an org chart with a parent child schema would require a recursive procedure. Here, this can be done with just one select statement. Here we’re going to query each of these org charts a certain number of times, and put some actual performance statistics behind them. Notice the dramatic difference in HierarchyID, which requires no INSERT, DELETE, or UPDATE statements. HierarchyID also executes no transactions, and takes far less execution time overall. This really helps illustrate the differences between the two types of charts. To summarize the differences, a parent child tree is simpler to add records to, but causes much more overhead when reporting the tree structure. By comparison, the HierachyID tree uses a bit more overhead to create and insert nodes, but improves system performance when reporting the tree structure – a common operation that might be done frequently on the database server. 13. Click the Execute button. 14. Double-click on the graph. 15. Close the graph window. 16. Click the Advance button.
Step 13
Hierarchical trees constantly need to be revised and updated. For example, one other common procedure we might want to perform on our org chart is to reparent, which means to move a node so that it has a new parent. In this example, let’s move the Parking node so that it’s now a child of the Home Operations node. For a standard parent-child table, this is quite simple. We simply need to change the ParentID for the Parking node. Since all the children of the Parking node still have Parking as their ParentID, no further action needs to be taken.
17. Click the Execute button. 18. Click the Advance button.
Step 17 This same procedure is actually a bit more complex with a HierarchyID table, because not only do we have to reparent the Parking node, but also everything below it in the org tree. To make this simpler, we will add a new MoveDepartmentNew stored procedure. To move a department beneath another parent, we first will use GetAncestor to determine the current highest valued child for the new parent and then use GetDescendant to create a new unique child node for the new parent. This is the same logic used in the AddDepartmentNew stored procedure. Next the stored procedure uses the Reparent method to move the department and all its descendants beneath the new parent. Now we will call MoveDepartmentNew to move Parking, which is currently department Step 19 19. Click the Execute button. 20. Click the Advance button.
4, beneath Home Operations, department 5. ShowDepartmentChartNew is called before and after the move. Finally, let’s look briefly at a couple additional HierarchyID methods, and how comparable operations would have to be performed on a standard parent-child table. IsDescendant returns whether a node is a descendant of another node. Interestingly, a node is a descendant of itself. Here, we are querying the Operations node, to find out which nodes are two levels beneath it. GetAncestor returns an ancestor node for a node. For this, you specify the number of levels up the tree as a parameter. Here, we want to find out which node is the grandfather (ancestor two levels up the tree) of the Home Operations node. 21. Click the Execute button. 22. Close the HierarchyID demo application.
Step 21
Simplify Development of Data Applications
SQL Server makes building data applications easier by significantly enhancing the productivity of developers using T-SQL enhancements and additions for new data types to simplify development of global applications. New Date/Time Data Types SQL Server 2008 introduces new date and time data types: DATE – a date only type TIME – a time only type DATETIMEOFFSET – a time zone aware datetime type DATETIME2 – a datetime type w/ larger fractional seconds and year range than the existing DATETIME type
The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.
Script
To see how the new date/time data types work, let’s consider the scenario of an international art brokerage with offices in London, New York and Seattle. As this company acquires new art to sell, it places it in an AcquiredAsset table. Here is the schema for this table using the old SQL Server data types.
Click Steps
1. From the desktop, double-click the SQL Server 2008 Demo Selector icon.
Screenshots
2. From the displayed list of SQL Server 2008 demos, select “New Date & Time Data Types Demo”. 3. After the demo window has opened, click the Execute button on the toolbar at the top of the form. Click the Advance button. Click the Execute button. Click the Advance button.
4. Since this company may be selling art created before 1753, it uses a varchar for the PublishDate. Notice, for example, that AssetID 5 was published in 1725. Storing data this way can actually lead to problems, including unintentionally storing data in a date field that isn’t supposed to be there. AssetID 4 actually has a PublishDate of “UNKNOWN”, which may not be desirable. Since this is a varchar field, the stored procedure that inserts data into this table doesn’t return an error that this is not a date or a NULL value. What the brokerage would like to be able to do is use a date column that stores only dates or NULLs, and strictly enforces the values stored in each row. The art brokerage also produces and sells videos showcasing some of the art they have for sale, and need to store the play length for each video in the database. In order to store these 5. 6.
Step 5
video lengths, the brokerage needs to use a float value to store the length in seconds. When displaying this data to customers, however, the brokerage always needs to remember to convert this value from seconds to minutes using an algorithm, which can sometimes cause problems. It would be much better if the brokerage could just store this data as an actual time value, and not have to worry about performing conversions. Applications with a user base spread across time zones need to be careful to translate the dates to a single time zone, such as GMT, when updating the database, and there is always the chance of an application misinterpreting a date value. It would be more natural and provide greater flexibility for the brokerage if they could enhance the AcquiredDateTime column with specific time zone information. The AvailableDate column, stored as a datetime data type, also has a problem here. This column is intended just to contain a date, much like a BirthDate. However, since this is a datetime field, a rogue application, like the one used in Seattle, may inadvertently include the time portion. This could lead to issues when attempting to query the data. To address these problems, let’s see what happens when the brokerage migrates this data to SQL Server 2008, where it can now take advantage of several new data types. Here is the new schema for the AcquiredAssets table, and you’ll notice that several of the new data types are used here. 7. 8. Click the Execute button. Click the Advance button.
The first new data type shown here is the DateTimeOffset data type. This data type allows you to store a time zone offset from GMT. It also has an extended range, from 1/1/0001 to 12/31/9999, and greater precision in the time portion, up to 100 nanoseconds or 7 digits for fractional seconds. The new DateTime2 type has the same range, but excludes the time zone offset. A new Date data type has also been added to SQL Server. This allows you to store a date from 1/1/0001 through 12/31/9999 without the time portion. This can resolve many annoyances where the time portion is inappropriate, such as a birth date, and when clients in a different time zone from the server may offset the stored value. So, a DateTime value for 1965-09-05 00:00:00 on a New York server may inadvertently be changed to 1965-09-04 21:00:00 on a web server located in Los Angeles when formatting the date for a browser client. Likewise, the new Time data type stores just the time portion. This could be the time portion of a date or a time interval. Here’s the AcquiredAsset table after these new data types have been implemented. Several of the database issues that the firm struggled with before have now been addressed. The AcquiredDateTime column has been implemented with the DateTimeOffset data type. This enables the company to more accurately track when assets were acquired in different time zones. The PublishDate has now been implemented 9. Click the Execute button.
10. Click the Advance button.
Step 9
using the new Date data type. Old dates can now be stored natively, without having to store the data as characters. Blank rows in this column must now be kept as null values, preventing unwanted data from being inserted into the column. Finally, the PlayLength is now stored as the new Time data type. The length of the videos can now be properly stored as minutes and hours, instead of seconds. This is a much friendlier and useful way of storing the data, and the company doesn’t have to worry about always converting the seconds into minutes and hours when querying the data. To illustrate the benefits of using these new data types, let’s see what happens when we query both the old and new tables for assets th added on August 29 . When we perform these queries, we’ll do it from the perspective of the London office. When we run this query on the old table, all ten items are returned checked. This is because the data was all entered in the table was from the perspective of the local times zone of the office acquiring the item. This means that items th acquired on August 29 in Seattle are treated exactly the same as items acquired in London. With the new table, not all the items are checked when we perform the query. This is because of the new localized DateTimeOffset field, which now stores all data as offsets from GMT. When the London office queries data, the items acquired in the Seattle office do not show up, because from the perspective of the London office, they were not actually acquired on Step 11 11. Click the Execute button. 12. Click the Advance button.
August 29 . Implementing the DateTimeOffset data type can yield much more accurate results in certain situations where it’s necessary to be aware of time zone differences. In this example, we’re querying both tables for assets available on October 1. In the old table, only assets 1 and 6 are checked. Asset 7 (“Rare Italian Paintings”) is not checked, because it does not give an exact match with the query string because it also contains the time information as part of the data record. Unless you know to handle this in your query, you will end up with unexpected query results like this. In the new table, the AvailableDate is simply stored as a Date data type, eliminating this query issue completely. Since no time information is kept in the table, the query returns the correct results for October 1. 13. Click the Execute button. 14. Click the Advance button.
th
Step 13
Finally, this example illustrates the pain with querying the PlayLength column. In the old table, when attempting to select videos that are less than an hour in length, we have to remember to multiply minutes by seconds to arrive at the correct number for our query. With the new Time data type, all we need to do is issue a straightforward query for videos less than an hour in length.
15. Click the Execute button. 16. Close the New Date and Time Data Types demo application.
Step 15
Table Value Parameters + MERGE statement In many customer scenarios, it is necessary to pass a set of table structured values (rows) to a stored procedure/function on the server. These values may be used for populating/updating a table directly or for more complex manipulation of data for business logic purposes. Table valued parameters provide an easier way to define a table type and allow applications to create, populate and pass table structured parameters to stored procedures and functions. With the introduction of the MERGE SQL statement, developers can more effectively handle common data warehousing scenarios like checking whether a row exists and then executing an insert or update.
Script
For this example we will create a stock trading scenario. First, we’ll create a table to hold “my” stock holdings. This includes 200 shares of Microsoft, 50 shares of Starbucks and 500 shares of Cisco. Notice that we use the new Date field to store the LastTradeDate. This is a date value without the time portion appended, and is a new data type in SQL Server 2008.
Click Steps
1. From the desktop, double-click the SQL Server 2008 Demo Selector icon.
Screenshots
2. From the displayed list of SQL Server 2008 demos, select “Table Value Parameters & MERGE Statement Demo”. 3. After the demo window has opened, click the Execute button on the toolbar at the top of the form. Step 3
Next, we create a table to store sales and purchases of stock and insert some trades. This table uses the new DateTimeOffset value, which includes a time zone offset. The time zone for all the trades is -08:00, or Pacific Standard Time.
4. 5.
Click the Advance arrow on the toolbar at the top of the form. Click the Execute button.
Step 5 Now we create a new user-defined data type (UDT) named myTableType. This UDT is of type TABLE. The TABLE data type has been available since SQL Server 2000, but now we can create a UDT of TABLE with a defined schema. Here are some examples of using the new UDT. First, we create a new variable of this type and populate it with a summary of net trades from the DailyTrades table. So, the @stockTrades variable actually contains a table with the schema that we defined when creating the UDT. We can update the row values in the UDT variable using the same SQL syntax that we would use to update a table. 6. 7. Click the Advance arrow on the toolbar at the top of the form. Click the Execute button.
8. 9.
Click the Advance arrow on the toolbar at the top of the form. Click the Execute button.
Step 9
In this scenario, we want to update the stock holdings table, MyStocks, with the net changes from the stock trades. To do this, we create a stored procedure, usp_Update_Stocks. This accepts a parameter of the new myTableTable UDT. This stored procedure uses the new MERGE statement to update MyStocks. MERGE compares two tables (or table variables) and allows you to specify an action on whether there is a join on both tables (MATCH) or if the right-side table includes a record not in the leftside table (NOT MATCHED). In this stored procedure, if a record for a stock exists in both tables and the net holdings are zero, then the stock is deleted from the holdings table. If the net holdings are non-zero, then the holdings table is updated to the new value. If the stock is new for the holdings table, then a new record is inserted. To call the new stored procedure, we create a new variable for the myTableType UDT, populate it with the net changes from the trades, and then pass the UDT variable to the usp_Update_Stocks stored procedure. The stored procedure will then update the MyStocks table to reflect the stock holdings after the trades. You can see the holdings before the trades, the net changes and trades, and finally, the holdings after the trades have been applied. I’ve sold my Cisco stock, added 30 shares of Microsoft and 500 shares of Starbucks. I’ve also added some new stocks to my holdings: 150
10. Click the Advance arrow on the toolbar at the top of the form. 11. Click the Execute button.
Step 11
shares of GM and 100 shares of Amazon.
XML Enhancements SQL Server 2008 allows for a seamless transition between managing relational and nonrelational data. This enables users to easily access documents as data, encode complex hierarchies within XML, and query across both relational and text data.
Script
SQL Server 2008 improves on SQL Server 2005’s XML capabilities by adding support for XML schemas which include processContents=”lax” sections. By defining nodes in an XML document as “lax”, it specifies that elements appearing in that node which are not defined in the current schema should not invalidate the document. This can be useful in situations where you want to be able to validate one XML document against different schemas in different contexts. SQL Server 2008 supports XML schemas containing “lax” sections in typed XML columns. SQL Server 2005 could not support typed XML columns with these schemas – the data had to be stored in non-validated, generic XML columns. SQL Server 2008 also includes support for several new date / time data types, and allows XML documents to store values in xs:dateTime typed elements or attributes. The lack of support for these two things made working with certain typed XML difficult. A perfect example of this is an InfoPath XML
Click Steps
1. 2. Launch Internet Explorer. Open the Favorites Center pane, and select the XML Demo – Price Change Forms link. Click on one of the Open Form links to open the form in InfoPath. Click the Open button. Close the InfoPath form.
Screenshots
3. 4. 5.
Step 3
document, whose schema includes a lax validation section, and which does not store time zone information in xs:dateTime values. These issues made it challenging to store and work with such XML documents – until now. With SQL Server 2008, an XML column can be strongly typed with an unmodified InfoPath schema. This makes it much easier to store and relate these documents as truly relational data sources. To understand how these new XML capabilities work, let’s consider a Price Update Form for a sales and marketing organization to fill out to request product prices be updated in the database. The form is an InfoPath form that is submitted via a web service. The web service stores the form in a strongly typed XML column on SQL Server 2008, which is bound to the form’s schema. Storing the form this way allows for much richer searching, without having to extract the data programmatically or otherwise. Everything can be handled directly with XQuery, XPath, and XML-DML in T-SQL. On this webpage, we’re reviewing a list of the InfoPath forms that have previously been submitted to the database. Note here that the dates are all being represented by the new date/time data type in SQL Server 2008.
Now let’s create and submit a new price update request using this InfoPath form. Once we’ve submitted the form, you can see that it shows up on the list of requests for review.
6. 7. 8. 9.
Click the Submit New Form button. Click the Open button. In the Requested by textbox, type in a name (e.g. Steve Masters). In the Product Name textbox, type in “Bike”.
10. In the Product Number textbox, type in “abc-123”. 11. In the Old Price textbox, type in “500”. 12. In the New Price textbox, type in “400”. 13. In the Reason textbox, type in “Sales promotion”. 14. On the InfoPath toolbar, click the Submit button. 15. Click OK. 16. Close InfoPath. 17. In Internet Explorer, click the Refresh button on the address bar. The form we created was stored in a typed XML column in a database table in SQL Server 2008. Let’s do some validation on these forms in the SQL Server Management Studio, taking advantage of XML-DML. 18. Launch Windows Explorer and browse to C:\SQL Server 2008 Demo Files\Productive Platform\XML Enhancements\. 19. Double-click on the XML Enhancements.ssmssln link. Presenter Note: You might want to have these two steps completed before running the demo, so you can just switch to the Visual Studio application window.
Steps 8-13
First, we’ll run this script to identify all the forms submitted with product numbers that don’t match any in our product table. Here in the Results pane we see two InfoPath forms that are flagged by the script. Opening the XML for one of these forms, we see it’s the one we just submitted. It looks like we didn’t use a valid product number when we submitted the form. Now let’s look at these bottom results. Notice that this is the same data that we were just looking at, but the XML for the form is different. There is a new node in this XML form called that we have added via an XMLDML query while we were processing the form.
20. In the Solution Explorer pane, doubleclick on Mark Bad Product Numbers.sql. 21. On the SQL Editor toolbar, click Execute.
Step 20
22. In the Results pane, click the blue hyperlink in the last row of the top of the two grids. 23. Close the window showing the XML results. 24. In the Results pane, click the blue hyperlink in the last row of the bottom of the two grids. 25. Highlight the node in the XML. 26. Close the window showing the XML results. Step 24 Step 22
Now let’s go back and see what the actual InfoPath form we submitted looks like. Note that the form itself also displays the new Status Log section.
27. Switch back to Internet Explorer. 28. In Internet Explorer, click the Refresh button on the address bar. 29. Click the Open Form link for the bottom form. 30. Click the Open button. 31. Close InfoPath.
Step 29 We’ll perform a similar procedure here to find and identify any forms submitted for a given Product Number for which there are duplicates in the database. Once again, here is the XML before we’ve processed the form, without the statusLog node. Here’s the same XML after we’ve processed the form. Here we’ve again added a statusLog node to the XML, in order to highlight the issue we found with the contents of the submitted form. 32. Switch back to SQL Server Management Studio. 33. In the Solution Explorer pane, doubleclick on Mark Duplicate Product Nubmers.sql. 34. On the SQL Editor toolbar, click Execute. 35. In the Results pane, click the blue hyperlink in the top row. 36. Close the window showing the XML results. 37. In the Results pane, click the blue hyperlink in the bottom row. 38. Close the window showing the XML results. Step 33
Let’s add a simple text status attribute and timestamp lastStatusUpdate attribute from a separate, system schema namespace in the priceUpdateForm root element’s lax validation section. This will allow us to do some simpler status tracking. The important thing to note here is that these attributes are not a part of the InfoPathgenerated schema/namespace for the form. These are defined in a separate schema and namespace. But because the attributes are added to the form’s XML, SQL Server 2008 handles it just fine in the lax-validated section of the InfoPath schema. And InfoPath still validates and opens the form as well. Here is the XML form before we processed it and added the new attributes. And here is the same form, with the new XML from the second schema added.
39. In the Solution Explorer pane, doubleclick on Mark Status.sql. 40. On the SQL Editor toolbar, click Execute.
Step 39
41. In the Results pane, click the blue hyperlink in the last row of the top grid. 42. View Form XML, noting the absence of advsys:status and advsys:lastStatusUpdate attributes on root my:priceUpdateForm element (scroll to far far right) 43. Close the window showing the XML results. 44. In the Results pane, click the blue hyperlink in the last row of the bottom grid. 45. Scroll to the right, and highlight the new advsys:status and advsys:lastStatusUpdate attributes that have been added to the root my:priceUpdateForm element. 46. Close the window showing the XML results.
Step 41
Returning to the webpage listing all our submitted forms, we can refresh the page and see that the status column has now been updated with the current status for each InfoPath form. Clicking on the link for the form that we previously submitted, we can see that it still opens just fine, even after the new elements from the second schema and namespace were incorporated into the XML structure of the form.
47. Switch back to Internet Explorer. 48. In Internet Explorer, click the Refresh button on the address bar. 49. Click the Open Form link for the bottom form. 50. Click the Open button. 51. Close InfoPath. 52. View previously created form, noting the new Status log entry at the bottom of the form, indicating the Product Number we entered was not found in the Database. We saw this before, but now InfoPath is opening it even though the status attributes have been added from an external namespace in the Form’s lax-validated section.
Step 49
Deliver Location Intelligence
Geographical information is rapidly becoming main stream to many business applications. SQL Server 2008 provides comprehensive spatial support to consume, extend, and use location information in spatially enabled applications. The two new spatial data types that are included in SQL Server 2008 are: GEOGRAPHY Data Type This feature enables you to store geodetic spatial data and perform operations on it. Use latitude and longitude coordinates to define areas on the earth’s surface and associate geographical data with industry-standard ellipsoid such as WGS84, which is used in GPS solutions worldwide. GEOMETRY Data Type This feature enables you to store planar spatial data that conforms to industry spatial standards like Open Geospatial Consortium (OGC). This enables developers to implement “Flat Earth” solutions by storing polygons, points, and lines that are associated with projected planar surfaces, as well as naturally planar data such as interior spaces.
New GEOGRAPHY Data Type Script
GEOGRAPHY Data Type For this demo we are working with a set of data provided from the US Census Bureau that describes the geographic boundaries of US zip codes as of the 2000 US census. This dataset has been parsed and converted to SQL Server geospatial data types. Each piece of data is a polygon object comprised of latitude / longitude points which defines the border of a zip code. Let’s run a quick sample query in SSMS to see what this geospatial data looks like. This query will query the database for three zip codes in the downtown Seattle area. Here, you can see in the results that each result returns a polygon object, which defines one of the zip codes. We also have some relevant metadata about each zip code stored in the database, such as population, total housing units, etc. What’s really interesting here in the area_sq_miles column. This data is actually not stored in the database as metadata, but is being dynamically generated by using the STArea function for a geospatial polygon object. This is just one of the new functions you are able to use when you store geospatial data in SQL Server 2008. Performance of queries against spatial data is further enhanced by the inclusion of spatial index support in SQL Server 2008. You can index spatial data with an adaptive multi-level grid index that is integrated into the SQL Server database engine.
Click Steps
1. Launch Windows Explorer. 2. Navigate to C:\SQL Server 2008 Demo Files\Productive Platform\Geospatial. 3. Double-click on GEOGRAPHY Query.sql. 4. Highlight all the code for the query and click Execute. 5. In the Object Explorer, expand the Databases -> SQL2008Demo ->Tables -> dbo.zctaArea -> Indexes node, and highlight the geospatial indexes. 6. From the desktop, double-click on the Geography Virtual Earth Demo icon. 7. Click the Display Map button. 8. Switch to the SQL tab. 9. Switch to the Results tab. 10. Click on one of the links under the polygon_text column. 11. Close the Geography Points window. 12. Switch back to the Map tab.
Screenshots
Step 5
Step 7
To better visualize this geographic data, we’re going to overlay it on top of Microsoft Virtual Earth. By doing this, we’re able to take advantage of all of the rich functionality that Virtual Earth provides, including being able to zoom the map at will, switch between views, etc. On this first screen you can enter one or more zip codes or partial zip codes and the polygons for the found zip codes are overlaid onto the Virtual Earth map. Our database also contains the Census Bureau population statistics for each zip code, which we can use to shade the polygon to indicate the population density. Zip codes shaded in red indicate high population density and zip codes shaded in green represent lower population densities. After we perform a search, we can examine the Transact-SQL statements from the SQL tab and the raw results in the Results tab. This query is exactly the same as the one we previously saw in SSMS. Next we will select Show adjacent zip codes. Here we can enter a single zip code and search the database for adjacent zip codes. If we look at the SQL, this query actually searches every zip code in the database to see if it touches the criteria zip code. It uses the geography data type intrinsic STIntersects method. Geospatial indexes are essential for good performance. In this particular query, we use a query hint to request that the optimizer use a geospatial index. As you can see, the performance is quite fast, even though we are 13. Select the Show adjacent zip codes radio button. 14. Click the Display Map button. 15. Optional: Show the SQL and Results tabs as needed.
Step 14
analyzing complex polygons for over 50,000 zip code areas. This illustrates two of the key advantages of using the new geospatial data types in SQL Server 2008. First, queries against geospatial data can be highly optimized compared to previous methods of storing and representing that kind of data. Second, we can now take advantage of special geospatial methods to construct new kinds of queries, such as this example, where we’re calculating which polygons intersect our target polygon. The next screen, Show Seattle zips, routes and sites, is the starting point for a set of screens demonstrating how we can analyze geographic data. Here, we’ve added a number of fictional Contoso Coffee shops with a latitude and longitude location. We’ve also mapped out some of the Seattle area highways. If you look at the SQL results, you can see that the zip code boundaries are described as polygons, the routes as multilines and the coffee shop sites as points. This illustrates the three different kinds of geospatial objects that can be stored as objects with the new GEOGRAPHY data type. 16. Select the Show Seattle zips, routes, and sites radio button. 17. Click the Display Map button. 18. Zoom the map in so more of downtown Seattle is visible. 19. Uncheck the Density check box. 20. Check the Density check box. 21. Uncheck the Sites check box. 22. Check the Sites check box. 23. Optional: Show the SQL and Results tabs as needed. Step 17
Moving to Show sites in a zip code, here we can locate sites in a zip code. Obviously, with zip codes, this isn’t particularly impressive, since we would generally know the zip codes for a store. But, with other geographic boundary data, such as census tracts (which are more refined than zip codes), this would be much more useful. If we examine the query, we are again using the STIntersects method.
24. Select the Show sites in a zip code radio button. 25. Click the Display Map button. 26. Optional: Show the SQL and Results tabs as needed.
Step 25 The next demo, Show sites near a route, displays coffee shops near a Seattle freeway. We can choose the freeway and the distance from the freeway. Looking at the SQL query, the sites are found using the STDistance method, which returns the shortest distance between two geography instances. Besides STIntersects and STDistance, there are many other intrinsic methods, including STArea, which returns the surface area; STUnion, which combines two geography instances, and STIntersect, which returns the intersection of two geography instances. 27. Select the Show sites near a route radio button. 28. Click the Display Map button. 29. Optional: Show the SQL and Results tabs as needed.
Step 28
In Show zip codes intersecting a route, we can display all the zip codes that intersect a Seattle freeway. This is a great example of the speed of geospatial indexes, as we are actually testing the geographic boundaries of every zip code in the United States to see if it touches the selected freeway.
30. Select the Show zip codes intersecting a route radio button. 31. Click the Display Map button. 32. Optional: Show the SQL and Results tabs as needed.
Step 31 This final demo allows us to perform some analysis for where to open new coffee shops. We can search for high density zip codes in the Seattle area that have no stores. We can also search for high density zip codes that have stores to see if they are underrepresented. By visually examining the map, we can locate potential new sites that are not too close to existing sites. 33. Select the Show high density zip codes with or without stores radio button. 34. Click the Display Map button. 35. Optional: Show the SQL and Results tabs as needed.
Step 34