Filestream

There are various types of data

• Structured Data is data which is stored in a relational database

• Semi Structured Data is often of xml type and can be stored in the database

• Unstructured Data is such as images, video etc

Unstructured Data or binary large object (BLOB) data needs to be reviewed to decide where it is stored. BLOB’s smaller than 256kb are better stored inside a database and BLOB’s larger than 1 MB, for filestream, are best stored outside of the database. Storing blobs in the database using the data type varbinary(max) is limited to 2GB per blob.

Filestream allows unstructed data to be stored in the NTFS file system in directories called data containers which are listed as filegroups in the database. This allows transactional consistancy between structured and unstructured data and allows point in time recovery. Deleting or renaming or any filestream files directly in the file system will corrupt the database. Each row has a unique row ID. Fragmentation can occur on the NTFS file system and regular defragmentation is required. Streaming of data through the filestream is more efficent than through the conventional database set up however backing up the database with filestream files is slower than just backing up the database. Each Filestream data container may need to be on it’s own volume to stop data contention. Data stored on the NTFS file system can be compressed but it is expensive if the data is uncompressable. Filestream requires integrated security and filestream cannot block antivirus scanning. If a file is infected it is best to quarantine the file and use DBCC CHECKDB to identify the missing file. Filestream has a garbage collection process to remove files no longer required, which is automatic, when the checkpoint process runs.

Filestream has to be enabled at the windows level before the SQL Server level.

Filestream data can not be encrypted. Database Mirroring does not support filestream although failover clustering does.

Data Collection Security

There are 3 roles

Mdw_admin
Grants read and write to the Management Data Warehouse (MDW). It allows users to purge and clean up jobs to manage the amount of data
Mdw_reader
Grants users with read only access
Mdw_writer
Allows write and upload of data to the MDW

Data Collection

This product is a development of performance dashboard, database reports and built on top of standard DMV's. Historical data is persisted and is not affected by service restarts.

Plan for data growth of up to 250 - 500 MB a day on the Management Data Warehouse (MDW). The default Data Retention Period in MDW is 4 weeks . Microsoft detected approximately 3 - 4% increase in CPU performance when running this application.

The System Data Collection Sets contain 3 types of data collector Disk Usage, Query Statistics and server activity. The data is stored in a Management Data Warehouse (MDW). The data is collected by running SQL Server Agent jobs and have associated SSIS packages.
Disk Usage Data Collection
This captures information about disk usage for data and log files for each database. This can be used to show disk space trends
Query Statistic Data Collection
This collects information about different queries that run on the server and statistics to help in tuning efforts and identify the greatest impact on resource usage
Server Activity Data Collection
Colelcts CPU, Memory and performance counts. Helps identify bottlenecks

Stopping and starting services

A script to Stop and Start the SQL Server Agent Service

-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

-- STOP SQL Server Agent
EXEC xp_servicecontrol 'STOP','SQLServerAGENT'
GO
-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

-- START SQL Server Agent
EXEC xp_servicecontrol 'START','SQLServerAGENT'
GO
-- Check status
EXEC xp_servicecontrol 'QUERYSTATE','SQLServerAGENT'

SQL Server Profiler

There are 2 ways to run SQL Profiler

• Run the SQL Profiler client tool through the GUI
• Server-side tracing where events are saved to a physical file on the server by using SQL Server system stored procedures and functions.

To view the number of traces running:

SELECT count(*)
FROM :: fn_trace_getinfo(default)
WHERE property = 5 and value = 1

To obtain more detail about the active traces:-

SELECT *
FROM :: fn_trace_getinfo(default)

To stop a trace using the traceid:-

EXEC sp_trace_setstatus 1, @status = 0
EXEC sp_trace_setstatus 1, @status = 2

status = 0 stops the trace
status = 2 closes the trace and deletes its definition from the server

A sample output :-

traceid,property,value
1,1,2
1,2,D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log_1504.trc
1,3,20
1,4,NULL
1,5,1


This means

