September 2009 - Posts

SQL Injection Attacks at Scottish Developers
24 September 09 06:41 AM | MartinBell | with no comments

No they haven’t been hit by one! But if you are running a web site you have probably had someone attempt one.

T
his is something that all DBAs should be aware of and take precautions against by implementing best practices. Colin Angus Mackay of Scottish Developers will be giving a talk in Dundee on 28th October and showing how they can occur.

Go to
http://www.eventbrite.com/event/443957890/sql for more.

Filed under:
Scottish Area SQL Server User Group Meeting, Edinburgh - Thursday 8th October
23 September 09 10:06 PM | MartinBell | 1 comment(s)

At last I’ve managed to get everything in place for a Scottish Area meeting on 8th October! Details as follows:

Your own Performance Analysis Toolkit - Rob Carrol
How easy is it to do your own performance analysis. In this session Rob will look at:

  • Using SQLDiag to run performance collections
  • Analysing Profiler output using SQL Nexus
  • Analysing Perfmon data using PAL
     

Rob Carrol
Rob Carrol has been working with SQL Server since 1999 when he started out his career as a web developer using ASP/.NET, ColdFusion and SQL Server. Since then he has continued to work with SQL Server in a DBA and consultancy capacity, specializing in performance tuning and database troubleshooting. He joined Microsoft in April 2008 as a SQL Server Premier Field Engineer, working with some of Microsoft's largest clients in the UK and Europe. Rob lives in South Ayrshire, Scotland and I am married with 2 daughters. When he's not working with SQL Server you'll usually find him playing Xbox or thinking about football or golf !

Introduction to Powershell for a DBA – Martin Bell
A new feature of SQL Server 2008 is the SQLPS mini-shell in this session Martin will be showing what you can do with this new scripting environment and why you should start using it!

Martin Bell
Martin Bell has been a freelance computer consultant in the UK for over 23 years. He has worked on many relational database systems and since the 1998 has specialized in SQL Server. He was first awarded Microsoft MVP (SQL Server) status in June 2003. Martin organises the Leeds and Scottish Area SQL Server User Group meetings and is one of the founding members of the SQLBits organising committee.

Agenda

18:30 - 19:00 - Introduction, Networking and Food

19:00 - 19:50 – Your own Performance Analysis Toolkit - Rob Carrol

19:50 - 20:00 - Break

20:00 - 20:50 – Introduction to SQL Server Powershell – Martin Bell

20:50 - 21:00 - Close

Location:

Microsoft Office at Waverley Gate

http://www.microsoft.com/uk/about/map-edinburgh.mspx

Registration will be necessary as I have to submit registrations to Microsoft on Thursday morning.

Filed under:
To all partners of SQL Server DBAs and Developers...
20 September 09 08:03 PM | MartinBell | 1 comment(s)

This is an open letter to any partner of a SQL Server DBA, Developer or BI practitioner regardless of gender or age!


Dear Sir/Madam,

As a partner of a SQL Server DBA I am sure you may be aware of the quirkily named SQLBits conferences organized in various parts of the country twice each year. Your partner may have disappeared for a weekend and returned enthused and eager to try out the many new things they have learnt, all SQL related unfortunately. Up to now this may have been a bit of a chore having to put up with them going on about SQL this and SQL that, but now that doesn’t have to be the case.

The next SQLBits conference, cunningly called “SQLBits goes West” will be at the luxurious Celtic Manor Golf and Spa resort; perfect for a well deserved relaxing weekend away.

If your partner books before the 30th September they will save up to £200 on the registration fee. That’s £200 that you could use on one of the Spa Experience days provided by the resort. Alternatively you could just choose your own combination from their range of therapies, massages or holistic treatments on offer; including the Rasul Mud Ritual, Dry Floatation and Hydrotherapy, Facials, Manicures and Pedicures.

So make sure your partner signs up at www.sqlbits.com before 30th September so that you can indulge in the relaxing weekend that you are entitled to, at one of the top Health Spas in the UK.

Yours faithfully,




Martin Bell

Filed under:
Checking Foreign Keys
19 September 09 08:38 PM | MartinBell | 2 comment(s)
A while ago I answered a question in the news groups where someone was having problems doing a data load because of the foreign key constraints on a table. To overcome this you can disable these constraints, but then you have to be wary because it is possible to have loaded data that is orphaned.

To show this look at the following example. We have two tables and a foreign key linking them.

USE [tempdb]
GO

