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

Sunday, October 11, 2009

Data loss easier than recovery

"This week, Microsoft announced that they had lost all Sidekick user data including pictures, contacts, calendars and other information from the Danger's servers. Since the devices sync with the servers, the devices also lost the data. The Sidekick data services had amazingly been out over a week.

From what they say, after a week of investigation, there is no way to retrieve the user data. Customers will have to start over. "


Link to story

And the moral of the story is... (for the MS cloud providers anyway)


Find Last BackUp Date Of All Databases on your Server

Whenever you perform a backup, SQL Server 2005 updates the following tables: msdb.dbo.backupfile, msdb.dbo.backupmediafamily, msdb.dbo.backupmediaset and msdb.dbo.backupset.

You can use these tables to retrieve backup information about your database. In the following query, a join is made between sys.sysdatabase and msdb.dbo.backupset to fetch the database name, last backup date and the user who took the backup.

SQL Server 2005 Solution
SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

You can run this on multiple servers using SQL 2008 and the new server group feature.

You can exclude servers that aren't required to be monitored in the group by placing
AND @@servername not in ('servername')
in the where clause.

This is my take on the query:

Use msdb
go
select name,type,database_name,last_backup
from

(SELECT sd.name,
bs.TYPE,
bs.database_name,
max(bs.backup_start_date) as last_backup
FROM sys.databases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
where bs.[type] not in ('I')
and (sd.recovery_model <> 3 or bs.type not in ('L'))
and sd.is_read_only = 0
and @@servername not in ('localhost')
Group by sd.name,
bs.TYPE,
bs.database_name

) sd
where sd.last_backup <= dateadd(d,-1,getdate())
Order by sd.name,last_backup

All the databases that haven't had full backups in the last day.

For Sidekick users, the moral of the story is... backup your data outside of the cloud.

Should be an interesting week for those using Sidekicks to their full potential, and a blow to cloud computing proponents.

What would happen of any of these vendors announced they had lost all your (their) data?

Facebook
Myspace
Google Apps
Amazon S3
Blogger
Youtube
iTunes
Kindle

Something to think about...

Cloud-computing and the seven deadly data risks

Friday, October 09, 2009

Features Supported by the Editions of SQL Server 2008

 

Features Supported by the Editions of SQL Server 2008

Use the following tables to determine which features are supported by the different editions of SQL Server 2008.

Features Supported by the Editions of SQL Server 2008

Wednesday, October 07, 2009

Farewell to the Excel 2003 addin and the BI Accelerator - Windows Live

Winding down availability for add-ins in earlier versions brings this add-in’s lifespan to a close.  It sounds like customers were using this instead of upgrading to Excel 2007… which is still missing some of the features of the add-in.

”The Excel Add-in for SQL Server Analysis Services has been removed to avoid customer confusion about support for this component. As noted in the details that accompanied the release of this product, Microsoft does not provide any support for this add-in and has no plans to release future versions. Newer versions of Excel include most of the functionality that is provided by this add-in; these newer versions are supported according to the Microsoft Product Lifecycle.

Farewell to the Excel 2003 addin and the BI Accelerator - Windows Live

What is the go-to path for Excel 2003 customers?  Upgrade.  It is unfortunate that they removed access to this tool rather than “open-sourcing” it or sending it off into MS Research land.

How about removal from MS downloads and addition into CodePlex?

http://www.codeplex.com/site/search?projectSearchText=analysis%20services

Same goes for tools like Proclarity.  Leaving customers in the dark is not necessarily the correct approach.

Excel 2003 has ended mainstream support as of April 14, 2009.

http://office.microsoft.com/en-us/products/HA102294401033.aspx

Extended support ends January 14, 2014.

Wednesday, September 02, 2009

SQL SERVER – Get Query Plan Along with Query Text and Execution Count « Journey to SQL Authority with Pinal Dave

Pinal Dave comes through with another useful query.

