SQL Server 2008 R2; Vision evolves from Data to Information Platform

imageOver the past 5 years Microsoft has been delivering on a vision to build a data platform for its customers. It has been a transformational time for SQL Server and they have delivered innovation to help their customers adapt to the explosion of data in their organizations. They approached this in terms of delivering a platform that supports all types of data as well as all tiers – from the edge, to the datacenter and then to the cloud. They have also delivered robust capabilities to enable you to build access to your data through rich applications and to manage this data with easy to use tools and interfaces, as well as new services to provide business intelligence.

One key evolution in this release (2008 R2) of SQL Server vision is moving from talking about data to talking about information. This reflects the shift that Customers want a platform that not only stores and manages all their data, but helps them deliver greater value from data across their business in the applications they use every day.

Today organizations are looking to compete and grow by reducing costs, reducing time to market and identifying the highest value opportunities for their business. Microsoft is moving forward rapidly to address these challenges with new capabilities in manageability, business intelligence and data warehousing and by delivering the first relational database cloud offering with SQL Azure and many game changing tools including Power Pivot, Report Builder 3.0 empowering end users to participate in Information Lifecycle .

Whats New in Team Foundation Server (TFS) 2010

This week around I spent some time in exploring Team Foundation Server (TFS) 2010. If you are familiar with 2008 and earlier releases of the Product you would find it a bit different and following are the changes which I have identified.

1) The Product is first installed and the files are copied on C:\Program Files\Your Path and then there comes a Configuration Wizard where the main game starts and you are required to configure TFS.

2) The Configuration Wizard is a cool add-up has a rich UI and allows you to configure the product in multiple ways such as you want to go for Basic Server Configuration or Advance Configuration.

3) There is no binding to must bind WSS 3.0 SP1 or Plus with your TFS and your TFS will continue to work without creating SharePoint sites as well.

4) In TFS 2010 you are required to configure SQL Server Reporting Services first rather then leaving on the TFS Configuration Wizard to configure them for you.

5) The Administration wizard is also a new addition and is such rich in nature that it allows you to actually re-configure or post configure WSS 3.0, Reporting Services, Analysis Services etc at the later stage.

6) If you plan to integrate TFS 2010 with SharePoint after the installation and configuration then you are required to have SharePoint extensions installed and use the TFS Administration Console to actually bind your SharePoint services to TFS 2010.

7) You must have heard of lot of tools coming along with TFS 2008 for Web Access now the Web Access Tool is built in TFS 2010 and it is also configured like other components this creates a separate TFS  website in the IIS and one can easily browse through the Team Project in the browser.

8 ) Hence the Configuration and Administration Consoles in TFS 2010 are really helpful. The Configuration Wizard allows you to Add/Remove components as per requirements and Administration Console helps you to Configure the Installed components and help in their Integration.

9) Overall the Product has improved its performance, health check activity and repair methodology but one should be very careful while choosing which version of the SQL and WSS 3.0 will go well with TFS 2010 and under which mode. For more information on installation please refer to by other post http://geekdeck.com/installing-configuring-team-foundation-server-2010-step-by-step/

10) Last but not least the Visual Studio 2010 Shell Console which you see after installing Team Explorer 2010 and man this has an awesome interface with much more options with compared to the previous version of Team Explorer and now this really looks like a console to manage TEAM PROJECTS!

Installing and Configuring Team Foundation Server 2010 (Step by Step)

Team Foundation Server (TFS) 2010 is already available and you can deploy and start using it. One ideal scenario for it’s installation is as follows:

1) Take a machine and Install Windows Server 2008 (Enterprise)

2) Create a User Named TFS with Admin rights

3) Then You need to install its Service Pack 2 (NOT R2), Just Service Pack 2

4) Install SQL Server 2008 (Enterprise Edition with all components. Mind you Install Reporting Services in Native mode (not the SharePoint Integrated mode) and DON’T Configure the Reporting Services NOW!

5) Download and Install WSS 3.0 + SP2 and You should be able to successfully complete its Configuration Wizard.

6) Once you do that you should be able to see the SharePoint site http://localhost

