Wednesday, February 19, 2014

Creating SQL Server 2012 Failover Cluster on Windows 12

Feb 19, 2014

This is my practice for today.

Facts:

Failover cluster provide high availability, but not scalability.
Network load balancing cluster provide both.
SQL Server failover cluster does not provide scalability.
There is only active-passive type of SQL Server cluster. NO active-active. that is talking about two clusters.misleading, unprofessional.

Impressions:


--do this on w12 the first:
dism /online /enable-feature /featurename:netfx3 /all /source:d:\sources\sxs
--It did not automatic failover until I manually make it to choose best possible owner.
--shutdown node that is currently owner, then the fail-over happened automatically

Summary:

--install iSCSI or other Shared storage solution the first.
(iSCSI san solution: iSCSI in windows 12, vitual san,freeNas,openFiler,starWind etc)
--have at least two shared disks
--install windows failover cluster
--create new SQL Server Cluster
--Add node to SQL Server Cluster

Detailed Steps:

1. Install Servers on VMWare, all with local network
1.1 DC
--name W12DC
--cluster failover feature
--DNS
-- iSCSC Target
--create two virtual disks on this server, one will be used as quorum
--join in domain
1.2 SVR1
--name: W12SVR1
--Failover cluster feature. install failover cluster on this machine.
--iSCSI initiator
1.3 SVR2
--name:W12SVR2
--faiover cluster
--iSCSI initiator
--join in domain

2.Create a windows failover cluster
add failover cluster on SVR1. inlcuding SVR2
Note: I tried to use only DC and SVR1, but failed due to iSCSI seems not working well on this configuration. Cluster on SVR1 and SVR2 is successfully. it seems you should not put iSCSI target and initiator on same node.

W12C1 is cluster name.

it's pretty quick to create the cluster on SVR1 and SVR2, while creating one for W12DC and W12SVR1 always failed.

Shared storage is automatically recognized.

3. Create SQL Server cluster
--new sql server faiover cluster installation on W12SVR1
it will fail if windows failover cluster is not installed at first place.
--I went back to windowsfaiover cluster manager to add extra disk for sql server to use because the first one is used as quarum disk.
--specify new cluster group name
--specify new network ip address for sql server cluster. used 192.168.1.11 since windows failover cluster used 192.168.1.10. want to have this defined to check ip addresses later.
--configure service account, authentication mode etc
--it failed one step to let me enable netfx3 feature and run it again.
--mount window 12 dvd, and run this to install netfx3 on both sv1 and sv2--I hated this
dism /online /enable-feature /featurename:netfx3 /all /source:d:\sources\sxs
--restart the installation -- this is why I hated lack of well-prepared releases.

4. add node to sql server failover cluster
--this node is already part of windows failover cluster environment, it discovers the previously created sql server cluster network name automatically to join in. give the node a name and join in the cluster network.
--cluster network configuration, it reuses the previously defined 1.11 address. good.
--much less steps, then start to install the software and configure it to be ready for use.

done

--During installation, I did not pay attention to how the database files are distributed since I have only one shared disk to use. But it seems installer automatically installed database onto shared storage so we do not need to bother to manually configure the system to identically use the files.

Sunday, February 16, 2014

Common Sense Failed to Work

I know unicode is supported by nvharchar and nxxx in SQL Server , but I also took it for granted that SQL Server would have an unicode collation for you to set as the database's default character set just as the other database do (Oracle can use UTF8 and AL32UTF8 as database level character set), since nowadays, these kinds of basic features are all kind of similar between major databases, until I started to think if a unicode collation is set as at database level, will it actually make no difference between vachar and nvarchar?

It turned out that I couldn't find an unicode collation for SQL Server, (somebody know one and tell me?) and unicode can only be supported by nxxx data types in SQL Server. The data in nxxx data types are in encode of UCS-2, which is very similar to UTF-16.

So for now, if you migrate an application that uses Oracle with UTF as its character set to SQL Server, you will have to define the corresponding string columns to be nxxx in order to support unicode.

Note: In recent versions of Oracle, you no longer be able to choose same character set for both varchar2 and nvarchar2.