Next - Next - Fin...ish - Installing SQL 2008 CTP

After downloading the SQL 2008 CTP 6 from MS Downloads, I started the install.  The usual "prerequisites" show up.

 

Then a big holy crap when I saw this screen.  Stress level goes up.  This laundry-list style installation screen is supposed to be addressed in later CTPs.  What's wrong with the Office-style install screen?  I don't want a failover cluster or configure cluster nodes... I just want to install SQL.

After that, I figured out (with the help of a co-worker) that I was supposed to click New Installation. ;)  Stress level goes down.  Only one failure.

Always with the reboots?  Why can't they just restart Explorer process or something?  Stress level goes down.  Go for coffee.

Apparently I'm running SQL 2000, 2005, and Express all on my laptop.  Why not throw 2008 in the mix?  I specify Named Instance so it doesn't blow up my 2000 install.  Stress level stays even.

I scratched my head for awhile on this screen.  I figured I'd just run everything under local system - best practice, right? :)  Click next.

Of course, the AGTSVCACCOUNT parameter!  System identifiers shouldn't be used in a UI.  Hopefully they fix this to say something like "use a domain account with x priviledges" or "create me an account to do this" or "email your domain admin with a template requesting what you need" or "open active directory or user management".  Apparently 1 error isn't good enough.  I have to be assaulted with 5 of them.  Stress levels elevated.

I added my sql2008user account with default permissions for now.  Setting the account individually didn't work for some reason, so I ended up under "Use the same account for all" which reset the Integration Services from Network to my specified account.  I would have expected it to stay as Network, since the Browser didn't change.  I searched forums and didn't get a whole lot of information on requirements for the service account, just info on how to get a similar error using the command-line install.  Guess I could have clicked help.  I never could figure out why there's a help button in an installation.... the perfect monkey install is Next Next Finish.  Configure afterwards.  Leave configuration to the application team.

Account Provisioning tab?  What's wrong with Security Model name or just security?  I'm not provisioning any accounts other than possibly SA.

The Add Current User button to add me to SQL Server administrators made me feel special.  I'm so important I don't have to type my name.  It would be semi-useful to see the current user's name next to the button, just in case you're terminalled in with a different account.  I chose Mixed Mode and set the SA password.

This directory is probably going to get flagged for being too long in Scandisk... but I chose the defaults anyway.

Where's the directory to set SQL error logs & SQL Agent logs? 

Of course you have to add FILESTREAM as a tab in this UI too, just to confuse the infrastructure guys during the install.  I turned this one on and exposed a file share.  Stress levels elevated.  I wasn't crazy enough to allow remote clients to have streaming access to FILESTREAM data. 

My favourite button is back.

This screen has the location of the Log files.  Why didn't SQL? Stress levels decreased, though those paths are way too long.  This makes Analysis Services look way too simple. :)

Decisions, decisions... stress levels elevated.   How come there isn't an option to back out of Reporting Services install altogether?  You telling me I need to go back to the beginning?  Agh.  Some mention should be made that there's no IIS required, as this was really a sticking point for DBAs in the past.

I'll pick native mode.  It would be good to know if we could choose both Native & SharePoint, for maximum flexibility.

I'll uncheck these two.  The first one is a bit vague... where exactly am I sending this info?

For kicks I view the privacy statement.  Stress levels elevated.

Microsoft is committed to protecting the security of your information. We use a variety of security technologies and procedures to help protect your information from unauthorized access, use, or disclosure. For example, we store the information you provide on computer servers with limited access that are located in controlled facilities.

Ok, so my private info is stored on servers behind lock and key.  Along with a unique, non-identifying GUID based on my machine.  I'm ok with that.  The privacy statement has enough details for me to be reassured that Microsoft techies won't be buying stuff on eBay with my credit card, though they may laugh at how poorly my computer is running with 10 SQL Services from 3 different versions.  Stress levels back to normal.

I have to scroll both Horizontally and Vertically?  What's BOL & OCS? :)  Hopefully the program identifiers & acronyms get ditched in the final release summary and it just gives me a message saying "I'm installing what you told me.  Go get a coffee."

Now I am Ready to install SQL Server.  What have I been doing for the past 1/2 hour?

Finalizing setting calculations... installing doomsday virus... configurating flopticality...

Doh.

I chopped out the rest, but It's something to do with the domain security account I used (mine) not existing (it does, since I'm logged on with it.)

It's error code 0x84B40000

