SQL Server 2008 Presenter Script - Release 1.5

Document Sample
SQL Server 2008 Presenter Script - Release 1.5 Powered By Docstoc
					Virtual PC Presenter Script
    Release 1.5 – RTM4 bits

   Last Updated September 19, 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
         Enterprise Key Management................................................................................................................................................................................ 7
    Spend Less Time on Ongoing Operations ................................................................................................................................................................. 8
         Declarative Management Framework (DMF) ...................................................................................................................................................... 8
         PowerShell Integration....................................................................................................................................................................................... 14
    Increase the Reliability of Applications ................................................................................................................................................................... 14
         Database Mirroring ............................................................................................................................................................................................ 14
         Page Recovery .................................................................................................................................................................................................... 17
    Reduce Troubleshooting with Improved System Analysis ...................................................................................................................................... 18
         Data Collection / Perf Warehouse ..................................................................................................................................................................... 18
         Extended Events (XEvents)................................................................................................................................................................................. 18
    Provide Predictable Response ................................................................................................................................................................................ 28
         Resource Governor ............................................................................................................................................................................................ 28
         Plan Guides / Plan Freezing ................................................................................................................................................................................ 28
Intelligent Platform ...................................................................................................................................................................................................... 30
    Scale and Manage Growing Volumes of Data ......................................................................................................................................................... 30
         Change Data Capture ......................................................................................................................................................................................... 30
         Query Performance – Star Join Queries ............................................................................................................................................................. 32
         Partitioning ......................................................................................................................................................................................................... 34
         Backup Compression .......................................................................................................................................................................................... 34
         Data Compression .............................................................................................................................................................................................. 34
         Grouping Sets ..................................................................................................................................................................................................... 35
    Build and Manage Sophisticated BI Solutions......................................................................................................................................................... 35
         Integration with the 2007 Microsoft Office System .......................................................................................................................................... 35
         Reporting Services Enhancements: Report Designer, Visualizations, and Tablix .............................................................................................. 35
         Dimension Designer and Best Practice Alerts .................................................................................................................................................... 44
         Named Sets ........................................................................................................................................................................................................ 50
Productive Platform ..................................................................................................................................................................................................... 51
    Develop with Business Entities ............................................................................................................................................................................... 51
         ADO.NET Entity Framework ............................................................................................................................................................................... 51
         LINQ to Entities .................................................................................................................................................................................................. 51
         LINQ to SQL ........................................................................................................................................................................................................ 51
         Database Applications ........................................................................................................................................................................................ 52
         Project Codename “Astoria” .............................................................................................................................................................................. 52
    Access Your Data from Anywhere........................................................................................................................................................................... 52
         Building Occasionally Connected Systems ......................................................................................................................................................... 52
    Store and Consume Any Type of Data .................................................................................................................................................................... 61
         FILESTREAM Data Type ...................................................................................................................................................................................... 61
    Sparse Columns / Filtered Index ........................................................................................................................................................................ 62
    Integrated Full Text Search ................................................................................................................................................................................ 62
    HierarchyID......................................................................................................................................................................................................... 62
Simplify Development of Data Applications ........................................................................................................................................................... 69
    New Date/Time Data Types ............................................................................................................................................................................... 69
    Table Value Parameters + MERGE statement .................................................................................................................................................... 75
    XML Enhancements............................................................................................................................................................................................ 78
Deliver Location Intelligence ................................................................................................................................................................................... 85
    New Geospatial Data Type ................................................................................................................................................................................. 85
                                                                 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 2004 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

Script                                        Click Steps                                  Screenshots

This section coming soon.                     1. Step 1
                                              2. Step 2
                                              3. Step 3



Transparent Database Encryption

Script                                        Click Steps                                  Screenshots

This section coming soon.                     1.   Step 1
                                              2. Step 2
                                              3. Step 3



Enterprise Key Management

Script                                        Click Steps                                  Screenshots

This section coming soon.                     1.   Step 1
                                              2. Step 2
                                              3. Step 3



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.
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:
        Enforce – Uses DDL triggers to prevent policy violations
        Check on Changes – Uses event notification to evaluate a policy when a relevant change occurs
        Check 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                                        Click Steps                                  Screenshots
Scenario 1: Policy Enforcement                      1.   From the desktop, double-click on the
                                                         SQL Server Management Studio icon.
In our first example, let’s take a quick look at
how the Declarative Management Framework            2. Ensure that the Server Type is set to
(MDF) works. Here, notice that we have a new           “Database Engine” and the Server Name
database we’ve just created called DMF_Demo,           is set to “SQL08DEMO”, and then click
which does not currently have any tables in it.        the Connect button.
This SQL script contains two queries, each of       3. In the Object Explorer, expand the
which will attempt to create a new table in the        Database node.
DMF_Demo database. Notice that when we run
                                                    4. Expand the DMF_Demo node.
the first query, we get an error message and the
table is not created. This error message tells us   5. Expand the Tables node.
that the table we just tried to create was in
                                                    6. Right-click on the DMF_Demo node, and
violation of the “Table Naming Policy”.
                                                       select Refresh.
Running the second query gives us better
                                                    7. From the Standard toolbar, click the
results. This table was created successfully.
                                                       Open File button.
When we right-click on the new table to inspect                                                      Steps 3-5
                                                    8. Navigate to C:\SQL Server 2008 Demo
it, notice that we have a new menu option here
                                                       Files\Business Critical\Declarative
for Policies. Each object in SQL Server 2008
                                                       Management Framework and select
now has this new option to help manage the
                                                       Policy Enforcement Demo.sql.
policies that are associated with it.
                                                    9. Click the Open button.
Viewing the policies here shows us that there is
just one policy currently active on this object –   10. Select the code under Query 1 and click
the Table Naming Policy. When we manually               the Execute button.
run the policy, we get a new table that shows us
                                                    11. Select the code under Query 2 and click
the results. The green check marks tell us that
                                                        the Execute button.
the table passed the policy check and there are
no issues we need to be concerned with.             12. Right-click on the DMF_Demo node, and
                                                        select Refresh.
So where do these new policies live in the
Management Studio? Looking under the                13. Expand the Tables node.
Management node, notice there is a new folder
                                                    14. Right-click on goodschema.goodtable,
called Policy Management. Drilling down here,
                                                        and select Policies->View.
we can see the Table Naming Policy that                                                             Steps 10-11
affected the DMF_Demo database.                     15. In the Table Naming Policy row, click the
                                                        Run link.
Let’s take a look and see what’s in this policy.     16. Click the Close button.
This window lets us define the attributes of an
                                                     17. Click the Close button.
individual policy. Notice that this policy is
currently enabled, uses a condition called           18. In the Object Explorer pane, scroll down
“disallow dbo schema for tables”, uses a filter to       and expand the Management node.
only apply to a database called DMF_Demo, and
                                                     19. Expand the Policy Management node.
is set to Enforce for execution mode, which
means it uses DDL triggers to check for policy       20. Expand the Policies node.
violations.
                                                     21. Double-click on Table Naming Policy.
Now we’ll look at exactly what this condition
                                                     22. Click the Edit button next to the
does. Each condition belongs to an individual
                                                         Condition drop-down menu.
facet, which is a collection of attributes all
associated with a logical entity. For example,       23. Click the Cancel button.
this condition uses the Table Facet, which                                                          Step 15
                                                     24. Click the Cancel button.
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.

Scenario 2: Monitoring Changes to the System         25. Under the Policies node, double-click on
                                                         the Database AutoSettings policy.
We’ve just seen an example that shows us how
polices can be used to monitor the name of an        26. Click the Edit button next to the
entity on the server, so now let’s look at an            Condition drop-down menu.
example where we’re actively monitoring the
                                                     27. Click the Cancel button.
attributes of an entity.
                                                     28. Click the Cancel button.
This Database AutoSettings policy is configured
to monitor four attributes for databases.            29. In the Object Explorer pane, right-click
Looking at the conditions, we can see what               on the Databases node and select New
those four conditions are. Note that since there         Database.
are no filters set on this policy, it applies to
                                                     30. In the Database name textbox, type in a
every database on the server.
                                                           new name for the database, such as
Let’s create a new database. Now, consider
                                                           “PolicyTest”.
what might happen if someone with access to
this server changes a couple of the default            31. Click OK.
options to settings we know will violate the
                                                       32. Right-click on the PolicyTest database,
Database AutoSettings policy. Let’s change the
                                                           and select Properties.
Auto Shrink property to True, and the Auto
Update Statistics property to False.                   33. Select the Options page.
Notice when we refresh the Object Explorer             34. Change the Auto Shrink property to
that our database is listed, but it’s flagged with         “True”.
a little red warning icon, indicating that it is not
                                                       35. Change the Auto Update Statistics
in compliance with one or more policies. When
                                                           property to “False”.
we inspect the policies that apply to this
database, we can see that the Database                 36. Click OK.                                      Steps 34-35
AutoSettings policy is effective. Looking at the
                                                       37. In the Object Explorer pane, right-click