Result Set Description
Traceid Unique identifier for the trace
Property = 1 Configured trace options
Property = 2 Trace file name
Property = 3 Max file size for the *.trc file
Property = 4 Stop time for the trace session
Property = 5 Current trace status (1 = On and 0 = Off)
Value Current value for the traceid\property combination

The Default trace
The default trace great for diagnosing performance problems, finding deadlocks, and auditing security information.The default trace does not capture all trace events, but it captures key information such as auditing events, database events, error events, full text events, object creation, object deletion and object alteration. It also captures log growth events which can be invaluable to troubleshooting disk capacity problems.

Create Windows folders

To create windows folders using T-SQL run

EXEC master.dbo.xp_create_subdir 'D:\sqlserverbackups'
GO

Stop the login SID mismatch

To stop the user SID's mismatch in SQL Server 2005 for databases that are continually moved and restored from one database server to another

Run a script to collect the current sid of the user after a user database has been restored

username = bennett
USE ;
GO
SELECT sid FROM sysusers WHERE name = 'bennett';
GO

Then create a server login. Use the SID from the above query output and replace the username and password with the relevant details.

USE master
CREATE LOGIN [bennett]
WITH PASSWORD = 'Ax$ef6!f', SID = 0xA2AE403E43ED084C8B3021E7E8DFD61C,
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

Then create a database user for bennett, with the default schema dbo.

USE ;
CREATE USER [bennett] FOR LOGIN [bennett]
WITH DEFAULT_SCHEMA = dbo;
GO

When you restore the database again the database user continues to work with no further action required.

Commands to help tune queries

SET STATISTICS IO ON

This command displays the amount of disk activity for transact SQL statements. Sample output is

Table 'servers'. Scan count 1, logical reads 7,physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To turn off run
SET STATISTICS IO OFF


SET STATISTICS TIME ON

SQL Server firstly makes logical reads to retrieve data from the data cache, if the data is not in the data cache it makes physical reads from disk. This script displays the amount of CPU resources needed by the query to run, parse, compile, and execute each statement in milliseconds

A sample output is

CPU time = 0 ms, elapsed time = 145 ms

To turn off run
SET STATISTICS TIME OFF

Change Ownership of a Maintenance Plan

To change the ownership of a Maintenance Plan (dts or SSIS package) the following scripts will make te changes

--SQL 2005 version
UPDATE msdb.dbo.sysdtspackages90
SET ownersid = 0x01 --this is the sa user but set to which ever user you want
WHERE name = 'MaintenancePlanName'

If this maintenance plan is scheduled the job owner could also need updating as well.

-- SQL 2005 version job owner update
USE msdb
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Your Job Name',
@owner_login_name=N'sqljobuser'
GO

Examples for other versions of sql server are

--SQL 2000 version
UPDATE msdb.dbo.sysdtspackages
SET owner = 'sa',
owner_sid = 0x01 --sa user
WHERE name = 'MaintenancePlanName'

--SQL 2008 version

UPDATE msdb.dbo.sysssispackages
SET ownersid=0x01 --sa user
WHERE name='MaintenancePlanName'

Login Failed Error States

The common security error states and their descriptions for the error 18456 severity 14 state 8
are listed below:-

ERROR STATE ERROR DESCRIPTION
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required

Error Log Management

The SQL server error log may require that the size of the log is limited if the growth rate is excessive. This can be done using the sp_cycle_errorlog system stored procedure. This will start a new error log. The script

use msdb
exec sp_cycle_errorlog

The log displays
Current log
Attempting to cycle error log. This is an informational message only; no user action is required.
New log
The error log has been reinitialized. See the previous log for older entries.

Virtual Log Files

Each transaction log file is divided logically into smaller segments called virtual log files (VLFs). Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions. VLF can be in 4 states

1.Active

2.Inactive and not backed up (or truncated) - recoverable

3.Inactive and backed up or truncated - recycable

4.Unused

DBCC LOGINFO shows a list of virtual log files that are active logs when their status flag is 2. The sample sql is

DBCC LOGINFO('Database_name')

This returns one row per VLF.

Number of rows = Number of VLFs

Sample output from this command.

