Showing posts with label Tips. Show all posts
Showing posts with label Tips. Show all posts

Friday, 16 March 2012

Why I don’t like maintenance plans


If you create a maintenance plan (SQL 9.0.3042) to backup databases and select All databases as below;


















Then at some point in the future if one of the databases is taken offline the job created by the maintenance plan will fail.

From what I have seen the job will still backup the rest of the databases (which is the only saving grace), you just get a lot of errors in you SQL log like the below;

BACKUP failed to complete the command BACKUP DATABSE SQLServer365. Check the backup application log for detailed messages.

Shortly followed by;

BackupDiskFile::OpenMedia: Backup device 'D:\Backups\SQLServer365\ SQLServer365_backup_201203160500.bak' failed to open. Operating system error 2(error not found).

Yes I know, you should know that the database was taken offline and have amended the maintenance plan accordingly.  In my experience though I have found that people create maintenance plans as a quick temporary fix on non-critical servers  (which becomes permanent).  These servers are often forgotten about until there is a problem.

I avoid creating maintenance plans, yes they are quick and easy but are often everything or nothing.  You are much better creating maintenance routines tailored to your environment.  I am in the process of creating a maintenance routine for backups, consistency checks and index maintenance to work across all flavours of SQL Server with a whole host of configurable parameters. 

I will provide this once complete.

Chris

Tuesday, 28 February 2012

A Script A Day - Day 24 - Object Qualification

Today’s script is one I use as an example to explain that there is method behind my standards that some people initially see as madness.  I’m sure some people think I am trying to make their job harder and that I get a kick out of telling them to “go away and do it again properly”.  The answer is yes I do get a kick out of it, but not because I am telling them they are wrong because it proves that the standards in place are providing value to the business by adding a layer of protection.

Let me explain the script below.  I create two schemas, two tables (one in each schema) and populate both with one record.  I then create two logins and two users, each user has a different default schema (one of each of the schemas created earlier).  These objects are to simply support the example.

Now lets assume that SchemaBlogUser1 is a developer and SchemaBlogUser2 is a DBA the process that I am trying to demonstrate is;

Developer creates and submits a script
DBA executes the script
Problems arrise with users reporting errors in the application
DBA investiagtes the issue

What the DBA ultimately finds is that because the script the developer submitted did not qualify the table with a schema in the check or the drop statement SQL Server used the default schema of the DBA which was different to that of the developer and ultimately dropped the wrong table!

It is important to remember that standards are there to protect everyone, yes they can make life more difficult in the now, but can save a lot of time and headaches in future, and that includes DBA’s!!!

/*
      -----------------------------------------------------------------
      Object Qualification
      -----------------------------------------------------------------
     
      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.
 
      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
     
      -----------------------------------------------------------------
*/
USE SQLServer365
GO
-- Create test schemas
CREATE SCHEMA SchemaBlog1 AUTHORIZATION dbo;
GO
CREATE SCHEMA SchemaBlog2 AUTHORIZATION dbo;
GO

-- Create test tables
CREATE TABLE SchemaBlog1.Table1
(
      Table1ID INT IDENTITY (1,1),
      Column1 VARCHAR(50)
);
GO
CREATE TABLE SchemaBlog2.Table1
(
      Table1ID INT IDENTITY (1,1),
      Column1 VARCHAR(50)
);
GO

-- Insert test data
INSERT INTO SchemaBlog1.Table1
VALUES ('This table is in the SchemaBlog1 schema');
GO
INSERT INTO SchemaBlog2.Table1
VALUES ('This table is in the SchemaBlog2 schema');
GO

-- Create Logins
USE [master]
GO
CREATE LOGIN [SchemaBlogUser1] WITH PASSWORD=N'SchemaBlogUser1', DEFAULT_DATABASE=[SQLServer365], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE LOGIN [SchemaBlogUser2] WITH PASSWORD=N'SchemaBlogUser2', DEFAULT_DATABASE=[SQLServer365], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