history for this policy, we can see some new
                                                           on the SQL08DEMO machine and select
warning entries that were created just a minute
                                                           Refresh.
ago.
                                                       38. Right-click on the PolicyTest database,
Expanding one of these entries, we can get
                                                           and select Policies->View.
more details about the individual entry. Here
we see that although AutoClose and                     39. In the Database AutoSettings row, click
AutoCreateStatics passed the policy check,                 on the History link.
AutoShrink and AutoUpdateStatistics did not.
                                                       40. Click on the + sign to the left of the first
If we choose, we can now run this policy against           row in the log file summary.
the database to automatically correct the
                                                       41. Click on the link that appears in the
attributes that are not in compliance. After
                                                           Details column.
we’ve clicked this Configure button, we can now
view the details for this item, and see that it is     42. Click the Close button.
now fully in compliance with the Database
                                                       43. Click the Close button.
AutoSettings policy.
                                                       44. In the Database AutoSettings row, click        Steps 40-41
                                                           on the Run link.
                                                       45. Click the Configure button.
                                                       46. In the Details column, click on the View
                                                           link.
                                                      47. Click the Close button.
                                                      48. Click the Close button.
                                                      49. Click the Close button.

Scenario 3: Creating a Policy / Facets                50. In the Object Explorer pane, expand the
                                                          Facets node (under Policy
Now that we’ve seen how a couple existing
                                                          Management).
policies work, it’s time for us to create a new
policy from scratch.                                  51. Double-click on the Database facet.
Before we do that, however, let’s take a quick        52. Click the Close button.
detour and look in a little bit more detail at the
                                                      53. Double-click on the Surface Area Facet.
facets we discussed earlier.
                                                      54. Click the Close button.
Here is a list of all the Facets that are currently
in the system. Each of these facets contains a        55. Right-click on the DMF_Demo database,
logical grouping of properties for a particular           and select Policies->Facets.
entity, or aspect of SQL Server 2008. For
                                                      56. Click the New Policy from Facet button.
example, here are all the attributes that can be
used to define properties for a Database object.      57. Click the Cancel button.
This Surface Area facet is interesting, because it    58. Click the Close button.
contains a collection of attributes related to the
                                                      59. Right-click on the Policies node (under
overall security of this SQL Server instance. This
                                                          Policy Management) and select New
could make auditing the security of the entire
                                                          Policy.
system much easier, by monitoring the server’s
compliance with an established security policy.       60. In the Name textbox, type in “App
                                                          Roles”.                                    Steps 51, 53
If we go back and look at the Policies options for
our DMF_Demo database, we can see that we             61. Click the New button to the right of
can view the properties of each facet that apply          Condition.
to this object. We can even create a new policy
                                                      62. In the Name textbox, type in “App Role”.
based on this facet, and either create it on the
local system or export it to a file. Currently        63. Expand the Field drop-down menu, and
polices can be exported as XML, but soon                  select Name.
support will be added for exporting in SML
                                                      64. Expand the Operator drop-down menu,
markup, which will be fully compatible with                                                          Steps 62-65
                                                          and select LIKE.
Microsoft System Center products.
Now let’s create a new policy. This policy will         65. In the Value text box, type in “AR_%”.
contain a new condition for the ApplicationRole
                                                        66. Click the OK button.
facet, which says that each Application Role
must begin with “AR_”. Once the new condition           67. Click the checkbox next to
has been added, we’ll make sure this policy                 Server/Database/ApplicationRole.
applies to all Application Roles on the server,
                                                        68. Select the “Enforce” Execution mode
that the Execution mode is set to enforce, and
                                                            radio button.
finally that the policy is actually enabled.
                                                        69. Click the Enabled checkbox.
Now that our new policy has been created, we
should perform a quick check to make sure the           70. Click the OK button.
policy is valid. It looks like everything is fine, so
                                                        71. Right-click on the App_Roles policy, and
let’s test this policy out by trying to create a
                                                            select Test Policy.
new application role for the DMF_Demo
database.                                               72. Click the Close button.
                                                                                                       Steps 67-69
We’ll call this new role “Bad_Role”, and assign it      73. In the Object Explorer, expand the
to the dbo schema. Just as we expected, when                Security node under the DMF_Database
we tried to create this new application role, we            node.
received an error message warning us that the
                                                        74. Expand the Roles node.
application role name violated the policy we
just created. If we change the name of this             75. Expand the Application Roles node.
application role, by adding an “AR_” in front of
                                                        76. Right-click on the Application Roles
the name, it passes the policy check and the
                                                            node, and select New Application Role.     Steps 77-79
application role is created.
                                                        77. In the Role name textbox, type in
                                                            “Bad_Role”.
                                                        78. In the Default schema textbox, type in
                                                            “dbo”.
                                                        79. In the Password and Confirm password
                                                            textboxes, type in “pass@word1”.
                                                        80. Click the OK button.
                                                        81. Click the OK button.
                                                        82. Change the name in the Role name
                                                            textbox to “AR_Good_Role”.
                                                 83. Click the OK button.



PowerShell Integration

Script                                           Click Steps                                  Screenshots

This section coming soon.                        1.   Step 1
                                                 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.

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
Script                                                Click Steps                                  Screenshots

Scenario 1: Establishing the Mirror                   1. From the desktop, launch SQL Server
                                                         Management Studio.
Database availability is critical, especially for
high volume customer-facing applications like e-      2.   Ensure that the Server Type is set to
commerce sites.                                            “Database Engine” and the Server Name
                                                           is set to “SQL08DEMO”, and then click
SQL Server 2008 provides a number of methods
                                                           the Connect button.
to achieve and maintain high availability in
different situations. These include failover          3. From the File menu, select Open ->
clustering, database mirroring, and replication.         Project/Solution.
With database mirroring, the primary database         4. Navigate to C:\SQL Server 2008 Demo
and a secondary mirror are kept synchronized.            Files\Trusted Platform\Database
If the primary database fails for any reason, and        Mirroring\Establish Mirror, and select
the database instances have been configured              Mirroring.ssmsln.
for automatic failover, then the mirror will take
                                                      5. Click OK.
over the duties of the primary. Users experience
little or no disruption.                              6. Double-click on the “1 –
                                                         PrimaryEndPointSetup.sql” script.
A DBA can quickly configure mirroring by
defining primary and secondary databases.             7. Click the Execute button.
Mirroring optionally allows configuration of a        8. Double-click on the “2 –
third database instance, called the witness. The         MirrorEndPointSetup.sql” script.
witness monitors both databases and serves as
                                                      9. Click the Execute button.
the final arbiter in case of a failure and confirms
the need for a failover operation. With the           10. Double-click on the “3 –
witness in place, failover is very fast, so               WitnessEndPointSetup.sql” script.
customers are not affected if there is a problem.
                                                      11. Click the Execute button.
Notice that Object Explorer shows that the
                                                      12. Double-click on the “4 –
mirror has been established between the
                                                          MirrorSetupPartner.sql” script.
primary and mirror instances.                                                                                    Steps 6,8,10,12, and 14
                                                      13. Click the Execute button.
                                                      14. Double-click on the “5 –
                                                          PrimarySetupPartner.sql” script.
                                                      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     Step 18
                                                       Databases under SQL08DEMO \MIRROR,
                                                       and point out the AdventureWorks
                                                       database is Mirror, Synchronized.




                                                                                                 Step 20

Scenario 2: Simulating a Failover                  21. From the desktop, double-click on the
                                                       Database Mirroring Demo icon.
To demonstrate database mirroring, we are
going to use a sample application that performs    22. Click the Begin Test button.
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.                                                                                        Step 22
Watch what happens when the primary                 23. Click the Fail Primary Instance button.
database fails. After a brief delay, the mirror
                                                    24. Click the End Test button.
picks up the transactions that the primary was
handling. This happens within a matter of           25. Click the Restart Primary Instance
seconds, because the witness instance                   button.
configured earlier was able to detect the failure
                                                    26. Close the Mirroring Application.
in the primary database server and switch the
active server to the mirror.                        27. Return to SQL Server Management
                                                        Studio.
Using SQL Server 2008 database mirroring, you
can help ensure that in the case of a database      28. In Object Explorer, right-click
failure, customers can continue to use database         sql08demo\mirror, and select
applications without interruption.                      Disconnect.
                                                    29. Right-click the AdventureWorks                          Step 23
                                                        database and select Properties.
                                                    30. Select the Mirroring page.
                                                    31. Click the Remove Mirroring button.
                                                    32. Click Yes.
                                                    33. Click OK.



Page Recovery

Script                                              Click Steps                                   Screenshots

This section coming soon.                           1.   Step 1
                                                    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

Script                                        Click Steps                                    Screenshots

This section coming soon.                     1.   Step 1
                                              2. Step 2
                                              3. Step 3



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.

XEvent – Queryable Schema
This demo highlights the key components of XEvent, including objects, events, and actions.


Script                                        Click Steps                                    Screenshots
All the metadata relevant to XEvents is stored in   1.   From the desktop, double-click the SQL
system tables. XEvent objects are stored in              Server 2008 Demo Selector icon.
packages. Packages are binaries, but the
                                                    2. From the displayed list of SQL Server