FileId,FileSize,StartOffset,FSeqNo,Status,Parity,CreateLSN
2,253952,8192,21,0,128,0
2,253952,262144,22,0,128,0
2,253952,516096,20,0,64,0
2,278528,770048,23,2,64,0

The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files.

It is good to Minimize VLF’s. Excessive autogrowth of the transaction log causes:

* Windows call to extend a file (may cause file fragmentation)
* Adds “VLFs” to the file on each autogrowth

If there are excessive VLFs (> 50) then the first cause of action should be to free up log space by first clearing space from the transaction log (using BACKUP LOG)

Another useful command is DBCC SQLPERF (logspace) which shows the transaction log size and log space used percentage.

Active transaction log DBCC log command

This undocumented command is used to view the transaction log

DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )

PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database
in question.

type - is the type of output:

0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name,
page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump
of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump
of the current transaction log's row, plus Checkpoint Begin,
DB Version, Max XDESID

by default type = 0

Here is sample data output

dbcc log (testdatabase, 2)

Current LSN,Operation,Context,Transaction ID,Tag Bits,Log Record Fixed Length,Log Record Length,Previous LSN,Flag Bits,AllocUnitId,AllocUnitName,Page ID,Slot ID,Previous Page LSN,Number of Locks,Lock Information,Description 00000017:000001e3:0001,LOP_BEGIN_CKPT,LCX_NULL,0000:00000000,0x0000,96,96,00000017:000001d4:0001,0x0000,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 00000017:000001e4:0001,LOP_END_CKPT,LCX_NULL,0000:00000000,0x0000,136,136,00000017:000001e3:0001,0x0000,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 00000017:000001ed:0001,LOP_BEGIN_XACT,LCX_NULL,0000:000003f2,0x0000,48,92,00000000:00000000:0000,0x0200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,user_transaction;0x01 00000017:000001ed:0002,LOP_INSERT_ROWS,LCX_HEAP,0000:000003f2,0x0000,62,104,00000017:000001ed:0001,0x1200,72057594043695104,dbo.tblmixednumbertext,0001:0000005d,10,00000017:000001e8:0003,3,ACQUIRE_LOCK_IX OBJECT: 10:21575115:0 ;ACQUIRE_LOCK_IX PAGE: 10:1:93;ACQUIRE_LOCK_X RID: 10:1:93:10, 00000017:000001ed:0003,LOP_COMMIT_XACT,LCX_NULL,0000:000003f2,0x0000,48,52,00000017:000001ed:0001,0x0200,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

How to clean up a Database Server

CHECKPOINT
This will write all dirty buffers to disk so that when you run DBCC DROPCLEANBUFFERS, you can be assured that all data buffers are cleaned out.

DBCC DROPCLEANBUFFERS
To remove all the data from the buffers. This is useful to run between performance tests to ensure accurate testing results. This command only removes clean buffers.

DBCC FREEPROCCACHE
Clears out the stored procedure cache for all SQL Server databases.

DBCC ERRORLOG: Rarely restarting the SQL Server service can cause the server log to get very large and take a long time to load and view. This can be truncated (essentially creating a new log) by running DBCC ERRORLOG. Consider scheduling a regular job that runs this command once a week. The same thing can be done using sp_cycle_errorlog.

Reading SQL Server log files

There is an undocumented system stored procedure sp_readerrorlog. This script returns all of the rows from the 3rd archived error log.

EXEC sp_readerrorlog 3

sp_readerrolog accepts only 4 parameters, but the extended stored procedure accepts at least 7 parameters.

EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4

@P1 = Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
@P2 = Log file type: 1 or NULL = error log, 2 = SQL Agent log
@P3 = Search string 1: String one you want to search for
@P4 = Search string 2: String two you want to search for to further refine the results

A few examples

This returns a result when 2008 appears.
EXEC sp_readerrorlog 3, 1, '2008'

Rows are returned where the value '2008' and 'backup' exist.
EXEC sp_readerrorlog 3, 1, '2008', 'backup'

Sample log

2009-06-02 16:38:39.12 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

