Export (0) Print
Expand All

Microsoft SQL Server 2000 for the Oracle Professional

Updated : August 22, 2001

Abstract: This article explains the differences in architecture between Microsoft® SQL Server™ 2000 database and Oracle databases. This document is intended for Oracle professionals preparing a database for migration to SQL Server 2000.

On This Page

Definition of Database
Database System Catalogs
Physical and Logical Storage Structures
Striping Data
Transaction Logs and Automatic Recovery
Backing Up and Restoring Data
Networks
Database Security and Roles
Conclusion
Finding More Information

Definition of Database

In Oracle, a database refers to the entire Oracle RDBMS environment and includes these components:

  • Oracle database processes and buffers (instance).

  • SYSTEM tablespace containing one centralized system catalog, which is made up of one or more datafiles.

  • Other tablespaces as defined by the database administrator (DBA) (optional), each made up of one or more datafiles.

  • Two or more online Redo Logs.

  • Archived Redo Logs (optional).

  • Miscellaneous files (control file, Init.ora, Config.ora, and so on).

A Microsoft® SQL Server™ 2000 database provides a logical separation of data, applications, and security mechanisms. An installation (instance) of SQL Server can support multiple databases. Applications built using SQL Server can use databases to logically divide business functionality. There can be multiple instances of SQL Server on a single computer. Each instance of SQL Server can have multiple databases.

Each SQL Server database can support filegroups, which provide the ability to physically distribute the placement of the data. A SQL Server filegroup categorizes the operating-system files containing data from a single SQL Server database to simplify database administration tasks, such as backing up. A filegroup is a property of a SQL Server database and cannot contain the operating-system files of more than one database, although a single database can contain more than one filegroup. After database creation, filegroups can be added to the database.

Cc966514.sqlpro01(en-us,TechNet.10).gif

Microsoft SQL Server also installs the following databases by default:

  • The model database is a template for all newly created user databases.

  • The tempdb database is similar to an Oracle temporary tablespace in that it is used for temporary working storage and sort operations. Unlike the Oracle temporary tablespace, users can create temporary tables that are automatically dropped when the user logs off.

  • The msdb database supports the SQL Server Agent and its scheduled jobs, alerts, and replication information.

  • The pubs and Northwind databases are provided as sample databases for training.

For more information about the default databases, see SQL Server Books Online.

Database System Catalogs

Each Oracle database runs on one centralized system catalog, or data dictionary, which resides in the SYSTEM tablespace. Each Microsoft SQL Server 2000 database maintains its own system catalog, which contains information about:

  • Database objects (tables, indexes, stored procedures, views, triggers, and so on).

  • Constraints.

  • Users and permissions.

  • User-defined data types.

  • Replication definitions.

  • Files used by the database.

SQL Server also contains a centralized system catalog in the master database, which contains system catalogs as well as some information about the individual databases, such as:

  • Database names and the primary file location for each database.

  • SQL Server login accounts.

  • System messages.

  • Database configuration values.

  • Remote and/or linked servers.

  • Current activity information.

  • System stored procedures.

Like the SYSTEM tablespace in Oracle, the SQL Server master database must be available to access any other database. As such, it is important to protect against failures by backing up the master database after any significant changes are made in the database. Database administrators can also mirror the files that make up the master database.

For a detailed list of the system tables contained in the master database and all other databases, see "System Tables" in SQL Server Books Online.

Physical and Logical Storage Structures

The Oracle RDBMS is comprised of tablespaces, which in turn are comprised of datafiles. Tablespace datafiles are formatted into internal units called blocks. The block size is set by the DBA when the Oracle database is first created. When an object is created in an Oracle tablespace, the user can specify its space in units called extents (initial extent, next extent, min extents, and max extents). If no extent size is explicitly defined, a default extent is created. An Oracle extent varies in size and must contain a chain of at least five contiguous blocks.

Microsoft SQL Server 2000 uses filegroups at the database level to control the physical placement of tables and indexes. Filegroups are logical containers of one or more files, and data contained within a filegroup is proportionally filled across all files belonging to the filegroup.

If filegroups are not defined and used, database objects are placed in a default filegroup that is implicitly defined during the creation of a database. Filegroups allow you to:

  • Distribute large tables across multiple files to improve I/O throughput.

  • Store indexes on files different from their respective tables, again to improve I/O throughput and disk concurrency.

  • Store text, ntext, and image columns (large objects) on separate files from the table.

  • Place database objects on specific disk spindles.

  • Back up and restore individual tables or sets of tables within a filegroup.

