Thursday 29 July 2010

Accessing a windows share from a Mac

Accessing a windows share from a Mac (ugh!)

smb://username@servername.fqdn/sharename

We were then prompted for the password, and presto!

Source : Apple Support - http://support.apple.com/kb/ht1568

Wednesday 28 July 2010

Backup all Filegroups

A short script I wrote to backup all filegroups has been featured on SQLServerCentral.com today.

SSC : Backup all Filegroups

Tuesday 27 July 2010

Bookmark : Security Tools

SQLBF - Brute force or dictionary password, from a hash.
http://www.cqure.net/wp/sqlpat/

SQLAT - SQL Auditing Tool
http://www.cqure.net/wp/sql-auditing-tools/

MSSQLScan - Find SQL Server instances
http://www.cqure.net/wp/mssqlscan/


* Links are supplied for information only. These are for testing your OWN servers, networks & passwords! *

Monday 26 July 2010

SQL 101 : Tempdb

This post explains exactly what tempdb is responsible for -

Iain Kick - Not another tempdb post

And what to do to tune it, namely -

1) RAID level (& separate version)
2) Instant File Initialization
3) Trace Flag T1118
4) Pre Size tempdb
5) Split tempdb per processor core

Friday 23 July 2010

Filegroup Backups

A quick runthrough experimenting with Filegroup Backups

BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' 
TO DISK = 'c:\Adventureworks.BAK'

Msg 3004, Level 16, State 1, Line 1
The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

So , to use this technique, you can't use SIMPLE recovery mode.
Why am i using SIMPLE? - It's a development box!
Setting to FULL resolves this -

ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT
GO

BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY' 
TO DISK = 'c:\Adventureworks.BAK'

Processed 21280 pages for database 'Adventureworks', file 'AdventureWorks_Data' on file 2.
Processed 1 pages for database 'Adventureworks', file 'AdventureWorks_Log' on file 2.
BACKUP DATABASE...FILE= successfully processed 21281 pages in 8.921 seconds (18.636 MB/sec).

Using the system table sys.filegroups, i can build a list of backup commands to backup all filegroups, like this ...


DECLARE @backupPath VARCHAR(500)
SET @backupPath = 'd:\sqlbackups\' 

DECLARE @backuptimestamp VARCHAR(30)
SET @backuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')

SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @backupPath + @backuptimestamp + '_' + DB_NAME() + '_' + name +'.BAK''' + ' WITH COMPRESSION ' AS BACKUPCOMMAND
FROM sys.filegroups
 
NB : My commands feature the compression setting as am on SQL 2008 Enterprise.

Thursday 22 July 2010

Bookmark : SQL Server DMV Starter Pack E-Book Published

The SQL Server DMV Starter Pack is a download comprising of an E-Book & Scripts for 28 DMV based queries. It has been put together by Glenn Berry, Louis Davidson and Tim Ford and is available as another freebie from Redgate

The 28 queries it provides, are -

Connections, Sessions, Requests, Queries

1: Are you Connected?
2: Session Ownership
3: Current expensive, or blocked, requests
4: Query Stats – Find the "top X" most expensive cached queries
5: How many single-use ad-hoc Plans?
6: Ad-hoc queries and the plan cache
7: Investigate expensive cached stored procedures
8: Find Queries that are waiting, or have waited, for a Memory Grant

Transactions

9: Monitor long-running transactions
10: Identify locking and blocking issues

Databases and Indexes

11: Find Missing Indexes
12: Interrogate Index Usage
13: Table Storage Stats (Pages and Row Counts)
14: Monitor TempDB

Disk I/O

15: Investigate Disk Bottlenecks via I/O Stalls
16: Investigate Disk Bottlenecks via Pending I/O

Operating System

17: Why are we Waiting?
18: Expose Performance Counters
19: Basic CPU Configuration
20: CPU Utilization History
21: Monitor Schedule activity
22: System-wide Memory Usage
23: Detect Memory Pressure
24: Investigate Memory Usage Across all Caches
25: Investigate memory use in the Buffer Pool

Other Useful DMVs

26: Rooting out Unruly CLR Tasks
27: Full Text Search
28: Page Repair attempts in Database Mirroring

Redgate : Download SQL Server DMV Starter Pack

Wednesday 21 July 2010