Quite often, we need to know how many any particular objects have been executed on our server and what their execution plan is. I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. You can add an additional WHERE condition if you want to learn about any specific object.

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'

In result set, you will find the last column QueryPlan with XML text in it. Click on it, and it will open a new tab displaying the graphical execution plan.

SQL SERVER – Get Query Plan Along with Query Text and Execution Count « Journey to SQL Authority with Pinal Dave

Wednesday, August 05, 2009

QueryUnit - Home

 

A project that allow the execution of Unit Testing against a database (Relational or Multidimensional).
It uses NUnit (http://www.nunit.org/) )as unit-testing framework and does not require DBA or BI Developer to know anything about .NET: just write your SQL or MDX queries and test them. QueryUnit will take care of automatically create the assembly that will test your query for you, using NVelocity (http://nvelocity.codeplex.com/) to generate .NET classes and the CodeDom engine to compile them at runtime.

QueryUnit - Home

Tuesday, August 04, 2009

SQL Server Helper - SQL Server 2008 - Compound Operators

 

Compound Operators

SQL Server 2008 introduces the Compound Operators as a programmability enhancement in Transact-SQL.  Compound operators execute some operation, such as +, -, * and /, and set an original value to the result of the operation.  It is just a simpler syntax that you can use on commonly performed operation such as incrementing a variable by a certain number.

As an example, instead of doing the following:

SET @Index = @Index + 1


it can now simplified as follows:



SET @Index += 1



SQL Server Helper - SQL Server 2008 - Compound Operators

JJClements.co.uk » Delete files older than certain number of days

 

I was recently asked to investigate a problem with a server and the lack of space on a partition. After a quick look using Treesize I noticed a suspicious folder being used by an application for logging purposes.

There were over 700,000 files in it! When I tried to browse the folder using explorer it took an absolute age to open as you can imagine. To rectify the problem and recover the majority of the disk space being used by the logs I wanted to delete the contents of the logging folder that was older than 30 days. After a quick search I discovered a command line utility called forfiles.exe that is included with Windows Server 2003. Using forfiles.exe I was able to delete all files older than 30 days like so:

forfiles.exe /p (pathtofilestodelete) /s /m *.* /d -30 /c "cmd /c del /q @path"

A working example is:

forfiles.exe /p d:\logs /s /m *.* /d -30 /c "cmd /c del /q @path"

This will delete ALL files from d:\logs (and all sub folders it contains because /s has been used to force recursion) older than 30 days without prompting you to confirm deletion.

Here is an explanation of the switches I used:

/p = The path to search for the files you want to check the date of and remove
/s = Recurse subdirectories contained within the path specified using /p and check them as well
/m = The search mask to be used for the file type you want to check the date on (*.* being all files)
/d = The date to compare the files against. A standard date type can also be used (dd/mm/yyyy)
/c = The command to be used on a file that matches the /m and /d criteria
/q = Used within /c to instruct the del command to delete files quietly

JJClements.co.uk » Delete files older than certain number of days

Friday, July 24, 2009

A Google-like Full Text Search - SQLServerCentral

 

You can tap into the self-education that comes from countless hours spent at home trying to locate information on the Web and turn it to your advantage in your own SQL Server-based search applications. With just a little bit of code you can help reduce your training costs and give your users an easy to use interface that will make them want to use your search applications. In this article I'll explain how to convert Google-style queries to SQL Server's full-text search CONTAINS predicate syntax

A Google-like Full Text Search - SQLServerCentral

Monday, July 20, 2009

Chris Webb's BI Blog's Blog - Windows Live

Some of the new features in Excel 2010 specific to Analysis Services

But anyway, now I’ve been given the go-ahead to blog I thought I’d list all of the new features I’ve found that are relevant to Analysis Services users. This does not include anything to do with Gemini, because Gemini isn’t part of the Technical Preview and I don’t have it yet unfortunately. I’m also not going to comment on bugs or things that don’t work in the way I’d want because, after all, this is not released software and things can and hopefully will change before RTM.

Chris Webb's BI Blog's Blog - Windows Live

Tuesday, July 07, 2009

Kevin Kline : Old Performance Tuning Recommendations Die Hard

  • Multiple data files for tempdb = yes, 1 of equal size per cpu (or cpu core)
  • Multiple log files = no
However, the recommendation fails when you get to the log portion of the equation. Why? It’s because data file IO is written using the proportional file algorithm where each data file has data written to it in round-robin style.  On the other hand, log files are written using the active file algorithm where LogFile1 is written first until full, then LogFile2 is written next until full, and so on… Long story made short (too late, I know) – you get no performance gain from having multiple files in the log because all writes occur on only one file. You can only get a performance gain from multiple files on the data portion of a database.

Kevin Kline : Old Performance Tuning Recommendations Die Hard

Monday, July 06, 2009

DbOctopus - powerful data editing for SQL Server

 

Your database knows how tables are relationally connected. DbOctopus takes advantage of that data wherever it can. Do you need to quickly see related rows from other table, without manual searching? Or to see descriptive names from FK table instead of numeric IDs? It's just a tip of iceberg of what DbOctopus can do to help you be more productive, while making everything easier.

DbOctopus - powerful data editing for SQL Server

Wednesday, June 24, 2009

Multipurpose Row_Number() Function - Madhivanan

 

Multipurpose Row_Number() Function

One of the features available in SQL Server 2005 is Row_Number() function. It is not only used to generate row number for each row but also used for other purposes as well. I breifly explain how it can be used for various purposes

Multipurpose Row_Number() Function - Madhivanan

Monday, June 22, 2009

Hey, Scripting Guy! How Can I Use Windows PowerShell to Automate Microsoft Excel?

Shudder.  If you need to, here’s the way automate Excel via PowerShell.  Just don’t stick this on a server someplace…

How Can I Use Windows PowerShell to Automate Microsoft Excel?

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I use Windows PowerShell to automate Microsoft Excel?

Hey, Scripting Guy! How Can I Use Windows PowerShell to Automate Microsoft Excel?

SSIS Junkie : ExecuteSQLExecutingQuery : SSIS Nugget

 

One of the custom log entries that I didn’t know about was the Execute SQL Task’s ExecuteSQLExecutingQuery and now having taken a look at it I wish I’d have known about it a lot sooner. The real power of this custom log entry is that it shows the SQL statement that gets executed against the data source which is especially valuable if the SQL statement is constructed using an expression.

SSIS Junkie : ExecuteSQLExecutingQuery : SSIS Nugget

Thursday, June 18, 2009

Kerberos Constrained Delegation – Troubleshooting Tools (Part 5 of 6) - Windows Live

 

In Part 4 of this series, I discussed Internet Explorer setup.  To date, the series has been about steps needed to set up Kerberos Constrained Delegation with Windows Integrated Authentication.  While the first parts of the series were not a step-by-step guide in how to set up Kerberos, it was meant to provide an overall checklist of things to make sure were properly set up.  What happens if you have an issue with Kerberos after it is set up?  That’s what this part of the series is about.

Unfortunately, many Kerberos setups have problems because:

Kerberos Constrained Delegation – Troubleshooting Tools (Part 5 of 6) - Windows Live

Tuesday, June 16, 2009

Lifehacker - Five Best Alternative File Copiers - File Management

 

If you do any serious file copying on a Windows system, you'll quickly discover that there are substantial limitations to the default file copier. Ease your file copying frustrations with these five alternative copiers.

Lifehacker - Five Best Alternative File Copiers - File Management

Thursday, June 11, 2009

Get Performance Tips Directly From SQL Server - SQLServerCentral

 

When SQL is compiled, an ‘optimal’ plan is created and stored in the plan cache, the plan contains details of table and index access. To produce this plan, the optimiser uses information from various sources, including indexes, statistics and row counts.

If the optimiser would like to use certain information but can’t find it, it adds details of what it wanted to use to the plan. Inspecting the plans for these details will help us improve the performance of our SQL.

For example, if a certain index could have been useful for a given SQL statement, the optimiser looks for that index, if that index is not found, the missing index’s details are stored together with the plan.

There are various performance related items we can search for including: missing indexes, columns with no statistics, and the occurrence of table scans.

This utility makes use of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), so can be used by SQL Server 2005 or greater.