objects within packages are described in the
                                                       2008 demos, select “XEvent Queryable
XEvent metadata.
                                                       Schema Demo”.
This top grid in the results pane shows the three
                                                    3. After the demo window has opened,
XEvent packages, package0, sqlos and sqlserver.
                                                       click the Execute button on the toolbar
The next grid show the total count of objects by
                                                       at the top of the form.
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.




                                                                                                  Step 3
There is a great deal of events - many more          4.   Click the Advance (forward arrow)
than those available to SQL Profiler and many at          button.
a much deeper level. Fortunately,the XEvent
                                                     5.   Click the Execute button.
metadata groups these events into channels
and keywords. This first grid shows all the          6.   Scroll the right pane to bring the second
available events, sorted by channel and                   grid (Fields Available in Events) into
keyword. The channel can help a user isolate              view.
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                                                      Step 6
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.       7.   Click the Advance button.
It’s possible for a single event session to have
                                                     8.   Click the Execute button.
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
                                                                                                      Step 8
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.
Actions are performed when an event is fired      9.   Click the Advance button.
and may be used to collect additional
                                                  10. Click the Execute button.
information that is available to a target. For
example, the sqlserver.sql_text action adds the
text of the executing SQL query.




                                                                                                               Step 10



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                                            Click Steps                                    Screenshots

This code creates an event session to send SQL    1.   From the desktop, double-click the SQL
Server events to an ETW (Event Tracing for             Server 2008 Demo Selector icon.
Windows) session. The events include
                                                  2.   From the displayed list of SQL Server
sp_statement_starting and
                                                       2008 demos, select “XEvent Procedure
sp_statement_completed, which bracket a SQL
                                                       & Kernel Analysis Demo”.
statement executed within a stored procedure.
The file_written and file_write_completed         3.   After the demo window has opened,
events are also captured.                              click the Execute button on the toolbar
                                                       at the top of the form.
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
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 Transact-
SQL statement to turn event sessions on or off.

Now we start the Windows kernel trace using            4.   Click the Advance (forward arrow)
the logman utility from the command prompt.                 button.
Here the trace output file is
                                                       5.   Click the Execute button.
C:\demo\traces\kernel.etl.




                                                                                                Step 5

First we start the trace using the ALTER EVENT         6.   Click the Advance button.
SESSION command and then three stored
                                                       7.   Click the Execute button.
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
transaction.

Back at the command prompt, we use the              8.   Click the Advance button.
logman utility again to flush the trace files and
                                                    9.   Click the Execute button.
stop the traces. The
XE_DEFAULT_ETW_SESSION is the name of the           10. Wait for the step to complete executing
data collector set for the SQL Server ETW               and then click on the xeetw.csv
session.                                                hyperlink in the command window to
                                                        open this file in Excel. Scroll down in the
The tracerpt utility is then used to merge the
                                                        file to see that SQL Server events and
SQL Server and Windows trace logs into a single
                                                        intermingled with Windows kernel
report. This file is ordered by the timestamp of
                                                        events, such as DiskIo.
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.                                                                Step 9

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.
We can then examine the output to see that the     11. Double-click on the first row (not the
first workload stored procedure produces a             header) on the grid in the lower left
significant number of DiskIo events with a small       pane. This should be sp_WorkLoad_1.
amount of transferred data while the second            In the newly opened window, the top
workload stored procedure generates just a few         grid includes paired
DiskIo events with a larger amount of                  sp_statement_starting and
transferred data.                                      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                     Step 11
                                                       for sp_WorkLoad_2. Now you can
                                                       examine the DiskIo events for this
                                                       second stored procedure.



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                                             Click Steps                                     Screenshots

The first step is to create a new event session.   1.   From the desktop, double-click the SQL
This session captures the                               Server 2008 Demo Selector icon.
sp_statement_completed event for the current
                                                   2. From the displayed list of SQL Server
database. This event is fired whenever a
                                                      2008 demos, select “XEvent Procedure
statement is executed within a stored
                                                      Usage Demo”.
procedure. The event session is then altered to
add the asynchronous bucketizer target. This       3. After the demo window has opened,
will keep a counter of sp_statement_completed         click the Execute button on the toolbar
events for each stored procedure in the               at the top of the form.
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
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.

The next step starts the event session and then    4. Click the Advance (forward arrow)
executes a number of stored procedures, called        button.
usp_Demo_0 through usp_Demo_9. This
                                                   5. Click the Execute button.
creates sp_statement_completed events. The
sequence skips usp_Demo_6 and usp_Demo_7.
These procedures exist, but are not called.
Now we can view the event session results in        6. Click the Advance button.
the database. The event session needs to stay
                                                    7. Click the Execute button.
running, or else the results are cleared.
                                                    8. Double-click on the cell in the first grid in
In the results pane, the top grid shows the raw
                                                       the results pane underneath the Raw
xml stored for the event session. The results for
                                                       Xml column.
an asynchronous bucketizer target are stored in
a single table cell.                                9. Close the popup window.
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                                                       Step 7
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.
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                                              Click Steps                                   Screenshots

First we create an event session for the            1.   From the desktop, double-click the SQL
sql_statement_completed event. For the event,            Server 2008 Demo Selector icon.
the sql_text action is added so that the text of
                                                    2. From the displayed list of SQL Server
the sql query is included. A predicate clause
                                                       2008 demos, select “XEvent Long
filters the events to those with a duration time
                                                       Running Queries Demo”.
greater than two seconds. The CPU time could
have been used instead.                             3. After the demo window has opened,
                                                       click the Execute button on the toolbar
Next, the session is altered to add the
                                                       at the top of the form.
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.

We start the session and then run the               4.   Click the Advance (forward arrow)
usp_cpu_hog stored procedure several times.              button.
The two parameters control the amount of
                                                    5.   Click the Execute button.
work that the stored procedure performs. The
last two calls should exceed our threshold.
Now that we have run a few queries, we can         6.   Click the Advance button.
see if any have exceeded our threshold of 2
                                                   7.   Click the Execute button.
seconds. First, we can query the raw XML that
is stored for the synchronous bucketizer target.   8.   In the first grid in the results pane,
                                                        double-click on the row beneath the
The second query parses the XML into tabular
                                                        Raw Xml header. This opens a window
results. This displays the text for each query                                                                    Step 7
                                                        so that it is easier to view the XML.
that exceeded the duration threshold plus a
                                                        Close the window.
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.



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

Script                                             Click Steps                                   Screenshots

This section coming soon.                          1.   Step 1
                                                   2. Step 2
                                                   3. Step 3



Plan Guides / Plan Freezing

Script                                             Click Steps                                   Screenshots

This section coming soon.                          1.   Step 1
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                                                Click Steps                                      Screenshots

CDC in Integration Services                           1.   Navigate to C:\SQL Server 2008 Demo
                                                           Files\Intelligent Platform\Change Data
To visualize how Change Data Capture (CDC)
                                                           Capture\CDCSample2.sln.
works, let’s run an Integration Services package
on the AdventureWorks database. CDC is                2. In the Solution Explorer pane, double-
targeted at applications which require visibility        click SetupCDCSample.dtsx.
to changes in data in specific tables over specific
                                                      3.   In the toolbar, click the Start Debugging
periods of time. The most typical example of
                                                           button.
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                                                                         Step 3
Data Capture feature allows such an application
to identify this data directly by querying the
                                                       4.   Wait until first (“Original Customer
CDC tables for all records within a given time
                                                            Data…”) Data Viewer displays
period of interest. Before CDC, such an
application would require querying some                5.   Click Detach on data viewer to resume
system or record of changes not integral to the             package execution
database engine itself. Such records depend on
                                                       6. Click to highlight row with CustomerID
custom-developed triggers on each table to
                                                          696.
record changed data in log or history tables, or
timestamp columns which themselves depend              7.   Wait until second (“Changed Customer
on triggers to update, or status columns, or a              Data…”) Data Viewer displays
combination of these. CDC not only provides a
                                                       8.   Click Detach on data viewer to resume
greater level of reliability by its integration with
                                                            package execution
the database engine itself, but allows a
                                                                                                    Steps 4-6
centralized and consistent format and location         9.   Double-click the right edge of the
in which to locate the changed data and                     rightmost column, so ModifiedDate
metadata regarding those changes.                           values are visible.
Before we run this package, let’s quickly look at      10. Click to highlight row with CustomerID
the different elements, and what they do. First,           696.
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                                                      Steps 7-10
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.
Note the column values on row 696, which we’ll
                                                   11. Wait until third (“CDC Data…”) Data
observe throughout this demo.
                                                       Viewer displays.
This second data viewer shows the Customer
                                                   12. Click Detach on data viewer to resume
table after a number of Updates, as well as
                                                       package execution.
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                                                                   Steps 11-12
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.



Query Performance – Star Join 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                                              Click Steps                                  Screenshots