It doesn't look like a recoverable error either.  I got booted out of the install.  I guess I won't add myself as an administrator with my favourite button.  :(

 

2nd Attempt

I didn't click my favourite button, and instead added the service account I setup as a SQL & SSAS admin.  After about 45 minutes all of the services installed, and I'm now up and running with SQL 2008.

 

Some of the things I'm looking forward to trying out that are related to projects I have worked on in the past. 

Getting Traction with SQL Server 2008 Filestream

Zach and I just spent a couple of days figuring out how to make use of the new FILESTREAM support in SQL Server 2008 and we thought we'd share a little bit about the experience in hopes it might save somebody some time.

rdoherty's WebLog : Getting Traction with SQL Server 2008 Filestream

 

The HierarchyID Datatype in SQL Server 2008

By J P R, 2008/03/25

Introduction

In this article, I will present a new feature of SQL Server 2008: the HierarchyID data-type. We will see that this new data-type provides a new way to design trees in databases. It adds functionalities to T-SQL language and improves whole performance.

That article explains deeply the new type and gives you some samples, with a comparison with classic way, based on CTE.

Source code is available here: code source

Note: SQL scripts work with SQL Server 2008 CTP2 (july)

http://www.sqlservercentral.com/articles/SQL+Server+2008/62204/

 

MOLAP Writeback

Introduction

In a recent post, I started investigating around a new Analysis Services 2008 feature: writeback partition with MOLAP storage.

Unfortunately, I got stopped by a bug in SQL Server 2008 CTP 5 which disallowed me from committing my updates.

SQL Server 2008 CTP 6 is now available. I can now continue from where I stopped last time.

http://bimatters.spaces.live.com/blog/cns!9CF41EA10109E385!164.entry

Decreasing the Size of VHD Files by Disabling the SFC

So what do I do if I have a 30GB VPC and 11GB of free disk space?  

There are several methods you can use to decrease the size of your virtual hard disk (VHD) files. One of the lesser known methods is to decrease the Windows system file checker (SFC). To disable to SFC on your guest operating system you can simply open a command prompt from within it and type the following commands:
sfc /cachesize=0
sfc /purgecache
Doing this can typically decrease the size of a VHD file by nearly 300 MB. Saving this much spacer per virtual machine can add up very quickly.

Decreasing the Size of VHD Files by Disabling the SFC

More here

http://dotnet.org.za/yah/archive/2007/07/26/howto-shrink-virtual-pc-virtual-hard-disks-vhds.aspx

OLAP PivotTable Extensions - Good stuff for Excel 2007

This looks useful for anyone working with cubes in MS Excel.  It allows advanced users to create their own custom private calculations, and view the MDX being executed within a pivot table.

OLAP PivotTable Extensions is an Excel 2007 add-in which extends the functionality of PivotTables on Analysis Services cubes. The Excel 2007 API has certain PivotTable functionality which is not exposed in the UI. OLAP PivotTable Extensions provides an interface for some of this functionality. It can be launched from the following menu option in the right-click menu for PivotTables:

OLAP PivotTable Extensions - Home

What Really Happened? Integrated Video Testing in VSTS/Next

 

The killer feature for me is integrated video, keystroke and action recording. If you hover over the Start Test Case link, it brings up a balloon which allows you to edit the video settings. You can edit the recording types by selecting Edit…

November Rosario CTP - Executing Manual Test Cases « Grant Holliday

Executing predictions from the SQL Server relational engine

Here's how to tie in SQL Server Analysis Services predictions into SQL Server.

A common request from the relational database gurus in the SQL Server Data Mining community is to execute predictions from the SQL Server relational database engine – either in batch mode or ‘on-the-fly’ while rows are being inserted into a table. In this article, we’ll start with the basics of executing DMX queries in T-SQL and walk you all the way to predicting values in real-time during the INSERT operation.

Executing predictions from the SQL Server relational engine

Active Directory - from SQL

Here's one way to get to AD for SQL, to do things like synchronizing your application's security with AD.

I have been able to link the server to query AD.  I use a Cursor to go through and import OU's, Groups, and associated Users.  This works like a charm, but upon further review, not all Group members are coming across. 

Query For OU's:  I only care about those under Departmental Accounts.  This returns accurately.

SELECT  name, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT  name, distinguishedName
   FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=company,DC=corp,DC=company,DC=com''
  WHERE objectClass = ''organizationalUnit'' ')

Query For Groups:  This is run within a Cursor from the above result set.  @DN is the distinguishedName for each OU from above.  This returns accurate results.

SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT  mail, displayName, distinguishedName
   FROM ''LDAP://corp.company.com/" + @DN + "''
  WHERE objectClass = ''Group'' ')

Query For Group Members:  This runs within a Cursor from the Groups result set above.  @DN is the distinguishedName for each Group from above.  This returns some Group members but not others and is where I need help.

SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT  mail, displayName, distinguishedName
    FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=company,DC=corp,DC=company,DC=com''
WHERE memberOf = ''" + @DN + "''

Most examples on the web use OpenQuery.  Anytime I try that method I get an error.  Permissions is not an issue, or so it should not be given that I had Domain Admin rights while trouble-shooting this.  The following query is an example of a Group that does not return any results:

SELECT mail, displayName, distinguishedName
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;',
'SELECT  mail, displayName, distinguishedName
   FROM ''LDAP://corp.company.com/OU=Departmental Accounts,OU=Company,DC=corp,DC=company,DC=com''
  WHERE memberOf = ''CN=Analysts,OU=Marketing,OU=Departmental Accounts,OU=Company,DC=corp,DC=company,DC=com''
    AND objectClass = ''User'' ')

I'v got pretty far after many trial and error routines.  This has me stumped.  Any help would be appreciated!

Active Directory - Yet Again

Ward Pond's SQL Server blog : The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement

 

The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement

This nifty little trick will allow you, with some limitations, to treat the output of a stored procedure as a named SQL record set rather than creating a table and going through an INSERT.. EXEC process. The output of the stored procedure is then available for direct manipulation in SELECT statements, JOINs, etc.

Ward Pond's SQL Server blog : The OPENROWSET Trick: Accessing Stored Procedure Output In A SELECT Statement

Chris Webb's BI Blog: Dimension Security Tips, Tricks and Problems

Some helpful information on dimension-level security in Analysis Services.

I've been doing some work with complex dimension security recently and come across a number of issues which I've either known about but never blogged about, or only just become aware of. It's a bit of a random list but hopefully it'll help someone out there...

Chris Webb's BI Blog: Dimension Security Tips, Tricks and Problems

Monitoring and Recording DDL changes on SQL 2005 (NARC) - SQL Server Central

Poor man's SQL auditor.

The classic rhetorical question when diagnosing a technical issue "what changed on the server?", may now be possibly answered. Since some of my servers do not have Service Broker and Notification Services activated or installed, I had to come up with a solution that would work on my installed base of SQL 2005 as I originally intended to make this a Notification Services running asynchronously just a learning exercise.

Monitoring and Recording DDL changes on SQL 2005 (NARC) - SQL Server Central

How to Summarize Excel Data For Reporting and Analysis, An Excel 2003 Tutorial

 

Learn something new every day... maybe even a couple things.

Business Intelligence with Microsoft Excel Formula Arrays and Named Ranges.

In Excel, you normally enter a formula by typing it into the formula bar and then pressing the Enter key. You array enter a formula by typing it into your formula bar, holding down the Ctrl and Shift keys, and then pressing Enter.

After you array-enter a formula, the formula bar shows that it begins and ends with braces, like this:

{=A1}

To be clear, you do not type in those "{" and "}" characters yourself. Instead, Excel displays them after you array-enter a formula by pressing Ctrl+Shift+Enter.

How to Summarize Excel Data
For Reporting and Analysis

Excel offers several ways to summarize data quickly and easily.
We explain the most powerful and flexible approaches, which
include using Excel arrays.

How to Summarize Excel Data For Reporting and Analysis, An Excel 2003 Tutorial

Microsoft vs. Teradata | Reg Developer

Comparing Microsoft vs. Teradata.  Don't flame me, I'm just the messenger! :)