7) Now Go To the SQL Server Reporting Services Configuration Manager Tool and NOW CONFIGURE your Reporting Services, Use the tool in a step by step approach specifying User, Creating DBs etc. Every where specify the User TFS which you have created in step 2.

8) Once you do that you should be able to see the Reporting Service Portal and Web Service site on http://localhost/Reports ; http://localhost/ReportServer (or on which every URL you specified during Reporting Services Configuration)

image

9 ) When you Run the TFS set-up it will copy all the files to Program files which is equal to Installation and then it will start to configure after deployment / installation. In earlier version Install and Deploy was done at the same time.

10) Now Open up the TFS Setup choose which roles you want to deploy like probably you would only want to have TFS and Build Services and then specify the execution account (tfs user which you created).

11) Set-up will run Health Check (don’t worry if there are errors or warning, face them resolve them, they will be resolved, most errors are related to Account Security, System Reqs, Running Services)

12) Once Health Check is passed setup will start deploying Services, Databases and here if you again face errors it means that TFS is unable to make connection to SQL Server, SharePoint or the User has not sufficient Privileges etc.

13) Step 10 and 11 will take most of your time, but once TFS is deployed and Configure, you can further install Team Explorer to Start Creating and Managing Team Projects.

SQL Server 2008 R2 Reporting Services, SharePoint List Data Source

Increasing numbers of companies use SharePoint lists to store information that needs to be shared with a broader audience or in a standard report format. Although there are some creative ways you could employ to get that data into Reporting Services, custom code was always part of the solution.

SQL Server 2008 R2 Reporting Services has a new data extension provider that allows you to access SharePoint 2007 or SharePoint 2010 lists. After you create the data source using the Microsoft SharePoint List connection type and provide credentials for authentication, you must supply a connection string to the site or sub-site in the form of a URL that references the site or sub-site. That is, use a connection string such as http://MySharePointWeb/MySharePointSite or http://MySharePointWeb/MySharePointSite/Subsite. A query designer is available with this connection provider, allowing you to select fields from the list to include in your report.

SQL Server 2008 R2 Hardware Requirements

HARDWARE COMPONENT REQUIREMENTS

Processor type: (64-bit) x64

■ Minimum: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64Tsupport
■ Processor speed: minimum 1.4 GHz; 2.0 GHz or faster recommended

Processor type: (32-bit)
■ Intel Pentium III-compatible processor or faster
■ Processor speed: minimum 1.0 GHz; 2.0 GHz or faster recommended

Memory (RAM)
■Minimum: 1 GB
■Recommended: 4 GB or more
■Maximum: Operating system maximum

Disk Space Database
■Engine: 280 MB
■Analysis Services: 90 MB
■Reporting Services: 120 MB
■Integration Services: 120 MB
■Client components: 850 MB
■SQL Server Books Online: 240 MB

SQL Server 2008 R2 Editions

SQL Server 2008 R2 Editions

SQL Server 2008 R2 is available in nine different editions. The editions were designed to meet

the needs of almost any customer and are broken down into the following three categories:

■ Premium editions

■ Core editions

■ Specialized editions

Premium Editions

The premium editions of SQL Server 2008 R2 are meant to meet the highest demands of

large-scale datacenters and data warehouse solutions. The two editions are

Datacenter For the first time in the history of SQL Server, a datacenter edition is offered.

SQL Server 2008 R2 Datacenter provides the highest levels of security, reliability,

and scalability when compared to any other edition. SQL Server 2008 R2 Datacenter delivers

an enterprise-class data platform that provides maximum levels of scalability for

organizations looking to run very large database workloads. In addition, this edition offers

the best platform for the most demanding virtualization and consolidation efforts.

It offers the same features and functionality as the Enterprise edition; however, it differs

by supporting up to 256 logical processors, more than 25 managed instances of SQL

Server enrolled into a single Utility Control Point, unlimited virtualization, multi-instance

dashboard views and drilldowns, policy-based resource utilization evaluation, high-scale

complex event processing with Microsoft SQL Server StreamInsight, and the potential to

