Sunday, November 30, 2008

UTF8 - No support in SQL Server

For Unicode support in SQL Server. Just choose NCHAR / NVARCHAR data types.

SQL implements Unicode through UCS-2 rather than UTF8.

Wikipedia : Comparison of unicode encodings

Saturday, November 29, 2008

SQL Humour...

A SQL query goes into a bar, walks up to two tables and says, "Can I join you".

Good grief....

Sunday, November 23, 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.

Thursday, November 20, 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 :

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.

Wednesday, November 19, 2008

Windows 2008 GPT Support

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



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.

Monday, November 17, 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]

-- 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)


-- 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]

SELECT [BusinessEntityID]
FROM [AdventureWorks2008].[HumanResources].[Employee]

-- delete 50 rows
DELETE FROM [HumanResources].[Employee_Sync]

-- 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 >

Friday, November 14, 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 @totalrows = COUNT(*) FROM #tempTableList
SELECT @currentrow = 1

WHILE @currentrow <= @totalrows  
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  
SELECT * FROM #tempTableList
ORDER BY [schemaname],[tablename]

Wednesday, November 12, 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"

Finding the Fragmentation of an Index and fixing it

(Does what is says on the tin really)

Tuesday, November 11, 2008

Hyper-V VM : Removing Hidden Network Adapter!

"The IP address 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

Random Number TSQL


Sunday, November 9, 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, November 7, 2008

RDS - Remote Desktop Services

'Terminal Services' to be renamed to 'Remote Desktop Services' in Windows 2008 SP2.

Hyper-V to be used to deploy the VDI (Virtual Desktop Infrastructure) environment.

Thursday, November 6, 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...

Wednesday, November 5, 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, November 4, 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, November 3, 2008

Hyper-V : Language Setting

Note to self! : Remember to install Windows 2008 as default language (US) for Hyper-V to function.

Originally blogged it here , when first playing....

Hyper-V : Installation on Windows 2008

Sunday, November 2, 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 +']'

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