 |
| 
|
| 2008 |
|
|
|
| 2007 |
|
|
|
| 2006 |
|
|
|
| 2005 |
|
|
|
| 2004 |
|
|
|
| 2003 |
|
|
|
| 2002 |
|
|
|
| 2006 |
|
|
|
| 2005 |
|
|
|
| 2004 |
|
|
|
| 2007 |
|
|
|
| 2006 |
|
|
|
| 2005 |
|
|
|
| 2004 |
|
|
|
| 2007 |
|
|
|
| 2006 |
|
|
|
| 2005 |
|
|
|
| 2004 |
|
|
|
| 2003 |
|
|
|
| 2007 |
|
|
|
| 2006 |
|
|
|
| 2005 |
|
|
|
| 2004 |
|
|
|
| 2003 |
|
|
|
| 2007 |
|
|
|
| 2006 |
|
|
|
| 2005 |
|
|
|
| 2004 |
|
|
|
| 2003 |
|
|
|
| 2004 |
|
|
|
| 2003 |
|
|
|
| 2002 |
|
|
|
| 2001 |
|
|
|
| 2000 |
|
|
|
| 1998 |
|
|
|
| 1997 |
|
|
|
| 1996 |
|
|
|
| 1993 |
|
|
|
| 2003 |
|
|
|
| 2002 |
|
|
|
| 2003 |
|
|
|
| 2001 |
|
|
|
| 2003 |
|
|
|
| 2002 |
|
|
|
| 2001 |
|
|
|
| 2003 |
|
|
|
| 2001 |
|
|
|
| Visual FoxPro 2001 |
|
|
|
| Visual FoxPro 2000 |
|
|
|
| Visual Studio 2000 |
|
|
|
| West Wind 2002 |
|
|
|
| Double Impact 2001 |
|
|
|
| FoxTeach 2001 |
|
|
|
| Jam sessions |
|
|
|
| Technical Guidelines |
|
|
|
| About |
|
|
|
| Acronyms |
|
|
|
| Contact information |
|
|
|
| Troubleshooting |
|
|
|
| Time zones |
|
|
|
| Web Service |
|
|
|
| Terms & Conditions |
|
|
|
| Copyright |
The Professional Association for SQL Server (PASS) will host the industry’s largest user-run educational event exclusively dedicated to the SQL Server in Seattle, Washington on November 14 – 17, 2006. Attend the 2006 PASS Community Summit, the largest event of the year exclusively dedicated to SQL Server education. Get the largest and most comprehensive coverage of SQL Server 2005 anywhere while you develop and expand your knowledge of SQL Server 2000. Meet and network with members of the Microsoft SQL Server development team, SQL Server MVP's and other SQL Server professionals. Check out the world's premier SQL Server vendors at the largest, most comprehensive tradeshow dedicated to SQL Server. And, much more!
| Date: | ||
| Location: |
November 13
by John Paul Cook
November 14
by John Paul Cook
Tuesday Preconference Session: Database Storage Engine PSS Boot Camp
Bob Ward, Senior Escalation Engineer with Product Support Services, started the preconference boot camp session by waking up the audience with
howling feedback from the microphone. Boot camp sessions originated at SQL PASS 2005 and are back this year by popular demand. Bob addressed the
audience by asking attendees to "Raise your hand if you really, really know what a LSN is." Few, if any, hands were raised. If you’d like to
know more about Log Sequence Numbers, read this and
this in Books Online. Bob explained that Log Sequence Numbers are important to understanding
how backups work.
Microsoft Tape Format is the protocol for writing any backup to any device. Trace flag 3216 dumps the MTF details. There are SQL specific extensions
to MTF. SQL_HEADER is where the RESTORE HEADERONLY gets its data from.
LSNs are used to maintain a chain of backups. A differential backup chain is broken when a full backup is made. A recovery fork is when a log
backup is made after performing a recovery, which is why you should make a full backup after a recovery. SQL Server 2005 stores LSNs as part of
the metadata to help you keep track of backups and make sure the correct backups are used.
Backups can sometimes be unexpectedly slow. Customers call PSS about slow backups and the cause is usually small chunks. There is a MAXTRANSFERSIZE
parameter on the BACKUP command. As Books Online points out, it is used to specify the size of the chunks used when performing the backup. The
smallest chunk size is specified as a multiple of 64kb, with a minimum of 64kb up to a maximum of 4mb. The default size for a backup to disk is 1mb.
What isn’t obvious is that the specified size or even the default size (if nothing is specified) may not be used. SQL Server may downgrade
the MAXTRANSFERSIZE if it is too large or if the allocation fails. A downgrade may occur when there isn’t enough virtual address space, which can
be caused by memory leaks or memory pressure from COM or extended stored procedures. These problems are unlikely to occur on 64 bit systems. Should
a downgrade occur, "Downgrading backup buffers" will appear in the log file.
VDI - virtual device interface - is used to stream a backup to wherever you want using BACKUP DATABASE dbname TO VIRTUAL_DEVICE. The MTF protocol
is not used and multiple threads are supported. It uses a COM dll named SQLVDI.dll. There are two versions of this dll on 64 bit systems, a 32 bit
version and a 64 bit version. If you are having a problem with VDI, you could actually be having a COM problem and need to reregister the appropriate DLL.
Suresh Kandoth introduced variable length decimals, new to SQL Server 2005 Service Pack 2. This vardecimal data type stores decimal data in the
variable length portion of a row. As with other variable length data, there is an overhead incurred. Two bytes of overhead are incurred for each
vardecimal. When a large percentage of the values are small or null, as is the case with sparsely populated data, the storage savings can be
substantial. Using vardecimal is CPU intensive as the data must be converted whenever it enters or leaves the database engine. The compressed format is
confined only to the database engine itself. Outside of the database engine, vardecimal is always represented as fixed decimal, so there are no
application changes when vardecimal is enabled on a table.
Michael Zawadzki pointed out that not only does SQL Server 2005 Service Pack 1 provide database mirroring capability, it also adds a new Database
Mirroring Monitor. Quoting from KB 916940, "To start this monitor, right-click a database node, point to Tasks, and then click Launch Database
Mirroring Monitor."
Michael also pointed out that the reason database mirroring has two endpoints, one for each direction, is to improve security by preventing spoofing.
Upcoming product milestones were discussed, such as Microsoft SQL Server 2005
Service Pack 2 (SP2) and Visual Studio Team Edition for Database Professionals, as well as the immediate availability of the Release Candidate (RC) of
SQL Server 2005 Compact Edition. More information here, too.
Kevin then showed a video that traced the development of the various versions of SQL Server through interviews with some of the product's leading lights at each stage.
Once the video was over, Kevin gave way to Paul Flessner, Microsoft Senior Vice President of Server Applications. Paul has played a key role in nurturing the product along,
and is stepping aside soon to pursue a new position as a "Seattle weatherman, who only has to work three or four days a year." Where many tech conference keynotes by senior
executives come across dry and over-hyped, Paul gave an engaging performance.
Paul started with a bit of a backgrounder on data capacity growth over time, couched in layman's terms:
Most of growth is driven by non-text data like medical imaging and video monitoring. We are drowning in a fast-growing sea of data!
Incredibly, the good news is that the cost and capacities of data storage continue to improve at rates faster than the data is growing. A GB of disk space that cost $40K
in 1980 costs just 38 cents today, and continues to slide. RAM is following a similar curve, leading to situations where entire large databases could live most of their
lives in fast memory, greatly improving data analysis performance.
Paul did reiterate the widely held notion that being connected is a mandate. With over 1 billion people connected to the Internet in some fashion now, connectivity is
becoming more and more of a commodity. Paul, going back to his belief in the future prevalence of loosely coupled data architectures, sees service oriented architectures
(SOA) as being the driving paradigm. Given the history of the software business over time, I'm sure SOA will have gone through several name and meaning changes before it is
really broadly accepted. Rather than an increasingly "occasionally connected" future, I think the promise of Internet connectivity that is as reliable and
fast as current LAN connectivity is a more likely long term outcome. There's
more money to be made in centralizing content and selling it over well-connected (and controlled) infrastructure than in pushing masses of it out to edge
devices and keeping
that in synch; the latter seems much more expensive to me. Why physically download a movie when you can view it over your connection at any time, anywhere? But then
again,
I could be wrong...
Paul then put away his crystal ball, and slipped in the required marketing-speak all too common at vendor-sponsored events. SQL Server 2005 is surrounded by a community of
over 3000 ISV partners, although the number of companies exhibiting here is very small compared to what one might expect. This is especially disappointing to some attendees,
like me, who actually love the chance to get some face time with vendors.
According to Gartner, SQL Server is the most widely deployed database product, more than IBM and Oracle combined, and that's not counting the more than four million SQL Express
downloads. SQL Server is experiencing 35% year-over-year growth, and 40% of new SAP deployments are on SQL Server. Actual sales numbers always impress me more than feature
hype.
Upon his return, Paul gave his opinions of what's next in the world of data management. The explosive growth of data makes data mining a hot topic; too much data makes
effective data mining a difficult task. This is only going to get harder as more and more emphasis is placed on a personalized real time data retrieval experience. Search,
fast and effective, is the Holy Grail, but with the growth in "sight and sound" data, this is going to get harder before it gets easier. The database is becoming a
data platform. Paul struck a chord with me when he mentioned the need for data to "work at a richer semantic level", treating data relationships as objects, e.g.,
"order" object rather than a view. This all sounds fine, as long as it leads to better ways to search. Hopefully, this "richer semantic" approach will get us there; in fact, it's inevitable because the industry as a whole is striving for more
human-like machine interfaces. The ultimate search interface is plain English (or your favorite human language), not a complex query-by-example interface. Now that's
interesting!
Microsoft is attempting to schedule future SQL Server releases ("Katmai" and "Katmai+1") on two- to three-year cycles, instead of the previous five plus years. CLR
integration was largely to blame, according to Paul. These future releases will be much more security and management focused.
One of the Microsoft Product Managers came up to demo some of the upcoming "Always On" features of SQL Server. Even though the demo gods intervened a few times, the gist
came through and was pretty compelling. You can actually take a live, running SQL Server transactional database and create a mirror instance of it from scratch via a
wizard without stopping (or even hardly slowing) down production processes. He forced a failover from the original database to the mirror, and the applications feeding in
transactions didn't miss a beat. Pretty cool stuff.
The first part of the session concentrated on setting up SQL Server 2005 Reporting Services, along with upgrading to it from the 2000 version. The upgrade process and
results are pretty seamless, with no major gotchas. You can run the 2005 version against 2000 databases, too. The WMI model has changed, however, which is important to
those organizations who've already invested in using it to automate repetitive processes.
Once everything is set up, the Reporting Services Configuration Tool provides a simple, powerful and intuitive interface to tweaking the installation. It can be done
locally or remotely, too.
Management of your Reporting Services instance is accomplished through a Web Services API and the WMI API, which is changed from the monolithic SOAP API provided in the
2000 version. Generally, the web service API is used to manage content, and is wrapped in an object model for ease of use in Visual Studio. The WMI API is used primarily
to manage service configurations or cross instance manipulation, and can do it remotely, even if web services are not available. The manageable Reporting Services
infrastructure is defined by a hierarchical namespace that uses a folder metaphor (Report, Folder, Data Source, Resource, Model) to help manage the role-based security model and organize
the reports themselves. Resources are things like Office documents and spreadsheets, images, etc., while a Model is a "business definition" of the underlying meaning of
data items. Really, "business modeling" for reporting. The management metadata is stored in XML-like RDF format outside of Reporting Services; when consumed at runtime, it
is stored in a different format internally.
Data Sources can be defined either at a specific report level or can be shared among multiple reports for reuse. SP2 brings add Sharepoint integration, too.
Lukasz then went into quite a bit of detail on Report Caching capabilities; this is pretty cool stuff with lots of options. Report Caching is, of course, a performance
enhancer. For example, you can cache a report in the case where multiple users access the same report in the same way; that way, it's not re-executed each time for each user;
they just get a cache copy. This could really reduce server loads in many situations. You can prepopulate the cache, say, with a nightly batch job creating daily, static
reports. Very powerful. Execution and History snapshots are similarly interesting, but used more to control how particular data is associated with a report.
Great session with a lot of depth.
Lunch was well-organized and held adjacent to the Exhibit area, as is usually the case with these conferences. The conference facilities and staff continue to impress;
it's a very smooth conference all around.
As I said before, there really seems to be too few vendors in the exhibit area; and not a lot of variety, either. Most fall into one of a few categories: high-availability,
performance enhancing and reporting packages. Maybe this is a function of the maturity of a feature-rich SQL Server 2005; I just can't help wondering why more of the
aforementioned 3000+ ISV's involved with this product aren't here.
Language Integrated Query (LINQ), due in the "Orcas" release of Visual Studio (due sometime in 2007), seeks to unify the various ways that applications interact with data. Relational tables
and XML are handled quite differently because of their inherent natures; LINQ provides a strongly typed framework for accessing and manipulating all forms of data. More
generally, LINQ creates an environment for developers where all data is treated as a collection of objects.
LINQ itself is entirely written in .NET code, so it relies on existing underlying technologies, such as ADO.NET, and performs accordingly. When dealing with XML data,
performance is enhanced because the traditional, slow ("mechanical") DOM functions are bypassed in favor of a sleeker, faster ("declarative") implementation.
If you're familiar with SQL syntax, then getting up to speed on LINQ should be relatively quick and painless. While LINQ expresses itself using familiar standard
query operators, an obvious departure from SQL syntax is that the column list (SELECT clause) appears at the end of the query statement, rather than at the beginning.
Another way to think of it is that while SQL delivers flat rows, LINQ delivers an object hierarchy. Your code references the object model as a namespace. Because it's a
hierarchic object model, you don't have to explicitly specify join conditions. It automatically uses RI definitions to do this behind the scenes. Updates are done similarly
to ADO: make changes to "properties" (columns) in the hierarchy, then do a "submit" (UPSERT). SQL Server stored procedures and user-defined functions can be wrapped in
methods that are part of that same object namespace.
A few notes: LINQ is not available for Compact .NET Framework, and it doesn't seem to be on the horizon, either. The C# implementation of LINQ doesn't support
Intellisense when building the query statement in code. LINQ doesn't support DDL directly, so it will only work against an established schema.
A very good overview of LINQ.
SQL Server 2005 Installations
Although none of our reporting team was able to attend any preconference sessions on Monday, I did speak to attendee Avnesh Gupta. Avnesh attended
the all day session presented by Microsoft Product Support Services and thought it was well worth attending. One thing in particular that Avnesh was
happy to learn about is SQL Server 2005's capability to determine which indexes are not being used. Dropping unused indexes reduces storage requirements
and improves performance. For more information on finding out about unused and little used indexes, read the Customer Advisory Team's blog.
Bob Ward, Suresh Kandoth, Michael Zawadzki
People need better backup and restore strategies. They also need to understand how the internals work. For example, it is generally believed that
backing up a log truncates the log, which frees up space. However, truncation will not occur if there is an active transaction. Replication will also
prevent log truncation.
According to Paul, this could lead to some counterintuitive changes in infrastructure and endpoint device utility. A PDA/phone device with 4-core CPU's, 200GB of
flash memory, high-definition internal displays (20 hours of HD capacity), basically a "carry-able PC", is not out of the question, leading to a widespread decentralization
of information that is constantly synchronized with central repositories. I question the ability of battery technology to support a platform like that in that timeframe,
but I also question the prognosis of large-scale decentralization of information. If you consider handheld device capacity as a percentage of total available data storage
capacity elsewhere, I would bet that the ratio of handheld capacity to total capacity is actually getting smaller not larger because of the exponential growth of
central data stores. Handheld capacity growth is more likely multiplicative, but not exponential. In other words, 200GB of storage, even on a PDA, ain't gonna impress a lot
of us in four years.
Next, Paul introduced his replacement at Microsoft, Ted Kummert, who said a few nice words about Paul, then some additional nice words to the audience, before retreating backstage.
I think I was completely distracted by his body language, mannerisms and delivery, which, from a spot deep in the large audience, was indistinguishable from Richard
M. Nixon in his heyday.
SQL Server 2005 Reporting Services Management and Security
Lukasz Pawlowski, Program Manager, Microsoft Corporation
Many developers and users began using Reporting Services with SQL Server 2000. Most prior emphasis, in my experience, has been on things like the design surface,
report deployment, end-user considerations, etc. It was good to see a session devoted to the mundane but equally important topics of configuration, management and
security. A lot of the sessions are emphasizing the usefulness of automating management processes, and Lukasz provided plenty of demonstrations and examples along
those lines, I found the scripting examples especially interesting.
There are useful functions for managing report execution (setting execution timeouts, manually stopping long-running reports), execution logging, management event
scheduling (for automating caching and snapshot handling, and are shareable), subscriptions (triggered by events that then might specify output format and delivery
mechanism) and scripting (for automating repetitive management tasks such as migrating from test to production).
Intermission
The .Net Language Integrated Query Framework
Dinesh Kulkarni, Program Manager, Microsoft 
LINQ is divided into three main parts: LINQ To Objects, LINQ To SQL (really, LINQ-enables ADO.NET) and LINQ To XML. Overall, the ability for LINQ to describe and manipulate
data via an object model allows for strongly typed coding, which allows the complier to catch more pre-runtime problems than was possible before. Conversely, the same object
orientation allows LINQ to provide SQL-like handling of any .NET collection. Create an in-memory collection of objects, then query that collection without having to
resort to FOR...EACH types of constructs. Cool!
by John Paul Cook
Nancy Hidy Wilson, Shell Information Technology International, Inc.
What's there to know about installing SQL Server 2005? It's just inserting a CD and clicking, right? Sometimes it is, but as Nancy explained, there can be surprises along the way, and not all of them pleasant. As a DBA for Shell with a global workforce in the tens of thousands, Nancy has plenty of experience installing and administering SQL Server 2005 in a large enterprise environment.
Nancy emphasized the importance of actually reading the Release Notes so you don't overlook documented requirements such as making sure MSDTC is configured and Window 2003 cryptographic services is started. She also pointed out the importance of searching the Knowledge Base for important facts discovered after the Release Notes were published. As an example, Nancy cited KB910070 "FIX: The SQL Server 2005 Setup program may take a very long time to be completed" as something you might need to know if you have many domains. Here's a quote from the article: "When the primary domain has many external trust relationships with other domains or many lookups are performed at the same time, the time that is required to look up domain group names may increase significantly. Therefore, the time that is required to install SQL Server 2005 may increase." That's really quite an understatement after hearing Nancy explain one of her installations took 19 hours to complete! She applied the hotfix mentioned in the KB article and her installation time dropped to 0.5 - 1.5 hours, depending on what options were specified.
SQL Server 2005 does a much better job of checking for prerequisites than SQL Server 2000, but you should not rely solely on the installer. Based on her experience and the documentation, Nancy provided the following additional manual checks:
SQL Server 2005 no longer uses InstallShield; instead it uses the Windows Installer 3.1. This means any custom scripts you've developed for installing SQL Server 2000 will have to be modified to work with SQL Server 2005. The installation wizard begins with a check for these prerequisites and installs them if they are not present:
Welcome to our coverage of The 2006 PASS Community Summit, from the Washington State Convention & Trade Center in Seattle, Washington. I've been to Seattle many times and,
luckily, the weather was gorgeous nearly every time, necessary to appreciate the awesome physical beauty of the area. On this trip, however, with all
travel to and from the conference in darkness, and a consistent low rainy cloud cover, the 1500+ attendees will be happy to remain inside and concentrate on the impressive collection of
sessions (100+) and network with the more than 200 attending Microsoft employees and more than 80 MVP's and other experts that are here.
SQLPASS President Kevin Kline lead off the keynote session and spent some time discussing the state of The Professional Association for SQL Server, including financial position and progress,
and recognizing the many people who give generously of their time to make it a success. It shows in the conference; there were all kinds of members and volunteers
in roles like "Ambassadors", A/V setup, room attendance counters (kind of like a secret shopper), etc. Quite a performance so far.
- Windows Installer 3.1
- .NET Framework 2.0
- SQL Client (SNAC)
You might be wondering exactly what SNAC is and what it can do for you. ADO 2.0 exposes all of the new SQL Server 2005 functionality such as MARS (Multiple Active Result Sets), which doesn't help you if you aren't using ADO. SNAC makes it possible for ODBC and OLEDB to take advantage of the new features. The installation wizard continues a System Configuration Check before proceeding to the actual installation. It is common to see the IIS Feature Requirement warning. Typically database servers do not have IIS installed, so a warning is displayed to let you know Reporting Services can't be installed.
Nancy pointed out some common pitfalls with using the Feature Selection screen. First, you have to click the Advanced button on the Components to Install screen. If you don't, you won't see the Feature Selection screen at all. Second, it's easy to make incorrect assumptions about what it is going to do. It is best to open all of the nodes and make absolutely certain every feature you want to install will actually be installed. Selecting a feature at a higher level does not necessarily mean that subordinate features are selected. Also, if you change the installation path for a particular feature, it may change the installation path for a different feature. That's why it is best to open all of the nodes and check each feature's installation path. Even if you specify something other than the C drive for every feature, some of the installation will still go to C anyway.
Be sure that you don't install what you don't need. If you don't need to support legacy DTS, you don't need the Legacy Components. Also, if you don't use Full-Text Search, you don't need to install it. My own personal observation is that you might want to override the default and not install SQL Server Books Online (BOL) on the grounds that the version on the installation media is a few versions out of date. Or you can make your own copy of the installation media and replace the msi for BOL with the latest and greatest version. Nancy pointed out that sample databases do not get installed by default in accordance with Microsoft's secure by design strategy. If you want sample databases, you'll need to explicitly specify them.
A person in the audience asked Nancy if his 1000s of DTS packages will migrate to SSIS? Nancy explained that the upgrade wizard from DTS to SSIS is not very good, so it's probably better to use the DTS Legacy runtime. If you do choose to migrate old DTS packages, you won't be able to edit them in 2005.
Using a user domain account is much better for security. Is it one account per service or instance? In the preconference security session, the advice is that it depends on the situation. At Shell, the same account is used for the server and the agent . Different accounts are used for every instance. Microsoft runs some low risk applications all under the same account. Although Nancy is a big proponent of running SQL Server services under domain account credentials, she doesn't specify them during installation. Since her installations are scripted, she doesn't want passwords in clear text in any installation script files. By specifying Local System, no passwords appear in any files. After the installation is finished, only then does Nancy specify the user domain accounts.
The SQL Browser Service replaces SSRP (SQL Server Resolution Protocol) on UDP 1434. It is shared among all database engine and SSAS instances. It should run in the lowest privileges possible.
Just like with SQL Server 2000, SQL Server 2005 needs a password for the sa account even when Windows Authentication only is used. Nancy recommends disabling the sa login. SQL Server 2005 Service Pack 2 prompts for a sa password even if Windows Authentication is selected.
When SQL Server 2005 is installed, local Windows groups are created. Here is a list of the groups from a full installation of SQL Server 2005:
SQLServer2005DTSUser$machineName SQLServer2005MSFTEUser$machineName$MSSQLSERVER SQLServer2005MSOLAPUser$machineName$MSSQLSERVER SQLServer2005MSSQLServerADHelperUser$machineName SQLServer2005MSSQLUser$machineName$MSSQLSERVER SQLServer2005NotificationServicesUser$machineName SQLServer2005ReportingServicesWebServiceUser$machineName$MSSQLSERVER SQLServer2005ReportServerUser$machineName$MSSQLSERVER SQLServer2005SQLAgentUser$machineName$MSSQLSERVER SQLServer2005SQLBrowserUser$machineName
Do not add or remove users or groups in any of the SQLServer groups.
SQL Server services should be administered only through the SQL Server Configuration Manager or SQL Server Surface Area Configuration tools. Use the SQL Server 2005 Configuration Manager to change accounts, do not use Services. This is necessary to make sure accounts stay in the proper local groups.
Nancy mentioned the lack of the ability to save a SQL Server 2005 installation parameters to a file for later reuse. SQL Server 2000 had the ability to replay an installation by using a setup.iss file. She referred people to my article on using an undocumented feature as a workaround.
Since Nancy's focus is on large enterprise installation, she didn't cover SQL Express or CD-ROM equipped desktops. SQL Server 2005 installation media comes on DVD. The day before her presentation, I met people who wanted to be able to install SQL Server 2005 from a CD because some of their machines only have CD-ROM drives. By deleting 64 bit and other files, you can squeeze 32 bit SQL Server 2005 files onto a single CD. I discuss this and how to overcome those pesky SQL Express defaults in this article.
Interviewing SQL 2005 Developers About NUMA Design Considerations
Microsoft Panel Discussion
This was a highly technical presentation given at the same time as Nancy Hidy Wilson's presentation. Since I can't be in two places at the same time, I have to refer you to Slava Oks's blogs on NUMA. Go here and here for detailed information on NUMA. You will see a reference to cc-NUMA, which is cache coherent NUMA.
SQL Server User Education Team
David Shank
I interviewed David Shank, the group manager for the SQL Server User Education team at lunch. David wanted to let people know they really are making a difference in improving Books Online (BOL) and how to get the most out of BOL. BOL pages have a link to submit feedback to Microsoft.
When the Send Feedback link is clicked, the email client opens an email with a subject line indicating the exact page reference. When Microsoft receives the email, it is processed to match it up with the author and a new bug is created in the product database. If the error is received during work hours, the email may be read within only minutes of it being received. Action is undertaken to confirm and fix a problem, which can occur very quickly. Once a topic is updated, the updated version is posted to MSDN. This is why it's a good idea to configure BOL to check online before checking locally. The local copy of BOL is really for occasionally connected users. The latest and greatest documentation is always online at MSDN. All of the updates to MSDN are rolled up into quarterly updates for the downloadable BOL.
Sometimes the author contacts the person who submitted the feedback. As I told David, I've been contacted on several occasions. Authors will work with the submitter if necessary to get an issue resolved. If you do submit feedback, be as detailed and specific as possible. If code samples are needed to explain something, include them. Customers are alerted to changes by providing a master change list as well as including version history notes on individual pages.
There are roughly 55,000 topics in BOL. Previously, all topics were created equally. The feedback mechanism has changed that, which is why it is important for customers to opt-in to the Customer Feedback option. Microsoft aggregates your BOL viewing patterns. They know what topics are being viewed, which are the highest rated, and which are the most popular. Documentation can be continuously improved. Resources are allocated according to what is most important. Microsoft has learned that customers are consistently asking for samples and snippets, which is not surprising because of how developers work.
I asked David if Windows Update could tell us when a new BOL is available. He said no, but they are looking at other ways to keep us up-to-date.
SQL Server 2005: What We Have Learned
Mark Sousa
Mark Sousa, Group Program Manager for the SQL Server team, presented a lengthy list of facts and statistics. SQL Server 2005 has been adopted very fast, particularly in the high end. There is not application in the world that can’t be successfully be implemented on SQL Server 2005. One customer has a 270 TB database. Microsoft has a 20 TB system on a 4 way server used by its Information Security Team. It loads 0.5 TB per day up to 1 TB per day. A comparable amount of data is deleted every day.
ISVs are adopting SQL Servers faster than at any time in the past. SQL Server 2005 was certified by SAP in 6 weeks compared to 18 months for Oracle 10g.
Expectations do not always align with the facts.
- Online utilities are expected to be 100% online, but online index rebuilding requires a schema lock at the beginning and at the end. Online restore may require a database lock at the beginning.
- Having a more sophisticated optimizer means more optimization time, which takes more CPU.
- SSIS and SSRS are not easy to upgrade.
- For high-end applications, SSAS redesign may be warranted.
- Sometimes 2005 performance is worse than 2000.
- 64 bit doesn’t always outperform 32 bit.
- OLE DB drivers going to other database providers have been a problem, particularly 64 bit Oracle and Teradata.
SSAS can run out of memory and appear to be hung, but actually is running very slowly. This is fixed in Service Pack 2. If you need a fix before Service Pack 2, contact PSS for a hotfix.
Multiple technologies may be needed to get high availability. Sometimes a third party solution is better because of simplicity in deployment and administration. A single SAN based solution may be preferred over multiple 2005 solutions such as mirroring and log shipping.
Hacking SQL Server
Brian S. Knight and Bayer White
Brian and Bayer presented a thorough demonstration of the now widely known techniques of SQL injection attacks. Predictably, the overwhelming majority of the audience raised their hands when Brian asked if they knew what SQL injection is.
Brian said the first step in preventing a hacker from succeeding is to think like a hacker. Most attacks occur at night or on the weekend. A perimeter defense is needed to raise alerts. The demonstration began with the classic " or 1=1" SQL injection attack. One client’s perimeter defense checked for "1=1" but failed to detect anything when they were attacked with "2=2".
Instead of taking up a lot of space repeating the well-known SQL injection hacks here, click here to read about them in detail or use your favorite search engine to find an abundant variety of articles. If you want to experiment with SQL injection on your own, I highly recommend using the SQL Profiler so you can see exactly what is being executed.
Brian offered some specific advice to protect your data.
- Always encrypt sensitive data.
- Remove BUILTIN\Administrators from SQL logins.
- Keep error messages hidden from users. Redirect to a custom error page instead.
- Never make your default database master.
- Use Surface Area Configuration to disable OLE Automation.
- Don’t enable xp_cmdshell.
- Always audit.
Brian also provided a list of free tools that can help you identify SQL injection vulnerabilities in your systems:
- Wikto162
- Paros
- Web Sleuth
- Napkin
- Absinthe - blind injection attack and analysis tool
- Cain and Able
Business Intelligence and Office 2007
Michael Tejedor
Michael Tejedor is a product manager with a focus on business intelligence. I asked him to demo new features and explain some concepts to me. I was particularly interested in understanding the role of Office 2007 in BI. Additionally, with many companies of the opinion Office 2003 or even earlier versions meet their needs, I wanted to know the value proposition for adopting Office 2007.
BI offers the potential of a high ROI, but this is difficult to realize. Business analysts have typically had to use unfamiliar tools that are difficult to learn. Deployment has been difficult because of the cost of the tools. Microsoft has built BI capabilities into Excel 2007. Information workers can use a familiar tool to visualize and work with data.
SharePoint 2007 is the bridge between the DBA and the information worker. The DBA creates a connection file that is published to SharePoint. Permissions to the data are specified in SharePoint. End users go into Excel and select the Data tab, which shows all of the connections the user has rights to. The user selects the desired connection and the data appears in Excel. The connection file provides an abstraction layer insulating the BI enabled Excel files from changes to the cube, even from an operation such as renaming the cube. When the DBA makes such a change, corresponding changes are made to the connection file and it is republished. End users are unaffected.
Excel 2007 has many new features for visualizing and working with data cubes. Financial analysts see live data inside Excel and perform root cause analysis. Once the analysis is complete, instead of emailing the spreadsheet, it is published to SharePoint to make it available to others.
SQL Server 2005 Service Pack 2’s release is coordinated with the release of Office 2007 because it offers an add-in to Excel to expose data mining capabilities. This allows business analysts the ability to use Excel with Analysis Services for data mining, which should make them happy. Service Pack 2 also includes what some people call BI performance enhancements and others call bug fixes.
November 16
by Dave Bernard
A Sunny Day?!
The conference is humming along like a well-oiled machine. The smallest details get plenty of attention. It's clear that the SQL PASS folks are highly
organized and well-led. Impressive.
Keynote
by Dave Bernard and John Paul Cook
Kevin Kline echoed those thoughts in opening the keynote session by observing that "the ratio of praise to complaints is higher than it's ever been." From
my vantage point, there's very little to complain about. One small session schedule nit: there is confusion about consecutive sets of sessions which, at first
glance, seem to be in separate time slots, but are actually overlaps. I think I'll have it figured out by this afternoon.
Kevin then asked how many people had taken advantage of the opportunity to meet with the developers of SQL Server 2005 and get
their questions answered. Only about 10-15% of attendees had done this. Kevin encouraged everybody to participate.
Kevin concluded his brief remarks in introducing Steve Ballmer, Microsoft CEO, as the keynote speaker, a man with a "special brand of energy and enthusiasm".
Steve's presence was highly anticipated; he's attained
true celebrity status (partly infamous) among Microsoft supporters and haters alike. The "Developers, Developers, Developers" video comes quickly to mind
when Steve Ballmer's name is mentioned, but a much trimmer and calmer person seemed to have taken his place. Throughout his talk, he spoke loudly, though
not hurridly, and walked slowly back and forth across the stage.
First, Steve said he couldn’t be more appreciative for all of the time and energy spent with SQL Server 2005. He said he couldn’t say enough about this
group being on the front line for adopting Visual Studio over a year ago. SQL Server is experiencing a 35% growth year after year. There have been over
500 million installations of the .NET Framework. "Thanks to everybody here."
Microsoft feels that it is well-positioned to drive a
consistency
of purpose across all four areas. Interestingly, Microsoft considers the consumer market (Entertainment & Devices) as the driver of the leading edge; to
stay relevant, Microsoft must pay close attention to that market segment.
He then set forth the Four Promises:
And just what is that bright yellow thing in the sky this morning? Today, I discovered that the pen is mightier than the sword
weather; a day after I lamented the wet gray environment (and a day after Seattle set a November record for rainfall), the sky couldn't be bluer nor clouds
scarcer. Weather Karma!
Next, Steve gave us a 40,000-foot view of things from Microsoft's viewpoint, delivered as a mix of marketing and cheerleading. He introduced his presentation as
"Infrastructure for a People-Ready Business", then offered that Microsoft was a company with four businesses: Desktop, Server, Entertainment & Devices and
Online. "That's too many!", say some; "You need to pick one on which to focus." Steve rejoins "We are a multi-core company." Why? Because technology
is shared across these boundaries, which have become increasingly blurred over time. "We grew up as a desktop company with Windows and Office. We spent a
lot of time hearing that we weren’t ready for the enterprise. We don’t hear that anymore. We have new entertainment ventures. We have new business and
partnership models. We want to use the same technologies everywhere. We don’t see rigid walls."
"It's what we call the people ready business - the business that empowers people through IT."
Amplifying the impact of people: make people more productive. Has the PC made people more productive in their work? "Back at Proctor and Gamble in 1977, "cut-and-paste" meant, literally, cut and paste. There was special paper, paste, and X-acto knives. Can you make the ROI case on that?"
IM, voice, and video communication are another big step forward. "Everybody is trying to get all of that productivity gain while they are on the road." To get the full Office and browsing experience, these things will get more powerful. Connect the people to the data for the business process. "To help me be more productive, you need to help me find structure in unstructured data."
Lastly, exception handling is important. "An old adage is the happiest customer is an unhappy customer you made happy." We need collaboration to make this customer happy. "If you look at the partnership with SAP for a product called Duet, it is all about helping with unstructured data."
A continuing theme was also echoed here: we have information overload that shows no signs of abating, and worker productivity depends on managing that glut and turning it into valuable, workable information. "Connecting people to business" really means helping knowledge workers mine their data effectively to bridge the "structured business world" and the "unstructured world of personal productivity." Steve admitted that he doesn't know anything about the company's internal CRM system (Seibel), he still wants to be able to easily find out "Who's the account manager for Ford?" Finding useful information in a timely fashion is still very, very difficult.
Steve gave way to Francois Ajenstat, SQL Server Product Team Product Manager. Francois gave a brief, eye candy-heavy
look at Vista, including the ability of
task-oriented help topics to not only show you how to do something step-by-step, but also to just do it for you in a single click.
The remainder of the demo was far more interesting, mixing advanced UI elements and a service architecture (using Sharepoint Server 2007 and Office 2007) to deliver "rich fidelity between server and client." Excel Services and SQL Server 2005 SP2 were heavily leveraged in the demo. I came away with several thoughts:
- This is all really cool and powerful, but it's far from clear what level of effort is needed to build something like this.
- Even though search is "integrated throughout Vista" and Reporting Services is "integrated with Sharepoint, it's still the same old keywords and filters approach. When are we going to get true natural language interfaces? Microsoft had a powerful, ahead-of-its-time product (English Query) with lots of promise (and apparently few customers) that lives only on SQL Server 2000 installation CD's. Man-machine interfaces are certainly headed in this direction; voice recognition is hot on the small-footprint platforms these days. But it is a thin solution without a strong natural language understanding component.
- I like that Microsoft is tackling tough business problems like semi-automated analysis of "key influencers" and "forecasting."
- I really liked the tight, smart integration of advance image and text-image techniques (SmartArt) within Powerpoint 2007. Wow!
Steve then returned ("I hadn’t seen that demo before. It was nice." [laughter from the audience]) and continued with a discussion of Managing (Reducing) Complexity and Achieving Agility, followed by a discussion of Information Protection and Security. "We have System Center. Virtualization is a big area of investment. With Virtual Server 2005 and the System Center Virtual Machine Manager, we will have a broad set of virtualization products at a good price."
"We have a lot of new technology in Vista to improve security such as BitLocker in high end Vista and RMS. I do like that we can now forward emails within Microsoft that can’t be forwarded or printed. We can set document control and protection. We will have a new ISA version next year. Forefront combines client AV and anti-malware."
"We win together."
Then something unexpected happened. Paul Flessner joined Steve Ballmer on stage and they opened the floor to questions. The first question concerned
the recent Microsoft/Novell/Linux agreement: "Are you selling out?"
The audience roared at this, and Steve clapped and joined the chorus. His response was impressive and business-like, with a dose of passion.
"At the end of the day, how many of have Windows and Linux in the datacenter? Oops, not what I want to see, but expected. How many of you have questions about interoperability? People now want Linux that runs on Intel architecture. What is the most popular form of Unix on Intel? It is Linux."
He continued: "At the end of the day, Linux will be there for the long foreseeable future. Vendors always wonder if interoperability will help gain customers or lose customers, and you won’t know for a long time. Because Linux comes from the community with the GPL, how can we do interoperability to protect patents and intellectual property (IP)? Our agreement with Novell is we would do the tech work. Novell has compensated Microsoft for their IP. Novell said if you are serious, you need to help us with SUSE Linux."
"We will try to grow Windows at the expense of Linux. But we want SUSE Linux to be the one adopted because the customer of SUSE Linux has paid Microsoft for our IP. We are willing to do a deal with Red Hat, and others. Customers don’t want to have IP problems. They asked for Microsoft to help: they don’t want to license with Microsoft individually."
Are we selling out? The old Ballmer surfaced: "Windows, Windows, Windows, Baby!"
Another audience member, from Germany, asked Steve his thoughts on the ongoing complaint by the European Commission. Is Microsoft "open" enough?
"Are we complying with EU directives? We think we are, we have another normal deadline we agreed to coming up. Compliance is all about documentation of protocols. We’ve written about 8200 pages."
Steve continued: "What does it mean to be open? Short of publishing the source code, which is inconsistent with making a profit, we are already open" via our API architecture, evidenced by the broad array of device drivers and interoperability software that's available for Windows. Publishing our source code "would not just be selling out, but would be sold out!"
Another interesting question, continuing the information search theme: "Do you think you are where you need to be at with turning data into information?" "No!", Steve emphatically responded; "How do I find out 'How many people work for us and what do they do?'" It requires a detailed description of business concepts, a taxonomy. "We're nowhere near where we aspire to be."
Last question: "When are you going to do a SQL Server dance?" At this, he jumped up and shouted "There's no music, but: DBA's, Baby!" and strode off the stage amidst laughter and applause.
We've just been entertained.
Lunch and the Microsoft Interaction Lounge
The food's been great so far, and today was no exception. I went down a bit early, ate and chatted with a couple of other attendees, then hopped on over to
the Microsoft Interaction Lounge. There was a bit of buzz about Ballmer's keynote "show", all positive in my experience.
I spent some time chatting with some of the Microsoft engineers in the Lounge. This is a really great idea, to have, in the flesh, 200 hundred (well, not all at once) real Microsoft developers and experts at your disposal for the duration of the conference. I had several interesting conversations about BI and advanced search technologies, and made several new strong acquaintances. I doubt this can be duplicated in any other city, so this is a really unique opportunity. These folks are, without exception, great listeners and are enthusiastic about what they do and about helping the customer.
I can't overstate the value that this conference delivers in the form of networking and relationship building. Often, making a single new valuable contact is worth several times the price of admission. To me, the sessions generally deliver a lot of value, but the networking is far and away more valuable. I think too many of us get caught up in our insular technical worlds, spending little, if any, time on expanding one's personal network.
Every industry, and some companies, have their own lingo. I do my best to keep up with the lingo of technology through reading and interacting with other technology folks. One term unfamiliar term, KPI, crops up here quite often. Now I know.
Successful Use of Scrum & Agile Development in Online Services Delivery
David Argent, David Fu, John Peng, Amit Sarkar, all of Microsoft
These four fellows gave an ensemble high level overview of a particular Agile methodology called Scrum. The four have between 10 and 15 years of experience
at Microsoft, in both development and operations, and now work largely in the Windows Live part of the company. Dave Argent, a self-described "misplaced
scientist from New Jersey", opened the discussion.Something certainly needs changing: the most conservative studies show that the software project failure rate is at least 65%, and some say it is as high as 80%. This kind of failure rate would be unacceptable in just about every other industry (think medical and nuclear power generation). If the 20% to 35% who claim successful software projects aren't lying, what are they doing right?
The most successful software projects are those that emphasize fitting the product to customer needs while maintaining high quality, along with minimizing maintenance, cost and time. These five dimensions are at the heart of Agile Development; Scrum is a set of processes and artifacts that outline one way to implement Agile processes and techniques. Microsoft's adoption of Agile guidelines started in a few disparate groups, and is spreading slowly throughout the development and testing arms of the company.
So how do you deliver the right "fit" to the customer? Simple: the team is constantly in the mode of delivering the 10% of the system that the customer needs the most. So who decides what makes up that 10%? The customer, of course; the customer is on the team.
Microsoft's adaptation is a bit more rigid, with longer "sprints", than how Agile techniques have been implemented in smaller organizations. However, it's important to treat it as a starting point, not as a Bible.
The primary features of Scrum are:
- Loads of commuication; the "Daily Scrum" is a quick get together run by the "Scrum Master".
- Smaller, self-managed teams, including the Product Owner (customer).
- Delivery incrementally and often via "sprints".
- Business and development are in close synchronization.
- Refactor often; change the code to improve quality while maintaining feature set.
- Frequent post-mortems.
Agile approaches enable the shifting of priorities to quickly meet changing requirements, leading to much higher "product velocity". But is this the best approach for all projects? Not necessarily; large-scale projects, geographically-dispersed teams and command-and-control cultures can dull the sharp edges of Agile implementations. For geographically distributed teams, a "Scrum of Scrums" hierarchy can be established to make it run.
For most projects, Agile is clearly a substantial improvement over more traditional software development lifecycle approaches, such as the Waterfall method; however, because of the largely self-managing nature of the processes, successful Agile implementations rely as much on putting together teams of top notch people, people who are smart, can take initiative, are excellent communicators and who have good interpersonal skills. An outside might maintain that almost any methodology would be successful with a team like that. I would have to agree; however, I also believe that Agile approaches would amplify the execution capacity of a really good team well above what they would be capable of otherwise.
A mature Agile process is self-healing and self-improving; a major benefit is that projects tend to be "right-sized", that is, just the right amount of resources are expended to reach the desired goals. Processes are essentially overhead; excess capacity costs money, while insufficient capacity costs customers.
Agile methodologies resonate with many developers because it describes the natural way of things in the software development business: ultimately, all systems that eventually make it to production are built collaboratively, a collaboration that can be very painful. Collaboration is necessary because both sides, developers and owners, have a very limited ability to predict milestones in the early going. Both parties are learning what they don't know; the owner doesn't see all that is possible, and the developer doesn't know the business. All Agile does is create a prioritized structure for bridging the disconnect between owners and developers over time, while ensuring that the a high quality product will be delivered on time and on budget.
Read more about Scrum and Agile methodology; it could change your success rate. Also, search for Agile Software Development at a similar presentation I covered last month for additional quotes and resources.
Test-Driven Database Development: Agile Methods for SQL Developers
Ted Malone, Principal Software Architect, Configuresoft, Inc.
Ted is a leader on a software development team that has gone a long way toward implementing Agile methodologies in their everyday work. Much of what Ted
covered echoes the previous session I attended (see above), so I will try not to duplicate most of what I've already shared.
Ted characterized Agile approaches as "always working on the current iteration of the prototype" and "process guidance."
This session focused more on applying Agile to the testing aspects of softare development. He emphasized that test-driven development (TDD) is not, itself, an Agile methodology, it is a tool used in Agile implementations. Ted quickly gave an overview of Agile and it's most popular incarnations (Extreme Programming, Scrum and Adaptive Software Development), then introduced the history and concepts of TDD.
TDD turns software development on it's head: tests are written and run before code is written. It's roots are in Extreme Programming and it can be defined as a simple series of tasks:
- Define a test case
- Write a test
- Write code to satisfy the test and only the test
- Repeat
For Ted, one of the biggest benefits of TDD in an Agile implementation is the enabling of a "comment-based" documentation framework; because test are written and verified ahead of code, comments in the testing process become detailed, de facto documentation. Another big benefit is that developers are forced to completely understand the task at hand before writing code, and how it fits into the larger system. Just the fact that all this testing is done before code is written ensures lower defect rates and far lower maintenance and support costs downstream.
Database developers need to implement TDD as an interface methodology, rather than against a block of code. Stored procedures provide an excellent example and roadmap for doing this; parameters are the interface to the stored procedure. This leads to a best practice of making your stored procedures simpler and more granular so that smaller and more focused tests can be constructed. You'll need to take those 10,000-line stored procedures and refactor them, which, in my mind, should be done anyway.
There's not doubt that TDD requires much more time invested up front, perhaps twice as much. This seems to be going backward, but consider that the later in the software development life cycle that bugs are fixed, the more expensive those hours are, sometimes orders of magnitude higher. So, a few extra hours invested upfront to eliminate bugs results in not having to spend 10's and 100's of hours later on the same problem, especially once it's in customer's hands. This is really a risk management scheme.
Ted described some of the software tools that can help implement TDD (NUnit and TSQLUnit. Microsoft Visual Studio Team System for Database Professionals has TDD support pieces, too.
It's pretty easy to find book and Internet resources on TDD and Agile; it deserves careful consideration by all developers.
by John Paul Cook
Cursors vs. Set-Based Queries
Itzik Ben-Gan
Cursors vs. Sets
Itzik gave a very interesting and in-depth presentation on using cursors vs. sets. Joe Celko was a front row attendee. Itzik is a good presenter and
is courteous to the audience by taking the time to properly configure SQL Server Management Studio so that it is easily readable when projected. Visual Studio, SSMS, Notepad, and a command prompt window can all be configured to use larger fonts suitable for reading when projected in a large room.
Itzik said he was opening a very old debate about whether to use cursors vs. sets. He explained that he wanted to change the discussion to when to use cursors. He said sometimes you can’t beat using a cursor.
Cursors use procedural, iterative logic. This is good for when each row needs to be processed individually or when you can rely on order. Even the fastest cursor has overhead. The majority of problems are best solved with set based solutions. They use less code and require less maintenance.
Itzik showed the following query as an example of where a new operator can provide a set based solution where formerly a cursor might be used. Find out more about CROSS APPLY here.
SELECT F.* FROM dbo.T1 CROSS APPLY dbo.fn1(T1.col1, T1.col2) AS F;Itzik pointed out that when ORDER BY is added to a query, it no longer returns a set, a cursor is returned. What is desirable is to replace ORDER BY with syntax that is still set oriented. Sometimes the OVER operator can be used. See this for more information.
Assuming there is an index on col1, it is possible to get ordered results without performing a sort.
with c as ( select col1, row_number() over(order by col1) as rn from t1 ) select from cAt the conclusion of the presentation, Itzik warned the audience that READ UNCOMMITTED can get a row multiple times or it can miss rows, all because of index page splits occurring is updates are being performed during the select.
Itzik’s blog can be found here.
The source code for this session can be found at http://www.sql.co.il/books/insidetsql2005/resources.htm.
Visual Studio Team Edition for Database Professionals is a soon to be released product to manage database change, which I will refer to as Db Pro
for the sake of simplicity. To maximize the effectiveness of Db Pro, you will need to use it in conjunction with a version control system such as
Team Foundation Server, although it has many very useful features that can be used without a version control system.
In the real world, the production database is the de facto version of the truth. Whatever the production database schema is, it is the truth. Since production databases should not be available to developers, organizations resort to creating copies of production databases to other servers in order for developers to have access to a version of the truth. Sometimes this can’t be done because of data privacy and security concerns. Outside of the production database or a copy thereof, there really is no design time version of the truth. One of the major objectives of Db Pro is to change this by moving the truth into a version controlled project.
Gert explained that one of the biggest problems in developing Db Pro is the requirement to be able to identify all dependencies in a database. If you have an awareness of SQL Server metadata tables, you’re probably familiar with sysdepends (SQL 2000) and sys.sql_dependencies (SQL 2005) which store metadata describing dependencies between database objects. It wasn’t possible to use either of these. Completely new code to create a dependency map had to be created from scratch. The problem is that SQL Server’s deferred name resolution can cause a dependency to be missed and not be recorded as metadata. Additionally, SQL Server tracks dependencies down to only the table level. Db Pro requires a dependency map down to the column level.
What follows are Gert’s comments edited for clarity.
We can build and deploy with Db Pro. Build says there is a project that represents a certain state. We want the database to get to that state. When you say build, an incremental script is created to get your database to that particular state. A schema comparison allows us to compare two databases or projects. You can find the changes necessary to change from one state to another.
Gert’s team beefed up the T-SQL editor in Visual Studio. But they didn’t have time to add a graphical showplan, but that will be added later. Db Pro adds the roles of DBA and developer to Visual Studio Team Edition in order to help manage a project.
Does Db Pro have Intellisense? It is currently being working on right now. Gert wants to add this to the product based on schema binding. Intellisesnse will be shipped later as an add-on.
The whole goal is to move the center of truth from the database to a project that can be version controlled.
Here’s an example. A customer deployed a database to a office in New York. Performance was bad, so they asked for Microsoft’s help. They sent the schema. They sent the query. The query they sent contained a column that doesn’t exist. The ISV didn’t know what the customer was running. They really didn’t know the query they thought was the query couldn’t even run in the actual database. This is true in a lot of cases. People make the database the center of truth. We are giving you a way to manage outside of the database.
In Db Pro, all objects are in their own files and tracked individually.
Is there any integration with Visio? We don’t give you pretty pictures. We don’t offer a modeling solution today. Third party vendors are developing visual modeling tools.
When using Db Pro, you can start green field by creating a new project from a template. But most of the time you start with something, you have an existing database that you import into a project. You go through the entire test and development cycle and at the end, when you are happy with the results, you finally deploy.
In application development, we as developers sometimes find ourselves wondering why we got a null value exception. With unit testing, we can build verification tests for both negative and positive conditions. Most tests are the minimum number of steps to show that code works. These kinds of tests usually include certain assumptions. NULLs are often overlooked in positive testing. Maybe NULLs will cause failures, but the simple positive test doesn’t have any NULLs because it was written to demonstrate success. If your test condition contains no NULLs, you are assuming NULLs won’t ever be a problem. We need tests that test against these assumptions, true negative tests that create failure conditions. We need to test our code against things that cause failures.
Here’s another case. The customer was testing a financial database. Performance was good in the tests, but performance in production didn’t come close. The production database was slower even with its better hardware. Most of the real data was credit cards. Test data was only partly credit card data. So we see that even if you have a sufficient quantity of test data, we also need a realistic distribution of the test data. While it is true a backup of production would by definition contain a realistic distribution of data, often it can’t be used because of privacy and security concerns. That’s why we created data generators to create more meaningful data. Eventually you will be able to take production statistics and generate test data. By default, data is bound to a generator based on the data type. We do not support circular references.
How do we lay down those data values? We offer mathematical distributions. Both the generator and distribution concepts are extension points. If you know how to generate credit card numbers that will actually validate, you can code that into your own custom data generator. Eventually we will look at your statistics and match it.
We try to understand your constraints when we generate data. We cannot always understand your constraints, but we do tell you when we don’t.
A database generator file is a dgen file, an XML file. It maps a column to a generator and a distribution. You can reuse a dgen file over and over again. Business people understand domain values in your model and they can help you generate realistic data. When we execute, we validate your security requirements. We make sure the target schema matches your generator. We spin up parallel insert streams. We have a customizable regular expression generator for generating data conforming to certain patterns. Data bound generator is a dictionary. We randomly select from the dictionary, which provides reuse of data.
Generators can be deterministic. The same results can be generated every time. But what if you use guids? How can you create repeatable test cases using guids. This can be done even with guids, if you generate sequential guids.
In my team, I don’t allow anything to be checked in without a unit test that exercises 70% of the code and actually validates the results. How do you do this? Use Team Test, which works for C# and VB. DBAs don’t want to learn C# or VB? Get a developer to write the extensibility features for them. Are we going to stay code centric or T-SQL centric? We took the middle.
Db Pro runs test using the same security context as the user. Execution connection is the user’s security context. We don’t want test results polluted by running with elevated privileges.
You can customize tests using C# or VB. You can add your own validation logic, even parameterized validation logic. Code coverage will be provided later.
Changing topics now to refractoring. Refactoring allows wholesale changes throughout a schema within a semantic context of a schema. We expand wildcards. There are two terms you need to understand, refactoring type and refactoring target. Examples of types of refactoring are renaming a database object (e.g., changing the name of a column) and changing a data type (e.g., changing a column from integer to decimal). The only refactoring type we support now is renaming. A refactoring target is what is changed. It could be a dgen file, a script file, or anything with references to the schema (such as an assembly). The contents of an assembly, the internal code in an assembly, are not supported as refactoring targets.
Do you have a habit of writing ambiguous SQL? Do you include the object owner? If you aren’t including the owner, it is ambiguous. Is the proper interpretation of K dbo.K or user.K? You need to specify the owner in your code as a best practice.
Managing and Deploying your SQL Server Schemas with Visual Studio Team Edition for Database Professionals
Gert Drapers
This session followed Gert’s other session. As before, what follows are Gert’s comments edited for clarity.
What we tried to do in Db Pro is decouple the schema from the data. The first step in this approach is to establish a project. Within a project, we chose to make a .sql file a first class file in Visual Studio. We also considered using XML files to represent database objects within a project. Customers asked if we were nuts. To them, there is only one canonical form - the .sql file. Plus, all other third party tools use .sql. We listened to our customers. A project is just a container for directories and .sql files.
Everything gravitates around this project system. Once you have made changes, you have lots of fragments, perhaps 1000s of files. When you deploy, things get packaged into a single file. If you are creating an incremental update, by default your packaged update file will have BEGIN TRANS and COMMIT TRANS statements and use READ COMMITTED isolation. The isolation level is switched to SERIALIZABLE only when you are loading data. The database project represents the "truth" with regards to schema versioning. Optionally, a database project can be placed under source control. This is a feature you should want to use.
How do we envision this will work in a team environment? There should be a trust boundary. Not everyone should have access to the production environment. Hopefully the DBA has a staging area. There should be some form of source control.
First you want to build your project. If you have a production database, the easiest thing to do is to import your schema which means you need permissions to view service state, etc. A project is established and everything is checked into source control. This becomes the baseline. Every developer works in his own sandbox environment. Developers work with each other through source control, not the database. When merge conflicts occur, they must be resolved through the source control system. You can make this be driven through work items. We have published templates to Agile and CMMI to include the Db Pro role. Work item and work stream integration are the only parts that integrate into TFS. Hopefully you have some form of a build cycle. At Microsoft, we have perpetual build servers. Microsoft has a check-in gateway. We can enforce as a matter of policy that when a check-in to a developer branch is made, a unit test performed. All teams should use nightly builds.
Your sandbox server must be SQL Server 2005 and must be on the local machine. You can use SQL Server 2005 Express for this purpose.
SQL Server does not have a concept of a version in the schema. It wouldn’t be authoritative, so why would you want it? But customers say it would be good for documentation. We are working on something.
Labels. You need to have a label to roll back to a certain point in time. You do this by having a label associated with the state of every file in your version control system at the time a deployment build is made.
You will run unit tests against a staging database. If all tests are passed, only then do you move to production. Application and database schema can now be managed together. You want to work in isolation from the production system. Deploy only when changes are verified by an empirical process.
Within Db Pro, objects are parsed and interpreted at these times:
- Project load
- Object change
- Source control sync
The build script has checks. We check to see if there are rows in our target database’s tables. We won’t execute commands that will cause data loss.
You aren’t restricted to using the GUI tools. There is full command line support for devenv.exe and msbuild. Everything can be done from the command line.
We want to represent the database as something that can be deployed and we support incremental updates. That is the main thing. We want to give the database developer a place in the development lifecycle. This is just a first step.
November 17
Keynote
According to Dave, "We are not building products for engineers. We are building products for real people. I like to look at patterns in history.
Nascent technology may not work well. You need to be an expert. If you owned a computer from 20-30 years ago, you need to be an expert,
but things have evolved where that is no
longer true. In terms of reliability, again, we have evolved. Computers are more complex, but they are more reliable. As you evolve, it progresses to more
features. As it evolves into something that is refined, we move more toward choice and preference."
He continued: "For this audience, what is the language of control.
How many people here now know what TV horizontal and vertical control knobs are? How many have actually used those knobs? How many miss them?"
To illustrate the "least asonishment" principle, Dave went through a series stark example of violations of the "least astonishment" principle, along the lines of
The Daily WTF Pop-Up Potpourri examples. I've included some of Dave's
own examples.
This first example is a conversation between Dave and his wife:
The next two are a pair; the first is what the user sees, followed by the translation:
Error Message Haiku:
This one's self-explanatory:
The right direction, but...
To this, you, the user, has to ask:
by Dave Bernard and John Paul Cook
Dave Campbell
Dave is a speaker who is very engaging and has a sly sense of humor. The theme of his talk was "The Winning Recipe", moving toward a refined software
development process platform. This is driven by the principle of "least astonishment", from the user's point of view. In other words, how can software behave
in such a way that the user almost never is presented with negative surprises. Dave's presentation is frequently given (and directed at) software
development teams within Microsoft.
Dave repeatedly emphasized "closing the loop" between the creators of software and the users of software, just like a true feedback loops works. Part of closing the loop is to embed smarter responses to unexpected exceptions (the need for which is evidenced above) and to improve code instrumentation so that hard exceptions can be debugged via useful situational "data dumps", which currently are largely a too-large mass of incoherent bytes. Lots of progress is being made in this area; sophisticated usability lab tools, focus groups and online surveys are being employed extensively. "One of the groups that reports to me is the User Experience team. They make the product more refined and look at how people do their jobs. I have watched DBAs work all day long. One of the rules is to not open your mouth as they struggle with one thing or another. It can be very frustrating."
The Microsoft Connect site (described below in John Paul Cook's review) has been busy:
- Closed 97% of the 4,000 suggestions and issues posted by over 2,000 customers.
- Answered 13,000 questions on the SQL Server forums.
- Posted 80,000 replies on the SQL Server newsgroups.
- Established 185 MVPs worldwide.
- Spent 35,000 hours working with customers and the community.
Continuing: "We do a lot of work inside our building recognizing program managers and developers who spend a lot of time answering problems. We want to log 35,000 hours of support. We bring customers in once or twice a week. We weren’t doing this 7 or 8 years ago."
"How we use the product has changed how we build the program. A few years ago, we ran SQL Server out to Excel hard copy. People would print out Excel spreadsheets and hand them out at meetings. Questions would be raised and someone would say 'I’ll get back to you.' Here is where it gets funny. If you look at X-Men or something like that, you have all the various specialized pieces represented in the room (query engine, T-SQL language, SSIS, etc.), each like one of those X-Men superheroes. If we were running a management report, and the 'report wheel' was turning slowly, each of the 'X-Men' would blame each other for the performance."
"Now, we can drill in. We are a fully wireless campus now. Several times a week we’ll be looking at a problem and we can enlist people on the other side of the world in real time. Latency of a few days is eliminated."
One interesting observation: the worst 1% of defects cause 50% of the failures, and the worst 20% of defects cause 80% of the failures. Attacking these
defects defines the goals of each service pack. This is defined as the "Watson Curve", and the idea is to minimize the area under the curve.
"One of the problems in the database world, particularly in 64 bit, a dump is too large to send", said Dave. "We created a 'mini dump', but found it does not always provide enough information to help us. We changed to collecting a mini dump and a full dump in our testing. We’d then provide the developer with the minidump. Only if it wasn’t enough to fix the problem would we provide the full dump and require a bug report to alter the mini dump so that, in the future, it provided enough detail. This helped close the loop."
"MVPs and user group leaders are multipliers. When we work with them, we help many times that number."
Dave then gave way to Mark Souza of the Customer Advisory Team, a funny and engaging fellow, wearing a Santa Claus hat. He's really more like a customer advocate, with he and his team almost constantly on site with customers, a direct liaison between customers and development teams.
"I know you’re saying, hey dude, what’s up with the hat?", he started. "Well, you’ve been very good this year! You’ve been migrating those Oracle databases. The best thing I could do is put on a hat and give presents to everybody. I could give t-shirts. Everybody has that. How about an X-box 360 for everybody? I’m going to provide something from the heart. We want to be the number one database vendor in the world for customer support. We want to close the loop. I have the best job in the world. We learn a lot every time we engage with you."
Mark's also a great advocate for the SQL Server product line. "What have we learned?", he asks; that "we can run any in the world, and we are today." He continued: "We can run any system in the world on SQL Server 2005. ISV adoption is faster than any prior release. SAP certified SQL Server 2005 in only six weeks, it took Oracle 18 months to get certfied!" He gave some examples of very large implementations in financial trading, banking, telecomm and data warehousing, some as large as 20TB running on eight 64-way boxes. "We blogged that we can do a 1TB load in 50 minutes", he said. One in-progress implementation they're involved with is a 100TB system! Mark's team has created a large number of powerful diagnostic tools for use in the field that are now being made available to the SQL Server community at large. He shared a couple of counterintuitive findings: that 64-bit implementations do not always out-perform 32-bit implementations, and that a SQL Server 2005 upgrade does not always out-perform the original implementation.
Mark said that they've collected a lot of field wisdom over time. "Now we're giving it back to our customers. Customers say they what more detailed information. I’m going to give you 400-level SQL Server best practices and technical information. Everything has been tested and validated by Microsoft. We concentrate on 3-10 pages because we know you’re not going to read a 70-page whitepaper. It is 400-level, it shows you everything we see. In this toolbox are the same scripts we use out in the field. We don’t need to hoard them. They are all available to you. We give you top 10 best practices per category."
That was it from Mark, and Dave returned. "We need to talk to Mark about managing expectations. We can’t talk about an X-box and deliver a web site." "We completely reengineered how we design and build SQL Server. We used to have a small team and we didn’t have to think about adding customer value a lot. Now that we are beyond that, that process just didn’t work any more. When we got to [SQL Server] 2005, the team had grown larger. What project managers didn’t realize is that when they were filling up their own bucket, they were filling up buckets for other teams. We had so many things going, we needed a different process. With 'Katmai', we have a different strategy.
"We thought about themes. How are we different from our competitors? What do we want to do? For this release, we bring all of the teams from all disciplines together and they all own its quality. The dirty little secret is that developers shoot for 'code complete'. Now, only when something is complete and shown to customers can it be included in mainline code. Mainline code no longer contains lots of half done features. The product is always ready to ship.
"The old process was to start a lot of things. Things are at different degrees of completion. Now we don’t start everything at the same time. We are trying to improve the velocity from end to end. When we decide to go RTM, we just don’t accept any additional feature integrations.
"You said there will be more frequent release cycles. What about customers? Universally people think 5 years is too long, 2 years is too short. I think about 3 years is right."
If this isn't Agile Development, then I don't know what is!
by John Paul Cook
In today’s keynote, Dave Campbell told PASS attendees that Microsoft really does want to interact with customers and act upon their feedback. One of
the ways he mentioned was Microsoft Connect, usually referred to as just Connect. Go to https://connect.microsoft.com/registration and create an account
with Connect using a Windows Live ID or Passport account. After signing in to Connect, go to https://connect.microsoft.com/SQLServer and add SQL Server
to the list of products you are interested in providing feedback on.
You can search existing bug reports or create new ones without the trouble and cost of opening a support incident with PSS (Product Support Services). Of course, if you have an urgent need, PSS is still your best option.
Does Microsoft listen or does clicking the Submit button send your bug to a black hole? I know from personal experience as a long time user of Connect, Microsoft definitely does listen and act upon entries in Connect. I’ve been provided with workarounds, requests for more information, as well as when I might expect a fix (e.g., hotfix, next service pack, next product release).
I interviewed two SQL Server program managers, Oscar Naim and Paul Mestemaker, to find out exactly what happens after the Submit button is pressed. Understand that Connect is for all kinds of Microsoft products and Oscar and Paul were describing only how SQL Server bugs and suggestions are handled. Before pressing the Submit button, you specify what specific product area the issue applies to. Once you hit submit, the item is entered into the SQL Server bug tracking database as an untriaged bug and email is sent to the team responsible for the specific product area. I wanted to know what happens if the wrong product area is entered. Paul explained that Microsoft will correct this if necessary.
Bugs go into the SQL Server bug tracking database as untriaged bugs. Someone from the SQL Server team reviews the bug and assigns it to the proper team such as relational engine, Analysis Services, SSIS, or one of the other major product functional groups. Once assigned to one of these higher level groups, someone within that group is made aware there is an untriaged bug. At this point, the bug is assigned to a particular subarea such as the relational engine manageability team or the management platform team. Next, someone such as the lead for the team assigns the bug to a particular program manager, who now owns the bug. The bug owner must determine if it is an actual issue, if it is reproducible, or if it is a duplicate. Once this process is complete, the customer is contacted. This initial customer contact should occur within seven days to meet SQL Server internal customer satisfaction metrics. This initial contact may include a request for more information, that it is an already known issue, or that it is being investigated. The second time the customer is contacted is when a decision is made on when a bug will be fixed. The third time the customer is contacted is when the issue is closed by categorizing to as will fix, won’t fix, or it is a duplicate. It is possible that unforeseen future circumstances could remove a bug fix from a future service pack or product release that was earlier expected to fix it. But what if the bug is a duplicate? It is still a problem for you. Microsoft marks an item as a duplicate only if there is another Connect item filed for this same issue. This is done to make sure the bug is still being tracked. Why this is important to you is that all people experiencing the same problem are looking at the same problem description and can vote on it. Voting helps Microsoft prioritize the importance of an issue.
Now that you understand the process, here is how you can help improve its effectiveness. Before entering a bug, you really need to search to see if the issue already exists in the bug database. If it does exist, save time, don’t enter it again, but do vote on it. Your vote helps Microsoft allocate resources to improving the product. Also, when you do enter a new item into Connect, you need to be a detailed and specific as possible. Please describe your environment and provide a step by step description of how to reproduce the bug. You can download Windows Media Encoder and capture all of your steps as video. Other helpful attachments are screenshots, T-SQL files, and log files. Of course, not all items are needed for all bug scenarios. One thing I recommend is to capture error dialogs as text instead of as a screenshot. Most SQL Server dialogs have a button to capture the contents to the clipboard as text. Also, many Windows dialogs can be captured as text by using Shift+Ctrl+C instead of just Ctrl+C.
After an issue is opened, take the full URL of your bug and post it in newsgroups and other community forums so other people can know about it and hopefully vote on it. In addition, if you see other items that are open and not important to you, please vote a lower number on it so that Microsoft knows what is important to a breadth of customers.
Changing subjects to bug reporting in general, ff you are asked to provide hardware information as I was on a Virtual PC 2007 display bug, download and run cpu-z. There is nothing to install, just run it. Click the About tab, then click the Register Dump button to capture all of the hardware information out to a file.
SQL Server Automation & SQL SMO
Peter Ward
Peter started the session by telling everybody to stand up, stretch their arms, and move around. He is an engaging speaker.
Server Management Objects provides a programmatic interface for administering SQL Server 7 and above. SMO can be programmed in either .NET or a scripting language such as VBScript. Peter used the following code samples to compare and contrast VBScript that uses DMO to SMO code written in VBScript.
By default, SMO connects using Windows authentication, which explains why there aren't any connection parameters specified in the second code sample. Another difference between DMO and SMO is that SMO has delayed initiation where objects aren't loaded until they are specifically referenced.
Dim DMO
Set DMO = CreateObject("SQLDMO.SQLserver")
DMO.LoginSecure = True
DMO.Connect "(local)"
MsgBox DMO.VersionString
Dim SMO
Set SMO = CreateObject("Microsoft.SQLserver.Management.SMO.Server")
MsgBox SMO.Information.VersionString
Peter also demonstrated how to back up a database using a relatively few lines of SMO code in a simple VB.NET project.While Peter's examples were informative and his presentation was effective, the code samples really weren't at the 300 level. If you'd like hands-on practice with SMO, you can do the free virtual lab on SMO. If you'd prefer to read more about SMO and download code samples, you can also read this article or, if you are a subscriber to SQL Server Magazine, read my article.
Getting the Most Out of Project REAL
Len Wyatt
I attended just long enough to hear Len’s opening remarks because of schedule conflicts and because Len’s demo of what you can download was highly visual
and does not lend itself to a written synopsis. What follows is a loose transcript of what Len actually said.
We are going to tie things together for the real world. Project REAL is based on a real customer scenario, Barnes and Noble. This data warehouse supports planners, buyers, managers, stores, and distribution centers.
Project REAL is
- Reference Implementation - meaning that you can see everything Microsoft did
- End-to-End
- At-Scale
- Lots of Users
"We have provided you the actual Barnes and Noble implementation. It’s real, it’s what we did, it’s all of the pieces, but not all of the data. You only get a tiny slice of the data and to protect Barnes & Noble, it’s not actual data. There is a Reference Implementation Kit containing everything you need. You can download it and run it yourself."
"We wanted to make SQL Server a better product and provide a vehicle for developing best practices. It is a reference implementation and showcase."
Barnes and Nobles pulls data from two other systems (sales data is contained in Oracle) using SSIS. Microsoft had 93 days of actual Barnes and Noble daily data, so Microsoft can replay actual data.
There are 15 dimension tables and four subject areas (fact tables). There are 10 billion rows of data that occupied 2 TB (including indexes).
Ralph Kimball likes to talk about the front room and the back room. Most of Microsoft’s emphasis has been on the backend leaving Proclarity and others to take care of the front room.
"By using project REAL, you understand the intended ways of using BI products."
What I wish developers knew about SQL Server
Bill Graziano
Bill is a good presenter and his demos were properly configured to be easily read when projected in a large room.
There are some of his key points:
- Avoid correlated subqueries because they can create multiple passes through a table.
- Do not use functions in where clauses because they can’t use indexes. (You’d have to have an index on the function applied to the column, not on the column itself.)
- Be careful of using NOT IN because negative logic can't use an index.
- Use EXISTS to check for existence.
- If more than 100 rows, use a temporary table instead of a table variable because statistics are kept on temporary tables.
- READONLY cursors are faster than while loops
- Use SqlClient for making database connections.
- DataReaders are almost always faster than DataTable on the client side.
- You should consider using an Application Name in your connection strings if you have lots of different applications. This enables you to filter by application when using SQL Server Profiler or analyzing trace files.
- Use SqlDataReader.GetSqlString() because it handles NULL correctly and is faster.
- A rollback always rolls back all of the way to the beginning of a transaction regardless of the number of intervening COMMIT statements.
- When performing error checking, check the Errors collection to see if multiple errors were found.
Bill showed a code sample which is a great tool for driving home how things actually work. Here’s a lightly edited version of his code:
BEGIN TRY
SELECT 1/0 -- one divided by zero, which always forces an error
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ERROR_NUMBER,
ERROR_SEVERITY() AS ERROR_SEVERITY,
ERROR_STATE() AS ERROR_STATE,
ERROR_PROCEDURE() AS ERROR_PROCEDURE,
ERROR_LINE() AS ERROR_LINE,
ERROR_MESSAGE() AS ERROR_MESSAGE
END CATCH
November 18
Video Interview of Speakers at PASS 2006
Jean-René Roy has interviewed key speakers, MVP and attendees at SQL PASS 2006.
Sean McCown and Adam Machanic talk about the best and the worst
of SQL Server 2005
The question was: "Which features in SQL Server 2005 did change your day to day job and which features are missing or can use some enhancement."
View the video
View the video
 |
| |||||
 |
| |||||
 |
| |||||