Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein

Friday 22 May 2009

SQL Server security issues

Microsoft Security Bulletin http://www.microsoft.com/technet/security/bulletin/ms09-004.mspx issued in February 2009 is not an issue if you have SQL Server 2005 SP3 installed. If this is problematic to be installed, due to the fact that rollback requires the uninstall and reinstall of SQL Server, a fix to the vulnerability is

USE master
DENY EXECUTE ON sys.sp_replwritetovarbin TO public
NOTE: don’t use this with Transactional Replication with Updateable Subscriptions

The other fairly recent security issue from July 2008 is

Tuesday 12 May 2009

Types of patches

Quick-Fix Engineering (QFE)

The QFE is a Microsoft term for the delivery of individual service updates to its operating systems and application programs. Formerly called a hotfix, QFE can be used to describe both the method of delivering and applying a patch or fix, and also to refer to any individual fix.

General Distribution (GDR)

A GDR is typically associated with a critical patch release the product groups feels should be provided to and installed by the whole customer base. GDRs are often associated with security releases, or other critical fixes needed to mitigate a problem the majority of customers will experience when using our product.

Cumulative update package (CU)

Multiple cummulative updates are released between Service Packs and contains all of the applicable hot fixes at the time.

Service Packs (SP)

When there are a large enough collection of changes they are gathered together to be released as a service pack. A service pack can also deliver new product features.

Wednesday 6 May 2009

Upgrading Sitecore databases

After upgrading existing Sitecore database the SQL Server internal stats need to be upgraded so that it is possible to query the internal tables and see the health of the sql server databases. If this is not done you receive false information and can miss the problems. Run

DBCC UPDATEUSAGE ('databasename')

Sitecore 6 Core Databases

The new version of Sitecore has significantly less databases than in previous versions. Previous versions had 7 now there are only 3. The Core, Master and Web Databases are the only databases in the latest version of Sitecore.


Master stores all versions of content


Core stores all Sitecore settings for editors


Web store published version of content

The “Sitecore” and “Extranet” security databases were removed as they are now handled by the .NET security model and stored in standard tables.

The “Archive” and “Recycle bin” databases were removed as each database now has its own internal archive and recycle bin storage areas. The archive and recycle bin have also been enhanced to contain a search facility similar to the content editor.

Sitecore 5 Default Database

Belows lists the default databases


Contains archived objects. Sitecore can be configured to automatically remove objects from the Master database to the Archive database on a specific date. This removes old objects from the Master database but backs up a copy of the object. You can specify the archive date in the Content Editor » Tasks » Archive Date field.


Contains the Sitecore client.


Contains the extranet security settings.


Contains the site under development.


Contains deleted objects. Items can be restored via the Sitecore » Administrator Tools » Recycle Bin application


Contains the client security settings.


Contains the published web site.

SQL Injection

SQL Injection is a common vulnerability which can lead to the database being disabled, the web site displaying malicious contnet or even the the database can be destroyed. . Attacks come primarily through Web applications that allow an attacker to execute their own SQL commands on the application database. The defense against SQL-injection attacks must be code-based.

Protecting your code

A few simple steps you can take to protect your Web applications from SQL-injection attacks.

-Principle of Least Privilege
The account an application uses to connect to the database should have only the privileges that application requires.

-Validate All Input
Make the input is what you expect it to be.

-Avoid Dynamic SQL
Dynamic SQL is a great tool for performing adhoc queries, but combining dynamic SQL with user input creates exposure. You should replace dynamic SQL with prepared SQL or stored procedures .

-Use Double Quotes
Replace all the single quotes that your users' input contains with double quotes. Single quotes often terminate SQL expressions and give the input more power than is necessary.

Tools to help prevent and identify SQL Injection

Ensure the SQL Server is fully patched to include all security vulnerabilities

HP Scrawlr