One of the most common types of queries             Because star join queries require
performed against a data warehouse is the star      multiprocessor environments to take
join query. This type of query requires data        advantage of the new parallelism
from several dimensional tables joined with a       enhancements, this new feature is not able
fact table to return the desired information.       to be demonstrated in a VPC.
Producing these types of queries can be fairly
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
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.




Partitioning

Script                      Click Steps   Screenshots

This section coming soon.   1.   Step 1
                            2. Step 2
                            3. Step 3



Backup Compression

Script                      Click Steps   Screenshots

This section coming soon.   1.   Step 1
                            2. Step 2
                            3. Step 3



Data Compression

Script                      Click Steps   Screenshots

This section coming soon.   1.   Step 1
                            2. Step 2
                            3. Step 3
Grouping Sets

Script                                         Click Steps                                    Screenshots

This section coming soon.                      1.   Step 1
                                               2. Step 2
                                               3. Step 3



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                                         Click Steps                                    Screenshots

This section coming soon.                      1.   Step 1
                                               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                                            Click Steps                                 Screenshots

Perform these steps to load the report template   1. From the Start menu, point to Programs
and set the correct deployment settings in           -> Microsoft SQL Server code name
Report Designer before giving the demo.              Katmai -> Reporting Services -> Report
                                                     Designer Preview.
                                                  2. From the File menu, select Open.
                                                  3. Browse to C:\SQL Server 2008 Demo
                                                     Files\Intelligent Platform\Reporting
                                                     Services Enhancements\Product
                                                     Sales.rdl.                                                  Step 5
                                                  4. From the File menu, select Deployment
                                                     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
                                                     Manager.
Tablix Tables                                      9. Switch back to Report Designer.
What we’re looking at here in the new interface    10. Click the Preview Report button.
for Report Designer. Previously, Report
                                                   11. Click the Design Report button.
Designer used to be part of the Business
Intelligence Development Studio, but customers     12. Select the table to enter Edit mode.
asked for the ability to deploy a robust design
                                                   13. Right-click on the Total Sales cell, and
surface for creating complex, professional
                                                       select Add Group -> Adjacent Right.
reports without having to install Visual Studio.
Now, the new Report Designer is a stand-alone      14. Expand the Group Expression drop-
application that makes it easier for information       down menu, and select the third item,
workers to create the reports that your                for OrderYear.Value.
organization relies on.
                                                   15. Click OK.
Looking at our design surface, you can see
                                                   16. In the top row in the new column, hover
we’ve already created a basic table showing
                                                       over the empty cell and expand the          Step 13
total sales by region. Let’s see what this looks
                                                       smart tag when it appears.
like when we preview it.
                                                   17. Select OrderYear.
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                                                    Steps 16-17
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         18. In the Properties pane, edit the Value
layout capabilities of Reporting Services              expression and remove the Sum
significantly.                                         component. It should read
                                                          “=Fields!OrderYear.Value”.
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
                                                      19. In the middle row of the new column,
column.
                                                          hover over the empty cell and expand
Next, we’ll add a new subtotal column to the              the smart tag when it appears.            Step 18
right of our OrderYear column, which will be
                                                      20. Select OrderQuantity.
used for summing up the order quantity data
separately from the region data.                      21. In the bottom row of the new column,
                                                          hover over the empty cell and expand
One of the things we might want to do here is
                                                          the smart tag when it appears.
widen this top title header so it spans all the
new columns in this report. As we do this,            22. Select OrderQuantity.
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                                                            Steps 19-20
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.                  23. Right-click on the top cell in the new
                                                          column, and select Add Subtotal.
To make this report even more complex, one
last thing we might want to do is add                 24. In the bottom right corner of Report
subcategories. This is as simple as dragging and       Designer, grab the zoom slider and zoom
dropping, and now when we preview the                  the view out slightly, until the whole
report, we can see the sales by region and             report is visible.
quantity by year data are both now broken
                                                   25. In the top cell of the new subtotal
down by subcategories in the rows.
                                                       column, change the Value to “Total
                                                       Quantity”.
                                                   26. In the middle row of the subtotal
                                                       column, hover over the empty cell and
                                                       expand the smart tag when it appears.
                                                   27. Select OrderQuantity.
                                                   28. In the bottom row of the subtotal
                                                       column, hover over the empty cell and
                                                       expand the smart tag when it appears.        Step 23
                                                   29. Select OrderQuantity.
                                                   30. Select the Product Sales Report text
                                                       item.
                                                   31. From the top toolbar, click the Center
                                                       button.                                     Steps 33-36
                                                   32. 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.
                                                   33. Select the top cell for the two new
                                                       columns.
                                                   34. In the right properties pane, adjust the
                                                       following properties:
                                                       BackgroundColor=Teal, FontSize=12pt,
                                                       FontWeight=Bold, Color=White.
                                                   35. Select the bottom two cells for each
                                                       column and set BackgroundColor=Silver.
                                                     36. Change the bottom cell in the OrderYear
                                                         column and the two cells in the Total
                                                         Quantity column to FontWeight=Bold.
                                                     37. Click the Preview Report button.
                                                     38. Click the Design Report button.
                                                     39. Select the table to enter Edit mode.
                                                     40. From the left pane, grab the
                                                         SubCategory item and drag it below
                                                         ProductCategory in the Row Groups
                                                         pane.
                                                     41. Highlight all the cells in the new
                                                         Subcategory column, and set the
                                                         BackgroundColor=Teal and                      Step 40
                                                         Color=White.
                                                     42. Click the Preview Report button.
                                                     43. Click the Design Report button.

Charts                                               44. Select the Product Sales Report header
                                                         and the Tablix table, and drag them
To enhance the data visualization capabilities of
                                                         lower down on the design surface, again
SQL Server 2008 Reporting Services, Microsoft
                                                         using the blue snap-to lines to help with
will be incorporating a new, advanced data
                                                         positioning.
visualization engine. The products to be
incorporated include new charting and gauge          45. In the left pane, grab the Chart item and
visualizations. The ability to design and format         drag it onto the design surface.
rich charts will be included directly in Report
                                                     46. Grab the edge of the chart and resize it,
Designer, giving information workers the ability
                                                         so it takes up more space on the design
to easily create rich, useful data visualizations.                                                     Step 45
                                                         surface.
To see how this works, let’s add a new chart to
                                                     47. Double-click on the chart to put it in Edit
this report that we’ve been working on. To do
                                                         mode.
this, we’ll just grab this chart item and drag it
onto our report. Now we’ll add the                   48. Right-click on the chart background and
ProductCategory data to our category fields,             select Properties.
and the SalesAmount data to the data category
                                                      49. Switch to the Legend tab.
field. This gives us a nice simple report showing
sales by product category, which we can see           50. Change the Legend Position to bottom
when we preview the report. Notice as we’re               middle.
previewing this chart that the Bike sales
                                                      51. Click OK.
category is much larger than the other
categories. This makes it difficult to easily see     52. In the left pane, under DataSet1, grab
the difference between some of the other                  the ProductCategory item, and drag it
categories, such as clothes and accessories.              over the “Drop category fields here”
                                                          area on the design surface.
Now let’s enhance the look of this chart by
using some of the formatting options provided         53. In the left pane, under DataSet1, grab
by the new visualizations. First, let’s format the        the SalesAmount item, and drag it over        Steps 52-53
vertical axis for the report, and add an axis             the “Drop data fields here” area on the
break. This will help us address the problem              design surface.
with the report we just discussed. Now when
                                                      54. On the design surface, select the Chart
we preview it, notice that we have this “tear”
                                                          Title. (You might need to zoom in
going through the middle of the report,
                                                          slightly to be able to select it.)
indicating where the axis break occurs. The
numbers on the vertical axis above the break          55. In the right-hand Properties pane,
are much larger than below the break, allowing            change the Caption field to “Product
us to more easily make comparisons between                Sales”.
all the product categories.
                                                      56. Click the Preview Report button.
Another nice advantage of the new charting
                                                      57. Click the Design Report button.
capacilities is the ability to use some really nice
formatting options to enhance the look of the         58. Right-click on the vertical Sales axis, and
report. For example, we can apply some                    select Properties.                             Step 58
shading to the bars in the report, and do the
                                                      59. Select the Axis Breaks checkbox.
same thing for the report background. This
level of detail and control over report elements      60. Click OK.
simply wasn’t possible with previous versions of      61. Click the Preview Report button.
Reporting Services.
                                                      62. Click the Design Report button.
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
OrderQuantity data, and also add it to the data
fields. However, it might be too confusing to
                                                    63. Right-click the Sales Amount data series
have both pieces of data show up as bars in this
                                                        item and select Properties.
report, so let’s change the type of visualization
we’re using for the OrderQuantity data.             64. Click the Series Appearance button.
In this dialog box, we can select the type and      65. Select the Gradient radio button.
subtype we want to use to display this data
                                                    66. Expand the Gradient start color drop-
with. There are over 30 different combinations
                                                        down menu, and select PaleTurquoise.