-- Create users
USE [SQLServer365];
GO
CREATE USER [SchemaBlogUser1] FOR LOGIN [SchemaBlogUser1];
GO
ALTER USER [SchemaBlogUser1] WITH DEFAULT_SCHEMA=[SchemaBlog1];
GO
EXEC sp_addrolemember N'db_owner', N'SchemaBlogUser1';
GO
CREATE USER [SchemaBlogUser2] FOR LOGIN [SchemaBlogUser2];
GO
ALTER USER [SchemaBlogUser2] WITH DEFAULT_SCHEMA=[SchemaBlog2];
GO
EXEC sp_addrolemember N'db_owner', N'SchemaBlogUser2';
GO

-- SchemaBlogUser1 created the below script to drop table1 -- DO NOT RUN THIS PART!
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('Table1')
                    AND [type] = 'U' )
    DROP TABLE Table1;
GO

/*
      Connect to the instance of SQL Server as SchemaBlogUser2 to run the script
*/

-- Sets the execution context to SchemaBlogUser2 (to mimic the user executing the script)
EXECUTE AS USER = 'SchemaBlogUser2';
GO
-- Drop the table
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('Table1')
                    AND [type] = 'U' )
    DROP TABLE Table1;
GO

-- Revert the execution context (just to show the syntax)
REVERT;
GO

-- Set the execution context to SchemaBlogUser2 (to mimic the user executing the script)
EXECUTE AS USER = 'SchemaBlogUser2';
GO

-- Quick check of record count in SchemaBlog2.Table1
SELECT
      COUNT(*)
FROM  SchemaBlog2.Table1;
GO

/*
      Msg 208, Level 16, State 1, Line 1
      Invalid object name 'SchemaBlog2.Table1'.
*/

-- 0 Records
SELECT 
      *
FROM   
      sys.objects
WHERE  
      [object_id] = OBJECT_ID('Table1')
      AND [type] = 'U';
GO   
     
-- 1 Record
SELECT 
      *
FROM   
      sys.objects
WHERE  
      [object_id] = OBJECT_ID('SchemaBlog1.Table1')
      AND [type] = 'U';
GO
     
-- 0 Records - Wrong table has been dropped!   
SELECT 
      *
FROM   
      sys.objects
WHERE  
      [object_id] = OBJECT_ID('SchemaBlog2.Table1')
      AND [type] = 'U';
GO
     
-- The script should have read!
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('SchemaBlog1.Table1')
                    AND [type] = 'U' )
    DROP TABLE Table1;
GO

Enjoy!

Chris

Tuesday, 21 February 2012

A Script A Day - Day 13 - Chris' Top Tip - Batches

Today’s post is pretty cool.  So you have been reading up about something on the World Wide Web or you want to test something that has come to light, you create a table and want to insert a million records.  In the past I imagine some of you have used a cursor or while loop like I have, that is until I came across this.

You can specify how many executions of a batch SSMS will run by passing a number after the batch separator.  So in the example below 1 million records will be inserted into the TopTip Table!  Now SSMS highlights the 1000000 as a syntax error but it does work. 

Go on give it a try!

/*
      -----------------------------------------------------------------
      Chris’ Top Tip - Batches
      -----------------------------------------------------------------
     
      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.
 
      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
     
      -----------------------------------------------------------------
*/
-- Change database context
USE DatabaseNameHere;
GO

-- Supress messages
SET NOCOUNT ON

-- Create test table
IF NOT EXISTS(SELECT 1 FROM sys.tables where name = 'TopTip')
CREATE TABLE TopTip
(
      TopTipID INT IDENTITY (1,1),
      Col1 VARCHAR(100),
      Col2 VARCHAR(100),
      Col3 VARCHAR(100),
      Col4 VARCHAR (100),
);
GO

-- Now this is the cool bit!!!
INSERT INTO TopTip (Col1,Col2,Col3,Col4)
VALUES ('This', 'Is', 'Cool!','asaADSFHKUASASDFJ9IEW8R934HR4C398T0WMCRWPEOKCPMO3R');
GO 1000000

Enjoy!

Chris