Thursday, 19 May 2011

SQL 2008 + : Prevent saving changes that require table re-creation

Adding a new column for a clustered primary key (don’t ask..) I came up against this on my development box.



This is sensible. After all, adding a new clustered primary key is going to need the whole table to change, a potentially lengthy operation.

Generating the script for the operation proves this as it -
  1. renames the existing table
  2. creates the new one with the additional column
  3. copies the data back
(and deals with removing and recreating indexes and constraints before and after respectively)

The option to control Management Studio’s behaviour is here -

Tools -> Options > Designer > ‘Prevent saving changes that require table re-creation’


ref; Brian Knight : Sql 2008 Designer Behaviour Change

Saturday, 14 May 2011

ALTER USER ... WITH LOGIN to fix orphaned users

sp_change_users_login is deprecated.

From sql 2005 SP2, ALTER USER .... WITH LOGIN comes into play to achieve the same, i.e. remapping orphaned users to logins

ALTER USER Username WITH LOGIN = LoginName

I like to keep usernames and logins name the same where possible, hence -
ALTER USER Doermouse WITH LOGIN = Doermouse

MSDN : ALTER USER


Here is what works in SQL 2000 / 2005 -

Lists usernames that are not mapped to logins
exec sp_change_users_login 'report'

Map db username to server login if names match -
exec sp_change_users_login 'update_one', 'username'

Maps db username to server login if names match, If no login exists, it creates one with the password given.
exec sp_change_users_login 'auto_fix', 'username' , 'password'

Links -

USP_FixUsers - Works for all users in a db
USP_FixOrphans - Works for all users in all dbs on a server
Mapping SQL Server Logins to Database Users
Fix Orphaned Users SQL 2005
MSDN : Sp_change_users_login
MSDN : Deprecated Database Engine Features in SQL Server 2008 R2

Tuesday, 10 May 2011

Virtualbox : Increasing the size of a Virtualbox drive

How to increase the size of a virtualbox drive (.vdi file). -

C:\VirtualBox> \"program files"\oracle\virtualbox\vboxmanage modifyhd xppro_workbench.vdi --resize 20000 
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

C:VirtualBox>

Then have to go into VM OS and create a new partition in the space or expand a partition if the OS supports this (not the drive hosting the OS itself).

Sunday, 8 May 2011

VirtualBox : Network Types

You have 4 types of Network in VirtualBox. But how to chose?
Here are the basics...


NAT
NAT (Netork Address Translation) means the virtual machines will have private IP addresses on the virtualBox virtual network
VMs are not available from other pcs networking but can initiate connections to outside resources (servers, internet etc)

Host :

Guest :

C:\>ipconfig

Windows IP Configuration

Wireless LAN adapter Wireless Network Connection:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10
IPv4 Address. . . . . . . . . . . : 192.168.0.9
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.0.1

Ethernet adapter VirtualBox Host-Only Network:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :


C:\>ipconfig

Windows 2000 IP Configuration

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 10.0.2.15
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 10.0.2.2

C:\>
Bridged Adapter
Bridged Adapter connects virtual machines via the host network card.
They are on the same subnet and get an IP from the same router/dhcp server that the host does.
VMs are effectively on the physical network.

Host :

Guest :

C:\>ipconfig

Windows IP Configuration

Wireless LAN adapter Wireless Network Connection:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10
IPv4 Address. . . . . . . . . . . : 192.168.0.9
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.0.1

Ethernet adapter VirtualBox Host-Only Network:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :



C:\>ipconfig

Windows 2000 IP Configuration

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.0.3
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.0.1

Host-only Adapter
VirtualBox acts as the router (via adapter vboxnet0) and allocates IP addresses to VMs
By default this is a private network although you can assign a gateway (to grant internet access) making a a Bridged Adapter anyway.

Host :

Guest :

C:\>ipconfig

Windows IP Configuration

Wireless LAN adapter Wireless Network Connection:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10
IPv4 Address. . . . . . . . . . . : 192.168.0.9
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.0.1

Ethernet adapter VirtualBox Host-Only Network:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :


C:\>ipconfig

Windows 2000 IP Configuration

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.56.101
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

Internal Network
VMs can see each other, but not the host or the outside world :(

Host :

Guest :

C:\>ipconfig

Windows IP Configuration

Wireless LAN adapter Wireless Network Connection:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::8c63:1859:487c:f577%10
IPv4 Address. . . . . . . . . . . : 192.168.0.9
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.0.1

Ethernet adapter VirtualBox Host-Only Network:

Connection-specific DNS Suffix . :
Link-local IPv6 Address . . . . . : fe80::7804:1a06:c47b:f37d%17
IPv4 Address. . . . . . . . . . . : 192.168.56.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

C:\>ipconfig

Windows 2000 IP Configuration

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . :
Autoconfiguration IP Address. . . : 169.254.21.26
Subnet Mask . . . . . . . . . . . : 255.255.0.0
Default Gateway . . . . . . . . . :

Saturday, 7 May 2011

VirtualBox - Change UUID

" Cannot Register the hard disk ‘harddisk_2.vdi’ {93476107-f8f8-44ae-83b9-e6be00432ab8} because a hard disk ‘harddisk.vdi’ with UUID {93476107-f8f8-44ae-83b9-e6be00432ab8} already exists. "


You cannot just copy hard drive files and expect them to work on the same VirtualBox instance. You need to change the UUID, like this -

C:\>cd\program files\oracle\virtualbox

C:\Program Files\Oracle\VirtualBox>vboxmanage internalcommands sethduuid "c:\!rd
\virtualbox\w2000_2.vdi"
UUID changed to: cb850c97-570f-4051-bebb-92951a90e6cf

Thursday, 5 May 2011

Free Tool : FreshDiagnose

Just a quick post to share a free tool I found today.
I wanted to get a figure for disk throughput on my desktop and after browsing some tools ended up downloading FreshDiagnose. You do have to register to get an code to unlock it's functionality, but an email address and password is as much as you need to supply.

It can benchmark just about any aspect of your pc, the screenshot below shows the disk benchmarking for my laptop. It's free and I can see it being highly useful as my role increasingly requires me to look at different systems.

Saturday, 30 April 2011

Virtualbox : Copying & Converting Drive Images

VBoxManage is a command line tool that allows you to perform administrative functions on VirtualBox installations.
To create a copy of a drive, the command is -
 VBoxManage clonehd sourcefile targetfile . e.g Vboxmanage winxp.vdi winxpcopy.vdi

To change the format of the target, add the format parameter -

VBoxManage clonehd sourcefile targetfile --format VDI e.g.

VBoxManage clonehd c:\!RD\Virtualbox\w2003.vmdk w2003.vdi --format VDI
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Clone hard disk created in format 'VDI'. UUID: 6a1ded22-edb4-4cef-b74a-871db2fcafd6

If you want to increase the logical size of an expanding volume, the following post shows how to use VBoxManage to create a larger drive.