Scrawlr, developed by the HP Web Security Research Group in coordination with the MSRC, is short for SQL Injector and Crawler. Scrawlr will crawl a website while simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities. Scrawlr is lightning fast and uses our intelligent engine technology to dynamically craft SQL Injection attacks on the fly.
Download here

UrlScan version 3.0 Beta is a Microsoft security tool that restricts the types of HTTP requests that Internet Information Services (IIS) will process.
Download here

Microsoft Source Code Analyzer for SQL Injection tool
The tool is a static code analysis tool that helps you find SQL injection vulnerabilities in Active Server Pages (ASP) code.
Download here

SQLInjectionFinder (v1.5.0) Tool to help determine .asp pages targeted by recent SQL Injection attacks
Download here

Policy Based Management


Facet - Defines a management area within the policy based framework for a predefined set of properties
Condition - contains a single condition that you want to enforce that evaluates to True or False; i.e. the state of a Facet
Policy - defines the permitted stated for properties of a single facet and is a set of conditions specified on the facets of a target.
Target - an entity that is managed by Policy-Based management; e.g. a database, a table, an index, etc.

Order creation

Select a facet that contains properties you want to configure
Define a condition that specifies the permitted states of the facet
Define a policy that contains the conditions and sets one of the evaltion modes

Policy evaluation modes

• On demand - The policy is evaluated only when directly ran by the administrator.
• On change: prevent - DDL triggers are used to prevent policy violations.
• On change: log only - Event notifications are used to check a policy when a change is made.
• On schedule - A SQL Agent job is used to periodically check policies for violations.

SQL Server Surface Area Configuration for Feature

Security configuration settings should only be enabled if you need to use the security feature. You should follow the principal of least privilege, which states that a system can be made more secure by granting a user or process only those privileges it requires.
  • Ad Hoc Remote Queries - Disabled
  • CLR Integration - Disabled. For use with .NET Assemblies on your SQL Server.
  • DAC - Disabled. For Database Administrators connection.
  • Database Mail - Enabled for SQL Server Agent alerting on jobs and sending emails from SQL Server
  • Native XML Web Services - This would only have an option if you've configured it manually. If so, consider transitioning to a web service written in .NET deployed on IIS due to the deprecation in SQL Server 2008.
  • OLE Autmation - Disabled.
  • Service Broker - Disabled.
  • SQL Mail - Disabled as Database Mail is the new version for SQL Mail.
  • Web Assistant - Disabled.
  • xp_cmdshell - Disabled.
Advanced option can be changed through the graphical interface or by script

-- To enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
-- To update the currently configured value for -- advanced options
-- To disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0;
-- To update the currently configured value for this -- feature

Surface Area Configuration tool removed

The Surface Area Configuration tool has been removed from the SQL Server 2008 version. Instead the settings can now be found under facets which is part of the new Policy-Based Management.

Tuesday 5 May 2009

Copy only backups

If you need to keep a sql server backup independent of the sequence of conventional backups it can be acheived with copy only backups. A copy-only backup will not impact the overall backup and restore procedures for the database and is created independently of the regular scheduled conventional backups.

A copy-only full backup is not supported within SQL Server Management Studio. The script below should be used:-

BACKUP DATABASE database_name
TO DISK = 'F:\oneoffbackups\database_name_date_time.bak'

Monday 4 May 2009

What is a SQL Server's name

This is a helpful script to check naming within SQL Server.

SERVERPROPERTY('MachineName')as [SERVERPROPERTY('MachineName')],
SERVERPROPERTY('InstanceName')as [SERVERPROPERTY('InstanceName')],
host_name()as [host_name()]

WMI script to enable TCP settings

This script returns the status of the TCP protocol settings and changes the status to enabled

set wmi = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement")
for each prop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'sqltools'")
WScript.Echo prop.ProtocolName & " - " & _
prop.ProtocolDisplayName & " " & _
' enable tcpip
for each changeprop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'sqltools' and " & _
"ProtocolName = 'Tcp'")