that can be used to display data with the charts,
providing many different options for creating       67. Expand the Gradient end color drop-          Step 63
charts. Here, we’re going to change the                 down menu, and select Teal.
ReportQuantity to a Smooth Line.
                                                    68. Expand the Gradient style drop-down
When we preview the report again, we can see            menu, and select TopBottom.
how quickly we’ve taken a basic chart, and
                                                    69. Click OK.
really enhanced it with the new capabilities of
the new chart visualizations.                       70. Click OK.
                                                    71. Right-click on the chart control
                                                        background, and select Properties.
                                                    72. Click the Chart Area Style button.
                                                    73. Select the Gradient radio button.
                                                                                                    Steps 65-68
                                                    74. Expand the Gradient start color drop-
                                                        down menu, and select LightGray.
                                                    75. Expand the Gradient end color drop-
                                                        down menu, and select DimGray.
                                                    76. Expand the Gradient style drop-down
                                                        menu, and select TopBottom.
                                                    77. Click OK.
                                                    78. Click OK.
                                                    79. In the left pane, under DataSet1, grab
                                                        the OrderQuantity item, and drag it over    Steps 73-76
                                                        the “Drop data fields here” area (next to
                                                        Sales Amount) on the design surface.
80. Right-click the Order Quantity data
    series item and select Properties.
81. Expand the Series type drop-down
    menu, and select Line.
82. Expand the drop-down menu to the right
    of the previous menu, and select
    Smooth.
83. Click the Series Appearance button.
84. Expand the Color drop-down menu, and
    select GreenYellow.                      Steps 81-82
85. Switch to the Border Line tab.
86. Expand the Line thickness drop-down
    menu, and select 2 pt.
87. Click OK.
88. Switch to the Advanced tab.
89. Change the Value Axis radio button to
    Secondary.
90. Click OK.
91. Click the Preview Report button.
92. Click the Design Report button.
93. From the File menu, select Deploy.
94. Click OK.
95. Switch back to Internet Explorer.
96. Click Reload.
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                                            Click Steps                                 Screenshots

Updated Create New Dimension Wizard               1. From the Start menu, point to All
                                                     Programs->Microsoft SQL Server code
OLAP models can be quite complex, with many
                                                     name Katmai->SQL Server Business
interdependencies between objects. Because
                                                     Intelligence Development Studio.
of this, best practices and performance tuning
tips are not generally well known and are         2. From the File menu, select Open-
difficult to uncover.                                >Project/Solution.
Much of the focus with SQL Server 2008            3. Navigate to C:\SQL Server 2008 Demo
Analysis Services has been placed on enhancing       Files\Intelligent Platform\Dimension                             Step 6
the ease-of-use of the solution designer, and        Designer\Adventure Works.sln.
reducing the time it takes to create solutions.
                                                  4. Click the Open button.
Best practices and performance tuning tips have
been embedded directly into the object model      5. In the Solution Explorer pane, double-
and the user interface. Also, key areas of the       click on AdventureWorks.cube.
design interface have been modified so that an
                                                  6. Right-click on the Dimensions folder,
optimal design is the natural outcome.
                                                     and select New Dimension.
Let’s move through the workflow in the
                                                  7. Click Next.
Business Intelligence Developer Studio, and see
how we can take advantage of these new            8. Ensure the Use an Existing Table radio
capabilities. Here, we’re working with the           button is selected, and click Next.
standard AdventureWorks data warehouse
                                                  9. Expand the Main table drop-down
model that ships as an example with SQL Server
                                                     menu, and select
2008.
                                                       FactInternetSalesReason. Highlight the
First, let’s create a new dimension using the
                                                       warning message that is displayed.
Dimension Wizard. This wizard has been greatly
simplified from previous versions, with fewer      10. Expand the Main table drop-down
paths and branches to navigate.                        menu, and select DimTime.
As we move through this wizard, you can see        11. Expand the Name column drop-down
that when certain selections are made, warning         menu, and select Date.
messages are displayed down in the bottom of
                                                   12. Click Next.
the dialog box, to better inform you about the
choices you are making. For example, when we       13. Attempt to uncheck the box next to
select a table with a composite key, a warning         Time Key. Highlight the warning
message is displayed recommending the                  message that is displayed.
creation of a named calculation in the Data
                                                   14. Click in the Attribute Name text box for
Source View.
                                                       Time Key, and change the name to           Steps 10-11
When we go to select dimension attributes, we          “Date”.
can see another change that has been made to
                                                   15. Check the boxes next to the Day
this wizard. It’s now much more conservative
                                                       Number of Week, Day Number of
about the items that are selected by default. In
                                                       Month, and Day Number of Year
fact, items are not selected unless the wizard
                                                       columns.
can detect a parent-child relationship between
the attributes. If we try and remove the           16. In the Enable Browsing column, uncheck
primary key from this dimension, we’ll also get        the boxes for Day Number of Week, Day
a warning about why we’re not able to remove           Number of Month, and Day Number of
it.                                                    Year.
Now let’s go ahead and define the attributes we    17. Check the boxes next to the Calendar
want to include in this dimension. As we move          Quarter, Calendar Year, Calendar
through this process, note that there’s an             Semester, and English Month Name
improved interface for defining the attribute          columns.
type. Attribute types are now grouped in a
                                                   18. Click in the Attribute Name text box for
familiar tree structure, allowing for easy
                                                       English Month Name, and change the
navigation and selection.
                                                       name to “Months”.
Finally, we can give our new dimension a name,
                                                   19. For Months, click in the Attribute Type
and finish the wizard.
                                                       column and expand the drop-down
                                                       menu.                                      Steps 14-18
                                                   20. Expand the Date node.
21. Expand the Calendar node.
22. Select Month and click OK.
23. For Calendar Semester, click in the
    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.                       Steps 19-34

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.
Dimension Structure                                38. In the Attributes pane, mouse over the
                                                       NewDate node with the blue squiggly
Now that we’ve created our new dimension,
                                                       line.
we’re presented with the standard dimension
structure interface. One of the things you might   39. In the Attributes pane, grab the
notice, however, is the blue squiggly line above       Calendar Year item and drag it into the
the NewDate attribute. Mousing over this               Hierarchies pane.
warning provides a helpful message,
                                                   40. In the Attributes pane, grab the
recommending to the developer that he might
                                                       Calendar Semester item and drag it onto
want to create a user-defined hierarchy, since
                                                       the <new level> box in the Hierarchies
no parent-child hierarchies were detected.
                                                       pane.
This is our first introduction to the new build
                                                   41. In the Attributes pane, grab the
time warnings. These warnings are intended to
                                                       Calendar Quarter item and drag it onto
make it easier for developers to be aware of                                                      Steps 39-43
                                                       the <new level> box in the Hierarchies
opportunities to improve cube design, optimize
                                                       pane.
performance, and generally take advantage of
best practices. They will not prevent you from     42. In the Attributes pane, grab the Months
building or deploying your model, and are              item and drag it onto the <new level>
intended as an unobtrusive addition to the             box in the Hierarchies pane.
interface. There are over 40 of these best
                                                   43. In the Attributes pane, grab the Date
practices integrated into real-time design
                                                       item and drag it onto the <new level>
checks throughout the solution designer. Later
                                                       box in the Hierarchies pane.
we’ll see how to manage these alerts.
                                                   44. In the Hierarchies pane, mouse over the
Now we’ll go ahead and create our user-defined
                                                       warning that appears in the top left
hierarchy. As we do this, you’ll notice we get a
                                                       corner of the Hierarchy box.
new warning, stating that we don’t have
attribute relationships established between the    45. In the Attributes pane, select the
                                                       Calendar Quarter attribute.                 Step 46
attributes used as levels in this hierarchy.
Before we look at attribute relationships, let’s   46. In the Properties pane, scroll down to
look at one more enhancement here. One of              the KeyColumns entry. Click in the value
the things we should do is create composite            column and then click the “…” button to
keys, so we’ll select the Calendar Quarter             open the Key Columns dialog box.
attribute, and edit the key columns in the
                                                   47. In the Available Columns list, double-
Properties pane. This brings up the new dialog
                                                       click on Calendar Year.
box for editing key columns, which provides a
simpler and more straightforward method of         48. Click OK.
adding key columns.
                                                     49. In the Attributes pane, mouse over the
Once we’ve created our composite key, notice             red squiggly line that appears under
that we now have a red squiggly line under               Calendar Quarter.
Calendar Quarter. Unlike the blue design-time
                                                     50. In the Properties pane, expand the drop-
warnings, the red squiggly lines indicate real-
                                                         down selector for NameColumn, and
time error notifications. These errors must be
                                                         select (new).
fixed before the cube can be deployed. In this
case, adding the composite key means we also         51. In the Source column list, scroll down
need to define the NameColumn for the                    and select CalendarQuarterOfYear.
attribute, so we’ll go ahead and do that now.
                                                     52. Click OK.

Attribute Relationship Designer                      53. Click on the Attribute Relationships tab.
Now let’s look at the new attribute relationship     54. Click the drop-down button on the Date
designer window. This new designer is used for           node.
viewing and editing attribute relationships, and
                                                     55. Select the Months node, and drag it