Get Performance Tips Directly From SQL Server - SQLServerCentral

Color Palette and the 56 Excel ColorIndex Colors

No wonder Excel graphs usually look like crap.   No 32 million color palette?

And who doesn’t use a color monitor with Excel these days?

Each Microsoft Excel workbook has a palette of 56 colors that you can apply to cells, fonts, gridlines, graphic objects, and fills and lines in a chart.  If you are using a color monitor, you can customize the shade and intensity of the colors in the color palette for each workbook.

Color Palette and the 56 Excel ColorIndex Colors

And some add-ons to get around this limitation.

http://color-palette.qarchive.org/

This step-by-step article explains how to change the color palette so that you can use custom colors for elements of the workbooks in Microsoft Excel. You can modify colors for many workbook elements, including the following elements:

  • Worksheet Tabs
  • Fonts
  • Charts and chart elements
  • Cells (fills and borders)

This article explains how to change the color palette so that you can use custom colors for these elements.

http://support.microsoft.com/kb/288412

There’s some “interesting” challenges keeping color consistent between Excel and PowerPoint too.

Retrieve List of Databases and their Properties using PowerShell

If you are a DBA monitoring more than 1 database server, Powershell is the way to go on a budget.

Problem
In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you've seen how you can use Windows PowerShell and SMO to administer SQL Server databases. I would like to translate some of the Transact-SQL scripts that I use every day, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes.