sustain up to the maximum amount of memory the operating system will support.

Parallel Data Warehouse New to the family of SQL Server editions is SQL Server

2008 R2 Parallel Data Warehouse. It is a highly scalable appliance for enterprise data

warehousing. SQL Server 2008 R2 Parallel Data Warehouse uses massively parallel

processing (MPP) technology and hub-and-spoke architecture to support the largest

data warehouse and BI workloads, from tens or hundreds of terabytes to more than 1

petabyte, in a single solution. SQL Server 2008 R2 Parallel Data Warehouse appliances

are pre-built from leading hardware venders and include both the SQL Server software

and appropriate licenses.

Core Editions

The traditional Enterprise and Standard editions of SQL Server are considered to be core edition

offerings in SQL Server 2008 R2. The following section outlines the features associated

with both SQL Server 2008 R2 Enterprise and Standard:

Enterprise SQL Server 2008 R2 Enterprise delivers a comprehensive, trusted data

platform for demanding, mission-critical applications, BI solutions, and reporting.

Some of the new features included in this edition include support for up to eight processors,

enrollment of up to 25 managed instances of SQL Server into a single Utility

Control Point, PowerPivot for SharePoint, data compression support for UCS-2 Unicode,

Master Data Services, support for up to four virtual machines, and the potential to

sustain up to 2 terabytes of RAM. It still provides high levels of availability, scalability, and

security, and includes classic SQL Server 2008 features such as data and backup compression,

Resource Governor, Transparent Data Encryption (TDE), advanced data mining

algorithms, mirrored backups, and Oracle publishing.

Standard SQL Server 2008 R2 Standard is a complete data management and BI

platform that provides medium-class solutions for smaller organizations. It does not

include all the bells and whistles included in Datacenter and Enterprise; however, it

continues to offer best-in-class ease of use and manageability. Backup compression,

which was an enterprise feature with SQL Server 2008, is now a feature included with

the SQL Server 2008 R2 Standard. Compared to Datacenter and Enterprise, Standard

supports only up to four processors, up to 64 GB of RAM, one virtual machine, and two

failover clustering nodes.

Specialized Editions

SQL Server 2008 R2 continues to deliver specialized editions for organizations that have

unique sets of requirements.

Developer Developer includes all of the features and functionality found in Datacenter;

however, it is strictly meant to be used for development, testing, and demonstration

purposes only. It is worth noting that it is possible to transition a SQL Server

Developer installation that is used for testing or development purposes directly into

production by upgrading it to SQL Server 2008 Enterprise without reinstallation.

Web At a much more affordable price compared to Datacenter, Enterprise, and Standard,

SQL Server 2008 R2 Web is focused on service providers hosting Internet-facing

Web serving environments. Unlike Workgroup and Express, this edition doesn’t have

a small database size restriction, and it supports four processors and up to 64 GB of

memory. SQL Server 2008 R2 Web does not offer the same premium features found in

Datacenter, Enterprise, and Standard; however, it is still the ideal platform for hosting Web

sites and Web applications.

Workgroup Workgroup is the next SQL Server 2008 R2 edition and is one step below

the Web edition in price and functionality. It is a cost-effective, secure, and reliable

database and reporting platform meant for running smaller workloads than Standard.

For example, this edition is ideal for branch office solutions such as branch data

storage, branch reporting, and remote synchronization. Similar to Web, it supports a

maximum database size of 524 terabytes; however, it supports only two processors

and up to 4 GB of RAM. It is worth noting that it is possible to upgrade Workgroup to

Standard or Enterprise.

Express This free edition is the best entry-level alternative for independent software

vendors, nonprofessional developers, and hobbyists building client applications. This

edition is integrated with Visual Studio and is great for individuals learning about databases

and how to build client applications. Express is limited to one processor, 1 GB of

memory, and a maximum database size of 10 GB.

Compact SQL Server 2008 R2 Compact is typically used to develop mobile and smalldesktop applications. It is free to use and is commonly redistributed with embedded and mobile independent software vendor (ISV) applications.

This Article is taken from SQL Server 2008 R2 Book by Ross Mistry & Stacia Misner