SQL Server formats files into internal units called pages. The page size is fixed at 8,192 bytes (8 KB). Pages are organized into extents that are fixed in size at 8 contiguous pages. When a table or index is created in a SQL Server database, it is automatically allocated one page within an extent. As a table or index grows, it is automatically allocated it's own extent by SQL Server. This allows for more efficient storage of smaller tables and indexes when compared to allocating an entire extent as in Oracle.

For more information, see "Physical Database Architecture" in SQL Server Books Online.

Striping Data

Oracle-type segments are not needed for most Microsoft SQL Server installations. Instead, SQL Server can distribute, or stripe, data more efficiently with hardware-based RAID or with software–based RAID available through the Windows NT Disk Administrator utility or from third parties. With RAID, you can set up stripe sets consisting of multiple disk drives that appear as one logical drive. If database files are created on this stripe set, the disk subsystem assumes responsibility for distributing I/O load across multiple disks. It is recommended that administrators spread out the data over multiple physical disks using RAID.

The recommended RAID configuration for SQL Server is RAID 1 (mirroring) or RAID 5 (stripe sets with an extra parity drive, for redundancy). RAID 10 (mirroring of striped sets with parity) is also recommended, but is much more expensive than the first two options. Stripe sets work very well to spread out the usually random I/O done on database files.

If RAID is not an option, filegroups are an attractive alternative and provide some of the same benefits available with RAID. Additionally, for very large databases that might span multiple physical RAID arrays, filegroups may be an attractive way to further distribute your I/O across multiple RAID arrays in a controlled fashion.

Transaction log files must be optimized for sequential I/O and must be secured against a single point of failure. Accordingly, RAID 1 (mirroring) is recommended for transaction logs. When migrating, the size of this drive should be at least as large as the sum of the size of the Oracle online redo logs and the Oracle rollback segment tablespace(s). Create one or more log files that take up all the space defined on the logical drive. Unlike data stored in filegroups, transaction log entries are always written sequentially and are not proportionally filled.

For more information about RAID, see SQL Server Books Online, your Windows NT Server documentation, and the Microsoft Windows NT Resource Kit.

Transaction Logs and Automatic Recovery

The Oracle RDBMS performs automatic recovery each time it is started. It verifies that the contents of the tablespace files are coordinated with the contents of the online redo log files. If they are not, Oracle applies the contents of the online redo log files to the tablespace files (roll forward), and then removes any uncommitted transactions that are found in the rollback segments (roll back). If Oracle cannot obtain the information it requires from the online redo log files, it consults the archived redo log files.

Microsoft SQL Server 2000 also performs automatic data recovery by checking each database in the system each time it is started. It first checks the master database and then launches threads to recover all of the other databases in the system. For each SQL Server database, the automatic recovery mechanism checks the transaction log. If the transaction log contains any uncommitted transactions, the transactions are rolled back. The recovery mechanism then checks the transaction log for committed transactions that have not yet been written out to the database. If it finds any, it performs those transactions again, rolling forward.

Each SQL Server transaction log has the combined functionality of an Oracle rollback segment and an Oracle online redo log. Each database has its own transaction log that records all changes to the database and is shared by all users of that database. When a transaction begins and a data modification occurs, a BEGIN TRANSACTION event (as well as the modification event) is recorded in the log. This event is used during automatic recovery to determine the starting point of a transaction. As each data modification statement is received, the changes are written to the transaction log prior to being written to the database itself. SQL Server has an automatic checkpoint mechanism that ensures completed transactions are regularly written from the SQL Server disk cache to the transaction log file. A checkpoint writes any cached page that has been modified since the last checkpoint to the database. Checkpointing these cached pages, known as dirty pages, onto the database, ensures that all completed transactions are written out to disk. This process shortens the time that it takes to recover from a system failure, such as a power outage. This setting can be changed by modifying the recovery interval setting by using SQL Server Enterprise Manager or the Transact-SQL sp_configure system stored procedure.

Backing Up and Restoring Data

Microsoft SQL Server 2000 offers several options for backing up data:

Full database backup

A database backup makes a copy of the full database. Not all pages are copied to the backup set, only those actually containing data. Both data pages and transaction log pages are copied to the backup set.