Solution
One of the things that we do as DBAs is to retrieve a list of databases and their properties for auditing and reporting purposes. We check for properties such as recovery model, available free space, autoshrink, etc., and generate action items based on them. We've already seen how to access the Server object - its properties and methods - using SMO. We will dig into the object hierarchy and look at the different members of the Server object. A SQL Server instance can be described using different properties like instance name, logins, settings, all of which are members of the Server object.

Retrieve List of Databases and their Properties using PowerShell

Tuesday, June 09, 2009

Dynamic SQL execution on remote SQL Server using EXEC AT

 

Problem
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using "EXEC AT" to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions.

In this tip I am going to start my brief discussion with OPENQUERY and OPENROWSET table functions, its limitation and how the new EXEC AT command overcomes them.

Dynamic SQL execution on remote SQL Server using EXEC AT

Friday, June 05, 2009

SQL Server backup and recovery: Idera SQL safe backup

Useful script to backup all databases on a server with Idera SQL safe

alter proc dbo.sp_backupalldb
as

declare @dbname as varchar(100)
declare @fname as varchar(255)
declare @rownum as int
SET @rownum = 1

declare @dbcount as int
select @dbcount = count(*)
    from sys.databases
    where name not in ('tempdb')

while @rownum <= @dbcount
begin

select @dbname = name from (
    select name, row_number() over (order by name) rownum
    from sys.databases
    where name not in ('tempdb') ) dbs
where rownum = @rownum

if @@rowcount = 0 RETURN

print @dbname

set @fname = 'D:\Backup\' + @dbname + '.safe'

print @fname

EXEC [master].[dbo].[xp_ss_backup]  
@database = @dbname,  
@filename = @fname,  
@backuptype = 'Full'

set @rownum = @rownum + 1

end

SQL Server backup and recovery: Idera SQL safe backup

Default trace - A Beginner's Guide - SQLServerCentral

 