IF
  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK_example_referencing]') AND type in (N'U'))
            DROP TABLE [dbo].[FK_example_referencing] ;
GO

IF
  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK_example_referenced]') AND type in (N'U'))
            DROP TABLE [dbo].[FK_example_referenced] ;
GO

CREATE
TABLE FK_example_referenced
(id INT NOT NULL IDENTITY CONSTRAINT PK_FK_example_referenced PRIMARY KEY,
    name VARCHAR(10) NOT NULL
);
GO

CREATE
TABLE FK_example_referencing
(id INT NOT NULL IDENTITY CONSTRAINT PK_FK_example_referencing PRIMARY KEY,
    fid int not null CONSTRAINT FK_FK_example_referenced FOREIGN KEY REFERENCES
FK_example_referenced
( id ),
    val char(10) NOT NULL
);
GO

If you are not careful on the order in which data is loaded you may want to load data into the referencing table that does not exist in the referenced table.

INSERT INTO FK_example_referencing(fid, val) VALUES ( 1, 'First' );
GO

Will give you the error:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FK_example_referenced". The conflict occurred in database "tempdb", table "dbo.FK_example_referenced", column 'id'.
The statement has been terminated.

So you may need to add data to the referenced table before inserting data into the referencing table:

INSERT INTO FK_example_referenced (name) VALUES ( 'One' );
GO

SELECT
* FROM FK_example_referenced ;
GO

id          name
----------- ----------
1           One

INSERT INTO FK_example_referencing(fid,val) VALUES ( 1, 'First' );
GO -- Now Works

SELECT * FROM FK_example_referencing ;
GO

id          fid         val
----------- ----------- ----------
2           1           First    

Another option to avoid having to load tables in order is to disable the foreign key constraint and then re-enable this when you have finished. You can do this using the statement:

-- Disable the constraint.
ALTER
TABLE FK_example_referencing NOCHECK CONSTRAINT FK_FK_example_referenced;
GO

Once it is disabled you can then insert data that does not exist in the referenced table.

INSERT INTO FK_example_referencing(fid, val) VALUES ( 2, 'Second' );
GO

SELECT
* FROM FK_example_referencing
;
GO

id          fid         val
----------- ----------- ----------
2           1           First    
3           2           Second   


If you then re-enable the constraint it will not check the validity of the data unless you specify the the WITH CHECK option, and without this option you can not guarantee the existing data will satisfy the Foreign Key constraint.

-- Re-enable the constraint.
ALTER
TABLE FK_example_referencing CHECK CONSTRAINT FK_FK_example_referenced;
GO

Subsequent insert statements will then check the Foreign Key constraint and validate the data, but existing data will remain. 

INSERT INTO FK_example_referencing(fid,val) VALUES ( 3, 'Third' );
GO
– Fails

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FK_example_referenced". The conflict occurred in database "tempdb", table "dbo.FK_example_referenced", column 'id'.

The statement has been terminated.

If you try to re-enable the constraint using WITH CHECK and there is some data that violates the Foreign Key constraint enabling the constraint will fail.

-- Reenable the constraint WITH CHECK.
ALTER TABLE FK_example_referencing WITH CHECK CHECK CONSTRAINT FK_FK_example_referenced;
GO

Msg 547, Level 16, State 0, Line 1

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_FK_example_referenced". The conflict occurred in database "tempdb", table "dbo.FK_example_referenced", column 'id'.

So how can you be sure that this has not happened on a system you have inherited?

In the system table sys.foreign_keys there is a column is_not_trusted that will have a value of 1 if the data has not been validated against the constraint. This column will be remain as 1 when a constraint has been enabled but not checked.

SELECT name,is_not_trusted
FROM sys.foreign_keys ;
GO

name                       is_not_trusted
------------------------- --------------
FK_FK_example_referenced  1

So once the data is in order and the Foreign Key has been checked the value in of is_not_trusted in sys.foreign_keys will then be 0.

INSERT INTO FK_example_referenced (name) VALUES ( 'Two' ), ( 'Three' );
GO

-- Reenable the constraint WITH CHECK.
ALTER TABLE FK_example_referencing WITH CHECK CHECK CONSTRAINT FK_FK_example_referenced
;
GO
– Works

SELECT name,is_not_trusted
FROM sys.foreign_keys
;
GO

name                      is_not_trusted
------------------------- --------------
FK_FK_example_referenced  0


If you disable an index (e.g. PRIMARY KEY index), all foreign keys referencing the index will be disabled.

ALTER INDEX PK_FK_example_referenced ON [dbo].[FK_example_referenced] DISABLE
GO
 
Warning: Foreign key 'FK_FK_example_referenced' on table 'FK_example_referencing' referencing table 'FK_example_referenced' was disabled as a result of disabling the index 'PK_FK_example_referenced'.

For a data load exercise this  is probably not going to be much use as any attempt to INSERT/UPDATE/DELETE or SELECT any data in a clustered index will fail, but you will be able to insert data into the referenced table.

INSERT INTO FK_example_referencing(fid,val) VALUES ( 4, 'Forth' );
GO -- Works

To enable the index you will need to rebuild it:

ALTER INDEX PK_FK_example_referenced ON [dbo].[FK_example_referenced] REBUILD
GO

But this will not re-enable the foreign key and you can still insert data.

INSERT
INTO FK_example_referencing(fid,val) VALUES ( 5, 'Fifth' );
GO -- Works

SELECT name,is_not_trusted
FROM sys.foreign_keys ;
GO

name                       is_not_trusted
------------------------- --------------
FK_FK_example_referenced  1

Disabling a clustered index will also disable any non-clustered indexes on that table. You will also need to re-enable these by either re-building them individually or using the ALTER INDEX ALL ... REBUILD statement e.g:

ALTER INDEX ALL ON [dbo].[FK_example_referenced] REBUILD

GO

If the clustered index is disabled you will get an error message when you try to rebuild a non-clustered index on that table.

This is covered in the Books Online topic “Guidelines for Disabling Indexes” (which should really be called “Guidelines for Disabling Indexes and Constraints”) and it’s sister topic “Guidelines for Enabling Indexes and Constraints”

 

Leeds Area Usergroup Meeting 1st October
15 September 09 09:25 PM | MartinBell | with no comments

You may have seen announcements last week about the acquisition of the Remarc Group by QA! Remarc has been an important partner to the Leeds Area Group because they have provided the venue for our meetings; so it’s great that QA have agreed to continue to provide a venue for our meetings. Thanks must go to Chris Testa-O’Neill who negotiated our continuing use of their offices for both the Manchester and Leeds SQL Server User Group. For the full press release see Chris’ blog post

Chris will be speaking at the next meeting of the Leeds Area group on 1st October. To register for the event go to sqlserverfaq.com

To view the SQL Server courses available from QA, visit www.qa.com

Tired of changing collations!
08 September 09 08:25 PM | MartinBell | 1 comment(s)

I was looking at writing a blog post on changing collations, so the first thing I did was to restore a backup of Adventureworks to give myself something I could test on.

As you may know changing the database collation is not a simple matter and you have to cascade the change through the columns of each table yourself.

I have answered countless newsgroup posts about how to change the collation and you can easily search online to get a script of how to change each table, but the problem is not that simple. For instance if you have to change a table collation, you will first have to drop any indexes, statistics, foreign keys, primary keys that reference a collated datatype but that is not all!

If you look in Books Online for the ALTER DATABASE topic you will see other items that may cause the ALTER DATABASE to fail such as:

  • User-defined functions and views created with SCHEMABINDING.
  • Computed columns.
  • CHECK constraints.
  • Table-valued functions that return tables with character columns with collations inherited from the default database collation.

    I noticed there was already a connect item saying that CHECK constraints on non-collated datatypes should not stop you altering the collation for the database:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=384157&wa=wsignin1.0

    I created the following script to show this:

    CREATE DATABASE dbcollate COLLATE Latin1_General_CI_AS
    GO

    USE
    dbcollate
    GO

    CREATE
    TABLE dbo.tbl_collate ( id char(10) not null CONSTRAINT PK_tbl_collate PRIMARY KEY
    ,
                              numcol int not null CONSTRAINT CK_numcol CHECK (numcol > 0
    ),
                              compcol AS numcol + 10
    )
    GO

    Then when you try to change the collation:

    USE master
    GO

    ALTER
    DATABASE dbcollate COLLATE SQL_Latin1_General_CP1_CI_AS
    GO

    You get the following messages:

    Msg 5075, Level 16, State 1, Line 1
    The column 'tbl_collate.compcol' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    Msg 5075, Level 16, State 1, Line 1The object 'CK_numcol' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    Msg 5072, Level 16, State 1, Line 1

    ALTER DATABASE failed. The default collation of database 'dbcollate' cannot be set to SQL_Latin1_General_CP1_CI_AS.

    So why do computed columns and check constraints on non-collated datatypes stoping you from changing the collation?

    The reason could be that computed columns and check contraints are stored in sys.syscolumns as text, but then stored procedures and triggers are not an issue!

    USE dbcollate
    GO

    SELECT
    OBJECT_NAME(id) AS [Object], colid, text
    FROM sys.syscomments
    WHERE OBJECT_NAME(id) IN ( 'tbl_collate', 'CK_numcol' )
    GO
     

    Object  Colid Text
    CK_numcol 1 ([numcol]>(0))
    tbl_collate 1 ([numcol]+(10))

    A change from a case sensitivity and non-case sensitive collations can mean that code held as text in syscomments could fail. In that scenario invalid constraints and defaults would impact the structure of the database.
     
    Erland Sommarskog has posted a request to make changing collations easier, if like me you think this problem has lingered too long then go and vote for it on connect:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=358320

  • Filed under:
    Using SQL Server in Powershell outside of SQLPS
    06 September 09 05:54 PM | MartinBell | 3 comment(s)

    Up to now I have only shown scripts that use SQL Server’s Powershell minishell (SQLPS.exe) and not the full-blown Powershell (Powershell.exe). If you want to use the scripts I have shown so far, you will need to load the appropriate SQL Server Assemblies. Michael Worries has blogged about which assemblies should be loaded if you wish to use Powershell and SQL Server and has written an initialisation script. If you save this script and start powershell with the recommend command options:

    Powershell -NoExit -Command "& '.\Initialize-SqlPsEnvironment.ps1'"

    You may get the message:

    File C:\powershell\Initialize-SqlPsEnvironment.ps1 cannot be loaded
    because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.
    At line:1 char:2
    + &  <<<< '.\Initialize-SqlPsEnvironment.ps1'


    This is because the execution policy is set to restricted. Restricted is the default policy which doesn’t allow any downloaded configuration files or scripts to be loaded or run. There are two ways to overcome this, the one recommended in the error message is to sign the files, but the easiest way (although not necessarily recommended!) is to set the execution policy to a level which allows you to run the script. To do this run powershell as the administrator and then use the command:

    set-executionpolicy unrestricted

    The Unrestricted policy allows all configuration files to be loaded and any script to run. This obviously has security implications because anyone can then run any script. Using the RemoteSigned policy instead will allow scripts and configuration files downloaded from the internet to be run if signed by a trusted publisher.
    If you want all scripts and configuration files to be signed, use the execution AllSigned policy.

    If you don’t want to run a script file to load the snapins each time you start up powershell you can instead export the current environment with the export-console command. This allows you to specify as psconsolefile when starting up powershell e.g if the console is exported to the file SQLEnvironment.psc1 in your documents directory start powershell as:

    powershell –psconsolefile %USERPROFILE%\Documents\SQLEnvironment.psc1

    One difference between the SQLPS minishell and Powershell itself, is the limitiation SQLPS imposes on loading snapins. In the Initialize-SqlPsEnvironment.ps1 two snapins are added with the commands:

    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100

    You can see which snapins have been loaded with the get-PSSnapins command.

    Not being able to load addition snapins is a problem if you write you own snapin or want to use a snapin such as SQLPSX.  SQLPSX is written by Chad Miller and can be downloaded from codeplex and has many useful commands that will cut down the amount of code you have to write.  As this will have been downloaded from the internet you will need to unblock the scripts see Chad’s article for SQLServerCentral on how to do this.

    If you don’t want to specify command line options when starting Powershell, you can modify one of the profile files to load snapins and create functions that you wish to have for each session. There are 4 possible locations where the default profile can be changed:

    %windir%\system32\WindowsPowerShell\v1.0\profile.ps1
    This profile is run for all users and all shells (including SQLPS)

    %windir%\system32\WindowsPowerShell\v1.0\ Microsoft.PowerShell_profile.ps1
    The profile will be run for all users but only for Powershell.exe

    %UserProfile%\My Documents\WindowsPowerShell\profile.ps1
    This profile will be applied for the given user for all shells.

    %UserProfile%\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1
    This profile will be applied for the given user for Powershell.exe

    Therefore if you always want to load the SQL Server Assemblies when you run Powershell you can copy Initialize-SqlPsEnvironment.ps1 file using the commands:

    REM Make the directory
    mkdir  "%UserProfile%\My Documents\WindowsPowerShell"
    copy Initialize-SqlPsEnvironment.ps1 "%UserProfile%\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1"

    Filed under:

    This Blog

    SQL Blogs

    Syndication