includes built-in validations to aid in creating
                                                         onto the Calendar Quarter node.
optimal dimension designs.
                                                                                                     Steps 55-57
                                                     56. Select the Calendar Quarter node, and
Using this visual design surface, it’s now quite
                                                         drag it onto the Calendar Semester
easy for us to define the attribute relationships,
                                                         node.
which we can do by dragging and dropping.
                                                     57. Select the Calendar Semester node, and
One important thing we can do here is edit the
                                                         drag it onto the Calendar Year node.
relationship type, which is done by editing the
arrows between attributes. If possible, you          58. Expand the Zoom drop-down menu, and
should define the relationship type as rigid, to         select 75%.
help maintain the data structures.
                                                     59. Double-click on the arrow between the
Let’s create a non-optimal relationship                  Date and Months node.
structure, and see what happens. Just like in
                                                     60. Expand the Relationship type drop-
other parts of the solution designer, we get a
                                                         down menu, and select Rigid.
warning, letting us know we have a redundant
attribute relationship. This can cause issues in     61. Click OK.
the aggregation of data and lead to unexpected
                                                     62. Select the Months node, and drag it
results, and should be corrected.
                                                         onto the Calendar Semester node.
We’ll go ahead and delete this warning, and
                                                     63. Mouse-over the blue arrow to highlight
now create a new situation, by defining a loop                                                        Step 63
in the attribute relationships. This is going to        the warning.
create issues with the cube structure, and again
                                                    64. Right-click on the blue arrow and select
is flagged as an error. This relationship must be
                                                        Delete.
adjusted before the cube can be deployed.
                                                    65. Click OK.
Since we’ve fixed all the errors with the cube
design as they’ve come up, we can now go            66. Select the Calendar Quarter node, and
ahead and build the solution. Here we can see           drag it onto the Date node.
all the warnings that come up for the cube.
                                                    67. From the View menu, select Error List.         Step 66
None of these will stop deployment, but all
indicate things we might want to look at in the     68. From the Build menu, select Build
future. One of the things we can do here is             AdventureWorks DW-X.
dismiss individual warnings. You can even
                                                    69. In the Error List pane, click on the Errors
specify a comment for each dismissal, so that
                                                        button.
other developers can later review your
reasoning for ignoring them.                        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.

Database Design                                     74. In the Error List pane, click on the
                                                        Warnings button.
Now, when we go to edit the database, we can
see this new tab for warnings. Here we can see      75. Right-click on the description for the first
a list of all the different design warning rules        warning, and select Dismiss.
that the solution designer is currently
                                                    76. In the Comments text box, type in a brief
monitoring, and we can choose to enable or
                                                        comment.
disable specific warnings or groups of warnings.
Each of these rules also is ranked by               77. Click OK.
importance, to allow the developer to
                                                    78. Close the Error List pane.
understand the possible implications of
disabling a particular rule.                        79. In the Solution Explorer pane, right click
                                                       on the main AdventureWorks DW-X
In the bottom portion of this window, we can
                                                       node, and select Edit Database.
view all the dismissed warnings, and read the
comments that have been left for them. If we      80. Select the Warnings tab.
choose, we can enable a specific warning again.
                                                  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                                            Click Steps                                    Screenshots

This section coming soon.                         1.   Step 1
                                                  2. Step 2
                                                  3. 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.

ADO.NET Entity Framework

Script                                       Click Steps                                  Screenshots

This section coming soon.                    4.   Step 1
                                             5. Step 2
                                             6. Step 3



LINQ to Entities

Script                                       Click Steps                                  Screenshots

This section coming soon.                    1.   Step 1
                                             2. Step 2
                                             3. Step 3



LINQ to SQL

Script                                       Click Steps                                  Screenshots

This section coming soon.                    1.   Step 1
                                             2. Step 2
                                             3. Step 3



Database Applications

Script                                       Click Steps                                 Screenshots

This section coming soon.                    4.   Step 1
                                             5. Step 2
                                             6. Step 3



Project Codename “Astoria”

Script                                       Click Steps                                 Screenshots

This section coming soon.                    1.   Step 1
                                             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, synchronization-
based, and notification-based applications using a robust implementation with minimal performance overhead.


Script                                          Click Steps                                        Screenshots

Visual Studio 2008 has been engineered          1.   From the desktop, double-click on the
from the ground up to support the easy and           Microsoft Visual Studio 2008 icon.
rapid development of occasionally connected
                                                2. From the File menu, select New-> Project.
applications. To see how this works, let’s
create a simple application that uses a local   3. In the Project Type pane, select the
data store to cache and browse product data        Windows project type under the Visual C#
for the Northwind Traders database.                node.
We’ll begin by creating a new Windows           4. In the Templates pane, select the Windows
Forms Application project in Visual Studio,        Forms Application template.
which we’ll name SyncServices.
                                                5. Change the project name textbox to
Once this project has been created, we’re          SyncServices.
going to add a new item to the project                                                                           Steps 3-5
                                                6. Click OK.
solution called a Local Database Cache. This
new service will manage the local caching of    7. Expand the Solution Explorer pane if it isn’t
data for the application.                          visible.
                                                8. Right-click on the SyncServices project, and
                                                   select Add->New Item.


                                                9. From the Add New Item dialog, select Local
                                                   Database Cache.
                                                10. Change the Name textbox to
                                                    NorthwindCache.sync.                                         Steps 9-10
                                                11. Click the Add button.
After we’ve added the Local Database Cache         12. From the Configure Data Synchronization
to the solution, this Configure Data                   dialog box that will pop up, expand the
Synchronization dialog box will pop up. First,         Server Connection drop-down menu and
we’ll select the database we want this                 select
application to connect to. Once we’ve                  sql08demo\sqlexpress.Northwind.dbo.
selected our database, we can specify which
                                                   13. Click the Add button on the bottom left of
tables we want to be able to access locally
                                                       the dialog.
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                                                           Steps 12-13
dialog box has been closed, Visual Studio
2008 generates and runs the server side SQL        14. In the Configure Tables for Offline Use
scripts to make the recommended changes                dialog box, select the Customers,
to the database to accommodate the                     Employees and Shippers tables.
synchronization. The scripts both to
                                                   15. Click OK.
replicate and undo those changes are added
to the solution.                                   16. From the Configure Data Synchronization
                                                       dialog box, click the Show Code Example
Returning to our Data Synchronization
                                                       link.
dialog, we’re going to grab this code sample
that shows how to refresh the local cache          17. Click the Copy Code to the Clipboard
and synchronize it with the database, since            button.
we’ll need it later in the demo.
                                                   18. Click the Close button.
Accepting our changes to the main Configure
                                                   19. Click OK.
Data Synchronization dialog, Visual Studio
                                                                                                     Step 14
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.
Immediately after the previous dialog box        20. From the Data Source Configuration Wizard,
closes, we’re presented with another dialog          expand Tables and select the Customers,
box. This Data Source Configuration Wizard           Employees, and Shippers tables.
is used to create and edit data sources in the
                                                 21. Click the Finish button.
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.

                                                                                                      Step 20



Now we’re going to use the Server Explorer       22. In the Solution Explorer pane, right-click on
to browse through the structure for the              NorthwindDataSet.xsd and select View
Northwind database. In addition to the               Designer.
three cached tables we’ve already added to
                                                 23. If the Server Explorer pane is not visible on
the project, we’re going to add the Orders
                                                     the left side of the Visual Studio application
and Order Detail tables. Since we have not
                                                     window, select View -> Server Explorer
configured these tables for data
                                                     from the menu.
synchronization, they will require live access
to the Northwind database to retrieve data.      24. From the Server Explorer pane, expand the
                                                     sql08express\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              Step 26
                                                     changes, click Yes.
Next we need to make some changes to              29. Switch to the Data Sources tab in the Server
fields associated with the Orders table, to           Explorer pane. If the Data Sources tab is not
better define how they will be used as part           visible at the bottom of the Server Explorer
of our sample application. What we want to            pane, select Data ->Show Data Sources
do here is change the OrderID to be used as           from the menu.
a Label field, and then change several of the
                                                  30. Click on the Orders node, expand the drop-
other items to be used as drop-down combo
                                                      down menu, and select Details.
boxes.
                                                  31. Expand the Orders node.
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                                                        Step 30
allow us to page through the order records.
Behind the scenes, Visual Studio has created      32. Change the following fields’ types (by
a significant amount of code to support this          selecting the field and selecting items from
form, including all the code that binds the           the drop down): OrderID -> Label,
data fields to form controls. It also adds all        CustomerID -> ComboBox, EmployeeID ->
the typed datasets, binding sources, table            ComboBox, ShipVia -> ComboBox.
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.                                                                                    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     37. From the desktop, double-click on the SQL
see how it works. Before we run the                  Server Profiler icon.
application, however, let’s also pull up the
                                                 38. From the File menu, select New Trace.