We have all been subject to or know someone who has been in a situation where an object has been altered/created/deleted, without our knowledge, and the application comes to a screeching halt. After fixing the problem, your boss asks you some questions, like what happened, why did it happen, and who did it. SQL Server 2005 introduced a new type of trigger called a DDL trigger that can provide all the answers we need; however, you did not get a chance to implement this functionality. So... what do you do?

Default trace - A Beginner's Guide - SQLServerCentral

How to Identify Blocking Problems with SQL Profiler

 

In SQL Server 2000 and earlier, identifying blocking issues was not an easy task. One option was to use Enterprise Manager to view existing connections to see if any blocking was occurring, or using the sp_who or sp_who2 commands. If you were really ambitious, you could write some code to extract blocking data from system tables.

In SQL Server 2005, the situation has improved. Besides Management Studio, stored procedures, and system tables, we also have DMVs and even the Performance Dashboard. However, most importantly, we have a new SQL Server Profiler event, Blocked Process Report. This event does a great job of helping you to identify blocking issues and, at the same time, provides you with much of the information you need to help correct the problem.

How to Identify Blocking Problems with SQL Profiler

Phoenix: SQL Server 2005 Best Practices

 

The following are some of the best practices adopted by SQL Server DBA's. I have covered some important stuffs starting with Tempdb, Memory, DB engine, Replication and Index.

Phoenix: SQL Server 2005 Best Practices