Creating a Map Report using Spatial Data in SQL Server 2008 R2

Note: We are using AdventureWorks2008R2 DB in this step by step lab as it contains Resellers Addresses as Spatial Data.

1. Start Microsoft SQL Server Business Intelligence Development Studio from Start | All Programs | Microsoft SQL Server 2008 R2.

2. Create a new Report Server Project.

image

3. Create a new report named Resellers Location.

Note: Maps cannot be created using the Report Wizard;

image

4. Add a Map item to the Resellers Locaion. To do this, drag a Map item (clip_image006) from the Toolbox to the Design surface. This will start the New Map Layer wizard.

5. In the New Map Layer wizard, select SQL Server spatial query and click Next as displayed in Figure

Note: The Sample Geographic data about resellers is available in the AdventureWorks2008R2 SQL Server database.

image

6. In the Choose a dataset with SQL Server spatial data step, select the Add a new dataset with SQL Server spatial data radio button and click Next.

7. In the Choose a connection to a SQL Server spatial data source step, create a data source connection to your spatial data. To do this, click New to open the Data Source Properties dialog and follow these steps:

a. Set the data source’s name to AdventureWorks2008R2.

b. Keep the Embedded Connection and Microsoft SQL Server (in Type combo box) default selections.

c. Click Edit to open the Connections Properties dialog. Set the server name to “YourServer”” server alias and select AdventureWorks2008R2 from the Select or Enter a Database Name dropdown list. Optionally click Test Connection, and finally click OK.

image

The resulting Data Source Properties dialog should appear. Click OK to go back to the New Map Layer wizard and click Next.

image

8. In the Design a Query step, type a T-SQL statement which returns a list of resellers along with their location. To do this, click Edit as Text and paste the T-SQL statement below into the query window. Click the run button (clip_image014) and note that the SpatialLocation column from the Person.Address table is a field of type Geography which stores the longitude and latitude of each reseller’s location. Click Next.

T-SQL

Select s.BusinessEntityID, s.Name, a.SpatialLocation

From Sales.store s

Inner Join Person.BusinessEntityAddress bea

On s.BusinessEntityID = bea.BusinessEntityID

Inner Join Person.AddressType at

On bea.AddressTypeID = at.AddressTypeID and at.Name = ‘Main Office’

Inner Join Person.Address a

On bea.AddressID = a.AddressID

Inner Join person.StateProvince sp

On a.StateProvinceID = sp.StateProvinceID

Inner Join sales.SalesTerritory st

On sp.TerritoryID = st.TerritoryID

Inner Join Person.CountryRegion cr

On st.CountryRegionCode = cr.CountryRegionCode

Where cr.name = ‘United States’

image

9. In the Choose spatial data and map view options step, make sure that SpatialLocation is specified as the Spatial Field, and that the Layer Type is set to Point. Click Next.

image

10. In the Choose Map Visualization step, keep the Basic Marker Map default selection and click Next.

image

11. In the Choose color theme and data visualization step, select the Corporate and Circle options from the Theme and Marker combo boxes respectively. Then click Finish to close the New Map Layer wizard.

12. Rename the Title of the map that appears on the Design surface to US Resellers. To do this, double-click on Map Title and type US Resellers.

image

Playing a little more with alignment and properties you get something like the one below:

image

And further adding up a Polygon layer through the MAP LAYER WIZARD to the Map we can finally show up the report like this… (this is not discussed in the article probably it might be the part of future posts)

image

So a new way to present your spatial data on the Report !!

PowerPivot in SQL Server 2008 R2