Given this common wish list, how did Microsoft and Teradata end up with such different strategies?

Microsoft vs. Teradata | Reg Developer

SSIS Junkie : SQL Server Analysis Services - Best Practices

 

As a pointer to best practice I would recommend that you always use named queries in your DSV.

SSIS Junkie : SQL Server Analysis Services

Chris Webb's BI Blog: Using Non_Empty_Behavior With YTD calculations

Chris hacks away at a slow performing MDX query... by creating a new fact table view.

The query now runs in, wait for it, in 7 seconds on a cold cache in SQLMS and if you look in Profiler you'll see that in fact the majority of that time is taken up by SQLMS rendering the resultset - it takes just over 2.5 seconds to actually run on the server.

Chris Webb's BI Blog: Using Non_Empty_Behavior With YTD calculations

SQL Server 2008 CTP6 gives you PowerShell - Rob Farley

 

SQL Server 2008 CTP6 gives you PowerShell

The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.

SQL Server 2008 CTP6 gives you PowerShell - Rob Farley

Free Online CSV to SQL Converter - mySQL Native Support!

 

This tool provides an easy way to convert CSV files to SQL to be inserted into a database. Simply upload your CSV file and the resulting page will display the INSERT statements to turn the CSV file into records in a database table!

Free Online CSV to SQL Converter - mySQL Native Support!