Create a script to backup user databases

This creates a script that will backup all user databases on the server.

+ 'TO DISK = ''Z:\oneoffbackups\' + NAME + '.bak'' WITH INIT'
FROM sysdatabases
WHERE dbid > 4

Create a script to grant stored procedure permissions

This script creates a script which allows stored procedure permissions to be granted to the public role

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

The output below is the scripts to run e.g.

GRANT EXECUTE ON usp_insert_address TO PUBLIC

change object owners

This script changes the owner of an object in the current database.
Note: A database user can't be deleted if it owns objects.

EXEC sp_changeobjectowner '.', 'dbo'

list all table sizes

This is a useful script for collecting table sizes with the number of rows

Print 'All table sizes with the number of rows'
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
PRINT 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Sunday 3 May 2009

SQLToolkit relaunched

I have now relaunched my website SQLtoolkit.co.uk with a new look and feel. Still loads of data to migrate from the old site to the new.

Friday 1 May 2009

Sharepoint 2003 Backup and Restore Utilities

Stsadm.exeMakes a full-fidelity backup of site collections.
SharePoint Migration tool (Smigrate.exe)Backs up and restores sites and subsites. Does not make a full-fidelity backup; you might lose some customizations or settings during the process.
SharePoint Portal Server Data Backup and Restore utility (Spsbackup.exe)Backs up and restores all databases, except the configuration database. Restores content indexes and content sources.
SPBackup.exeDetermines which site collections have changed and generates a batch file that backs up changed site collections using the Stsadm.exe tool.

Sharepoint 2003 Database Recovery models

DatabaseDefault installChanged
Database ending with _Config_dbFULLFULL
Database ending with _SITEFULLFULL
Database ending with _PROFSIMPLEFULL
Database ending with _SERVSIMPLEFULL

Full Recovery Mode is enabled because it is created by Windows SharePoint Services (_SITES and Config DB database). The other databases (_PROF and _SERV) are created by SharePoint Portal Server.

Microsoft SharePoint Portal Server 2003

The following table lists the default databases
Database ending with _Config_dbit stores the entire configuration database for the SharePoint server/farm. Anything pertaining to global configuration and which is set through the SharePoint central administration is stored in this database.
Database ending with _SITEit stores all the content of a specific portal. It contains the files, web pages, webs, sites, and all inter-related SharePoint infrastructure.
Database ending with _PROFit stores entire information about User Profiles; it also stores details of what data to gather about various User Profiles.
Database ending with _SERVIt stores data regarding search, notification, and indexing. It stores the gatherer log information as well as the text indexes for the content that is crawled. In fact, it can be easily checked up by right-clicking on any of the database and choosing Properties. In fact, that could be a way to know how much space each function/activity of your portal is taking up

Microsoft BizTalk Server 2004

The following table lists the databases and provides details of the ones that you must back up on a regular basis.

BAM Primary Import (BAMPrimaryImport)
This is the database where the Business Activity Monitoring tool collects the raw tracking data.

BAM Star Schema (BAMStarSchema)
This database contains the staging table, and the measure and dimension tables.

BAM Analysis (BAMAnalysis)
This database contains Business Activity Monitoring OLAP cubes for both online and offline analysis.

BAM Archive (BAMArchive)
This database archives old Business Activity data. Create a BAM Archive database to minimize the accumulation of Business Activity data in the BAM Primary Import database.

HWS Administration (BizTalkHWSDb)
This database contains all administration information related to Human Workflow Services (HWS).

BizTalk Tracking (BizTalkDTADb)
This database stores business and health monitoring data tracked by the BizTalk Server tracking engine.

BizTalk Configuration (BizTalkMgmtDb)
This database is the central meta-information store for all BizTalk Servers.

BizTalk Message Box (BizTalkMsgBoxDb)
This database stores subscription predicates. It is a host platform, where the queues and state tables for each BizTalk Server host are kept. This database also stores the messages and message properties.