A database backup set is used to re-create the database as it was at the time the BACKUP statement completed. If only database backups exist for a database, it can be recovered only to the time of the last database backup taken before the failure of the server or database. To make a full database backup, use the BACKUP DATABASE statement or the Backup Wizard.

Differential backup

After a full database backup, regularly back up just the changed data and index pages using the BACKUP DATABASE WITH DIFFERENTIAL statement or the Backup Wizard.

Transaction log backup

Transaction logs in Microsoft SQL Server are associated with individual databases. The transaction log fills until it is backed up or truncated. The default configuration of SQL Server 2000 is for the transaction log to grow automatically until it uses all available disk space or it meets its maximum configured size. When a transaction log gets too full, it can create an error and prevent further data modifications until it is backed up or truncated. Other databases are not affected. Transaction logs can be backed up using the BACKUP LOG statement or the Backup Wizard.

File backup, filegroup backup

A file or filegroup backup copies one or more files of a specified database, allowing a database to be backed up in smaller units: at the file or filegroup level. For more information, see SQL Server Books Online.

Backups can be performed while the database is in use, allowing backups to be made of systems that must run continually. The backup processing and internal data structures of SQL Server maximize their rate of data transfer with minimal effect on transaction throughput.

Both Oracle and SQL Server require a specific format for log files. In SQL Server, these files, called backup devices, are created using SQL Server Enterprise Manager, the Transact-SQL sp_addumpdevice stored procedure, or the equivalent SQL-DMO command.

Although backups can be performed manually, it is recommended that you use SQL Server Enterprise Manager and/or the Database Maintenance Plan Wizard to schedule periodic backups, or backups based on database activity.

A database can be restored to a certain point in time by applying transaction log backups and/or differential backups to a full database backup (device). A database restore overwrites the data with the information contained in the backups. Restores can be performed using SQL Server Enterprise Manager, Transact-SQL (RESTORE DATABASE), or SQL-DMO.

Just as you can override automatic backups in Oracle, in Microsoft SQL Server, members of the db_owner fixed database role can force the transaction log to erase its contents every time a checkpoint occurs. This can be accomplished by using SQL Server Enterprise Manager (set the Recovery Model to Simple), Transact-SQL (ALTER DATABASE), or SQL-DMO.

Networks

Oracle SQL*Net supports networked connections between Oracle database servers and their clients. It communicates with the Transparent Network Substrate (TNS) data stream protocol, and allows users to run many different network protocols without writing specialized code.

With Microsoft SQL Server, Net-Libraries (network libraries) support the networked connections between the clients and the server by using the Tabular Data Stream (TDS) protocol. They enable simultaneous connections from clients running Named Pipes, TCP/IP Sockets, or other Inter-Process Communication (IPC) mechanisms. The SQL Server 2000 CD-ROM includes all client Net-Libraries so there is no need to acquire them separately.

Cc966514.sqlpro02(en-us,TechNet.10).gif

SQL Server Net-Library options can be changed after installation. The Client Network utility configures the default Net-Library and server connection information for a client running the Microsoft Windows® 2000, Microsoft Windows NT® 4.0, Microsoft Windows 95, or Microsoft Windows 98 operating systems. All ODBC client applications use the same default Net-Library and server connection information, unless it is changed during ODBC data source setup or explicitly coded in the ODBC connection string. For more information about Net-Libraries, see SQL Server Books Online.

Database Security and Roles

To adequately migrate your Oracle applications to Microsoft SQL Server 2000, you must understand how SQL Server implements database security and roles.

Database File Encryption

Microsoft Windows 2000 allows users to encrypt files using the Encrypting File System (EFS). SQL Server 2000 is enabled to make use EFS. The database files can be encrypted, preventing other users from moving, copying or viewing their contents. This encryption is done on the operating-system level, not the logical-database level. Once SQL Server opens the encrypted file, the data within the file appears as unencrypted.

Network Security

Microsoft SQL Server 2000 supports the use of the Secure Sockets Layer (SSL) to encrypt network communications between itself and clients. This encryption applies to all inter-computer protocols supported by SQL Server 2000 and is either 40 or 128-bit depending on the version of the Microsoft Windows operating system that SQL Server is running on.

For more information, see "Net-Library Encryption" in SQL Server Books Online.

Login Accounts

A login account allows a user to access SQL Server data or administrative options. The guest login account allows users to log in to SQL Server and only view databases that allow guest access. (The guest account is not set up by default and must be created.)