Aggregation design, row counts and the 1/3 rule

 

Mosha and Thierry give us multiple examples of how to provide annotations in SSAS 2005.

The solution revolve around the fact that Analysis Services Storage engine doesn’t know how to handle String for Measure data but does know for Dimension data and also the fact that the Formula engine does know how to handle text data.

So in a nutshell, Comment and Annotation are handled as Dimension members, entered by the user using Dimension writeback, then are retrieved as part of the measure group using a calculated member and thus queried from any front-end using regular MDX.

Handling Comment or Annotation as dimension member can seem scary at first, but since Yukon doesn’t handle the entire Dimension in memory anymore, its scalability capability in term of size of dimension members has greatly increased and can easily go above 50 millions members. For the very large majority of applications it seems reasonable to assume that this type of volume will never be reached. Indeed if we assume that an application has a lifespan of 5 years, then it gives us 10 millions members or Comment per year. Now let’s say that this application has 1000 active users, then every user has a workspace of 10 000 comments per year or 833 comments per month. It is a lot of text to enter every month for a single user…

Aggregation design, row counts and the 1/3 rule

The Road to Know Where: Download Free Office 2007 Software from Microsoft

 

Even though Microsoft Office 2007 has only been release for a short time, there are already some free additions from Microsoft. If you're looking for free Microsoft Office 2003 software -- click here!

The Road to Know Where: Download Free Office 2007 Software from Microsoft

K. Scott Allen : Three Rules for Database Work

 1. Never use a shared database server for development work.

2. Always Have a Single, Authoritative Source For Your Schema

3. Always Version Your Database  

K. Scott Allen : Three Rules for Database Work

Five sqlcmd features to automate SQL Server database tasks

 

Five sqlcmd features to automate SQL Server database tasks

Roman Rehak
02.18.2008
Rating: -4.20- (out of 5)

Five sqlcmd features to automate SQL Server database tasks

Microsoft Gets Gartner's Business Intelligence Top Ranking -- Business Intelligence

Looks like Microsoft's BI Strategy (KPIs for everyone in all of our products) is working out.

Business Objects, Cognos, and Microsoft were placed among the leaders in Gartner's just-released Magic Quadrant for Business Intelligence Platforms, 2008. But the research firm placed Microsoft above the other two in its ability to execute, including the competitiveness and success of its BI goods and services, its viability and investment in BI, and the execution of its sales and pricing. In last year's BI platform report, Gartner put Microsoft in the challenger quadrant.

Microsoft Gets Gartner's Business Intelligence Top Ranking -- Business Intelligence

Cubes and Universes « Stéphane-Robert Langer

A couple years old but still worth a read to better understand differences between BO and SSAS.

Mark Miller recently posted a link to a whitepaper comparing the BI offerings from Business Objects and Microsoft. This is something I had been looking for for quite a while and I think it does a good job of presenting a comparative history of both platforms. The content remain fairly high level, though. What I’d like to do here is to discuss some of the conceptual differences between a Business Objects (BO) universe and a Microsoft SQL Server Analysis Services (SSAS) cube.

Cubes and Universes « Stéphane-Robert Langer

format date excel export reporting services - Google Search

Not a lot of results on this one... so I figured it out myself.

Instead of formatting just the field, you need to format the entire column in order to retain the date format in Reporting Services Excel export.

Hope this helps someone else.

format date excel export reporting services - Google Search

Microsoft Research: MSAGL: Microsoft Automatic Graph Layout

A cool Visio-like graph tool for under $300 that you can drop into C# code.

MSAGL is a .NET tool for graph layout and viewing. It was developed in Microsoft Research by Lev Nachmanson. MSAGL is built on the principle of the Sugiyama scheme; it produces so called layered, or hierarchical layouts. This kind of a layout naturally applies to graphs with some flow of information. The graph could represent a control flow graph of a program, a state machine, a C++ class hierarchy, etc.

Microsoft Research: MSAGL: Microsoft Automatic Graph Layout

JIT Development - Managed Visio

Visio (and office in general) is still in the dark ages with its automation support.  Namely COM+ & VBA.  It works, though I'm a C# kind of guy.

Luckily here's one project that may help to improve the coding experience.  It helps that I just installed VS 2008.

Project Description

VisioAutoExt is a library that simplifies using Visio 2007 from C#

VisioAutoExt - Home

Diagrammatic Reasoning Related Research Sites

Interesting site for those looking at Data Visualization, OLAP and Silverlight application ideas, as I am.

Diagrammatic Reasoning Related Research Sites

Diagrammatic Reasoning Related Research Sites

and this to demonstrate a cube example.

http://www.wideman-one.com/gw/Models/sdsubigpic/DataCube1.htm

This is from '96.  How long has OLAP been around anyway?

According to the OLAP Report, before 1962...

http://www.olapreport.com/origins.htm

What's even more incredible than this tidbit of information?  Cognos Planning is based on APL, the OLAP language designed by IBM... and once again owned by IBM as of last year.  Over 37 years later.

Who has one of the most long-lived software products ever?  Oracle's Express, which has gone through over 35 years of development.

Who had features that are still not widely adopted?  Comshare's System W had these features:

• Full non-procedural rules
• Full screen multidimensional viewing and data editing
• Automatic recalculation
• Batch integration with relational data

#1 is in IBM Cognos Applix TM1.  I can see Silverlight hitting number 2 pretty easily with a writeback cube and a cube browser.  #3 is in Analysis Services.   #4... well... that would be nice.  I have built a few custom solutions around merging MDX & SQL data.  It would be great to see these pulled into a single view without writing complex MDX.

Oracle might offer #4 with its materialized views.

Which OLAP server didn't integrate with Excel when it was first released?  Microsoft OLAP.

What's the most popular BI tool in the world?  Microsoft Excel.

So the killer app to promote Silverlight adoption for BI (other than installing it for the MS downloads site) is to create a Silverlight visualization plugin for Microsoft Excel (Services?) and PerformancePoint.

I guess I've answered my data visualization question.

Visio Guy » Blog Archive » SharePoint Planning Visio Diagrams

 

For your convenience, here's the list of Visio drawing-links:

Models

• Model: Business Intelligence
• Model: Office SharePoint Server Application Security
• Model: Office SharePoint Server Backup and Restore
• Model: Office SharePoint Server Topologies
• Model: Office SharePoint Server Baseline Site Hierarchies
• Model: Office SharePoint Server Customization
• Model: Office SharePoint Server Database Administration
• Model: Office SharePoint Server Enterprise Content Management
• Model: Office SharePoint Server Extranet Topologies
• Model: Office SharePoint Server Inter-Farm Shared Services
• Model: Office SharePoint Server Products Migration
• Model: Office SharePoint Server Search Administration
• Model: Office SharePoint Server Shared Services
• Model: Upgrading Office SharePoint Server Products
• Model: Upgrading SharePoint Portal Server 2003 to Office SharePoint Server (1 of 2)
• Model: Upgrading SharePoint Portal Server Version 2 to Office SharePoint Server (2 of 2)

Scenarios

• Scenario: Enterprise Content Management
• Scenario: InfoPath Forms Services Customer Interaction Form
• Scenario: InfoPath Forms Services Expense Reporting
• Scenario: InfoPath Forms Services Insurance Claims
• Scenario: InfoPath Forms Services Permits
• Scenario: InfoPath Forms Services Supplier Survey
• Scenario: Keeping Connections to Data Sources Current
• Scenario: Large Corporate Internet Site (Infrequent Changes to Web Content)
• Scenario: Office SharePoint Server Call Center
• Scenario: Office SharePoint Server Corporate Intranet Portal Site
• Scenario: Office SharePoint Server Departmental Portal Site
• Scenario: Office SharePoint Server Equities Research
• Scenario: Office SharePoint Server Informational Portal Site
• Scenario: Office SharePoint Server Out-Hosted Portal Sites (ASPs)
• Scenario: Online News Internet Site (Frequent Web Content Updates and Searching)
• Scenario: Preserving "One Version of the Truth" for Distributed Workbooks
• Scenario: Records Management in Office SharePoint Server
• Scenario: Rendering Data from Spreadsheets to Web Parts

Flowcharts

• Office SharePoint Server Planning Flowchart, Part I (application administrators)
• Office SharePoint Server Planning Flowchart, Part II (IT pros)
• Office SharePoint Server Deployment Flowchart