2009-06-02 16:38:39.13 Server (c) 2005 Microsoft Corporation.
2009-06-02 16:38:39.19 Server All rights reserved.
2009-06-02 16:38:39.19 Server Server process ID is 856.
2009-06-02 16:38:39.19 Server Authentication mode is MIXED.
2009-06-02 16:38:39.19 Server Logging SQL Server messages in file 'D:\MSSQL10.SQLMANAGE\MSSQL\Log\ERRORLOG'.....

How to find the log space usage

DBCC SQLPERF (logspace) provides transaction log space usage statistics for all databases. The sample output returns

Database Name,Log Size (MB),Log Space Used (%),Status
master,1.242188,40.8805,0
tempdb,0.4921875,47.32143,0
model,6.117188,94.44444,0
msdb,1.992188,43.13726,0

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
GO
DENY EXECUTE ON sys.sp_replwritetovarbin TO public
GO
NOTE: don’t use this with Transactional Replication with Updateable Subscriptions

The other fairly recent security issue from July 2008 is
http://www.microsoft.com/technet/security/bulletin/ms08-040.mspx

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.

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.

scMaster

Master stores all versions of content

scCore

Core stores all Sitecore settings for editors

scWeb

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

scArchive

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.

scCore

Contains the Sitecore client.

scExtranet

Contains the extranet security settings.

scMaster

Contains the site under development.

scRecycleBin

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

scSecurity

Contains the client security settings.

scWeb

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
https://h30406.www3.hp.com/campaigns/2008/wwcampaign/1-57C4K/index.php?mcc=DNXA&jumpid=in_r11374_us/en/large/tsg/w1_0908_scrawlr_redirect/mcc_DNXA

urlscan
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
http://www.iis.net/downloads/default.aspx?tabid=34&g=6&i=1697


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
http://www.microsoft.com/downloads/details.aspx?FamilyId=58A7C46E-A599-4FCB-9AB4-A4334146B6BA&displaylang=en

SQLInjectionFinder
SQLInjectionFinder (v1.5.0) Tool to help determine .asp pages targeted by recent SQL Injection attacks
Download here
http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=WSUS&ReleaseId=13436

Policy Based Management

Terminology

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;
GO
-- To update the currently configured value for -- advanced options
RECONFIGURE;
GO
-- To disable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0;
GO
-- To update the currently configured value for this -- feature
RECONFIGURE;
GO

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.

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'
WITH INIT,COPY_ONLY

What is a SQL Server's name

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

select @@SERVERNAME as [@@SERVERNAME],
SERVERPROPERTY('ServerName')as [SERVERPROPERTY('ServerName')],
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 & " " & _
prop.Enabled
next
' enable tcpip
for each changeprop in wmi.ExecQuery("select * " & _
"from ServerNetworkProtocol " & _
"where InstanceName = 'sqltools' and " & _
"ProtocolName = 'Tcp'")
changeprop.SetEnable()
next

Create a script to backup user databases

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

USE MASTER
SELECT 'USE [' + NAME + ']'+ 'BACKUP DATABASE [' + NAME + ']'
+ '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
FROM SYSOBJECTS
WHERE TYPE = 'P'
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_user TO PUBLIC
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 '?'"

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.

Sharepoint 2003 Backup and Restore Utilities

Tool	Purpose
Stsadm.exe	Makes 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.exe	Determines 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

Database	Default install	Changed
Database ending with _Config_db	FULL	FULL
Database ending with _SITE	FULL	FULL
Database ending with _PROF	SIMPLE	FULL
Database ending with _SERV	SIMPLE	FULL

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	Description
Database ending with _Config_db	it 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 _SITE	it 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 _PROF	it stores entire information about User Profiles; it also stores details of what data to gather about various User Profiles.
Database ending with _SERV	It 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

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/operations/htm/ebiz_ops_backuprestore_inpu.asp

Microsoft SQL Server 2005 Reporting Services

SQL Server reporting services (subsequently SSRS) default install databases are:-

Database	Description
ReportServer	Report 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. Note: 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.
ReportServerTempDB	This database is responsible for storing intermediate processing products, such as cached reports (to increase performance), and session and execution data. Note 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. TIP 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.
RSExecutionLog	Additionally, 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
GO
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'
GO

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
http://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

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
http://www.codeplex.com/sqlnexus