Microsoft SQL Server 2008 R2 delivers many powerful new features. PowerPivot is one of the most significant, arguably Microsoft’s most important Business Intelligence (BI) advance since the advent of Microsoft SQL Server Analysis Services 2005. It seriously delivers on the long-awaited promise of “self-service BI” and “BI for the masses”, by leveraging Microsoft Excel, the world’s most-used analysis platform, Microsoft SharePoint, the rapidly growing collaboration platform, and a new, in-memory version of Analysis Services. PowerPivot enables end users to build high performance analytic applications without having to wait for IT to build cubes and develop the extract transform and load (ETL) process to populate them, or to learn MDX to achieve multidimensional calculations. At the same time it allows users to collaborate by sharing a single copy of the application and allows IT to monitor such usage, ultimately taking over management and control of the application where that makes sense. This lab will expose you to the full range of new features in Microsoft Office 2010, Microsoft SharePoint 2010 and Microsoft SQL Server 2008 R2 which, working together, achieve the self-service BI vision.

The point about users not having to wait for IT is more significant than it might seem at first. It is not just about users being able to do timely analysis because the typical IT backlog of work can be avoided. At least as important is the enablement of true self-service BI, understood as the user being able to build their own analytic models from whatever data sources are available to them, unconstrained by the predefined structures, data relationships and data sources that would be inevitable in anything pre-built for them by IT. This vision turns the typical analysis/build cycle on its head, enabling users to show IT what they need by building it themselves. Later, if it makes sense, IT can use the analytic model as a starting point for re-architecting to achieve any number of optimizations, and in the meantime can monitor its usage and manage it as a full-fledged information asset.

The new version of Analysis Services implements an in-memory, columnar database. The data compression associated with (and necessitated by) a columnar database architecture allows vast amounts of source data to be stored in-memory, and because in memory, to be sliced and diced orders of magnitude faster than would otherwise be possible. In PowerPivot this new analysis engine is packaged in two forms: a client add-in for Excel 2010 called SQL Server PowerPivot for Excel (PowerPivot for Excel) and a server component delivered thru SQL Server 2008 R2 to SharePoint 2010 called SQL Server PowerPivot for SharePoint (PowerPivot for SharePoint). The server version, though managed by SharePoint, appears to client tools like a normal Analysis Services server, the only difference being it is accessed using a URL.

The SQL Server PowerPivot for Excel component is initially used to load and organize BI data and build an analysis on it using pivot tables. The result is called a “model”. A wide range of data sources can be loaded. The overall experience is similar to how Excel pivot tables have traditionally been used for years, except that with PowerPivot, far more data can be manipulated (100 million rows has been demonstrated in 64 bit mode with sufficient memory). The data also can be analyzed with much faster performance than before. PowerPivot for Excel is an overlay on Excel but is designed to look and act like Excel as much as possible so that this greatly enhanced capability still feels familiar to both casual and power Excel users. Power users can also use a new expression language called Data Analysis Expressions (DAX) to exploit a host of multidimensional capabilities from within Excel that are difficult or impossible otherwise.

Workbooks containing PowerPivot data and pivot tables are XLSX files and can be shared, copied, emailed, uploaded to a SharePoint document library and otherwise treated just like non-PowerPivot workbooks. They can be opened in a browse-only mode by versions of Excel lacking PowerPivot for Excel (such as pre-Office 2010 versions). There may be limitations to treating PowerPivot workbooks in these traditional ways, however:

  • The file size may preclude emailing if a large amount of data was loaded, in spite of the compression.
  • A client must have PowerPivot for Excel to fully leverage such workbooks.
  • Such use does not avoid the proliferation of “spreadmarts” which are the bane of many IT departments.

Note: PowerPivot for SharePoint addresses all of these issues.

When users are satisfied with their client-based PowerPivot models and wish to share them with others, the “new” next step is to share them to Excel Services on a PowerPivot-enabled Sharepoint server. When this is done the models are automatically transformed into a new type of Analysis Services cube – the in-memory cube. SharePoint manages instances of Analysis Services as necessary to host the cubes and provide appropriate performance as users collaborate and share them. Shared PowerPivot models behave like any workbook in Excel Services, except that the data scale and analytic performance of PowerPivot continues to be available. In addition, once on the server, Reporting Services can use PowerPivot models as data sources. The data within them can be refreshed from the original sources. Finally, because a PowerPivot model becomes an actual Analysis Services cube on the server, IT can take over management of the model from the original creator, enhancing or even recreating the model using standard cube design best practices and leveraging all of the capabilities of “regular” Analysis Services.