Rule Engine (BizTalkRuleEngineDb)
This database is a repository for:
• Policies, which are sets of related rules.
• Vocabularies, which are collections of user-friendly, domain-specific names for data references in rules.

Credential (SSODB)
This Enterprise Single Sign-On credential database securely stores the configuration information for receive locations.

Trading Partner Management (TPM)
This database stores trading partner data for Business Activity Services (BAS).

Tracking Analysis Server (BizTalkAnalysisdb)
This database stores both business and health monitoring OLAP cubes.

BizTalk EDI (BizTalkEDIdb)
This database stores state for the electronic data interchange (EDI) feature.
Backing Up Your Databases

Backing Up Your Databases


Microsoft SQL Server 2005 Reporting Services

SQL Server reporting services (subsequently SSRS) default install databases are:-
ReportServerReport Server database is a SQL Server database that stores part of SSRS configuration, report definitions, report metadata, report history, cache policy, snapshots, resources, security settings, encrypted data, scheduling and delivery data, extension information and report execution log information.
Although users can certainly directly access databases in the SSRS catalog and directly modify objects that SSRS uses, this is not a recommended (or supported) practice. Underlying data and structures within the SSRS catalog are not guaranteed to be compatible between different releases of SSRS, service packs, or patches.
ReportServerTempDBThis database is responsible for storing intermediate processing products, such as cached reports (to increase performance), and session and execution data.
To store temporary snapshots in the file system, instead of the database, administrators should complete the following steps. First modify RSReportServer.config and set WebServiceUseFileShareStorage and WindowsServiceUseFileShareStorage to True. Then set FileShareStorageLocation to a fully qualified path. The default path is C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\RSTempFiles. Unlike SQL Server's tempdb, data in ReportServerTempDB survives SQL Server and Report Server restarts. Report Server periodically cleans expired and orphan data in ReportServerTempDB.
All data in ReportServerTempDB can be deleted at any time with minimal or no impact. The minimal impact that a user might experience, for example, is a temporary performance reduction due to lost cache data and a loss of an execution state. The execution state is stored in the table SessionData. Loss of the execution state results in an error: "Execution 'j4j3vfblcanzv3qzcqhvml55' cannot be found (rsExecutionNotFound)." To resolve the loss of the execution state, a user would need to reopen a report.
SSRS does not recover deleted ReportServerTempDB or tables within this database. To quickly recover from erroneous deletions of objects in this database, keep a script or a backup of an empty ReportServerTempDB handy.
RSExecutionLogAdditionally, the RSExecutionLog database can be added after the initial installation. This database stores additional logging information.

Fix: sa login is not mapped to dbo

The sa login is the login of the system administrator.
Check whether the login is matched.

USE databasename
SELECT u.name AS "Name", ISNULL(l.name, 'dbo is unmatched') AS "Matched Login"
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid
WHERE u.name = 'dbo'

If unmatched change the database owner.

USE databasename
EXEC sp_changedbowner 'sa'

Then fix the sa dbo mapping.

USE databasename
EXEC sp_change_users_login 'auto_fix', sa

To run a report to check username logins have matching userSID

USE databasename
EXEC sp_change_users_login 'report'

Best Practice for Security

The white paper entitled
Best practices for setting up and maintaining security in SQL Server 2005
can be downloaded here

” http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc

Data Dictionary

Every database should have a data dictionary. A data dictionary is a file that defines the basic organization of a database. It contains a collection of data element definitions in the database. More advanced data dictionary contains database schema with reference keys, still more advanced data dictionary contains entity-relationship model of the data elements or objects. Data dictionaries do not contain any actual data from the database, only bookkeeping information for managing it. The contents

1. Data element definitions
2. Table definitions
3. Database schema
4. Entity-relationship model of data
5. Database security model

Entity Relationship Diagrams (ERD)