A login account allows a user to administer or access data in an instance of SQL Server 2000. SQL Server 2000 uses two different methods to authenticate logins:

Windows Authentication

A DBA specifies which Windows login accounts can be used to connect to an instance of SQL Server 2000. Users logged in to Windows using these accounts can connect to SQL Server 2000 without having to specify a separate database login and password. When using Windows Authentication, SQL Server 2000 uses the security mechanisms of Windows NT 4.0 or Windows 2000 to validate login connections, and relies on a user's Windows security credentials. Users do not need to enter login IDs or passwords for SQL Server 2000 because their login information is taken directly from the trusted network connection. This functions like the IDENTIFIED EXTERNALLY option associated with Oracle user accounts.

SQL Server Authentication

A DBA defines a separate database login account. A user must specify this login account and its password when they attempt to connect to SQL Server 2000. The database login is not related to the user's Windows login. This functions like the IDENTIFIED BY PASSWORD option associated with Oracle user accounts.

Each instance of SQL Server 2000 is run in one of two authentication modes:

  1. Windows Authentication Mode (known as integrated security in earlier versions of SQL Server). In this mode, SQL Server 2000 allows only connections that use Windows Authentication.

  2. Mixed Mode. In this mode, connections can be made using either Windows Authentication or SQL Server Authentication.

For more information about these security mechanisms, see SQL Server Books Online.

Groups, Roles, and Permissions

SQL Server and Oracle use permissions to enforce database security. SQL Server statement-level permissions are used to restrict the ability to create new database objects (similar to the Oracle system-level permissions).

SQL Server also offers object-level permissions. As in Oracle, object-level ownership is assigned to the creator of the object and cannot be transferred. Object-level permissions must be granted to other database users before they can access the object. Members of the sysadmin fixed server role, db_owner fixed database role, or db_securityadmin fixed database role can also grant permissions on one user's objects to other users.

SQL Server statement- and object-level permissions can be granted directly to database user accounts. However, it is often easier to administer permissions to database roles. SQL Server roles are used for granting and revoking privileges to groups of database users (much like Oracle roles). Roles are database objects associated with a specific database. There are a few specific fixed server roles associated with each installation, which work across databases. An example of a fixed server role is sysadmin. Windows groups can also be added as SQL Server logins, as well as database users. Permissions can be granted to a Windows group or a Windows user.

A database can have any number of roles or Windows groups. The default role public is always found in every database and cannot be removed. The public role functions much like the PUBLIC account in Oracle. Each database user is always a member of the public role. A database user can be a member of any number of roles in addition to the public role. A Windows user or group can also be a member of any number of roles, and is also always in the public role.

Groups, Roles, and Permissions

Microsoft SQL Server and Oracle use permissions to enforce database security. SQL Server statement-level permissions are used to restrict the ability to create new database objects (similar to the Oracle system-level permissions).

SQL Server also offers object-level permissions. As in Oracle, object-level ownership is assigned to the creator of the object and cannot be transferred. Object-level permissions must be granted to other database users before they can access the object. Members of the sysadmin fixed server role, db_owner fixed database role, or db_securityadmin fixed database role can also grant permissions on one user's objects to other users.

SQL Server statement- and object-level permissions can be granted directly to database user accounts. However, it is often simpler to administer permissions to database roles. SQL Server roles are used for granting and revoking privileges to groups of database users (much like Oracle roles). Roles are database objects associated with a specific database. There are a few specific fixed server roles associated with each installation, which work across databases. An example of a fixed server role is sysadmin. Windows NT groups can also be added as SQL Server logins, as well as database users. Permissions can be granted to a Windows NT group or a Windows NT user.

A database can have any number of roles or Windows NT groups. The default role public is always found in every database and cannot be removed. The public role functions much like the PUBLIC account in Oracle. Each database user is always a member of the public role. A database user can be a member of any number of roles in addition to the public role. A Windows NT user or group can also be a member of any number of roles, and is also always in the public role.

Database Users and the guest Account

In Microsoft SQL Server, a user login account must be authorized to use a database and its objects. One of the following methods can be used by a login account to access a database:

  • The login account can be specified as a database user.

  • The login account can use a guest account in the database.

  • A Windows NT group login can be mapped to a database role. Individual Windows NT accounts that are members of that group can then connect to the database.