SQL Profiler tool so we can watch what
happens on the Northwind database while          39. Ensure that the Server Type is set to
the application is running. Here in the              “Database Engine” and the Server Name is
Profiler, we’ll create a new trace to watch          set to “SQL08DEMO\SQLEXPRESS”, and
activity on the Northwind database, using            then click the Connect button.
this template we’ve already created.
                                                 40. Change the text in the Trace name textbox
Now let’s switch back to Visual Studio and           to read “SyncServicesTrace”.
compile our application. Here’s our basic
                                                 41. From the Template drop-down menu,
form, and as you can see, it looks like it is
                                                     select the OCS template.
working fine. All of the fields are populated
with data, which means that all of the form      42. Click the Run button.
controls are correctly bound to their data
                                                 43. Switch back to Visual Studio 2008.
sources. We can page through the records
using the application toolbar, to further test   44. From the Visual Studio 2008 toolbar, select     Step 45
that the controls are working correctly.             the Start Debugging button (green arrow).
Locally, the application is storing the three    45. On the Form1 application, click the Move
tables we configured for offline use in SQL          Next button several times to page through
Server Compact Edition 3.5. This lightweight         the data.
version of SQL Server offers a maintenance
                                                 46. Switch back to the SQL Profiler application.
free, compact embedded database for
single-user client applications for all          47. Switch back to the Form1 application.
Windows platforms, including Tablet PCs,
                                                 48. Close the Form1 application.
Pocket PCs, Smart Phones, and desktop
computers.                                       49. Switch back to Visual Studio 2008.
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      50. In the Form1.cs design pane, click on the
button to the toolbar that actually                  ordersBindingNavigator item to select it.
synchronizes the local cache with the remote
database.
                                                                                                    Step 50
We’ll select the ordersBindingNavigator
control, which handles all the functionality
for the top toolbar in our application. From
                                                  51. With ordersBindingNavigator selected,
this drop-down menu, we’re going to choose
                                                      expand the Add ToolStripButton item and
to add a new button to this toolbar. Double-
                                                      select Button.
clicking on this new button will take us to the
actual code relating to the button, and here      52. Double-click on the new button that was
we’ll simply paste in the sample code we              added to the toolbar to bring up code view.
copied earlier to perform synchronization.
                                                  53. From the Edit menu, select Paste.
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                                                                Step 51
occasionally connected environments a
                                                  54. Scroll up, and select the first six lines of
logical extension of building applications
                                                      code from within the Form1_Load method,
where you can count on a consistent
                                                      and select Edit -> Insert File as Text.
network connection.
                                                  55. Browse to C:\SQL Server 2008 Demo
Since the original code in the Form1_Load
                                                      Files\Productive Platform\OCS\LoadCode.cs
method was intended for loading, and not
                                                      and click Open.
synchronization, we need to modify it to use
the Merge method. We’ll do this quickly by        56. Select the first six lines (three lines of code
inserting the new code from this text file.           and three comments) from within this
                                                      method that were just pasted in, right click
Next, we’re going to refactor these four lines
                                                      and select Refactor -> Extract Method.
of code from the Form1_Load method into a
new method that can be called from both           57. Change the New method name to                     Step 54
the old Form1_Load method and our new                 LoadLocalData.
click method for the toolbar button.              58. Click the OK button.
Finally, we’ll change the name of the button
                                                  59. Copy the line of code that was just created
in the Properties pane and call it “Sync              to the clipboard.
Now”.
                                                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      64. Switch back to the Server Explorer pane.
going to actually open the Customers table
                                                65. Expand
for the Northwind database and make a
                                                    sql08demo\sqlexpress.Northwind.dbo.
change to the first record. Let’s change the
Company Name associated with this record.       66. Expand Tables.
Loading up our application again, notice that   67. Right-click on the Customers table, and
the old company name is still showing up.           select Show Table Data.
                                                                                                   Step 68
This is because the local cache has not yet
                                                68. In the Customers table, modify the
been synchronized with the remote
                                                    CompanyName for the data record whose
database. Fortunately, we’ve just added the
                                                    CustomerID is ALFKI (the first row of data).
functionality to handle this situation.
                                                    Change the record to read “Contoso” and
Clicking on our new Sync Now button, we
                                                    hit Enter.
can see that the new company name is now
showing up on this table.                       69. From the Visual Studio 2008 toolbar, select
                                                    the Start Debugging button (green arrow).
As you can see, getting off the ground with
the new synchronization technologies in         70. From the SyncServices application, click the
Visual Studio 2008 is fairly easy. The next         Sync Now button on the toolbar.
step would be to enhance this application
                                                71. Switch back to SQL Server Profiler.
even further, by adding support for two-way
synchronization, which would allow changes      72. Select one of the “exec sp_executesql” rows
and deletions made in the local cache to be         and show that the update (Merge) was
reflected in the remote database.                   accomplished via T-SQL statements which
                                                    can be formulated and used anywhere you
                                                    have connectivity to the SQL Server 2008
                                              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

Script                                       Click Steps                                     Screenshots

This section coming soon.                    1.   Step 1
                                              2. Step 2
                                              3. Step 3



Sparse Columns / Filtered Index

Script                                        Click Steps                                  Screenshots

This section coming soon.                     1.   Step 1
                                              2. Step 2
                                              3. Step 3



Integrated Full Text Search

Script                                        Click Steps                                  Screenshots

This section coming soon.                     1.   Step 1
                                              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         1.   From the desktop, double-click the
“beyond relational”, a new HierarchyID data               SQL Server 2008 Demo Selector icon.
type has been added. The HierarchyID type
                                                     2. From the displayed list of SQL Server
logically stores information about a single
                                                        2008 demos, select “HierarchyID Data
node in a hierarchy tree by encoding the path
                                                        Type Demo”.
from the root of the tree to the node.
Examples of how this may be used include an          3. After the demo window has opened,
organizational structure, a file system, a set of       click the Execute button on the
tasks in a project, or a taxonomy of language           toolbar at the top of the form.
terms.
                                                     4. Click the Advance button.
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
                                                                                                Step 3
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.
Now that we’ve populated the org chart, one          5.   Click the Execute button.
of the most common things we might want to
                                                     6.   Click the Advance button.
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.




                                                                                      Step 5

Let’s see how we can create this same org            7.   Click the Execute button.
tree using the new HierarchyID data type.
                                                     8.   Click the Advance button.
The sample table, OrgNew, is used to
structure the departments of a corporation. It
includes a unique identifier for the                                                  Step 7
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,
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             9.   Click the Execute button.
create a new stored procedure to make this
                                                   10. Click the Advance button.
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                                           Step 9
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.
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 sub-
levels. 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           11. Click the Execute button.
structure, we will create a
                                                    12. Click the Advance button.
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
                                                                                    Step 11
less system resources than the comparable
method with a standard org tree. Typically,
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      13. Click the Execute button.
charts a certain number of times, and put
                                                 14. Double-click on the graph.
some actual performance statistics behind
them. Notice the dramatic difference in          15. Close the graph window.
HierarchyID, which requires no INSERT,
                                                 16. Click the Advance button.
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
                                                                                  Step 13
nodes, but improves system performance
when reporting the tree structure – a common
operation that might be done frequently on
the database server.
Hierarchical trees constantly need to be           17. Click the Execute button.
revised and updated. For example, one other
                                                   18. Click the Advance button.
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.


                                                                                   Step 17

This same procedure is actually a bit more         19. Click the Execute button.
complex with a HierarchyID table, because not
                                                   20. Click the Advance button.
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                                              Step 19
move Parking, which is currently department
4, beneath Home Operations, department 5.
ShowDepartmentChartNew is called before
and after the move.

Finally, let’s look briefly at a couple additional   21. Click the Execute button.
HierarchyID methods, and how comparable
                                                     22. Close the HierarchyID demo
operations would have to be performed on a
                                                         application.
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                                                                             Step 21
grandfather (ancestor two levels up the tree)
of the Home Operations node.



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                                              Click Steps                                    Screenshots

To see how the new date/time data types work,       1.   From the desktop, double-click the SQL
let’s consider the scenario of an international          Server 2008 Demo Selector icon.
art brokerage with offices in London, New York
                                                    2. From the displayed list of SQL Server
and Seattle. As this company acquires new art
                                                       2008 demos, select “New Date & Time
to sell, it places it in an AcquiredAsset table.
                                                       Data Types Demo”.
Here is the schema for this table using the old
SQL Server data types.                              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.

Since this company may be selling art created       5.   Click the Execute button.
before 1753, it uses a varchar for the
                                                    6.   Click the Advance button.
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                                                                 Step 5
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
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             7.   Click the Execute button.
happens when the brokerage migrates this data
                                                      8.   Click the Advance button.
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.
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       9.   Click the Execute button.
data types have been implemented. Several of
                                                     10. Click the Advance button.
the database issues that the firm struggled with
before have now been addressed.
The AcquiredDateTime column has been
                                                                                      Step 9
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
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       11. Click the Execute button.
data types, let’s see what happens when we
                                                    12. Click the Advance button.
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                                           Step 11
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
          th
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    13. Click the Execute button.
assets available on October 1. In the old table,
                                                   14. Click the Advance button.
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.



                                                                                          Step 13