Windows 2008 : Hyper-V - Disappearing Client for Microsoft Networks

Two years ago, i posted about a networking eccentricity with Hyper-V where the 'Client for Microsoft Networks' would disappear from the clients.....


On inspecting the Local Area Connection Properties, ‘Client for Microsoft Networks’ is unticked.  Simply clicking the tickbox appears to work fine, but when you click OK , the following appears-
" Your current selection will also disable the following features: Client for Microsoft Networks "
" Are you sure you want to disable these feature(s)? "

An easier way to resolve this is as follows >

1) Go to Network Connections -




2) Press ALT to display the menu bar and select 'Advanced Settings' from the revealed 'Advanced' menu.



3) Select the virtual NIC (Local Area Conection 4 in my screenshot). Tick the Client for Microsoft Networks check box and the 2 boxes under it for IPv4 and IPv6.


MSDN Forum

Tuesday 20 July 2010

SSIS , OLEDB and Stored Procedures

SSIS , OLEDB and Stored Procedures.

SSIS cannot 'see' the metadata for returned data from stored procedures like it can for tables and views. Here is a collection of methods to try when using sprocs with SSIS.

On oledb connection managers, set -

DelayValidation = True

On data sources and components inside the data flow, set -

ValidateExternalMetaData = False

When calling your stored procedure, prefix as follows -

"SET FMTONLY OFF; EXEC dbo.myprocedure @param = 1"

In the stored procedure itself, create a header as follows -

CREATE PROCEDURE dbo.myprocedure (@param INT)
AS
BEGIN

-- {options to set...}
SET NOCOUNT ON; -- {supress 'rows affected' messages}

SET FMTONLY OFF; -- {ensure full data retuned. incase FMTONLY ON was in effect}


-- {false header to pass metadata}

IF 1 = 0
 BEGIN
 SELECT
   CAST(NULL AS INT) AS [intColumn1]
 , CAST(NULL AS INT) AS [intColumn2]
 , CAST(NULL AS VARCHAR(5)) AS [varcharColumn1] 
 , CAST(NULL AS VARCHAR(255)) AS [varcharColumn2] 
 , CAST(NULL AS DATETIME2(0)) AS [datetime2Column1]
     , CAST(NULL AS VARBINARY(20)) AS [varbinaryColumn1]  
 END

-- {genuine query}

SELECT intColumn1, intColumn2, varcharcolumn1 ...

END
GO


SSISTalk: Phil Brammer - Stored Procedures and the OLE DB Source
ReplicationAnswers : Coping with no column names in the oledb source

Saturday 17 July 2010

SQL Server Processor Configuration

AFFINITY controls processors and threads used by SQL server,
MAXDOP controls the maximum number of processors a single query can use.

solace : Affinity & Affinity I/O
solace : Max Degree of Parallelism (MAXDOP)

Friday 16 July 2010

SQL 2008 on Windows 2008 R2 / Windows 7

Quick notes :

To install SQL 2008 on Windows 2008 R2 you must install the application server role first!

To successfully run SQL 2008 on Windows 7, make sure you install Service Pack 1,

Andrew Fryer's blog : SQL Server 2008 on Windows 7 / Windows server 2008 r2
Softpedia : Make Windows 7 Play Nice with SQL Server 2008
Joes Barreto : Changes in Roles, Role Services and Features from Windows 2008 to Windows 2008 R2

Wednesday 14 July 2010

Bookmark : Troubleshooting SSPI errors : Detecting a bad SPN

'Advanced Troubleshooting Week at SQL University, Lesson 1' caught my eye today.
Not because of it's catchy title, because of the content (which i could have done with 2 years ago).

It's on SPNs (service principle names) those Active Directory entries needed for Kerberos authentication and I heartily recommend it.

Bookmarking here, so I find it again....

SSC: Troubleshooting SSPI errors - Detecting a bad SPN

Bookmark : Error Line Numbers

I have always found stored proc error line numbers in Management Studio somewhat confusing.
They never match what you see on screen!

The link below explains it fully, but in short -

"its the line in the original batch that compiled the procedure" so, "it includes the comments and the white space before it"...

Link :  What line does the error line number refer to?

Tuesday 13 July 2010

Bookmark : How do you learn BI as a DBA?

Johnathan Kehayias has published something very much at the forefront of my thoughts right now, an article entitled How do you learn BI as a DBA? 

Having earned my MCITP Database Administrator 2008 and MCITP Database Developer 2008 last year, I'm keen both to certify in BI , expand my SSIS development work and get to grips with SSAS development.

The following articles are referenced in Jonathan's post and will serve as my starting point too.

Planning Your First Microsoft BI Solution
Building a Data Foundation for a BI Solution
Building Your First Cube

Monday 12 July 2010

Scripting Data to a .CSV format

A simple way to script example data, so it can be posted in forums etc...

SELECT  'SELECT '
      + QUOTENAME(column1,'''')+','
      + QUOTENAME(column2,'''')+','
      + QUOTENAME(column3,'''')
      + ' UNION ALL'
 FROM myschema.mytable

SSC : How to post data/code on a forum

Sunday 11 July 2010

SQL Server Default Trace

SQL Server Default Trace. Useful to tell who did what and when!

-- Get info about the default trace (includes the trace file location) ...
SELECT * FROM ::fn_trace_getinfo(default)

-- Show everything you can get from querying the default trace ...
SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description
FROM ::fn_trace_geteventinfo(1) t
INNER JOIN sys.trace_events e ON t.eventID = e.trace_event_id
INNER JOIN sys.trace_columns c ON t.columnid = c.trace_column_id

-- Query the default trace ...
SELECT 
loginname,
loginsid,
spid,
hostname,
applicationname,
servername,
databasename,
objectName,
e.category_id,
cat.name,
textdata,
starttime,
endtime,
duration,
eventclass,
eventsubclass,
e.name as EventName
FROM ::fn_trace_gettable('D:\Data\MSSQL10.MSSQLSERVER\MSSQL\Log\log_152.trc',0)
INNER JOIN sys.trace_events e ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id


Links :
There's Something about SQL! : Default Trace
MSDN : Default Trace

Thursday 8 July 2010

Creating a 'System' Stored Procedure

Want to create a procedure that is available in any database (and will run in the context of that database) ?
  1. Create the procedure in the master database
  2. Prefix the name with sp_

USE master
GO

CREATE PROCEDURE sp_myproc AS
BEGIN
SELECT DB_NAME() AS CurrentDatabase
END
GO

USE tempdb
GO

EXEC sp_myproc
GO

Are my statistics up to date?

SELECT
   s.name   AS SchemaName
 , t.name   AS TableName
 , i.name   AS IndexName
 , i.type_desc AS IndexType
 , STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
FROM    sys.objects t  
INNER JOIN sys.indexes i  ON i.[object_id] = t.[object_id]
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id] 
WHERE i.name IS NOT NULL AND s.name <> 'sys'
ORDER BY STATS_DATE(i.[object_id], i.index_id)  , t.name, i.type 

Wednesday 7 July 2010

Long Running Queries

A colleague sent me this to show what is currently running.
Look at the WHERE clause to adjust it for the duration threshold you are interested in.

SELECT
    r.session_id
  , p.kpid
  , r.start_time
  , DATEDIFF(SECOND, r.start_time, GETDATE()) as elapsed_time
  , st.text
  , r.status
  , r.command
  , r.cpu_time
  , r.wait_type
  , DB_NAME(r.database_id)
  , p.hostname
  , qp.query_plan
FROM
    sys.dm_exec_requests AS r
    INNER JOIN sys.sysprocesses AS p on r.session_id = p.spid
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE session_id > 50
AND DATEDIFF(SECOND, r.start_time, GETDATE())  > 10 -- duration in seconds
ORDER BY r.start_time


From Measure TSQL Statement Performance , this query provides performance statistics for cached query plans.

SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

Thursday 1 July 2010

Fast Delete !

Performance wise, a very fast delete can be achieved using a VIEW utilising an ORDER BY clause!!!

Create the view -
CREATE VIEW [dbo].[del_data] as
SELECT TOP(500) * FROM dbo.data WHERE id < 219150348 ORDER BY id
GO

Run a while loop to delete the data effeciently in batches -
WHILE(1=1)
BEGIN
DELETE dbo.del_data
IF @@ROWCOUNT < 500 BREAK
END

Link : SQLCAT - Fast ordered delete