[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string - Jon Galloway

Learn something new every day.  Until now, I was using the sql client alias configuration tool to set this one up.

No more dependencies on server admins!

Barry Dorrans recently mentioned that you can force the database connection protocol by specifying np: or tcp: before the server name in your connection string. I've jumped through some hoops before using localhost to target tcp and (local) to target named pipes, but it looks like there's a much better way to do this (since MDAC 2.6).

There's more info in MS KB Article 313295:

TCP/IP:

server=tcp:hostname

You can optionally specify a specific port number. By default, the port is 1433.

server=tcp:hostname, portNumber


Named Pipes:



server=np:hostname

You can optionally specify a specific named pipe.



server=np:\\hostname\pipe\pipeName




[SQL] Force the protocol (TCP, Named Pipes, etc.) in your connection string - Jon Galloway

Wednesday, May 27, 2009

Moving Database Files Detach/Attach or ALTER DATABASE? - SQLServerCentral

 

At times it can be necessary to move the data and or log files from one location to another on the same SQL Server. There are two ways to go about doing this task, detaching the database from the SQL Server Instance, moving the files to the new location in the operating system, and then reattaching the database to the SQL Server Instance, and using ALTER DATABASE with the MODIFY FILE option to move the files through a metadata switch, taking the database offline, moving the file in the operating system and then bringing the database back online. Both accomplish the same task, but there are a number of reasons why the ALTER DATABASE method can make more sense for doing this kind of task.

Moving Database Files Detach/Attach or ALTER DATABASE? - SQLServerCentral

Friday, May 22, 2009

Windows 7 and Windows Application Compatibility : Boot from Windows 7 VHD Boot without having any native Operating System

 

Boot from Windows 7 VHD Boot without having any native Operating System

VHD boot is a new feature of Windows 7 and can be used in very creative ways. If you have a need for Reimaging a machine (like a testing environment) back to its original configuration then booting from VHD seem to be a very easy way to do it.

Windows 7 and Windows Application Compatibility : Boot from Windows 7 VHD Boot without having any native Operating System

Thursday, May 21, 2009

Microsoft SQL Server Development Customer Advisory Team – Mirroring SQL

 

To get more information on SQL Server database mirroring, check these links (Some of these were written for SQL 2005 but still apply to SQL 2008):

· Database Mirroring and Log Shipping Working Together: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Implementing Application Failover with Database Mirroring: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Database Mirroring Best Practices and Performance Considerations: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx

· Database Mirroring FAQ: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

· Performance Boost for Database Mirroring: http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx

· Troubleshooting Database Mirroring Deployment: http://msdn.microsoft.com/en-us/library/ms189127.aspx

· How to Avoid Orphaned Users With Database Mirroring:

http://glennberrysqlperformance.spaces.live.com/Blog/cns!45041418ECCAA960!494.entry

Microsoft SQL Server Development Customer Advisory Team

Halving your delete times with large datasets

Here is info from MySpace and the SQL Performance Team on how to delete large ordered blocks of data.

create view v1 as (select top (10000) * from t1 order by a)

and we can delete the “top” rows using simply

delete from v1

The query plan for this delete is much simpler.

Pic2

and the I/O and cpu statistics demonstrate the improvement:

Microsoft SQL Server Development Customer Advisory Team

Tuesday, May 19, 2009

Digital Volcano – Find and Replace multiple files

TextCrawler and Duplicate Cleaner are two useful tools for a DBA working with Reporting Services.

In combination with Reporting Services Scripter, TextCrawler allows you to perform comparisons of reports between servers (by changing the server name in the RDL) and to change the root path for folders in the rss scripts (provided the name is distinct).

Digital Volcano

Thursday, May 07, 2009

BI-tch – Dropping the time in datetime

Sometimes door #1 isn’t the best option.

Option 3: DATEADD

DATEADD(dd, DATEDIFF(dd, 0 getdate()), 0)

Returns 2009-04-09 00:00:00.000 (shock horror!)

BI-tch

Andrew Fryer's Blog

 

To help make sense of your options there is a Metadata toolkit containing a whitepaper and and a number of tools:

  • DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.
  • DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.
  • Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.
  • Lineage Repository – A database called SSIS_META that can be used to house metadata from nearly any system.
  • Reports – Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.
  • Report Model – A report model that you can use with Report Builder to allow end-users to create ad-hoc reports.
  • Integration Services Samples – A few sample packages to start auditing and viewing lineage on.

Andrew Fryer's Blog

Friday, May 01, 2009

RamDisk for Performance and Security

This technology was around since the dawn of XT and could be valuable for improving SQL performance.

RamDisk and RamDisk Plus®
(a.k.a Ramdrive)

RamDisk and RamDisk Plus offer dramatic improvement in the storage performance of servers and applications running on Windows Server 2003, Windows XP, and Windows 2000. Intel’s IoMeter benchmark reveals that our ramdrives typically show a 50 times performance gain over a physical hard disk. In many instances, that translates into an improvement of 3 to 10 times in overall application speed.

RamDisk for Performance and Security

Wednesday, April 22, 2009

THE BI Blog : The Bird is the Word

Straight from the BI Blog comes social BI networks.

Here is a useful list of some of the communities and social groups for Microsoft BI, out there to get involved.

Follow the bird, the bbb-bird bird bird, bird is the word…

Twitter feeds

Microsoft BI

Nic Smith

Donald Farmer

Guy Weismantel

Microsoft SharePoint

SQL Server Pro’s

Microsoft_Excel

Facebook groups

Microsoft Business Intelligence

Microsoft SharePoint

Microsoft SQL Server

Excel


Linkedin groups

Microsoft Business Intelligence

SharePoint Users Group
SQLServer Central

Excel

Microsoft YouTube Channel

http://www.youtube.com/user/wowmsft

Microsoft BI blogs

The Microsoft BI Blog

http://blogs.msdn.com/bi

THE BI Blogroll

Intelligent Insight on Performance Management
PerformancePoint Team Blog
Norm's PerformancePoint Server Blog
Excel Team Technical Blog
SQL Server Reporting Services Team Blog
RDA Business Intelligence Blog
Sacha Tomey's BI Blog
LATAM Business Productivity Blog
SharePoint Team Blog
SQL Data Services Team Blog
The I in BI Hayley Rixon’s Blog

Microsoft BI Websites

Microsoft BI

Microsoft People Ready Business

SharePoint

SQL Server

Excel

Partners

http://www.microsoft.com/bi/partners/default.aspx

https://partner.microsoft.com/bi

TechNet Discussion Forums:

SQL Server Analysis Services
SQL Server Data Access
SQL Server Reporting Services
Management Reporter
ProClarity - General
SharePoint - Excel Services
Data Mining
Monitoring and Analytics
SharePoint - Social Computing
SQL Server Data Warehousing
SharePoint - Collaboration
SharePoint - Business Intelligence

Office Online Discussion Forums

Excel

THE BI Blog : The Bird is the Word

Friday, April 17, 2009

Amit's blog : Disk Partitioning Offset

 

Volume alignment, commonly referred to as sector alignment, should be performed on the file system (NTFS) whenever a volume is created on a RAID device. Failure to do so can lead to significant performance degradation; these are most commonly the result of partition misalignment with stripe unit boundaries. This can also lead to hardware cache misalignment, resulting in inefficient utilization of the array cache. For more information on this, see Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000.

Amit's blog : Disk Partitioning Offset

Monday, April 13, 2009

SQL Steve’s SQL Tips - While replacement

Instead of counters and while loops, this is a simple replacement.

But what if you have billions of rows in the “ErrorLog” table and you need to delete them a batch at a time so that you don’t lock up the entire table? You can use the “TOP” operator to specify the batch size and then specify an integer value that follows the “GO” keyword specifying the number of batches to execute.

For example:

DELETE
TOP(100000)

FROM

    ErrorLog

WHERE

    ErrorTime < ‘2008-01-01′

GO 10000

SQL Steve’s SQL Tips

Tuesday, February 03, 2009

Two Time-saving VS Command Line Parameters

Who needs Windows...

“…\devenv.exe” “path to solution file”

You can copy an paste your exising shortcut file to a new shortcut file and then modify the Target field to have the additional solution parameter.

To use both of these together, put the solution file first and the /nosplash second.

To see other command line options, run devenv.exe with the /? parameter.

Two Time-saving VS Command Line Parameters

Sunday, January 04, 2009

Microsoft .NET & C#: Complete OLAP infrastructure without Microsoft Analysis Services, part 3

 

Complete OLAP infrastructure without Microsoft Analysis Services, part 3

In previous part of this tutorial we've built an analytical database using Microsoft Analysis Services. We've been able to browse the database using SQL Management Studio and execute MDX queries on it.

In this part of the tutorial we will:

  • build an offline static cube (*.cub file) from the relational database using C# and ADOMD.NET
  • query the static cube using Microsoft Excell as static cube browser
  • query the static cube with MDX queries using C# and ADOMD.NET

Microsoft .NET & C#: Complete OLAP infrastructure without Microsoft Analysis Services, part 3

Saturday, January 03, 2009

Internals Viewer for SQL Server - Home

 

Project Description
Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.
All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works
Note There was a problem with the first release of Beta 2 20081228. If no add-in is visible please follow the instructions in Troubleshooting.
User Guide
Troubleshooting
Features

  • Integration with SSMS (SQL Server Management Studio) 2005 and 2008
    • The application is installed as a SSMS add-in
    • Internals information integrated into the Object Explorer
    • Transaction Log viewer integrated into the Query Results
  • Allocation Map
    • Displays the physical layout of tables and indexes
    • Displays PFS status
    • Overlay pages in the Buffer Pool
  • Page Viewer
    • Displays Data pages including forwarding records and sparse columns
    • Displays Index pages
    • Displays allocation pages (IAM, GAM, SGAM, DCM, and BCM pages)
    • Displays pages with SQL Server 2008 row and page compression

Internals Viewer for SQL Server - Home

Friday, January 02, 2009

Quick and dirty clone table schema

 

SELECT top 0 * into customer_backup
  FROM customers

Be sure to create your primary keys and indexes afterwards!