XML Notepad 2007

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

Download from
http://www.microsoft.com/downloads/details.aspx?familyid=72d6aa49-787d-4118-ba5f-4f30fe913628&displaylang=en

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
http://technet.microsoft.com/en-us/sysinternals/bb842062.aspx

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
http://support.microsoft.com/kb/231619

Scriptomatic 2.0

Scriptomatic is a utility that writes WMI scripts for you.

Download from

http://www.microsoft.com/downloads/details.aspx?familyid=09dfc342-648b-4119-b7eb-783b0f7d1178&displaylang=en

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

http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

LINQPad

LINQPad lets you interactively query SQL databases in LINQ.

LINQPad supports everything in C# 3.0 and Framework 3.5:

* LINQ to Objects
* LINQ to SQL
* Entity Framework
* LINQ to XML

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

http://www.linqpad.net/

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
http://www.codeplex.com/OpenDBiff

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
http://www.microsoft.com/downloads/details.aspx?familyid=531C53E7-8A2A-4375-8F2F-5D799AA67B5C&displaylang=en

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

http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en

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

http://www.microsoft.com/downloads/details.aspx?familyid=EEDD10D6-75F7-4763-86DE-D2347B8B5F89&displaylang=en

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
http://www.microsoft.com/downloads/details.aspx?familyid=1D3A4A0D-7E0C-4730-8204-E419218C1EFC&displaylang=en

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
http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en

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
http://internalsviewer.codeplex.com/

SQLPing

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
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx

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
http://www.microsoft.com/downloads/details.aspx?familyid=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

DBA Code of Ethics

Databases are now being used across multiple industries and environments and there is the need to look not only at the ethics of the data contained within the databases but at the group of people who administer and are the guardians of the database. There is ethical concern over the information contained within the database how it is stored, accessed, secured and gathered however regardless of this the administrators need to ensure that they follow some ethical principals or guidelines. The guidelines should cover not only the overarching ethics but the core aspects which need to be covered whilst administering the database.

Ethic 1 - The DBA role consists of
• Being a champion of the database
• Numerous stated and unstated responsibilities
• Explaining the DBA role and recommendations to the public
• The DBA hero is one who avoids any problems rather than a firefighter of issues
Ethic 2 - The Company’s data is the most precious asset it has. A DBA must protect it.
Ethic 3 - A DBA is responsible for making data available to authorized users and ensuring data inaccessible to the unauthorized user. Also Identification of sensitive data, managing it securely and auditing access is a key responsibility.
Ethic 4 - A DBA should have a patching philosophy for when
• Security issues arise
• When software bugs and enhancements arise
Ethic 5 - A DBA should monitor the system to ensure it is always available when it is needed
Ethic 6 - A DBA should ensure backups are taken regularly and verify the quality of the said backup.
Ethic 7- Ensuring every database has a documented disaster recovery point specified
• What is the time to recover?
• What is the recovery point interval?
Ethic 8 - A DBA should monitor changes to the system
• Following Information Technology Infrastructure Library (ITIL) best practice for IT Service Management.
• Having multi tier environments to validate changes
• Ensure rollback can occur for failed changes
• Ensure development scripts are written and version controlled to change the environments
• Identify the consequences of this change
Ethic 9 - A DBA should ensure appropriate documentation is written or obtained
• A summary of essential information such as configuration
• A process for keeping it up-to-date is the crucial aspect.
• Try to create Self-documenting systems where possible
Ethic 10 - A DBA should ensure good Data Modelling is applied so the data is useable across the systems
Ethic 11- Have an interactive process for problem management
Ethic 12- Ensure there is a capacity management process in place
Ethic 13 - A DBA should ensure there are regular database Audits which
• Periodically audit each database
• Have a Checklist for problems
• Have a sheet describing the purpose of each check
Ethic 14 - A DBA should design and follow best practice for design, development and administration
Ethic 15 - When you’ve made a mistake admit it, quickly so corrective action can be taken immediately.