Monday, November 30, 2009

CSS SQL Server Engineers - DB Snapshots & Data Loss

Not sure how common this scenario is, but something to be aware of if you are using DB snapshots (SQL 2005 & SQL 2008 feature) and a 3rd-party backup tool that acts on the database files.

 

SQL Server 2005 introduced snapshot databases and modified DBCC to create secondary snapshot streams for online DBCC operations.   The online DBCC creates a secondary stream of the database files that is SPARSE.  CSS has found that if a 3rd party backups and utilities or NT Backup is used against the database files the SPARSE setting may get incorrectly, propagated to the parent stream.   In the case of DBCC this is the original database files(s).

CSS SQL Server Engineers

Monday, November 09, 2009

Beta 4 SQL Server > Home

Test-drive SQL 2008 R2 here, without the downloads.

What manner of lunacy is this?
No, we’re not insane (not criminally insane, anyway). We just decided to allow the most brilliant and creative Microsoft  Administrators and Developers we know to have unfettered access into the highly protected core of the Microsoft® SQL Server® 2008 data platform.  View all SQL Server editions, here.

Beta 4 SQL Server > Home

Monday, October 26, 2009

Power Pivot

Power Pivot is the new name for Microsoft Gemini, a user-based cube building and data analysis add-in for Excel 2010.

Power Pivot

Not to be confused with Pivot Power.

http://www.contextures.com/xlPivotAddIn.html

Wednesday, October 21, 2009

Location Location Location

A few years ago, one of my clients determined that they should centralize their real estate and shift around workers to better optimize the facilities.  Read – downsizing.  I found this out from a student who was working on the project much later.  All I knew was as contractors we had to be out of the building, and we might want to bring our computer equipment with us (and chairs, which I found out later).

So our development team was shuffled off from one of the nicest penthouse offices in the city, attached to a huge mall, next to the largest assortment of restaurants in the city, to a 1960s-design nuclear bunker disguised as an office.

We found out quickly that our small team of people had the run of the floor.  Every chair was broken, and designed before Herman Miller got out of high school.  There was a doomsday clock attached to the ceiling in the centre of the floor.  It didn’t work either.  The decor was styled with vintage warning signs.  “Danger due to acid.”  “Diesel generators – do not open this door!”.

It was a great work environment that motivated me to work from home (and other client locations) much more often.

Phil Factor reminded me about this, and has a funny story about how location is sometimes more important than appearance or prestige. 

My friend, with some exaggeration, explained that the management had decided that it seemed wrong to give any offices of a major retail bank the appearance of a deserted building, so all contractors were given offices near the windows.

Phil Factor's Phrenetic Phoughts : Moving Down in the World of Work

Ten Common SQL Server Reporting Services Challenges and Solutions

Simple Talk has a great article on the top 10 things you might have issues in dealing with SQL Reporting Services.

Challenges/Solutions

SSRS offers a range of different reporting techniques and technologies, to cater for the reporting needs of all levels of users, from the chief executives, to business analysts, to operational staff. Their reporting needs range from simple, tabular ad-hoc reports, to parameterized, linked or snapshot reports, to complex drill-down and drill-through multi-level reports.

Following is the list of some of the challenges I have encountered while developing such reports using Reporting Services 2000/2005. In the sections that follow, I will cover each challenge individually, providing insight into what may cause the difficulty, alongside a possible solution.

  1. Horizontal Tables: Calendar Reports
  2. Select "ALL" Query Parameter option
  3. Multiple Sheets in Excel
  4. Excel Merged Cell Issues
  5. Blank Pages
  6. Vertical Text
  7. Report Data in Header/Footer
  8. Are you missing XML/CSV data on your exports?
  9. Template Reports
  10. Using the Reporting Services database

A ZIP file containing samples of the reports detailed in this article is available to download, try out and amend to suit your own needs.

Ten Common SQL Server Reporting Services Challenges and Solutions

Monday, October 19, 2009

Basics of the IIS Database Manager : Using the IIS Database Manager : Managing and Maintaining IIS 7.0 : The Official Microsoft IIS Site

Microsoft has released a new version of the IIS Database Manager, providing the ability to manage SQL from a web page.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=231989b4-2a52-4c31-b2d6-96e8e97f8295 

Overview

IIS Database Manager allows you to easily manage your local and remote databases from within IIS Manager. IIS Database Manager automatically discovers databases based on the Web server or application configuration and also provides the ability to connect to any database on the network. Once connected, IIS Database Manager provides a full array of management options including managing tables, views, stored procedures and data, as well as running ad hoc queries.
IIS Database Manager provides native support for SQL Server and is also fully extensible for developers to add support for other database systems. In addition, because IIS Database Manager is an extension of IIS Manager, administrators can securely delegate the management of databases to authorized local or remote users, without having to open additional management ports on the server.
Here are a few articles to get you started on using the IIS Database Manager:


For more information, check out the IIS Database Manager Home Page.

Basics of the IIS Database Manager : Using the IIS Database Manager : Managing and Maintaining IIS 7.0 : The Official Microsoft IIS Site

Friday, October 16, 2009

Exam 70-433 - SQL 2008 Database Development

Just passed the SQL 2008 exam above. Without divulging the content, it seemed very obscure, and there wasn't a lot of maintenance or installation questions. Which is obvious in retrospect, because I ended up studying for 70-432 - Implementation and Maintenance but booked one exam too far ahead. Makes me even happier I passed it!

To study, I used MS OneNote to take the exam outline and break it down into multiple sections, crossing off the items I covered as I studied. Most of my studying included books online with some testing out of the new partition functionality and full-text functionality. I did a few reviews of the new features of SQL 2008 when it first came out in CTP, and have since implemented it a few more times. I didn't do a whole lot of deep-dives. The key things I focused on during studying were the new features of SQL 2008.

The exam itself was glitchy - some of the exhibits didn't show up, and a couple questions had duplicate answers. (which made it easier to find the right one.) For questions that were wordy, not obvious or not easy to read I marked for later.

These turned out to be almost half the questions on the exam.

It was definately a challenging one. Next step is to book exam 70-432 - SQL 2008 Implementation and Maintenance - the one I actually studied for!

Wednesday, October 14, 2009

Microsoft SQL Server and VMware Virtual Infrastructure

 

Publisher:
VMware

Latest Revision:
Mar 18, 2009

Download:
http://www.vmware.com/files/pdf/solutions/sql_server_virtual_bp.pdf

Description

Microsoft SQL Server is a very popular and widely deployed general purpose database server supported on Windows Sever operating systems. As customers embrace a “virtualize first” policy for all applications within their enterprises, they often need guidelines for deploying SQL Server in VMware Infrastructure. This paper should help you understand how to characterize your SQL Server databases for virtualization and the best practices for designing VMware Infrastructure to support SQL Server.

Microsoft SQL Server and VMware Virtual Infrastructure

Concatenating Rows - SQLServerCentral

 

Intent

Sometimes when generating reports or text for web pages or form letters, it becomes handy to concatenate multiple column values from rows of data into a single value entirely within T-SQL. For reference, a problem I encountered recently involved selecting one or more department names and converting them to a comma-separated list to appear in a drop-down list.
The SQL language itself doesn't easily facilitate this but there are ways around this. Below I outline two alternate queries using SQL Server.

Concatenating Rows - SQLServerCentral

Open DBDiff for SQL Server 2008 — DatabaseJournal.com

 

Open DBDiff for SQL Server 2008

By Muthusamy Anantha Kumar aka The MAK

Open DBDiff 1.0 is the utility provided by Microsoft via CodePlex. You may have used the Open DBDiff utility for SQL Server 2005 before. DBDiff utility compares two databases and generates a script that could be used to synchronize two databases. The Open DBDiff utility is in beta version 8. This article is going to illustrate the various use of the Open Diff utility.

Open DBDiff for SQL Server 2008 — DatabaseJournal.com