Members of the db_owner or db_accessadmin roles, or the sysadmin fixed server role, create the database user account roles. An account can include several parameters: the SQL Server login ID, database user name (optional), and up to one role name (optional). The database user name does not have to be the same as the user's login ID. If a database user name is not provided, the user's login ID and database user name are identical. If a role name is not provided, the database user is only a member of the public role. After creating the database user, the user can be assigned to as many roles as necessary.

Members of the db_owner or db_accessadmin roles can also create a guest account. The guest account allows any valid SQL Server login account to access a database even without a database user account. By default, the guest account inherits any privileges that have been assigned to the public role; however, these privileges can be changed to be greater or less than that of the public role.

A Windows NT user account or group account can be granted access to a database, just as a SQL Server login can. When a Windows NT user who is a member in a group connects to the database, the user receives the permissions assigned to the Windows NT group. If a member of more than one Windows NT group that has been granted access to the database, the user receives the combined rights of all of the groups to which he belongs.

The sysadmin Role

Members of the Microsoft SQL Server sysadmin fixed server role have similar permissions to that of an Oracle DBA. In SQL Server, the sa SQL Server Authentication Mode login account is a member of this role by default, as are members of the local Administrators group if SQL Server is installed on a Windows 2000–based computer. A member of the sysadmin role can add or remove Windows users and groups, as well as SQL Server logins. Members of this role typically have the following responsibilities:

  • Installing SQL Server.

  • Configuring servers and clients.

  • Creating databases.*

  • Establishing login rights and user permissions.*

  • Transferring data in and out of SQL Server databases.*

  • Backing up and restoring databases.*

  • Implementing and maintaining replication.

  • Scheduling unattended operations.*

  • Monitoring and tuning SQL Server performance.*

  • Diagnosing system problems.

The tasks designated with an asterisk (*) can be delegated to other security roles or users.

A member of the sysadmin fixed server role can access any database and all of the objects (including data) on a particular instance of SQL Server. Like an Oracle DBA, there are several commands and system procedures that only members of the sysadmin role can issue.

The db_owner Role

Although a Microsoft SQL Server database is similar to an Oracle tablespace in use, it is administered differently. Each SQL Server database is a self-contained administrative domain. Each database is assigned a database owner (dbo). This user is always a member of the db_owner fixed database role. Other users can also be members of the db_owner role. Any user who is a member of this role can manage the administrative tasks related to her database (unlike Oracle, in which one DBA manages the administrative tasks for all tablespaces). These administrative tasks include:

  • Managing database access.

  • Changing database options (read-only, single user, and so on).

  • Backing up and restoring the database contents.

  • Granting and revoking database permissions.

  • Creating and dropping database objects.

Members of the db_owner role have permissions to do anything within their database. Most rights assigned to this role are separated into several fixed database roles, or can be granted to database users. It is not necessary to have sysadmin server-wide privileges to have db_owner privileges in a database.

Conclusion

Microsoft SQL Server 2000 delivers business value measured by a low total cost of ownership, ease of use (wizards, tuning tools, and automated tuning features), as well as integrated data warehousing capabilities. SQL Server 2000 has garnered some of the highest commendations and awards available from industry reviewers, and has demonstrated its ability to meet the demands of leading enterprises and electronic commerce sites.

This paper has reviewed many of the architectural differences between Oracle and Microsoft SQL Server, and has explained some of the differences in the terminology used to describe Oracle and SQL Server. With proper planning and familiarization with SQL Server, you can migrate your Oracle application and begin to enjoy the benefits of SQL Server 2000.

Finding More Information

For more information about Microsoft SQL Server 2000, see the following resources:

SQL Server Books Online for SQL Server 2000

This is the online documentation for SQL Server 2000. SQL Server Books Online is installed by default when you install any edition of SQL Server 2000. To view SQL Server Books Online, in the Microsoft SQL Server program group, click Books Online. You can opt to install just SQL Server Books Online from the SQL Server 2000 compact disc.

SQL Server 2000 Resource Kit

The SQL Server 2000 Resource Kit will be available April 2001 from Microsoft Press®. The resource kit will contain information about migrating Oracle8i applications to SQL Server 2000.

MSDN® developer program

The Microsoft Developers Network provides a wealth of information, including the latest technical documents, service packs and articles for Microsoft development environments. For more information, see http://msdn2.microsoft.com/sqlserver/default.aspx.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft