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.