Related Posts:

• Integrating Visio 2007 with Microsoft Technologies
• Shapes
• Microsoft Exchange Server 2007 Stencil
• Visio Links
• Visio Conference 2008 - Awards, Notes, Epilogue

Visio Guy » Blog Archive » SharePoint Planning Visio Diagrams

Check List for Performance Optimization in SQL Server Analysis Service 2005

 

No matter what your platform or the tools that you are using, performance is almost always the number one feature that customers require.  In the case of SQL Server Analysis Service (SSAS), there are four areas where we can improve the performance. They are database design, processing, querying and configuring SSAS.

Check List for Performance Optimisation in SQL Server Analysis Service 2005

Generating Missing Dates and Numbers - SQL Server Central

Here's how to find gaps in data with an extremely fast solution. 

This article presents an approach to generate missing numbers and dates without using a temp table. I do not intend to claim that this approach is better than the others. There is no programming approach that suites all requirements. Based on the specific application scenario a certain approach may be found suitable than the others. I guess some of you may find this approach easier to integrate into your application specific requirements.

Source: Generating Missing Dates and Numbers - SQL Server Central

101 Design Patterns & Tips

Lots of good ones here... 

101 Design Patterns & Tips for Developers

Source: 101 Design Patterns & Tips

Revenge of the BLOB - SQL Server Central

One of my articles has been published to SQL Server Central.  It was an odd scenario that I had never seen put to use in SQL Server before.  To store a table of records records in a table column as a blob sounds like madness to me, or perhaps something retro from the '80s.

I think it was more fun digging up the origin of Blob than finding a solution though...

Link to Revenge of the BLOB - SQL Server Central

The Data Platform Insider : Microsoft SQL Server 2008 Roadmap Clarification

Looks like SQL 2008 is not quite ready for primetime just yet...  

To continue in this spirit of open communication, we want to provide clarification on the roadmap for SQL Server 2008. Over the coming months, customers and partners can look forward to significant product milestones for SQL Server.  Microsoft is excited to deliver a feature complete CTP during the Heroes Happen Here launch wave and a release candidate (RC) in Q2 calendar year 2008, with final Release to manufacturing (RTM) of SQL Server 2008 expected in Q3. Our goal is to deliver the highest quality product possible and we simply want to use the time to meet the high bar that you, our customers, expect.

This does not in any way change our plans for the February 27 launch and we look forward to seeing many of you in Los Angeles and other events around the world. Please keep the great feedback coming and thank you again for your ongoing support of SQL Server!

Source: The Data Platform Insider : Microsoft SQL Server 2008 Roadmap Clarification

A Reporting System Architecture - SQL Server Central

An interesting use of Database Mirroring to create a low-impact reporting solution.

These seemingly simple requirements added more complexity to the project than one might think. We mulled over numerous ideas and varying solutions to satisfy the business needs. Our final solution involved the use of a staging database setup on the reporting server (a seperate box) that is populated via SQL Server mirroring. ETL Scripts are then written in SSIS that utilize a snapshot of the mirror database to denormalize the data and populate two reporting databases (details below). The solution turns out to be elegant, to perform well and has basically zero negative impact on the current OLTP system.

A Reporting System Architecture - SQL Server Central

Comparing Multiple Datasets with the INTERSECT and EXCEPT operators

A manual Diff tool for SQL 2005...

With SQL Server 2005, Microsoft introduced the INTERSECT and EXCEPT operators to further extend what you could already do with the UNION and UNION ALL operators.

• INTERSECT - gives you the final result set where values in both of the tables match
• EXCEPT - gives you the final result set where data exists in the first dataset and not in the second dataset

The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.  

Source: Comparing Multiple Datasets with the INTERSECT and EXCEPT operators

Basics of Statistics in SQL Server 2005 - Load testing without any data

For example, you can vastly increase the apparent rowcount and pagecount of the Sales.SalesOrderDetail table by issuing this command:

UPDATE STATISTICS Sales.SalesOrderDetail 
WITH ROWCOUNT=5000000, PAGECOUNT = 5000000

Why would you want to do this? SQL Server's query optimizer does things differently on small tables and on large tables. During development, you likely don't have a whole lot of data in your tables. Rather than spend time generating huge amounts of sample data, you can use the undocumented options of UPDATE STATISTICS to see how the query optimizer will treat your tables after they've been in production for a while. This can be a quick and dirty method to uncover potential performance issues before they arise, and perhaps point out the need for additional indexes or other schema work. I'd recommend confirming any such changes by retesting with realistic amounts of sample data, though

