Installing and Configuring Microsoft SQL Server

With an understanding of the basic structural differences between Oracle and SQL Server, you are ready to perform the first step in the migration process. SQL Server Query Analyzer should be used to run these scripts:

1. Use Windows NT software–based RAID or hardware-based RAID level 5 to create a logical drive large enough to hold all of your data. An estimate of space can be calculated by adding the total file space used by the Oracle system, temporary, and application tablespaces.
2. Create a second logical drive for holding transaction logs by using Windows NT software–based RAID or hardware-based RAID level 1. The size of this drive should be at least as large as the sum of the size of the online redo and rollback segment tablespace(s).
3. Use SQL Server Enterprise Manager to create a database with the same name as the Oracle application’s tablespace. (The sample application uses the database name USER_DB.) Specify the file locations to coincide with the disks you created in steps 1 and 2 for the data and transaction logs, respectively. If you are using multiple Oracle tablespaces, it is not necessary or even recommended that you create multiple SQL Server databases. RAID will distribute the data for you.
4. Create the SQL Server login accounts:

   USE MASTER
EXEC SP_ADDLOGIN STUDENT_ADMIN, STUDENT_ADMIN
EXEC SP_ADDLOGIN DEPT_ADMIN, DEPT_ADMIN
EXEC SP_ADDLOGIN ENDUSER1, ENDUSER1
GO

     

5. Add the roles to the database:

   USE USER_DB
EXEC SP_ADDROLE DATA_ADMIN
EXEC SP_ADDROLE USER_LOGON
GO

     

6. Grant permissions to the roles:

   GRANT CREATE TABLE, CREATE TRIGGER, CREATE VIEW,
CREATE PROCEDURE TO DATA_ADMIN
GO

     

7. Add the login accounts as database user accounts:

   EXEC SP_ADDUSER ENDUSER1, ENDUSER1, USER_LOGON
EXEC SP_ADDUSER DEPT_ADMIN, DEPT_ADMIN, DATA_ADMIN
EXEC SP_ADDUSER STUDENT_ADMIN, STUDENT_ADMIN, DATA_ADMIN
GO

     

The illustration shows the SQL Server and Oracle environments after this process is completed.