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

http://journeymanjournal.blogspot.com/2005/02/no-utf-8-support-in-sql-server.html

SQL Humour...

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

Good grief....

http://sql-server-performance.com/Community/blogs/editorsblog/archive/2008/11/26/happy-thanksgiving.aspx

SPAM is on the increase...

http://www.vnunet.com/vnunet/news/2231392/spam-levels-expected-rise

I’ve definitely observed an increase...

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

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

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

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

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]

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)

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

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. :/

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.

http://4sysops.com/archives/remote-desktop-services-windows-server-2008-r2-will-support-virtual-desktop-infrastructure-vdi/

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

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.

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.

Hyper-V : Language Setting

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

http://www.servercare.nl/Lists/Posts/Post.aspx?ID=17

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

Hyper-V : Installation on Windows 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