Basics of Statistics in SQL Server 2005

Microsoft PowerPoint - procedurecache-sql.ppt

 

Lots of useful stuff on optimizing queries in SQL 2005.

Becoming a Query Plan

Caching Superhero!

Microsoft PowerPoint - procedurecache-sql.ppt

Very Large Database index optimization

 

We're talking hundreds of GBs or 1 TB or more. These databases are now common on SQL Server 2000 and 2005 - at TechEd IT Forum this week (and at SQL Connections the week before) there are many customers with multi-TB databases. Any experienced DBA knows the value of running consistency checks, even when the system is behaving perfectly and the hardware is rock-solid. The two problems that people have with running a full CHECKDB on their VLDB are:

• It takes a long time to run (based on many factors – see my previous post here for details).
• It uses lots of resources – memory, CPU, IO bandwidth, tempdb space.

So it uses lots of resources for a long time. Even with a decent sized maintenance window, the CHECKDB may run over into normal operations. There's also the case of a system that's already pegged in more or more resource dimensions. Whatever the case, there are a number of options:

• Don't run consistency checks
• Run CHECKDB using the WITH PHYSICAL_ONLY option
• Use SQL Server 2005's partitioning feature and devise a consistency checking plan around that
• Figure out your own scheme to divide up the consistency checking work over several days
• Offload the consistency checks to a separate system

In Recovery... - Paul S. Randal on SQL Server

Howard @ MSFT selling BI: Proclarity tricks

 

Proclarity tricks

There is a cool trick that allows you to use a proclarity analytics server page web part in a performance point dashboard, with the toolbar functionality and supports the passing of filters.

Basically you set up the ProClarity Analytics Server Page report view like you normally would, except that after you select the desired report, you modify the server URL parameter to point to a different web page on the server.

Here are the steps. You’ll need to plug in your PAS server URLs:

#1 – start by using http://pas-server/pas  in the Server URL when configuring your PC Report View; then navigate select the PC view you wish to display

#2 – after having chosen a view, change the Server URL to:  http://pas-server/pas/en/src/proclarity.asp?uiConfig=tb;ht

#3 – Then, if you want to connect up a filter, drag the Member UniqueName property from the filter onto your PC Report View

Bonus – adding the URL parm fs; places an extra button onto the toolbar which comes in handy if you want to let end users open the underlying view in the PC Web pro client:

http://pas-server/pas/en/src/proclarity.asp?uiConfig=tb;ht;fs;

Howard @ MSFT selling BI: Proclarity tricks

Inferred Members & SSAS 2005

How to ignore missing keys in dimensions.

This is about you can handle new fact records that have one or many dimension keys that is not part of your dimension table or a dimension key is null or blank.

Either you can solve this in the ETL process by adding an inferred member in each dimension, like 'N/A' or 'unknown', or you can let SSAS2005 handle this.

This post is about the last scenario.

Thomas.I Microsoft BI & Analytics

Journey to SQL Authority with Pinal Dave

Very useful.

How many times we have wondered what were the last few queries ran on SQL Server? Following quick script demonstrates last ran query along with the time it was executed on SQL Server 2005.

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Journey to SQL Authority with Pinal Dave

SQL Nexus Tool - Home

 

What is SQL Nexus?

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

SQL Nexus Tool - Home

PSS SQL Server Engineers : RML Utilities for Microsoft SQL Server Released

 

The Microsoft SQL Server support team uses several internally-written utilities to make it easier to work on a typical customer support case. These utilities may also be useful to database developers and system administrators who work with Microsoft SQL Server 2000 and Microsoft SQL Server 2005.  The utilities are collectively called the RML Utilities for SQL Server.

With the RML Utilities you can answer questions such as the following:

· Which application, database or login is consuming the most resources, and which queries are responsible for that.

· Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed.

· What queries are running slower in today's data as compared to a previous set of data.

PSS SQL Server Engineers : RML Utilities for Microsoft SQL Server Released

SQL Courses

Large list of database resources from Joe Celko..

SQL Courses

Passing the 70-445 BI Exam

Jorg Klein has some great info if you're studying for 70-445, or MS exams in general.

http://sqlblogcasts.com/blogs/jorg/archive/2007/11/15/MCTS-_2D00_-I-passed-the-70_2D00_445-exam_2100_.aspx

And he linked to my article on Sql Server Central.

Thanks Jorg!

SQL Server Engine Tips : SQL Server 2005

 

