Breaking News

Thursday, October 4, 2012

SQL Server 2008 R2 Clustering on Domain Controller using SAN Drive–Part 1/2

It been quite a while that I created a SQL Server 2003 R2 Cluster. Though of recreating it. This is considered fairly advanced topic and is related to high availability (HA), business continuity, and disaster recovery.

What you will learn?

  • How to install and configure Windows Server 2008 R2 Cluster?
  • How to install and configure Domain Controller?
  • How to install and configure Quorum Disk?
  • How to install and configure Fail Over Clustering?
  • How to install and configure SQL Server 2008 R2?

So, we will walk through following steps.

Required steps towards SQL Server 2008 R2 Cluster

  1. Build a node for Windows Server 2008 R2 Cluster with Domain Controller
  2. Install SQL Server 2008 R2 on Fail-Over (tip: Fail-back) cluster.
    1. Build two nodes (Windows Server 2008 R2)
    2. Install SP1 on both nodes
    3. Setup Active-Active Cluster
  3. Setup Cluster: SQL Server Analysis Services (SSAS)
  4. Setup Cluster: SQL Server Integration Services (SSIS)

Do you have what it takes? (read: pre-requisites)

  1. First and foremost: Bare-metal(read: physical) host machine with enough memory/space (~4GB/20GB) – works well if you’ve gotta work horse!
  2. VMWare WorkStation v7.0 or above would be great
  3. Windows Server 2008 R2, trial edition
  4. SQL Server 2008 R2 Enterprise Edition, trial
  5. StarWind Software for Cluster/Node configuration (freeware)

The above is run and test for following specs: Intel Core i7, 6GB Memory

image

What will be IP address scheme?

Lets use following IP address scheming. Note that you can use any that you want, but for the sake of simplicity and the ease of use, since we all are very well familiar with 192.168… so is one of the reason.

  1. Windows Server 2008 R2 Domain Controller:
    • IP: 192.168.1.110
    • Subnet: 225.225.225.0
    • Gateway: Switch/Router IP (Etisalat or Du if you have?)
    • DNS: 127.0.0.1
  2. Node 1: SQL Server Cluster node
    • IP: 192.168.1.111
    • Subnet: 225.225.225.0
    • Gateway: Router IP
    • DNS: 192.168.1.110
    • Private IP: 192.168.2.11
  3. Node 2: SQL Server Cluster node
    • IP: 192.168.1.112
    • Subnet: 225.225.225.0
    • Gateway: Router IP
    • DNS: 192.168.1.110
    • Private IP: 192.168.2.12
  4. Windows Cluster IP: 192.168.1.113
  5. SQL Server VIP: 192.168.1.115
  6. SQL Server VIP (Active/Active): 192.168.1.116
  7. MSDTC IP: 192.168.1.114
  8. SQL Server Analysis Services VIP: .118

Notes: Checkout my another post for quick glossary.

  • VIP, is Virtual IP, assigned to the resources within the cluster.
  • Active/Passive: Usually used for database clustering
  • Active/Active: Usually used in application servers

STEP 1: Setup Domain Controller:

Open the domain controller OS from the VM workstation.

image

image

STEP 2: Run windows update

We need to run windows update just so the operating system is up-to-date with the latest patches and service packs.

Rename computer name to something valid, for instance DC-W2008R2

image

STEP 3: Install Domain Controller

Go to run, type dcpromo.exe and enter to execute; you will see following popup.

image

Followed by following screenshots:

image

image

image

Create a new domain forest

image

Select windows 2008 R2 for the forest functional level, as shown in the figure below. Domain functionality activates features that decides the scope of function application, to the whole domain. There multiple domain functional levels (three to 4). Please note, there are cases where bringing the forest to a lower functional level from higher OS level, that is, process in irreversible. SO, you might like to do your thorough research and decision making, before you proceed with this option.

image

MSDN: What Are Active Directory Functional Levels?

STEP 4: Create a DNS Server

