.Net Oracle Connection String

with tnsname
Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;
Integrated Security=no;

common:
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));
uid=myUsername;pwd=myPassword;

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.

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.

Computer Storage

Simple to complex:

1. Directly attached disks/Storages (DAS), no RAID.

Most of home computers in home and office are this configure.

2. Directly attached  disks, but with RAID

Provide better data protection. Most of small business's servers are having this configuration.

RAID 0: Striping

RAID 1: Mirroring

RAID 5: Block-level striping with distributed parity

RAID 10: 0 + 1.

http://en.wikipedia.org/wiki/RAID

3.  File level shared storage

Network attached storage (NAS). used by home and small businesses. storage is provided by network. the NAS server has its own file systems and IP.

Accessed over network, usually IP network. OS sees NAS as file server.

4. Shared block level storage.

SAN is a type of shared block level storage. itself is a private network with storage linked with fiber channels and switches etc. it has no file systems, it's connected to computer via similar interface used in DAS, such as iSCSI. So usually a physical interface is installed onto computer to access the SAN.

Access over physical interface installed on computer, similar to network card. OS sees SAN as disk.

5. RAID vs SAN

storage exposed by SAN appears to hard drives to a computer, RAID can be built upon it.
SAN itself since composed by hard drives, can also be built upon RAID arrays.

One advantage of NAS and SAN is the storage is no longer attached to any specific computer so that you do not need to move storage from one server to another when a server has to be replaced. By sharing the storage among multiple applications, storage utilization is also supposed to be higher.

Below are useful illustrations from wiki.

Columnstore Index

If all the columns are used to build the index, this become a copy of original table with column values stored differently, column oriented instead of row oriented.

Data in table is treated as readyonly, good for data warehousing analytic queries, not good for everyday OLTP since table with columnstore index can't be updated.

For a query that already uses most of the columns in a table, performance gaining can be limited. in this case, columnstore  index's management overhead (recombining the rows etc) can be worse than the benefits it brings in.

Create an Oracle Linked Server

Using GUI, choose Oracle Provider for OLE DB, product name as OraOLEDB.Oracle(same as oracle provider for OLE DB), data source is the tnsname.

Going to Security and provide user and password in remote login section

In Server options, enable RPC and RPC out. Or for the provider itself, enable the Inprocess.

--this is an automatically generated script by SSMS
USE [master]
GO

/****** Object:  LinkedServer [ORADB1]    Script Date: 13/11/2013 2:47:47 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ORADB1', @srvproduct=N'OraOLEDB.Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'ORADB1'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ORADB1',@useself=N'False',@locallogin=NULL,@rmtuser=N'scott',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORADB1', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

SQLAgent Properties

To view its properties, use 

EXEC msdb..sp_get_sqlagent_properties

To set a SQLAgent property, use

EXEC msdb.dbo.sp_set_sqlagent_propertie @property=''

An example can be changing SQLAgent's log file directory:

EXEC msdb.dbo.sp_set_sqlagent_properties 

@errorlog_file=N'a dir\SQLAGENT.OUT'