Updating a large table is a common scenario. This is often encountered in cases of applications that performs series of tasks in the background or data warehousing loading scenarios. To reduce locking and logging resources, such update statements are broken down into smaller batches or units of work. This has been traditionally done using SET ROWCOUNT setting in SQL Server. Now, SQL Server 2005 provides you with a simpler construct that the optimizer can understand and use efficiently. TOP clause has been enhanced in SQL Server 2005 to support expressions and can be used now in all DML operations. Let's look at a quick example on how to use TOP with UPDATE:

SQL Server Engine Tips : SQL Server 2005

Rocket Science: OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

I think sp_msforeachdb might be a solution that's a bit easier to read, but this works too.

Get the top 10 statements that took the most time for each database on a SQL 2005 server.

select coalesce(QUOTENAME(DB_NAME(t.dbid), '"'), '') /* NULL means resource db*/
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid), '"')
+ N'.'
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid), '"') as full_obj_name
, SUBSTRING(t.text, (t.statement_start_offset/2)+1,
((CASE t.statement_end_offset
WHEN -1 THEN DATALENGTH(t.text)
ELSE t.statement_end_offset
END - t.statement_start_offset)/2) + 1) AS statement_text
, t.*
from (
select *, DENSE_RANK() OVER(PARTITION BY t.dbid ORDER BY qs.total_elapsed_time) as rnk
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as t
where t.objectid is not null
) as t
where t.rnk <= 10
order by t.dbid, t.rnk;

SQL Server Engine Tips : OBJECT_NAME enhancement and OBJECT_SCHEMA_NAME addition in SQL Server 2005 SP2

MDX Outline Formulas

Essbase to MDX translation.

MDX Outline Formulas

Troubleshooting 64 bit COM+ Apps

One interesting behaviour on 64-bit systems I saw today is how applications are run, and the fact that two command prompts are available (32 & 64 bit).

"Windows gets around these issues by offering two command prompts: one 64-bit and one 32-bit. Environment variables are set according to which command environment is being used.
For example, if you open a command prompt by entering the CMD.EXE command at the Run prompt, Windows will open a 64-bit command prompt. In most cases, the %ProgramFiles% environment variable for the command environment will be set to C:\Program Files. If you run a script, the script can interact with 64-bit applications, but not with 32-bit apps.
On the flip side, if you enter the C:\Windows\SysWOW64\cmd.exe command at the run prompt, you'll be running a 32-bit command prompt. In that case, the %ProgramFiles% environment variable will be set to C:\Program Files (x86). "

http://searchwincomputing.techtarget.com/tip/0,289483,sid68_gci1218185,00.html

The key issue to remember here is that c:\program files should not be used for 32-bit apps or 32-bit COM+ components. Use c:\program files (x86) instead.

Tips, Tricks, and Advice from the SQL Server Query Optimization Team : Fun for the day - Automated Auto-Indexing!

 

Effectively, this will periodically determine top index candidates for your workload.  It currently runs in a recommendation mode, but you can also have it run fully automated if you uncomment a line in the file.

Source: Tips, Tricks, and Advice from the SQL Server Query Optimization Team : Fun for the day - Automated Auto-Indexing!

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Curious cursor optimization options

I don't like cursors.  Usually there are ways to avoid them, more so in SQL 2005.  But if they exist in your environment and are causing performance problems, take a look at this article.

If you have to optimize a cursor for performance, keep the following considerations in mind:

1. Always try to replace the cursor by a set-based equivalent first. If you fail to see how, do not hesitate to ask in one of the SQL Server newsgroups.
2. If you are really stuck with a cursor, then do NOT rely on the default options. They will result in the slowest of all possible option combinations
3. If you think that the FAST_FORWARD option results in the fastest possible performance, think again. I have not found one single test case where it was faster than, or even as fast as, a STATIC cursor.
4. Do NOT use the WHERE CURRENT OF syntax of the UPDATE command. Using a regular WHERE clause with the primary key values will speed up your performance by a factor of two to three.
5. Do not rely blindly on my performance results. Remember, the one thing that is always true when working with SQL Server is: “it depends”.

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Curious cursor optimization options

Craig Freedman's SQL Server Blog : Scans vs. Seeks

 

Scans vs. Seeks

Scans and seeks are the iterators that SQL Server uses to read data from tables and indexes.  These iterators are among the most fundamental ones that we support.  They appear in nearly every query plan.

Craig Freedman's SQL Server Blog : Scans vs. Seeks