Check the DNS Server box. Hopefully you know that a DNS server is a naming server; and what that really means is that, it is a server that converts a domain name (google.com, microsoft.com, or izlooite.blogspot.com, etc) to actual IP address.

image

Clicking on next button will popup a confirmation windows.

image

Active Directory settings, configurations folder

image

Provide domain controller password

image

Completing DC setup

image

STEP 5: Create Zone

What is zone? Above the top, know that every domain and subdomain is considered a Zone.

Reference: Windows Server 2008 : Domain Name System and IPv6 - Understanding DNS Zones

Now we need to create a zone. Go to DNS manager and click on create new zone.

Just so if you do not know, a DNS contains list of domain names (also called DNS records), and to keep DNS records in order DNS Zone is created. It is a combination of A-MX-CNAME-NS-TXT, for instance following is a sample DNS record for my weblog:

izlooite.blogspot.com. NS dns1.somehosting.com. ; NS=Name server for izlooite

Anyway, lets open the DNS manager from Start—>Administrative Tools—>DNS

image

Now you may ask what is Forward Lookup Zone, or what is Reverse Lookup Zone; to keep it simple, just remember, when you provide a domain name to DNS it resolves the name and provides you with IP address, this is called Forward Lookup.

Conversely, when you provide IP address to find out the domain name or host name, is called Reverse Lookup Zone; and it written in reverse order. It has more into it, but for now for the sake of this article just remember,

  • Forward = Hostname to IP
  • Reverse = IP to hostname

Example: DNS reverse lookup

MSDN: Reverse Lookup

Now select Reverse Lookup Zone, right click to create a new zone. Please remember this is our Windows Server 2008R2 Domain Controller machine, on which we just installed the DC setup. You will see following screen to setup the Zone. Just click next.

image

Here we need to provide the Zone Type and the Storage, where it will keep the zone information. By default, Primary Zone is selected and Store the Zone in Active Directory option is checked, which is what we want. So just click next.

image

Next we need to select Zone Replication Scope, which means how the DNS information will be replicated throughout the network across all the server machines. We will choose the default option “domain controllers in this domain”, and click next.

image

Next choose the IP address scheming, which is IPv4 or v6. I have chosen v6 for this example, but you can go ahead and try v6 if you want. In this case it is better you have all the machines set for v6, and you wouldn’t know much difference in configuration but on the packet level 64bit IP addresses are being used. I doubt many company using v6.

image

Then, we need to provide the Zone, and for this we are using same old: 192.168.1.0…255. Notice, in the red highlighted box in figure below, how system calculated the reverse proxy as you typed in the Network ID, and also notice that it is in reverse order, as described above. Click next…

image

Next, select default option “allow secure dynamic updates” and click next, and finish.

image

image

You can right click and check the configuration settings.

image

image

Now we will add the hosts (Nod1 and Node2) in forward lookup. In the same DNS Console, right click forward lookup and select New Host, like following figure:

image

We will provide following configuration:

Hostname: node1 and IP: 192.168.1.111. If you want you can refer above to section “IP address scheme”. Just so you are aware of the scheming that I have chosen. And check the box for “host PTR record”.

image

Know that, PTR (host PTR records) records are used for the reverse DNS lookup, that is, they have your IP address and want to know the host name. Here PTR stands for pointer.

Do that for node 2 as well.

image

You will have following items available as a result: both nodes

image

Now while we added the hosts in forward lookup and checked the box for “host PTR record”. The entries will appear in the reverse lookup section as well.

image

We are done with the DNS Zone setup.

STEP 6: Create Cluster Administrator

Here we need a user (create or use an existing one) that will act as cluster administrator and will be used from other machines to bring those machines in this cluster. Hope you get the idea.

Open active directory console and add a user, say cluster-administrator, and assign the group Domain Admins.

image

image

image

image

