AdventureWorks 2000 and SQL Server 2005

Wow !!! It was Lewis Hamilton in India – On hearing the news I almost thought he was in india for the inauguration of our formula one circuit at Delhi or Hyderabad. But later heard that it was for a commercial deal. Ok Friends, Here I am today trying to explain some of the activities that needs to be performed for attaching the AdventureWorks2000 database to SQL Server 2005.

What is AdventureWorks Database? – Microsoft provides a sample database for demonstration of various functional as well as technical aspects of a business need. An excerpt from microsoft website reads ‘The purpose of AdventureWorks Databases is to demonstrate best practices for designing an integrated enterprise-class schema, and provide a sample database for that schema’. This database is not automatically installed with SQL Server Express.

Now, having known little about what AdventureWorks is, let us get into the details of downloading the database and attaching it to the SQL Server 2005.

By accessing the microsoft download center, download Adventure Works Database 2000.msi and install.Once installed, the file AdventureWorks2000_Data.mdf gets loaded into the specific MSSQL folder within the program files whose path could always be found by a windows search. Having performed this activity, one could follow the procedure present in microsoft’s msdn page. The very first issue when executing the command SQLCMD -S (local)\sqlexpress -E in the Visual Studio 2008 command prompt is unable to remote in to the server error. Inorder to enable the remote connectivity, we need to perform the setup in SQL Server 2005 Surface Area Configuration. After the surface area configurations for services and connections are setup with the help of microsoft’s support, we would be able to attach the Adventureworks 2000 database for our testing and learning process. And always the best way to test the above activity is to verify whether the database is attached or not.

For all the people who are new to MSSQL, Hope this gives you an heads up on what AdventureWorks 2000 is ? and how it could be installed in SQL Server 2005 ?.

Read More: SQL Server 2005

Part II – DBA Refresher for Peoplesoft Database Cloning

Please read the Instant Peoplesoft Cloning Refresher Kit before continuing in this post,

Take care to remove the reuse word in the create control file statement and replace it with SET

Once the Control file is created

alter database open resetlogs

If you encounter problems,

login to command prompt,

sqlplus /nolog
connect sys/manager as sysdba
shutdown immediate
startup
exit

select * from psdbowner;

update PSDBOWNER SET DBNAME = ‘destination db name’

create user sysadm identified by <password> default tablespace <tablespacename> temporary tablespace <tablespacename>

Create user PS identified by <password> default tablespace <tablespacename> temporary tablespace <tablespacename>

Create user PEOPLE identified by <password> default tablespace <tablespacename> temporary tablespace <tablespacename>

Grant connect, resource, dba to sysadm

Grant connect, resource to ps

Grant create session to people

And after revisiting all the above points, you would be able to access application designer after configurations in configuration manager.

Read About: DBA Refresher

Part II – Peoplesoft and Microsoft Active Directory Single Sign On Setup

Hip Hip Hurray !!!! 2 Lewis Hamilton Fans !!!!!

Its a dream run on monaco for his team. And it is also time for us to hit the fast track to part 2 of the single sign on between Microsoft and Peoplesoft,

Steps to perform on LDAP:

1. Access ldp.exe from within the Microsoft Active Directory

2. Connect to the LDAP server with the default port 389 (click on Connection->connect)

3. Click connection-> bind and enter the user account name and password

4. Click View->TreeView and enter the Base DN (DC=Helix,DC=Hexaware,DC=com)

5. Select the User Group present with the ldap, CN=Users, DC=Helix, DC=Hexaware, DC=com

6. Select the administrator listed down under the selected user group (e.g CN = Hexaware Admin, CN=Users, DC=Helix, DC=Hexaware, DC=com)

Sample Environment Used: DNS- Helix.Hexaware.com

Steps to do on Peoplesoft:

1. Navigate to PeopleTools > Security > Directory > Configure Directory

2. Create a New Directory ID

3. Select the directory product as ‘Microsoft Active Directory’

4. Enter the default connect DN (The steps are listed on the above section, e.g CN = Hexaware Admin, CN=Users, DC=Helix, DC=Hexaware, DC=com)

5. Enter the administrator password for the administrator user id ‘Hadminis’

6. Enter the LDAP Server Name and the default port 389

7. In the Additional Connect DN’s tab, enter the User DN “Helix\Hadminis” and its password

8. Now test the connectivity to get the success on all the sub sections.

Most Common Errors:
===================

1. User who is setting up the single sign on should be aware of the difference between the Administrator User ID and Its Canaonical Name

2. In Connectivity Failure – Use Network Analyzer Tool ( I will bring in more details in my next post)

Hope the start off steps helps ,.. Await more on Peoplesoft Schema Caching in pitstop’s next post.

Thanks For Give Time To Read This Blog. Read About: Peoplesoft and Microsoft Active Directory

Web Services – The Fuel For Service Oriented Architecture (SOA)

As we are eagerly awaiting the Montreal fest this weekend and with surge in fuel prices, its right time to discuss the driving force behind SOA.

Web Services are the core to a SOA System and they are the driving factor for a lot of present day state of the art systems.Days are gone by where a small piece of change request would require 2 to 4 months of work on the legacy systems with application service provider ASP.
It all depends on the compatibility factor, say for example we need to leverage the functionality of a JCL or a COBOL program to be used in a Visual Basic or AJAX application. It would sound complicated as the solution was designed with a lot of interfaces for the Microsoft or Unix environment; It was really a hard job to perform a couple of years ago. But now It’s web services, a cheat code to a hard to win game. Web Services are a bunch of code within a system which is modular and acts as a plug and play for the users who require a specific service.

By now, web services are far capable, far scalable, far reusable, far accessible and far user friendly to the users. Lets see how Peoplesoft had mastered the Service Oriented Architecture and the various chunks like

• Web Services Creation
• Web Services Publishing, and
• Standards and Technology Behind Web Services

in the following posts. Integration between Peoplesoft and Java Based applications as well as Peoplesoft and Microsoft Sharepoint Services are basically driven by SOA.

Kronos Data Collection Manager – DCM

Be it a Software Industry or Hardware Industry, wherever there is a considerable amount of workforce involved, Time and Attendance plays an important role. Especially, the automation of the time and attendance process. Its the Kronos Workforce Timekeeper that tops the list on the day of chequered flag. Lets see how Kronos does it ,..

Having known that Kronos Workforce Timekeeper is used for processing the time and attendance Data of the workforce in an organization, Lets concentrate on the “Data Collection Manager” of kronos. DCM is a set of applications that enables the interaction between data collection devices or clocks and a host application like kronos workforce timekeeper.

DCM constitutes the

• Communication Monitor,
• Device Manager,
• Device Work Rules,
• Event Manager, and
• Sentinel Service applications.

Communication Monitor: When an employee swipes in the access card to enter his/her workplace, the exact time is captured/collected in a collection device such as clocks, biometric devices,.. The collected information needs to reach the host application for further processing, If there is a data flow error or when a collection device is down, then we use the communication monitor to test device communications and its transactions.

Device Manager: is used to check the status of the collection devices and to configure the device firmware. Start and Stop services of the data collection devices is one of the frequently used features of the device manager during the configuration and re-initialization of the device.

Lets see more about pay rule, work rule, their associations and few interesting facts about event manager and sentinel service in the next post.

**DCM – Data Collection Manager
**DCD – Data Collection Devices

To Know More Vistit: Kronos