Every Database should have an ER Diagram. These data models are tools used in analysis to describe the data requirements and assumptions in the system. They also set the stage for the design of databases.

There are three basic elements in ER models:

1. Entities are the "things" about which we seek information.
2. Attributes are the data we collect about the entities.
3. Relationships provide the structure needed to draw information from multiple entities.

RML Utilities for SQL Server

The RML utilities allow you to process SQL Server trace files and view reports showing how SQL Server is performing. For example, you can quickly see:
- Which application, database or login is using the most resources, and which queries are responsible for that
- Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed
- What queries are running slower in today's data compared to a previous set of data

Download from

SQL Nexus Tool

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

Download from

XML Notepad 2007

XML Notepad 2007 provides a simple intuitive user interface for browsing and editing XML documents

Download from

Sysinternals Suite

The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools. These are advanced system utilities for troubleshooting.

Download from

SQLIOSim utility

The SQLIOSim utility replaces the SQLIOStress utility

The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server 2005, of SQL Server 2000, and of SQL Server 7.0. The I/O patterns of these versions of SQL Server resemble one another. The SQLIOStress utility has been used to test SQL Server 2005 I/O requirements for many years.

Download from

Scriptomatic 2.0

Scriptomatic is a utility that writes WMI scripts for you.

Download from


Log Parser 2.2

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.

Download from



LINQPad lets you interactively query SQL databases in LINQ.

LINQPad supports everything in C# 3.0 and Framework 3.5:

* LINQ to Objects
* Entity Framework

LINQPad comes preloaded with 200 examples from the book, C# 3.0 in a Nutshell. There's no better way to experience the coolness of LINQ and functional programming.

Download from


Open DBDiff

Open DBDiff is an open source database schema comparison tool for SQL Server 2005/2008. It reports differences between two database schemas and provides a synchronization script to upgrade a database from one to the other.

Open DBDiff can synchronize

* Tables (including Table Options like vardecimal, text in row, etc.)
* Columns (including Computed Columns, XML options, Identities, etc.)
* Constraints
* Indexes (and XML Indexes)
* XML Schemas
* Table Types
* User Data Types (UDT)
* CLR Objects (Assemblies, CLR-UDT, CLR-Store Procedure, CLR-Triggers)
* Triggers (including DDL Triggers)
* Synonyms
* Schemas
* File groups
* Views
* Functions
* Store Procedures
* Partition Functions/Schemes
* Users
* Roles

Download from

SQL Server 2008 System Views Map

The Microsoft SQL Server 2008 System Views Map shows the key system views included in SQL Server 2008, and the relationships between them. The map is similar to the Microsoft SQL Server 2005 version and includes updates for the new and updated the Microsoft SQL Server 2008 features such as resource governor, extended events, full-text search, and others.

Download from

SQL Server 2005 System Views Map

The Microsoft SQL Server 2005 System Views Map shows the key system views included in SQL Server 2005, and the relationships between them.

Download from


SQL Server Health and History Tool (SQLH2)

The Microsoft SQL Server Health and History Tool (SQLH2) allows you to collect information from instances of SQL Server, store this information, and run reports against the data in order to determine how SQL Server is being used.

SQLH2 collects four main types of information:
1. Feature Usage – What services/features are installed, running and level of workload on the service.
2. Configuration Settings – Machine, OS and SQL configuration settings, SQL instance and database metadata.
3. Uptime of the SQL Server service
4. Performance Counters (optional) – Used to determine performance trends

Download from


SQL Server 2005 Performance Dashboard Reports

The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

Download from

SQLIO Disk Subsystem Benchmark Tool

SQLIO is a tool provided by Microsoft which can also be used to determine the I/O capacity of a given configuration.

Download from

Internals Viewer for SQL Server

Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.

Download from


SQLPing 3.0 performs both active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise.

Download from

SQL Server 2005 Best Practices Analyzer (BPA)

This tool collects data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

Download from