OK, here at this point in time, you are done creating the domain controller, congratulations! (0:

STEP 7: Add Nodes to Cluster

This step is fairly simple, all you have to do is to go where you go to change the computer name, and there you have option of Workgroup or Domain. So in this case you will have to choose Domain, and provide IZLOOITE.PK there, and it will ask the domain controller administrator to login and validate.

Next Article: Part 2/2 – Completing Installation/Configuration SQL Server 2008 R2 Cluster (Active/Passive) Setup

Blogger Labels: Server,Domain,Controller,Drive,Part,Cluster,Though,topic,disaster,recovery,Windows,Quorum,Disk,Fail,Over,Build,node,Install,nodes,Setup,Active,Analysis,Services,SSAS,Integration,SSIS,requisites,Bare,machine,memory,horse,VMWare,WorkStation,edition,Enterprise,StarWind,Software,configuration,specs,Intel,Lets,Note,Subnet,Gateway,Switch,Router,Etisalat,Private,MSDTC,Notes,Checkout,glossary,Virtual,resources,Passive,database,servers,STEP,Open,system,Rename,computer,instance,Create,Select,scope,decision,option,MSDN,Directory,Functional,Levels,Check,confirmation,settings,configurations,folder,Provide,password,Above,Reference,Name,Zones,manager,Just,combination,CNAME,Anyway,Start,Administrative,Tools,Forward,Lookup,Reverse,article,Hostname,Here,Type,Storage,information,Primary,Replication,controllers,example,difference,packet,Notice,proxy,Network,Click,Console,Host,pointer,items,Administrator,user,Hope,Admins,Workgroup,IZLOOITE,Installation,sake,popup,blogspot
Read more ...

Tuesday, August 14, 2012

Best Teller Cash Recycler (TCR)–Vendor Product Analysis

Being a vendor, some time back, I had an opportunity to recommend a customer with teller cash recycler machine. The plan was to have initial sessions with the vendors, analyze the pros/cons based upon the customer requirement and then propose a vendor for proof-of-concept (POC).

Just so, it may help anyone looking for a Teller Cash Recycler (TCR) based upon various aspects, following might help.

This article is analyzes TCR machine vendors’ specifically focusing on the ease-of-integration (EOI?) of TCR software with existing enterprise application; and the ways seamless authentication could be provided.

Business requirement

Teller cash recycler is a hardware requirement, and TCR software requires integration with the centralized/unified user interface, that is, one interface for all applications.

Solution Proposal

In order to achieve the above, TCR software shall be integrated with the enterprise, more specifically the CRM application, and Agent Desktop (ADT) application.

CCA delivers contact center functionality and by combining, displaying, and manipulating data from disparate line of business applications in a single user interface. CCA provides a number of capabilities, including:

  • Integrated agent desktop
  • Scripting to eliminate duplicate data entry
  • Agent activity reporting

Some of the items that we assumed as a part of customer requirement for the enterprise integration; one of the item was that

  • .NET based UI api will be provided by TCR vendor.

How Proof of Concept activity will flow?

Following the flow of events that may be performed with TCR vendors to identify the possibility of seamless integration with the enterprise applications.

  1. User shall log in to the system using internet browser (Internet Explorer 8 above).
  2. System shall validate the user from Active Directory (LDAP)
  3. User shall perform TCR functionality (cash deposit, cash withdrawal). Note that this TCR also provides counterfeit detection besides currency counting.
  4. System shall call the TCR user interface Application Programming Interface (API) with TCR software provided credentials
  5. TCR shall authenticate the user.
  6. TCR shall provide respective User Interface in HTML format.
  7. The software will render the HTML in internet browser.

What could be test scenarios?

There could be lots, but some, above the top of my head are as follows:

  1. Perform cash deposit functionality
  2. Perform cash withdrawal
  3. System shall call the cash deposit api, along with the user id/password

Legend

Following weights are marked considering keeping in consideration integration mechanism, security authentication, maintainability, time impact, scope of work, and required changes:

  • 3, High: Highly desirable; minimal changes
  • 2, Medium; some changes, including writing code
  • 1, Low; considerable change, not desirable but may work with low level workarounds
  • 0, function not available, or not wanted.

 

Vendor 1: TALARIS - CashInsight Assure

Benchmarks

#

Category

Type

Available

Weight

Comment

1.

Architecture

Local

Yes

L

1

2.

Branch Server

Yes

M

2

3.

Centralized Server

Yes

H

3

Required: Talaris to confirm

4.

CCA

Integrated before

Yes

H

3

Un/fortunately, I don’t know much about TwinSafe, it is also not used here, but I can give you the details of how the core banking application is communicating with the Talaris device.

· There are three actors in the scenario, the corebanking UI, The CCF and the Talaris UI.

· The core banking user interface application is hosted inside the CCF and these two have their own ways of communication.

· Talaris has a java client application (user interface) and also provides a programming interface in the form of a java API.

o Since Talaris did not have a .NET version of the API, they implemented a socket server into this UI application to listen for commands to dispense or collect money.

o Talaris promised to provide the .NET version of the API if the bank would select Talaris as their cash recycling device.

· Once the business decision to dispense or collect Money is made, the core banking UI passes this information to the covering CCF application.

· The CCF application then opens a tcp connection to the Talaris UI and forms and sends the respective command

· The Talaris UI handles the request and dispenses or collects the banknotes or coins, returns the response, success or fail, along with some additional information (# of banknotes disposed each, etc.)

5.

Two teller, one machine

Two teller, one machine

Yes

H

3

6.

Independent

No

L

0

If the machine that on which, since device server is on one machine;

7.

Connectivity port

Serial port

Yes

L

1

8.

USB port

Yes

H

3

9.

OS Support

Windows 2000

Yes

M

2

10.

Windows XP

Yes

M

2

11.

Windows 2008

Yes

M

2

12.

Windows 7

Yes

H

3

13.

Database

MS SQL Server

Yes

H

3

14.

Derby

Yes

L

1

15.

File based

No

L

0

16.

Memory based

No

L

0

17.

API

User Interface API calls

Yes

H

3

18.

Low level API calls

Yes

L

1

19.

Development platform

C#

Yes

H

3

Talaris to provide C# based implementation

20.

Java

Yes

L

1

clip_image002

Any matrix available to use the default, or acceptable values in this code? is a question! that you should ask Talaris, because they did not have any answer to this. Probably they don’t have it.

Where/how to provide the password of this username?

21.

C++

Yes

L

1

Provide example

22.

Communication mechanism

TCPIP

No

L

0

23.

Web Service calls

Yes

M

2

Required: How UI will render when integrating with web service calls?

24.

DLL calls

Yes

H

3

25.

Security

SSO Integration

Yes

H

3

Available as a part of API; Is handled by CashInsight software itself

26.

Custom security

Yes

L

1

Available in the database

27.

Roles/permissions

Yes

M

2

Reside in machine

28.

Encryption

RSA

No

M

0

Required.

29.

3/DES

No

H

0

Talaris to respond on this.

30.

Reports

Transaction log

Yes

H

3

31.

Audit log

Yes

H

3

32.

Theft Proof

Velocity

Yes

H

3

A rule, to dispense the cash per minute; or do not dispense the cash of same amount

33.

Jamming

Upper/outer surface

Yes

H

3

Easy, no vendor intervention

34.

Inner surface

Yes

H

3

Difficult, requires vendor intervention

35.

Safety

Fire

Yes

H

3

36.

Water

No

H

0

Table 1: Integration benchmarks, based upon initial discussion with vendor

Result of the above table is: 67

Conclusion – Points to Ponder!

  • There are three actors in the scenario, the corebanking UI, The CCF and the Talaris UI.
  • The core banking user interface application is hosted inside the CCF and these two have their own ways of communication.
  • Talaris has a java client application (user interface) and also provides a programming interface in the form of a java API.
  • Since Talaris did not have a .NET version of the API, they implemented a socket server into this UI application to listen for commands to dispense or collect money.
  • Talaris promised to provide the .NET version of the API if the bank would select Talaris as their cash recycling device. So, if you are a customer, and looking a Talaris API port of .NET, you need to request them and they can provide you that.
  • Now it depends upon your customers’ business department’s decision on how to dispense or collect Money is made, the core banking UI passes this information to the covering CCF application.
  • The CCF application then opens a TCP(synchronous) connection to the Talaris UI and forms and sends the respective command.
  • The Talaris UI handles the request and dispenses or collects the banknotes or coins, returns the response, success or fail, along with some additional information (# of banknotes disposed each, etc.)

Impact

1. Screen shall be created to render TCR options

2. Screen shall be created to map TCP users to Back Office users

In this case, following may be required from vendor

1. .NET based C# user interface API wrapper

2. SSO connectivity samples

3. Dictionary required for: Transaction name, and currency code, etc.

4. Demo application in VM to perform code level integration

 

Vendor 2: NCR – Aptra

Benchmarks

#

Category

Type

Available

Weight

Comment

1.

Architecture

Local

L

1

2.

Branch Server

M

2

3.

Centralized Server

H

0

No centralized reporting, for instance for all of the branches

4.

CCA

Integrated before

No

H

0

5.

Two teller, one machine

Two teller, one machine

Yes

H

3

6.

Independent

Yes

L

1

7.

Connectivity port

Serial port

L

1

Serial - Not on network; requires, Drivers, and installation apps, aptra cash connect

8.

USB port

H

USB - Not on network; requires, Drivers, and installation apps, aptra cash connect

9.

OS Support

Windows 2000

Yes

M

2

10.

Windows XP

Yes

M

2

11.

Windows 2008

Yes

M

2

12.

Windows 7

Yes

H

3

13.

Database

MS SQL Server

No

H

0

14.

Derby

No

L

0

15.

File based

Yes

L

1

16.

Memory based

No

L

0

17.

API

User Interface API calls

No

H

0

18.

Low level API calls

Yes

L

1

Will require re-implementation/verification the business rules that will be added on custom user interfaces; all configuration needs to be in place

19.

Development platform

C#

Yes

H

3

Vendor to provide sample documentation

20.

Java

No

L

0

21.

C++

No

L

0

22.

Communication mechanism

TCPIP

No

L

0

23.

Web Service calls

Yes

M

2

24.

DLL calls

Yes

H

3

25.

Security

SSO Integration

Yes

H

3

26.

Custom security

Yes

L

1

27.

Roles/permissions

Yes

M

2

28.

Encryption

RSA

No

M

0

29.

3/DES

No

H

0

30.

Reports

Transaction log

Yes

H

3

Using api calls in form of delimited text; as well as management console UI

31.

Audit log

Yes

H

3

Using api calls in form of delimited text; as well as management console UI. No centralized reporting, for instance for all of the branches

32.

Theft Proof

Velocity

Yes

H

3

33.

Jamming

Upper/outer surface

Yes

H

3

34.

Inner surface

No

H

3

Requires help from vendor

35.

Safety

Fire

Yes

H

3

36.

Water

Yes

H

3

Table 2: Integration benchmarks, based upon initial discussion with vendor

Result of the above table is: 54

 

Impact

  1. Screen shall be created to render TCR options
  2. Screen shall be created to map TCP users to Back Office users

Items you may required from vendor:

  1. .NET based C# user interface API wrapper
  2. SSO connectivity samples
  3. Dictionary required for: Transaction name, and currency code, etc.
  4. Demo application in VM to perform code level integration

 

Vendor 3: Wincor Nixdorf - ProAKT Automated Teller Safe(ATS) PC/E – Manager

 

Introduction

Wincore suggested ProCash 6000xe development automatic teller safe (Cash Recycler), a PC/E Cash Recycler Manager teller safe application (for client workstations utilizing the Cash Recycler) and an approach to integrate to the proposed teller applications in the future.

 

Benchmarks

#

Category

Type

Available

Weight

Comment

1.

Architecture

Local

Yes

L

1

2.

Branch Server

Yes

M

2

3.

Centralized Server

Yes

H

3

Following architecture options were discussed:

1. Thin client - using Sunray-270 +DANCE (not sure what does it mean?) architecture + black box

2. Thin client - scenario 2: Server deployed on main server, and rest of the clients having java swing UI, connected to central server

3. Thin client - scenario 3: main server deployed on centralized data center/head office

4.

CCA

Integrated before

No

H

0

5.

Two teller, one machine

Two teller, one machine

Yes

H

3

Two tellers can work on same machine

6.

Independent

Yes

L

1

If one machine is down, then other machine can be run smoothly. It is not dependent on machine one.

7.

Connectivity port

Serial port

Yes

L

1

8.

USB port

No

H

3

9.

OS Support

Windows 2000

No

M

0

10.

Windows XP

Yes

M

2

11.

Windows 2008

No

M

0

12.

Windows 7

No

H

0

13.

Database

MS SQL Server

No

H

0

14.

Derby

No

L

0

15.

File based

No

L

0

16.

Memory based

Yes

L

1

No database?! Data is saved inside the app

17.

API

User Interface API calls

Yes

H

3

18.

Low level API calls

No

L

0

19.

Development platform

C#

Yes

H

3

A wrapper needs to be written to access java based WOSA.XFS

20.

Java

Yes

L

1

21.

C++

No

L

0

22.

Communication mechanism

TCPIP

No

L

0

23.

Web Service calls

Yes

M

2

24.

DLL calls

Yes

H

3

25.

Security

SSO Integration

Yes

H

3

26.

Custom security

Yes

L

1

27.

Roles/permissions

Yes

M

2

Custom roles/permission defined inside the machine itself.

28.

Encryption

RSA

No

M

0

29.

3/DES

No

H

0

30.

Reports

Transaction log

Yes

H

3

Reports, where are they stored: electronic journal inside machine; time open/close, denomination.

31.

Audit log

Yes

H

3

32.

Theft Proof

Velocity

Yes

H

3

Safe - out door 2 keys; door - numeric/physical lock

33.

Jamming

Upper/outer surface

Yes

H

3

34.

Inner surface

No

H

3

35.

Safety

Fire

Yes

H

3

36.

Water

No

H

0

Vendor to come back to us.

Table 3: Integration benchmarks, based upon initial discussion with vendor

Result of the above table is: 53

 

Clearly, Talaris was the way to go.

 

References

Blogger Labels: Best,Teller,Cash,Recycler,Vendor,Product,Analysis,customer,machine,sessions,vendors,pros,requirement,concept,Just,aspects,article,integration,enterprise,authentication,user,interface,Solution,Proposal,Care,Accelerator,Microsoft,Dynamics,data,capabilities,agent,Some,items,item,Proof,events,system,Internet,Explorer,Active,Directory,LDAP,Note,detection,currency,Application,credentials,HTML,scenarios,Perform,password,Legend,weights,mechanism,impact,scope,High,Medium,TALARIS,Assure,Benchmarks,Category,Type,Available,Comment,Architecture,Local,Branch,Server,TwinSafe,device,actors,scenario,communication,client,version,socket,money,Once,decision,information,connection,banknotes,response,Independent,port,Serial,Support,Windows,Database,Derby,File,Memory,Development,platform,implementation,Java,matrix,Where,Provide,example,TCPIP,Service,Custom,Roles,permissions,Reside,Encryption,Reports,Transaction,Audit,Theft,Upper,Easy,intervention,Inner,Difficult,Fire,Water,Table,discussion,Result,Conclusion,Points,Ponder,customers,department,options,users,Back,Office,wrapper,Dictionary,Demo,Aptra,instance,Drivers,installation,verification,interfaces,configuration,documentation,text,management,Requires,Wincor,Nixdorf,ProAKT,Safe,Manager,Introduction,ProCash,workstations,Thin,Sunray,DANCE,clients,tellers,WOSA,permission,journal,denomination,door,References,event,upon,software,browser,three,apps
Read more ...
Designed By Published.. Blogger Templates