Finally, this example illustrates the pain with    15. Click the Execute button.
querying the PlayLength column. In the old
                                                   16. Close the New Date and Time Data
table, when attempting to select videos that are
                                                       Types demo application.
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.                                                                   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                                               Click Steps                                    Screenshots

For this example we will create a stock trading      1.   From the desktop, double-click the SQL
scenario. First, we’ll create a table to hold “my”        Server 2008 Demo Selector icon.
stock holdings. This includes 200 shares of
                                                     2. From the displayed list of SQL Server
Microsoft, 50 shares of Starbucks and 500
                                                        2008 demos, select “Table Value
shares of Cisco. Notice that we use the new
                                                        Parameters & MERGE Statement
Date field to store the LastTradeDate. This is a
                                                        Demo”.
date value without the time portion appended,
                                                                                                                   Step 3
and is a new data type in SQL Server 2008.           3.   After the demo window has opened,
                                                          click the Execute button on the toolbar
                                                          at the top of the form.
Next, we create a table to store sales and            4.   Click the Advance arrow on the toolbar
purchases of stock and insert some trades. This            at the top of the form.
table uses the new DateTimeOffset value, which
                                                      5.   Click the Execute button.
includes a time zone offset. The time zone for
all the trades is -08:00, or Pacific Standard Time.




                                                                                                    Step 5

Now we create a new user-defined data type            6.   Click the Advance arrow on the toolbar
(UDT) named myTableType. This UDT is of type               at the top of the form.
TABLE. The TABLE data type has been available
                                                      7.   Click the Execute button.
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.          8.   Click the Advance arrow on the toolbar
First, we create a new variable of this type and           at the top of the form.
populate it with a summary of net trades from
                                                      9.   Click the Execute button.
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.




                                                                                                    Step 9
In this scenario, we want to update the stock         10. Click the Advance arrow on the toolbar
holdings table, MyStocks, with the net changes            at the top of the form.
from the stock trades. To do this, we create a
                                                      11. Click the Execute button.
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 left-
side 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,                                                              Step 11
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
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                                           Click Steps                                   Screenshots

Before beginning this demo, it is recommended    1.   Launch Windows Explorer and browse
that you execute these warm-up steps to               to C:\SQL Server 2008 Demo
ensure that the database is correctly prepared        Files\Productive Platform\XML
for the demo.                                         Enhancements\Setup.
                                                 2.   Double-click on Init.sql.
                                                 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.
                                                 4.   From the SQL Editor toolbar, click the
                                                      Execute button.
                                                 5.   Close the SQL Server Management
                                                      Studio.
SQL Server 2008 improves on SQL Server 2005’s     6.   Launch Internet Explorer.
XML capabilities by adding support for XML
                                                  7.   Open the Favorites Center pane, and
schemas which include processContents=”lax”
                                                       select the XML Demo – Price Change
sections. By defining nodes in an XML
                                                       Forms link.
document as “lax”, it specifies that elements
appearing in that node which are not defined in   8.   Click on one of the Open Form links to
the current schema should not invalidate the           open the form in InfoPath.
document. This can be useful in situations
                                                  9.   Click the Open button.
where you want to be able to validate one XML
document against different schemas in different   10. Close the InfoPath form.
contexts. SQL Server 2008 supports XML
schemas containing “lax” sections in typed XML                                                  Step 8
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
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     11. Click the Submit New Form button.
request using this InfoPath form. Once we’ve
                                                   12. Click the Open button.
submitted the form, you can see that it shows
up on the list of requests for review.             13. In the Requested by textbox, type in a
                                                       name (e.g. Steve Masters).
                                                   14. In the Product Name textbox, type in
                                                       “Bike”.
                                                   15. In the Product Number textbox, type in
                                                       “abc-123”.
                                                                                                  Steps 13-18
                                                   16. In the Old Price textbox, type in “500”.
                                                   17. In the New Price textbox, type in “400”.
                                                   18. In the Reason textbox, type in “Sales
                                                       promotion”.
                                                   19. On the InfoPath toolbar, click the
                                                       Submit button.
                                                     20. Click OK.
                                                     21. Close InfoPath.
                                                     22. In Internet Explorer, click the Refresh
                                                         button on the address bar.

The form we created was stored in a typed XML        23. Launch Windows Explorer and browse
column in a database table in SQL Server 2008.           to C:\SQL Server 2008 Demo
Let’s do some validation on these forms in the           Files\Productive Platform\XML
SQL Server Management Studio, taking                     Enhancements\.
advantage of XML-DML.
                                                     24. 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.

First, we’ll run this script to identify all the     25. In the Solution Explorer pane, double-
forms submitted with product numbers that                click on Mark Bad Product Numbers.sql.
don’t match any in our product table.
                                                     26. On the SQL Editor toolbar, click Execute.
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                                                         Step 25
looking at, but the XML for the form is different.
There is a new node in this XML form called
<my:statusLog> that we have added via an XML-
DML query while we were processing the form.
                                                    27. In the Results pane, click the blue
                                                        hyperlink in the last row of the top of
                                                        the two grids.
                                                    28. Close the window showing the XML
                                                        results.
                                                    29. In the Results pane, click the blue       Step 27
                                                        hyperlink in the last row of the bottom
                                                        of the two grids.
                                                    30. Highlight the <my:statusLog> node in
                                                        the XML.
                                                    31. Close the window showing the XML          Step 29
                                                        results.

Now let’s go back and see what the actual           32. Switch back to Internet Explorer.
InfoPath form we submitted looks like. Note
                                                    33. In Internet Explorer, click the Refresh
that the form itself also displays the new Status
                                                        button on the address bar.
Log section.
                                                    34. Click the Open Form link for the bottom
                                                        form.
                                                    35. Click the Open button.
                                                    36. Close InfoPath.




                                                                                                  Step 34
We’ll perform a similar procedure here to find         37. Switch back to SQL Server Management
and identify any forms submitted for a given               Studio.
Product Number for which there are duplicates
                                                       38. In the Solution Explorer pane, double-
in the database.
                                                           click on Mark Duplicate Product
Once again, here is the XML before we’ve                   Nubmers.sql.
processed the form, without the statusLog
                                                       39. On the SQL Editor toolbar, click Execute.
node.
                                                       40. In the Results pane, click the blue
Here’s the same XML after we’ve processed the
                                                           hyperlink in the top row.
form. Here we’ve again added a statusLog node
to the XML, in order to highlight the issue we         41. Close the window showing the XML            Step 38
found with the contents of the submitted form.             results.
                                                       42. In the Results pane, click the blue
                                                           hyperlink in the bottom row.
                                                       43. Close the window showing the XML
                                                           results.

Let’s add a simple text status attribute and           44. In the Solution Explorer pane, double-
timestamp lastStatusUpdate attribute from a                click on Mark Status.sql.
separate, system schema namespace in the
                                                       45. On the SQL Editor toolbar, click Execute.
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 InfoPath-
generated schema/namespace for the form.
These are defined in a separate schema and
namespace. But because the attributes are                                                              Step 44
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.
                                                 46. In the Results pane, click the blue
                                                     hyperlink in the last row of the top grid.
                                                 47. View Form XML, noting the absence of
                                                     advsys:status and
                                                     advsys:lastStatusUpdate attributes on
                                                     root my:priceUpdateForm element
                                                     (scroll to far far right)
                                                                                                  Step 46
                                                 48. Close the window showing the XML
                                                     results.
                                                 49. In the Results pane, click the blue
                                                     hyperlink in the last row of the bottom
                                                     grid.
                                                 50. Scroll to the right, and highlight the new
                                                     advsys:status and
                                                     advsys:lastStatusUpdate attributes that
                                                     have been added to the root
                                                     my:priceUpdateForm element.
                                                 51. Close the window showing the XML
                                                     results.

Returning to the webpage listing all our         52. Switch back to Internet Explorer.
submitted forms, we can refresh the page and
                                                 53. In Internet Explorer, click the Refresh
see that the status column has now been
                                                     button on the address bar.
updated with the current status for each
InfoPath form.                                   54. Click the Open Form link for the bottom
                                                     form.
Clicking on the link for the form that we
previously submitted, we can see that it still   55. Click the Open button.
opens just fine, even after the new elements
                                                 56. Close InfoPath.
from the second schema and namespace were
incorporated into the XML structure of the       57. View previously created form, noting
form.                                                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        Step 54
                                                  now InfoPath is opening it even though
                                                  the status attributes have been added
                                                  from an external namespace in the
                                                  Form’s lax-validated section.



Deliver Location Intelligence
Geographical information is rapidly becoming main stream to many business applications. SQL Server 2008 provides support for a new
geospatial data type that lets developers build location-aware applications.

New Geospatial Data Type

Script                                       Click Steps                                   Screenshots

This section coming soon.                    1.   Step 1
                                             2. Step 2
                                             3. Step 3

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:11
posted:11/19/2012
language:English
pages:85