Oracle to SQL Server Migration

Acronym

Details

Feature ID

Sequence ID used in the scanning tool

Feature Enablement

This SQL script to determine if a feature is enabled (utilized) in the database.

Effort Score

Effort Score in the scale of 1-10 (1 represent low effort and 10 high). The scale is used to understand the level of complexity for the feature.

Category

Classification of the features

Feature ID

Feature

Category

Notes

1

Database Audit

Security

2

Security

Security

3

Automated Maintenance Tasks

Admin

4

Database Size

Performance

5

Used Size

Performance

6

Database Mail

Admin

7

Collation

SQL

8

Authentication

Security

9

Database Version

Admin

10

Data Encryption

Security

11

Global Temporary Tables

Admin

12

Table Partitioning

Admin

13

Database Replication

Admin

14

Database Mirroring

Admin

15

Stored Procedures

SQL

16

Memory Usage

Performance

17

CPU Resources Usage

Performance

18

Shut down

Admin

19

Data Concurrency and Consistency

Performance

20

Views

SQL

21

Indexing

SQL

22

Queries

SQL

23

Database Backup

Admin

24

Database Restore

Admin

25

Log Files

Admin

26

Trace Files

Admin

27

Regular Expressions

SQL

28

Constraints

SQL

29

DML

SQL

30

Data Types

SQL

31

DDL

SQL

32

Joins and Operations

SQL

33

NoSQL Features

General

34

Set Schema

General

35

Oracle licensing components

General

36

Logins

Security

37

Triggers

SQL

38

Data Dictionary

SQL

39

Privileges

Security

40

Access Control

Security

41

Log Shipping

Admin

42

Tables

SQL

43

Data Navigator

Tools

44

SQL Book Marks

Tools

45

Charts

General

46

Favorites

Tools

47

Monitor

Tools

48

Compare

General

49

Cluster

SQL

50

Column-level check constraint

SQL

51

Packages

SQL

52

Synonyms

SQL

53

Sequences

SQL

54

Snapshot

SQL

55

Operators

SQL

56

Built-in-Functions

SQL

57

Locking Concepts and Data Concurrency Issues

SQL

58

Change data capture

SQL

59

Data Collector

Tools

60

Database Logs

General

61

File Groups

SQL

62

Text Search

SQL

63

Functions

SQL

64

Linked Server instances

General

65

Service Broker

Admin

66

Processes and Threads

General

67

High availability

General

68

Scalability

Admin

69

In-Memory optimization

General

70

Connection multiplexing

Admin

71

Connection Pooling

Admin

72

Database Queuing

Admin

73

Incremental backup and recovery

Admin

74

Instead of Triggers

SQL

75

Parallel load

General

76

Sample scan

SQL

77

Transparent Application Failover 

SQL

78

Fast-start fault recovery

General

79

SQL Optimizer Plan Stability (Stored Outlines)

Admin

80

Online index rebuilds

SQL

81

Export transportable tablespaces 

SQL

82

Materialized Views

SQL

83

Bitmap indexes

General

84

Oracle Parallel Query (OPQ)

SQL

85

Parallel DML

SQL

86

Parallel index rebuilding

SQL

87

Parallel index scans

SQL

88

Parallel backup & recovery

Admin

89

Oracle connection manager (CMAN)

General

90

Oracle Streams

General

91

Function-based in indexes

SQL

92

Tablespace point in time recovery (TSPITR)

SQL

93

Flashback Data Archive

Admin

94

Automated SQL Tuning

SQL

95

Oracle Licensing Components

Admin

96

Parallel Shared pool

Admin

97

Parallel Buffers

98

Oracle DB Vault

Admin

99

Advanced Queue

Admin

100

Event Triggers

SQL

101

Supplemental Logging

Admin

102

Degree of Parallelism

Admin

103

Oracle Resource Profiler

Admin

104

Case Sensitive Password

Security

105

RAC Cluster

Admin

106

Block Change Tracking

Admin

107

Streams, CDC, Capture

Admin

108

Advanced rewrite

Admin

109

Feature Usage Statistics

Admin

110

Data Guard Replication

Admin

Feature ID

25

Feature

Database Logging

Description

Redo logs are transaction journals. Each transaction is recorded in the redo logs. Though redo generation is expensive operation, Oracle uses online redo logs as hot backups in case of instance crashes to ensure recoverability to a consistent state. The online redo log files contain the information necessary to replay a transaction, committed or not. Even uncommitted transactions can be written to the online redo log files. Before a commit is complete, the transaction information is written to the online redo log files.

And changes to your rollback or undo segments are also written to the online redo log files. In that sense, they also contain the information to undo a transaction.

Category

HA/DR

To Find Feature Enablement

Oracle gave users the ability to limit redo generation on tables and indexes for better performance by setting them in NOLOGGING mode. Be careful never to use NOLOGGING option under Data guard setup. DB replication relies on redo logs.

On the other hand, FORCE LOGGING can be used on tablespace or database level to force logging of changes to the redo. This may be required for sites that are mining log data, using Oracle Streams or using Data Guard (standby databases).

SELECT force_logging FROM v$database;

SELECT tablespace_name, force_logging FROM dba_tablespaces;

SELECT * FROM v$logfile;

To create a table in NOLOGGING mode:

CREATE TABLE t1 (c1 NUMBER) NOLOGGING;

To enable NOLOGGING for a table/database:

ALTER TABLE t1 NOLOGGING;

ALTER DATABASE force logging;

Recommendation

Feature Description:

SQL Server requires a transaction log in order to function. That said there are two modes of operation for the transaction log: Simple and Full. In Full mode, the transaction log keeps growing until you back up the database. In Simple mode: space in the transaction log is 'recycled' every Checkpoint.

SQL Server ensures data durability and recovery capabilities using Write-Ahead Logging, hardening a log record before a transaction begins. SQL Server can write log records describing a DB modification before it writes the actual change to the data or object. If SQL Server can't write log records, it won't commit. For this reason, its recommended leaving log auto-growth enabled.

Log file: C:\Program Files\Microsoft SQL Server\MSSWL\Data\MyDB.Idf

Feature Comparison:

Like Oracle redo logging, SQL Server records database transactions in transaction logs. Each transaction record contains the undo and redo image of the transaction. Database logging in SQL Server is typically sent through a single log .ldf file. On the surface, this appears to be much different from oracle where logs are broken up into groups of logs called Redo Log Groups, but both architectures are very similar when look at the structure of the .LDF. Each physical .LDF file is a group of Virtual Log Files, (VLFs), that behave much like a Redo Log Group does in Oracle.

VLFs can be viewed by running DBCC LOGINFO;

Archiving is controlled via a periodic BACKUP LOG job in SQL Server. VLFs are compressed and set to .TRN files.

After backup, VLF is cleared and can be reused.

This differs from Oracle where they ARC internal process automatically moves full log files to an archive directory as they fill up, not on a reoccurring schedule. These files typically have a .ARC extension in Oracle are just copied/renamed right from the Redo Log Group.

Migration Approach

Migrating Transaction Logs

In Oracle, information on transactions and the changes they make is recorded in REDO logs. The redo logs are common to the entire instance.

In SQL Server, transactional changes are logged in the transaction log for the database whose objects are involved in the transaction. A database is created with a single default transaction log. The default transaction log has to be sized or new ones added based on the update activity against the database.

To add a transaction log to a database using T-SQL, use the following syntax:

ALTER DATABASE database

{ ADD LOG FILE < filespec > [ ,...n ]

where <filespec> ::=

( NAME = logical_file_name

[ , FILENAME = 'os_file_name' ]

[ , SIZE = size ]

[ , MAXSIZE = { max_size | UNLIMITED } ]

[ , FILEGROWTH = growth_increment ] )

Database Logging is enabled by default in SQL Server.

Logging is based on three recovery models: simple, full, and bulk-logged. The recovery model for new databases is taken from the Model database. After the creation of the new database, you can change the recovery model using SSMS or following T-SQL:

To set the Recovery Model:

USE master ;

ALTER DATABASE model SET RECOVERY FULL ;

References

http://searchoracle.techtarget.com/answer/What-information-do-redo-log-files-contain

http://www.databases-la.com/?q=node/33

http://www.dba-oracle.com/concepts/archivelog_archived_redo_logs.htm

http://users.wfu.edu/rollins/oracle/archive.html

https://msdn.microsoft.com/en-us/library/ms190925.aspx

http://www.sqlshack.com/beginners-guide-sql-server-transaction-logs/

Feature ID

23

Feature

Database Backup

Description

The following methods are valid for backing-up an Oracle database:

Recovery Manager (RMAN) is an Oracle Database client that's recommended way to perform backup and recovery tasks on your databases and automates administration of your backup strategies. RMAN ships with the database server and doesn't require a separate installation. The RMAN executable is located in your ORACLE_HOME/bin directory. It greatly simplifies backing up, restoring, and recovering database files.

Using RMAN, you can take a hot backup for your database, which will take a consistent backup even when your DB is up and running.

RMAN can be manual or automated by scripting with crontab scheduler or configured via Enterprise Manager Database Control Tool. RMAN optimizes performance by compression.

The RMAN BACKUP command supports backing up the following types of files:

• Datafiles and control files
• Server parameter file
• Archived redo logs
• RMAN backups
• The current server parameter file

Other files as network configuration files, password files, and the contents of the Oracle home, cannot be backed up with RMAN. Likewise, some features of Oracle, such as external tables, may depend upon files other than the datafiles, control files, and redo log. RMAN cannot back up these files. Use some non-RMAN backup solution for any files not in the preceding list.

Incremental backups can only be created with RMAN.

RMAN supports backup encryption for backup sets. You can use wallet-based transparent encryption, password-based encryption, or both.

Category

HA/DR

To Find Feature Enablement

Issuing below script will:

• report on all RMAN backups like full, incremental & archivelog backups.
• And will give you RMAN backup status along with start and stop timing.

select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

Following script will give you SID, Total Work, Sofar & % of completion:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

SELECT start_time, end_time, input_type, input_type, status FROM v$rman_backup_job_details ORDER BY 1;

SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time AND vrbjd.input_type <> 'ARCHIVELOG' ORDER BY 2,1;

While executing backup, RMAN will generate backup logs, you can verify its backup logs to verify status of RMAN backups.

Additionally, You can query to V$RMAN_STATUS dictionary view for completed job information:

select OUTPUT from V$RMAN_OUTPUT;

To determine if RMAN is running a full backup or incremental backup, use INPUT_TYPE column from dictionary view V$RMAN_BACKUP_JOB_DETAILS

Recommendation

Feature Description:

In SQL Server, different types of backups can be create based on recovery model:

In SQL Server, use Maintenance Plans for scheduling backups. Use the Back Up Database Task in SQL Server Management Studio (SSMS) to add a backup task to the maintenance plan.

There are fine grained options to create backups for all system databases (master, msdb, model), all user databases, specific databases, portion of database- Files & Filegroups; backup type, set backup extension type, verify backup integrity and whether Back up the database to a file or to tape.

Feature Comparison:

There are variety of hot & cold backups available in both Oracle and SQL Server to suit any business environment.

Starting with SQL Server 2014, SQL Server supports backup encryption. Oracle Standard Edition, on the other hand, does not have backup encryption.

Migration Approach

Backup mechanism cannot to migrated through SSMA tool.

In SQL Server, use Maintenance Plans for scheduling backups. Use the Back Up Database Task in SQL Server Management Studio (SSMS) to add a backup task to the maintenance plan.

There are fine grained options to create backups for all system databases (master, msdb, model), all user databases, specific databases, portion of database- Files & Filegroups; backup type, set backup extension type, verify backup integrity and whether Back up the database to a file or to tape.

SQL Server's built-in backup options support disk, tape and the cloud as backup devices. SQL Server Managed Backup to Azure allows an automatic database backup to Azure, based on changes done in the database. This feature schedules, performs and maintains the backups–all a DBA needs to do is specify a retention period. SQL Server Backup to URL allows you to easily backup directly to Microsoft Azure Blob Storage, removing the need to manage hardware for backups

To create a maintenance plan using the Maintenance Plan Wizard in SSMS

• In Object Explorer, click the plus sign to expand the server where you want to create a maintenance plan.
• Click the plus sign to expand the Management folder.
• Right-click the Maintenance Plans folder and select Maintenance Plan Wizard.
• Follow the steps of the wizard to create a maintenance plan.
• Use the Back Up Database Task dialog to add a backup task to the maintenance plan. Backing up the database is important in case of system or hardware failure (or user errors) that cause the database to be damaged in some way, thus requiring a backed-up copy to be restored. This task allows you to perform full, differential, files and filegroups, and transaction log backups.

References

http://www.orafaq.com/wiki/Oracle_database_Backup_and_Recovery_FAQ

http://searchdatabackup.techtarget.com/feature/Choosing-the-best-Oracle-backup-strategy-for-your-environment

https://www.mssqltips.com/sqlservertutorial/6/types-of-sql-server-backups/

https://msdn.microsoft.com/en-us/library/ms189647.aspx

(back up maintenance task)

https://msdn.microsoft.com/en-us/library/ms187510.aspx

Feature ID

24

Feature

Database Restore

Description

It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. Nevertheless, on-line backups are less disruptive and don't require database downtime. Point-in-time recovery (regardless if you do on-line or off-line backups) is only available when the database is in ARCHIVELOG mode.

To restore a physical backup of a datafile or control file is to reconstruct it and make it available to the Oracle database server. To recover a restored datafile is to update it by applying archived redo logs and online redo logs, that is, records of changes made to the database after the backup was taken. If you use RMAN, then you can also recover datafiles with incremental backups, which are backups of a datafile that contain only blocks that changed after a previous incremental backup.

You have a choice between two basic methods for recovering physical files. You can:

• Use the RMAN utility to restore and recover the database
• Restore backups by means of operating system utilities, and then recover by running the SQL*Plus RECOVER command

Category

HA/DR

To Find Feature Enablement

Recommendation

Feature Description:

You can restore database by using SQL Server Management Studio(SSMS) or Transact-SQL. SQL Server Management Studio makes the restore process simple. Select the restore point you want to use, since a SQL backup file can hold multiple backups you may see more than one restore point listed, and also you can overwrite the existing database or rename a database. You could also restore backups created on another SQL Server using the SQL Management Studio tool.

To restore an encrypted database, you need access to the certificate or asymmetric key used to encrypt that database. Without the certificate or asymmetric key, you cannot restore that database. You must retain the certificate used to encrypt the database encryption key for as long as you need to save the backup.

You could even restore an older version database to SQL Server 2016, that database will automatically upgrade to SQL Server 2016. Typically, the database becomes available immediately.

Feature Comparison:

Similar to Oracle, SQL Server provides utilities as well as SQL commands to restore backed up database.

Migration Approach

Restore mechanism cannot be migrated through SSMA tool.

SQL Server restoration can be configured manually via utilities or SQL commands. Choosing appropriate Backup and Restore Strategy is governed by your application's DR SLA requirements- typically measured by Recovery Time Objective (RTO) and Recovery Point Objective (RPO).

Restore database using T-SQL

RESTORE command restores backups taken using the BACKUP command:

--To Restore an Entire Database from a Full database backup (a Complete Restore):

RESTORE DATABASE { database_name | @database_name_var }

[ FROM <backup_device> [ ,...n ] ]

[ WITH

{

[ RECOVERY | NORECOVERY | STANDBY =

{standby_file_name | @standby_file_name_var }

]

| , <general_WITH_options> [ ,...n ]

| , <replication_WITH_option>

| , <change_data_capture_WITH_option>

| , <FILESTREAM_WITH_option>

| , <service_broker_WITH options>

| , <point_in_time_WITH_options—RESTORE_DATABASE>

} [ ,...n ]

]

[;]

Restore database using SQL Server Management Studio

References

https://docs.oracle.com/cd/B19306_01/server.102/b14220/backrec.htm

https://msdn.microsoft.com/en-us/library/ms189275.aspx

https://msdn.microsoft.com/en-us/library/ms177429.aspx ( Restore a Database Backup Using SSMS)

Feature ID

41

Feature

Log Shipping

Description

• Oracle doesn't offer log shipping as a part of the core product, but it's possible to set up log shipping in Oracle.
• Oracle's log shipping works by copying archived redo log files. There are no extra backup jobs to add. Instead, the log shipping jobs copy archived redo log files from the flash recovery area. Rotating out active redo log files will move them into the archive redo log file area. DBAs can take advantage of their existing backup strategy. It is still possible for an Oracle DBA to break log shipping by using NOARCHIVELOG mode or adding tablespaces or files without adding them on the secondary. Of course, a DBA can also use the FORCE LOGGING option to prevent users from switching to NOARCHIVELOG mode and breaking the log shipping.
• When you're moving backups across the network, compression can help meet your recovery point objective. Oracle's compression is only found in either Oracle Enterprise Edition or customers using Oracle's backup to the cloud feature – Oracle Database Backup Service. However, it's trivial to leverage in-flight compression when moving files between Oracle instances. You can also use rsync to move files between primary and standby and enable the -z flag to ensure you get compression.

Category

HA/DR

To Find Feature Enablement

to check if the ARCHIVELOG mode is enabled:

SQL> archive log list;

Recommendation

Feature Description:

In SQL Server can do Log Shipping using SSMS or T-SQL scripts.

Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled. you can even make your log shipping secondary readable and use it for reporting using STANDBY mode.

A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

Additionally, Log shipping can be used with following other features of SQL Server:

• You can migrate from Log Shipping to Always On Availability Groups (SQL Server)
• Database Mirroring and Log Shipping (SQL Server)
• Log Shipping can be used in conjunction with Replication (SQL Server)

SQL Server can compress backups in the Standard Edition of the product. This can either be enabled as a default SQL Server level setting or in the log shipping jobs.

Feature Comparison:

Like Oracle, SQL Server has support for log shipping options available, and can compress backups for better performance.

Migration Approach

SSMA doesn't support migrating Log Shipping.

In SQL Server, you can set up Log Shipping manually using SSMS or T-SQL scripts.

A Typical Log Shipping Configuration

The following figure shows a log shipping configuration with the primary server instance, three secondary server instances, and a monitor server instance. The figure illustrates the steps performed by backup, copy, and restore jobs, as follows:

1. The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
2. Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
3. Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.

The primary and secondary server instances send their own history and status to the monitor server instance.

To configure log shipping using Transact-SQL

1. Initialize the secondary database by restoring a full backup of the primary database on the secondary server.
2. On the primary server, execute sp_add_log_shipping_primary_database to add a primary database. The stored procedure returns the backup job ID and primary ID.
3. On the primary server, execute sp_add_jobschedule to add a schedule for the backup job.
4. On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.
5. On the primary server, enable the backup job.
6. On the secondary server, execute sp_add_log_shipping_secondary_primary supplying the details of the primary server and database. This stored procedure returns the secondary ID and the copy and restore job IDs.
7. On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.
8. On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.
9. On the primary server, execute sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server.
10. On the secondary server, enable the copy and restore jobs.

References

http://docs.oracle.com/database/121/SBYDB/concepts.htm#SBYDB00010

https://www.brentozar.com/archive/2015/02/comparing-sql-server-oracle-log-shipping/

http://users.wfu.edu/rollins/oracle/archive.html

https://msdn.microsoft.com/en-us/library/ms187103.aspx

Feature ID

98

Feature

Oracle Database Vault

Description

A standard problem with database security stems from the need for database administrators to have full access to the data they manage- a potential security hole.

Oracle Database Vault with Oracle Database 12c provides greater access controls on data. It can be used to protect application data from the DBA and other privileged users as well as implementing robust controls on access to the database and application.

The Database Vault Option allows you to restrict access granted with system-wide privileges, restrict administrative access to a defined realm of data, allowing for finer grained separation of administrative duties.

A security administrator can set factors to define access to the database including Oracle commands available to the different classes of users and administrators and audit-specific dimensions of security.

Realms can be defined for limiting access to specific database schemas and roles at a more granular level.

Category

Security

To Find Feature Enablement

SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

Recommendation

In SQL Server, there is no direct equivalent feature to Database Vault.

However, SQL Server does provide capability to restrict user data access from DBAs.

Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, or to reduce security clearance requirements for their own DBA staff.

With Always Encrypted, you can configure encryption for selected columns to protect sensitive data. These encrypted columns can then be managed by Access Control by keeping DBA restricted with privilege to decrypt or access sensitive data.

Migration Approach

SSMA can't migrate Database Vault features automatically.

In SQL Server, configure Always On encryption.

To access encrypted columns (even if not decrypting them) VIEW ANY COLUMN permissions need to be explicitly granted.

T-SQL example to enable encryption

The following Transact-SQL creates column master key metadata, column encryption key metadata, and a table with encrypted columns.

CREATE COLUMN MASTER KEY MyCMK

WITH (

KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',

KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420'

);

---------------------------------------------

CREATE COLUMN ENCRYPTION KEY MyCEK

WITH VALUES

(

COLUMN_MASTER_KEY = MyCMK,

ALGORITHM = 'RSA_OAEP',

ENCRYPTED_VALUE = 0x04E234173C....154F86

);

---------------------------------------------

CREATE TABLE [dbo].[Students] (

[StudentID] INT IDENTITY (1, 1) NOT NULL,

[SSN] CHAR (11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

[FirstName] NVARCHAR (50) NULL,

[LastName] NVARCHAR (50) NOT NULL,

[StreetAddress] NVARCHAR (50) NOT NULL,

[City] NVARCHAR (50) NOT NULL,

[ZipCode] CHAR (5) NOT NULL,

[BirthDate] DATE ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

CONSTRAINT [PK_dbo.Students] PRIMARY KEY CLUSTERED ([StudentID] ASC)

);

References

http://www.oracle.com/technetwork/database/options/oracle-database-vault-external-faq-2032888.pdf

https://www.oracle.com/database/database-vault/index.html

https://docs.oracle.com/database/121/DVADM/getting_started.htm#DVADM002

https://msdn.microsoft.com/en-us/library/mt163865.aspx

Feature ID

1

Feature

Database Audit

Description

• Auditing facilitates database activity monitoring. It's the recording of selected user database actions. Monitoring statements, privileges, or objects
• Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered.
• AUDIT_SYS_OPERATIONS initialization parameter- Enables or disables the auditing of top-level operations directly issued by user SYS, and users connecting with SYSDBA or SYSOPER privilege. This parameter should be enabled on ALL production databases.
• Oracle Database writes the audit records to the audit trail of the operating system. The database audit trail consists of a single table named SYS.AUD$. Audit trail records contain different types of info, depending on the events audited and the auditing options set.
• Oracle Database allows audit trail records to be directed to an operating system audit trail if the operating system makes such an audit trail available to Oracle DB. If not, then audit records are written to a file outside the database. the database will write a trace file of the session actions (for sys or sysdba) to the dump directory location specified by AUDIT_FILE_DEST.
• If you set the AUDIT_TRAIL initialization parameter to XML or XML, EXTENDED, it writes the audit records in XML format. AUDIT_TRAIL enables or disables database auditing.
• To enable
  • alter system set audit_sys_operations=TRUE scope=spfile;
  • then restart

Category

Security

To Find Feature Enablement

show parameter audit_sys_operations;

show parameter audit_trail;

select * from dba_stmt_audit_opts union select * from dba_priv_audit_opts;

-- if a non-container database

conn / as sysdba

-- connect to each PDB in turn and run the following queries

show parameter audit

SELECT MAX(logoff$time)

FROM sys.aud$;

SELECT MAX(timestamp#), MAX(ntimestamp#)

FROM sys.fga_log$;

SELECT table_name, tablespace_name, num_rows

FROM dba_tables

WHERE table_name IN ('AUD$', 'FGA_LOG$')

ORDER BY 1;

The audit trail is stored in the SYS.AUD$ table. It's contents can be viewed directly or via the following views.

• DBA_AUDIT_EXISTS
• DBA_AUDIT_OBJECT
• DBA_AUDIT_SESSION
• DBA_AUDIT_STATEMENT
• DBA_AUDIT_TRAIL
• DBA_OBJ_AUDIT_OPTS
• DBA_PRIV_AUDIT_OPTS
• DBA_STMT_AUDIT_OPTS

Recommendation

Feature Description:

DDL triggers and notifications can aid in auditing

SQL Server server-level auditing is resilient, available in all editions, and provides T-SQL call stack frame info

SQL Server supports user-defined audit groups and audit filtering

Can use T-SQL to enable audit by creating the audit specification for specific database and specific access group.

The Audit action items can be individual actions such as SELECT operations on a Table, or a group of actions such as SERVER_PERMISSION_CHANGE_GROUP.

SQL Audit Events track the following three categories of Events:

• Server Level: These actions include server operations, such as management changes, and logon and logoff operations.
• Database Level: These actions include data manipulation languages (DML) and Data Definition Language (DDL).
• Audit Level: These actions include actions in the auditing process.

You could implement an audit trail quickly in SQL Server by creating shadow table for each table in database and triggers to log every time when a record is inserted, updated or deleted in the table. see last link in the list for Audit Trail Generator Script.

The SQL Server Audit feature is built on top of Extended Events to leverage the performance benefits and provide both asynchronous and synchronous write capabilities (by default, SQL Server Audit uses the asynchronous event model). You could use SQL Profiler to see Workload Performance impact of Auditing and turn on audit on specific objects and specific logins.

All editions of SQL Server support server level audits. Database level auditing is limited to Enterprise, Developer, and Evaluation editions.

Feature Comparison:

Similar to Oracle Audit Vault for DDL and DML statements

All actions (DDL and DML) are auditable in SQL Server

Migration Approach

SSMA does not support migrating Auditing configurations.

In SQL server, use T-SQL to enable audit by creating the audit specification for specific database and specific access group.

T-SQL to create a server audit

USE master ;

GO

-- Create the server audit.

CREATE SERVER AUDIT Payrole_Security_Audit

TO FILE ( FILEPATH =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA' ) ;

GO

-- Enable the server audit.

ALTER SERVER AUDIT Payrole_Security_Audit

WITH (STATE = ON) ;

T-SQL to create a database-level audit specification

(Following example creates a database audit specification called Audit_Pay_Tables that audits SELECT and INSERT statements by the dbo user, for the HumanResources.EmployeePayHistory table based on the server audit defined above.)

USE AdventureWorks2012 ;

GO

-- Create the database audit specification.

CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables

FOR SERVER AUDIT Payrole_Security_Audit

ADD (SELECT , INSERT

ON HumanResources.EmployeePayHistory BY dbo )

WITH (STATE = ON) ;

GO

References

http://docs.oracle.com/cd/B19306_01/network.102/b14266/auditing.htm#DBSEG525

https://oracle-base.com/articles/8i/auditing#AuditOptions

http://solutioncenter.apexsql.com/how-to-setup-and-use-sql-server-audit-feature/

https://msdn.microsoft.com/en-us/library/cc280663.aspx (Sql Server Audit Actions)

https://msdn.microsoft.com/en-us/library/cc280386.aspx (SQL Server Audit)

http://solutioncenter.apexsql.com/sql-server-database-auditing-techniques/

http://techbrij.com/audit-trail-microsoft-sql-server-quickly

Feature ID

8

Feature

Authentication

Description

Authentication the process of verifying that the login ID or username supplied by a user to connect to the database belongs to an authorized user. Oracle allows authentication of user account through the OS or through the database (server). Oracle allows a single database instance to use any or all methods. Oracle requires special authentication procedures for database administrators, because they perform special database operations. Oracle also encrypts passwords during transmission to ensure the security of network authentication.

Once authenticated by the operating system, users can connect to Oracle more conveniently, without specifying a user name or password.

Oracle Database can authenticate users attempting to connect to a database by using information stored in that database itself. To configure Oracle Database to use database authentication, you must create each user with an associated password.

Category

Security

Find Feature Enablement

an operating-system-authenticated user can invoke SQL*Plus and skip the user name and password prompts by entering the following: SQLPLUS /

Recommendation

Feature Description:

SQL Server has two methods of authentication:

• Windows authentication
• SQL Server authentication

Windows Authentication:

When you are accessing SQL Server from the same computer it is installed on, you won't be prompted to type in username and password if you're using Windows Authentication. Authenticating with Windows domain logins, the SQL Server service already knows that someone is logged in into the operating system with the correct credentials, and it uses these credentials to allow the user into its databases. This works as long as the client resides on the same computer as the SQL Server, or as long as the connecting client matches the Windows credentials of the server. Ideally, Windows authentication must be used when working in an Intranet type of an environment. In enterprise environments, these credentials are normally Active Directory domain credentials. Windows Authentication is also a more convenient way to log-in into a SQL Server instance without typing a username and a password, however when more users are involved, or remote connections are being established with the SQL Server, SQL authentication should be used.

Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server.

SQL Server Authentication:

SQL Authentication is the typical authentication used for various database systems, composed of a username and a password. An instance of SQL Server can have multiple such user accounts (using SQL authentication) with different usernames and passwords. In shared servers where different users should have access to different databases, SQL authentication should be used. Also, when a client (remote computer) connects to an instance of SQL Server on other computer than the one on which the client is running, SQL Server authentication is needed. Even if you don't define any SQL Server user accounts, at the time of installation a root account - sa - is added with the password you provided. Just like any SQL Server account, this can be used to log-in locally or remotely, however if an application is the one that does the log in, and it should have access to only one database, it's strongly recommended that you don't use the sa account, but create a new one with limited access.

Microsoft's best practice recommendation is to use Windows authentication mode whenever possible. It allows you to centralize account administration for your entire enterprise in a single place: Active Directory.

Feature Comparison:

Like Oracle, SQL Server has two major methods of authentication:

• OS authentication
• Database authentication

Password Policies can be enforced with authentications in both databases. These policies control password management including account locking, password aging and expiration, password history, and password complexity verification.

Migration Approach

In Oracle, most used Authentication methods are authentication by the database and authentication by the operating system.

In SQL Server, the database modes in use are SQL Server Authentication Mode and the Windows Authentication Mode. The database authentication modes in Oracle and SQL Server are closely compatible and use a user name and password pair. The operating system authentication is quite different between Oracle and SQL Server. Oracle's operating system mode can only authenticate users with local accounts on UNIX servers. Windows authentication for SQL Server is actually performed by the domain and not the local account on the Windows server.

The Oracle RDBMS also provides password management functions, such as account locking, password lifetime and expiration, password history, and password complexity verification.

The SQL Server RDBMS does not provide these services, and Windows security is used to provide these features. Hence the migration of Oracle user names to SQL Server logins and users is dependent on the type of authentication in use as well as the requirements of password management.

Migration options for Oracle logins based on authentication mode and the requirements on password management functionality:

To add a new Windows authenticated login to a SQL Server instance using T-SQL, use the following syntax:

sp_grantlogin [ @loginame = ] 'login_name'

where

login_name

is of the form

domain_name\domain_login_name

To add a new database authenticated login to a SQL Server instance use following T-SQL:

sp_addlogin [ @loginame = ] 'login_name

[ , [ @passwd = ] 'password' ]

[ , [ @defdb = ] 'database_name' ]

[ , [ @encryptopt = ] 'encryption_option' ]

where database_name specifies the database the login connects to after logging in (default database). While passwords are encrypted in SQL Server by default, the option exists to skip encryption to allow custom password encryption by the application using a different algorithm.

A user account should be created separately for the login in the default database.

sp_grantdbaccess [ @loginame = ] 'login_name'[, [ @name_in_db = ] 'user_name'

To create a user account to a SQL Server database using T-SQL, use the following syntax:

sp_grantdbaccess [ @loginame = ] 'login_name'[, [ @name_in_db = ] 'user_name'

The name chosen for the user account can be different from that for the login account.

References

https://docs.oracle.com/cd/B19306_01/network.102/b14266/authmeth.htm#BABCGGEB (Oracle Authentication Methods)

http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authentication.htm#i1006458

https://msdn.microsoft.com/en-us/library/ms144284.aspx (Authentication modes in SQL Server)

Feature ID

39

Feature

Privileges

Description

A privilege is a right to execute an SQL statement or to access another user's object.

In Oracle, there are two types of privileges: system privileges and object privileges. A privilege can be assigned to a user or a role.

The set of privileges is fixed, that is, there is no SQL statement like create privilege xyz...

System privileges

In Oracle 9.2, there are 157 system privileges, and 10g has even 173. These are privileges like create job, drop user, alter database, and can be displayed with:

select name from system_privilege_map;

System privileges can be audited.

sysdba and sysoper the most important system privileges.

There are five operations on Oracle that require the user to have SYSDBA privileges in order to perform them:

• startup a database,
• shutdown a database,
• backup a database,
• recover a database
• and create a database

v$pwfile_users lists all users who have been granted sysdba or sysoper privileges.

Object privileges

While Oracle has several object privileges, the ones commonly granted to users are SELECT, INSERT, DELETE, and UPDATE on tables and EXECUTE on stored programs.

Object Privileges can be assigned on following DB objects:

• Tables- select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all
• Views- select, insert, update, delete, under, references, flashback, debug
• Sequence- alter, select
• Packages, Procedures, Functions (Java classes, sources...)- execute, debug
• Materialized Views- delete, flashback, insert, select, update
• Directories- read, write
• Libraries- execute
• User defined types- execute, debug, under
• Operators- execute
• Indextypes- execute

For a user to access an object in another user's schema, they need privilege to the object. Object privileges can be displayed using all_tab_privs_made or user_tab_privs_made.

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles.

The GRANT ANY OBJECT PRIVILEGE system privilege allows users to grant and revoke any object privilege on behalf of the object owner.

INSERT, UPDATE, or REFERENCES privileges can be granted on individual columns in a table.

Assigning privileges to users and roles

GRANT- assigns a privilege to a user

REVOKE- allows to take away such privileges from users and roles.

Oracle stores the granted privileges in its data dictionary.

Category

Security

To Find Feature Enablement

Following query returns all system privilege grants made to roles and users:

SELECT count(*) FROM DBA_SYS_PRIVS;

Recommendation

Feature Description:

Like Oracle. SQL Server supports system and object level privileges.

System and object privileges can be granted to Users directly or via Roles using the GRANT statement and removed using the REVOKE statement.

SQL Server also has the additional DENY statement, which prevents users from exercising a privilege even when it has been granted to the user.

In SQL Server, the REVOKE statement is used to remove (or cancel out) a previously granted or denied privilege. Conflict in permissions granted directly and through roles is always resolved in favor of the higher-level permission. The only exception to this is if users have been denied permissions (DENY) to an object either explicitly or through their membership in a role. If that is the case, they will not be granted the requested access to the object.

Permission Hierarchy

• Permissions have a parent/child hierarchy.
• If you grant SELECT permission on a database, that permission includes SELECT permission on all (child) schemas in the database.
• If you grant SELECT permission on a schema, it includes SELECT permission on all the (child) tables and views in the schema.
• The permissions are transitive; that is, if you grant SELECT permission on a database, it includes SELECT permission on all (child) schemas, and all (grandchild) tables and views.
• Permissions also have covering permissions. The CONTROL permission on an object, normally gives you all other permissions on the object.

Feature Comparison:

The following terminologies relating to privileges in Oracle and SQL Server are equivalent:

Like Oracle, SQL Server has the same database object privileges.

Oracle and SQL Server differ a lot in the system privileges that are available. Oracle has very granular (more than 100) system privileges. SQL Server system privileges, called statement permissions, are restricted to the following list:

• BACKUP DATABASE
• BACKUP LOG
• CREATE DATABASE
• CREATE DEFAULT
• CREATE FUNCTION
• CREATE PROCEDURE
• CREATE RULE
• CREATE TABLE
• CREATE VIEW

The rest of the Oracle system privileges are bundled into several large fixed roles. For example, the fixed database role db_datareader is equivalent to the SELECT ANY TABLE system privilege in Oracle.

Migration Approach

SSMA tool doesn't support automatic migration of privileges.

In SQL Server, these privileges would need to be manually created using T-SQL/SSMS and assigned to principals like user, or roles.

Use T-SQL queries with GRANT, DENY, and REVOKE to manipulate permissions. sys.server_permissions and sys.database_permissions catalog views provide information on permissions.

You can GRANT and REVOKE privileges on database objects in SQL Server.

You can grant users various privileges to tables- permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL. REFERENCES- Ability to create a constraint that refers to the table.

ALTER- Ability to perform ALTER TABLE statements to change the table definition.

Use <database name>;

Grant <permission name> on <object name> to <username\principle>;

GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO smithj;

ALL does not grant all permissions for the table. Rather, it grants the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES

GRANT ALL ON employees TO smithj;

Grant EXECUTE permission on stored procedures to a user

GRANT EXECUTE ON dbo.procname TO username;

SELECT permission on the table (Region) , in a schema (Customers), in a database (SalesDB) can be achieved through any of below statements:

GRANT SELECT ON OBJECT::Region TO Ted

GRANT CONTROL ON OBJECT::Region TO Ted

GRANT SELECT ON SCHEMA::Customers TO Ted

GRANT SELECT ON DATABASE::SalesDB TO Ted

References

http://www.adp-gmbh.ch/ora/misc/users_roles_privs.html

https://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#DBSEG10000 ( Administering User Privileges, Roles, and Profiles)

https://docs.oracle.com/cd/E21901_01/timesten.1122/e21642/privileges.htm#TTSQL339

https://blogs.msdn.microsoft.com/sqlsecurity/2011/08/25/database-engine-permission-basics/

Feature ID

114

Feature

Roles

Description

Role-based security, allows you to assign set of permissions to a role, instead of granting them to individual users. This role can then be assigned to group of users.

Fixed server and fixed database roles have a fixed set of permissions assigned to them.

In Oracle, Single DBA role has database instance wide privileges spanning all schemas. Users with explicit object privileges or those who connect with administrative privileges (SYSDBA) can access objects in the SYS schema.

Predefined Roles

Along with the installation, and creation of an oracle database, Oracle creates many predefined roles:

• CONNECT includes the privileges needed to connect to the database.
• RESOURCE includes many of the roles a developer might use to create and manage an application, such as creating and altering many types of objects including tables, view, and sequences.
• EXP_FULL_DATABASE/IMP_FULL_DATABASE allows the grantee to do logical backups of the database.
• RECOVERY_CATALOG_OWNER allows grantee to administer Oracle Recovery Manager catalog.
• SCHEDULER_ADMIN allows the grantee to manage the Oracle job scheduler.
• DBA gives a user most of the major privileges required to administer a database. These privileges can manage users, security, space, system parameters, and backups. Accessing data dictionary views (v$ views and static dictionary views). exp_full_database, imp_full_database is needed to export objects found in another user's schema.
• connect, resource, dba- these might not be created anymore in future versions of Oracle.

The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role.

Category

Security

To Find Feature Enablement

Below query returns all the roles granted to users and other roles:

SELECT count(*) FROM DBA_ROLE_PRIVS;

USER_ROLE_PRIVS describes the roles granted to the current user.

Recommendation

Feature Description:

All versions of SQL Server use role-based security, which allows you to assign permissions to a role, or group of users, instead of to individual users. Fixed server and fixed database roles have a fixed set of permissions assigned to them. SQL Server provides nine fixed server roles.

These roles are security principals that group other principals. Roles are like groups in the Windows operating system.

Server Roles:

Server roles are pre-defined and can't be modified. Nor can you define a new server-wide role. Server roles can be very effective for sharing admin responsibilities among several logins. You don't share the SA account password to all logins; rather, you grant the necessary level of admin permissions by adding specific login to a server role. each member of a built-in server role can add other logins to the same role.

Fixed server roles have a fixed set of permissions and server-wide scope. They are intended for use in administering SQL Server and the permissions assigned to them cannot be changed.

Be selective when you add users to fixed server roles. For example, users with bulkadmin role can run the BULK INSERT statement, which could jeopardize data integrity.

Fixed SQL Server Roles (8 in total)

Fixed Database Roles:

Databases too have pre-defined roles that allow role members to perform a certain set of activities within the database. Built-in database roles exist in every database and can't be dropped. At the database level, security is managed by members of the db_owner and db_securityadmin roles: only members of db_owner can add other users to the db_owner role; db_securityadmin can add users to all other roles except db_owner.

Few Built-in/Fixed SQL Server database roles

You can view roles in SQL Server via SSMS:

Server Roles

Database Roles

User-defined or Application Roles:

Users with the CREATE ROLE permission can create new user-defined database roles to represent groups of users with common permissions.

User defined roles can be created via T-SQL or SSMS.

Feature Comparison:

Oracle and SQL Server both provide system roles with predefined privileges and user-defined roles.

Migration Approach

SSMA tool doesn't support automatic migration of roles. In SQL Server, Roles can be created manually using T-SQL or SSMA.

In Oracle, roles are available at the instance or server level and can be granted privileges on more than one schema. SQL Server user-defined roles are local to a database and owned by a user. Hence, when migrating a role from Oracle to SQL Server, a role has to be created in each of the databases in which privileges have to be granted.

Two important stored procedures for granting roles in SQL Server

sp_addsrvrolemember can be used for granting fixed system roles and sp_addrolemember can be used for granting fixed database roles.

To add a login to a server role

EXEC sp_addsrvrolemember 'JohnDoe', 'dbcreator';

To remove a login from a fixed server role

EXEC sp_dropsrvrolemember 'JohnDoe', 'dbcreator';

To get server roles list, use sp_helpsrvrole.

To get permissions list each server role has, use sp_srvrolepermission.

T-SQL to create user-defined role(s) in SQL Server

sp_addrole [ @rolename = ] 'role_name'

[ , [ @ownername = ] 'owner' ]

To give user read permissions on all tables

N'db_datareader role:

EXEC sp_addrolemember N'db_datareader', N'your-user-name'

To give user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables (use db_datawriter role)

EXEC sp_addrolemember N'db_datawriter', N'your-user-name'

The scope of db_owner is a database; the scope of sysadmin is the whole server.

To add users to a database role

exec sp_addrolemember 'db_owner', 'UserName'

Users can be assigned to database roles, inheriting any permission sets associated with those roles. sp_addrolemember adds a database user, database role, Windows login, or Windows group to a database role in the current database.

To get fixed db roles list

sp_helpdbfixedrole

To get permissions list each database role has

sp_dbfixedrolepermission.

References

https://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm

https://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1006858

https://msdn.microsoft.com/en-us/library/ms188659.aspx ( Server-level roles)

https://www.toadworld.com/platforms/sql-server/w/wiki/9764.built-in-server-roles-database-roles

http://searchsqlserver.techtarget.com/feature/Create-a-user-defined-server-role-in-SQL-Server-2012-with-T-SQL-SSMS

Feature ID

10

Feature

Data Encryption

Description

Authentication, authorization, and auditing mechanisms secure data in the database, but not in the operating system data files where data is stored. Oracle introduced Transparent Data Encryption (TDE). TDE provides mechanism to encrypt the data stored in the OS data files. To prevent, unauthorized decryption, TDE stores the encryption keys in a security module outside of the database called Wallet (Keystore in Oracle Database 12c).

You can configure Oracle Key Vault as part of the TDE implementation. This enables you to centrally manage TDE keystores (called TDE wallets in Oracle Key Vault) in your enterprise. For example, you can upload a software keystore to Oracle Key Vault and then make the contents of this keystore available to other TDE-enabled databases.

Category

Security

Find Feature Enablement

SELECT count(*) FROM dba_encrypted_columns;

Recommendation

Feature Description:

Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted. You can use encryption in SQL Server for connections, data, and stored procedures.

Although encryption is a valuable tool to help ensure security, it should not be considered for all data or connections. Consider how users will access data- If users access data over a public network, data encryption might be required to increase security. However, if all access involves a secure intranet configuration, encryption might not be required. Any use of encryption should also include a maintenance strategy for passwords, keys, and certificates.

Transparent Data Encryption (TDE) encrypts SQL Server, Azure SQL Database, and Azure SQL Data Warehouse data files, known as encrypting data at rest. TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database. Transparent Data Encryption" can help achieve compliancy with Payment Card Industry Data Security Standard. TDE provides strong encryption, but with some shortcomings. First, you must encrypt an entire database. No granularity is offered at a lower level, such as encrypting specific tables or certain data within a table. Second, TDE encrypts only data at rest, in files. Data in memory or in-flight between the application and server are unencrypted.

SQL Server 2016 adds a new security feature that helps protect data at rest and in motion, on-premises & cloud: Always Encrypted

Always Encrypted allows very granular encryption, all the way down to individual columns. Always Encrypted also fully encrypts data at rest, in memory, and in-flight.

Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).

Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

SQL Server encrypts data with a hierarchical encryption and key management infrastructure. Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys. Asymmetric keys and symmetric keys can be stored outside of SQL Server in an Extensible Key Management (EKM) module or external trusted key stores, such as Azure Key Vault, Windows Certificate Store on a client machine, or a hardware security module.

Feature Comparison:

Like Oracle, Encryption at Rest for Data files is supported in SQL Server. Like Oracle, Encryption keys can be stored outside of database in Key Vaults.

Migration Approach

SSMA does not support migrating encryption configurations.

First, we need to decrypt all the Oracle data; migrate and then set up encryption in SQL Server.

TDE can be set up in SQL Server by using T-SQL to first create master key, certificate, and database encryption key and then enable encryption using T-SQL ALTER DATABASE command.

Configuring Always Encrypted

The initial setup of Always Encrypted in SQL Server involves generating Always Encrypted keys, creating key metadata, configuring encryption properties of selected database columns, and/or encrypting data that may already exist in columns that need to be encrypted.

Please note that some of these tasks are not supported in Transact-SQL and require the use of client-side tools. As Always Encrypted keys and protected sensitive data are never revealed in plaintext to the server, the Database Engine cannot be involved in key provisioning and perform data encryption or decryption operations.

You can use SQL Server Management Studio or PowerShell to accomplish such tasks.

T-SQL example to enable encryption

CREATE TABLE [dbo].[Students] (

[StudentID] INT IDENTITY (1, 1) NOT NULL,

[SSN] CHAR (11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

[FirstName] NVARCHAR (50) NULL,

[LastName] NVARCHAR (50) NOT NULL,

[StreetAddress] NVARCHAR (50) NOT NULL,

[City] NVARCHAR (50) NOT NULL,

[ZipCode] CHAR (5) NOT NULL,

[BirthDate] DATE ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

CONSTRAINT [PK_dbo.Students] PRIMARY KEY CLUSTERED ([StudentID] ASC)

);

To access encrypted columns (even if not decrypting them) VIEW ANY COLUMN permissions need to be explicitly granted.

References

https://docs.oracle.com/database/121/ASOAG/asotrans.htm#ASOAG10136 (Oracle Transparent Data Encryption)

http://www.oracle.com/technetwork/database/security/tde-faq-093689.html

https://msdn.microsoft.com/en-us/library/bb510663.aspx (SQL Server Encryption)

http://dba.stackexchange.com/questions/137531/in-sql-server-2016-what-is-the-difference-between-always-encrypted-and-transpar

http://www.dbta.com/Columns/SQL-Server-Drill-Down/Stronger-Security-Via-Always-Encrypted-in-SQL-Server-2016-106815.aspx

Feature ID    

36

Feature

Login/User Accounts

Description

Oracle provides logins for authorized users to connect to the database. which are referred to as the user or username, and any operation the user can perform is controlled by privileges granted to the login. A user name is database system wide in Oracle, though Oracle 12c pluggable databases can have their own users.

• In Oracle, users and schemas are essentially same.
• Consider a user as the account you use to connect to a database, and A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates.
• When you create a user, you are also implicitly creating a schema for that user.
• Schema is owned by a user. A user may be given access to schema objects owned by different Users.

Category

Server

Find Feature Enablement

User accounts can be accessed through a system view called ALL_USERS

SELECT * FROM ALL_USERS;

Recommendation

Feature Description:

In SQL Server, the privileges at the instance are assigned to the login, and privileges inside a database are given to the related database user. A database user is mapped back to an instance login.

• In SQL Server, schema and user are separate things. The users are only used to log in and define permissions. One schema is the dbo (or database owner) schema.
• In the three-part name 'mydb.dbo.mytable', mydb is a database (physical grouping), while dbo is a schema (logical grouping).
• Although the terms login and user are often used interchangeably, they are very different.
  • A "Login" grants the principal entry into the SERVER.
  • A "User" grants a login entry into a single DATABASE.
• One "Login" can be associated with many users (one per database).
• In SQL Server, DBA add logins to the SQL Server instance, and these logins are mapped to users in individual databases on the SQL Server instance.
• Database users who will create tables and feature classes must have privileges necessary to create these objects in the database, and they must have a schema in which they can create them.

Feature Comparison:

A user name is database system wide in Oracle, but SQL Server uses login IDs to access the instance and user accounts for individual databases.

Therefore, compared to Oracle; In SQL Server, additionally, a user account must be created in every database that a login needs access to and can be named differently from the login name.

Migration Approach

SSMA doesn't support automatic migration of User Accounts. In SQL Server, use T-SQL to create logins & users and assign permissions.

Below are helpful hints/guidance to migrate Users from Oracle to SQL Server:

Users of Oracle and SQL Server databases are broadly classified as administrative users, application users, and schema owners.

• Administrative users are users with special roles, such as database administrator and security administrator.
• Application users are users who manipulate data in the owning user's tables.
• Schema owners are users who create and maintain objects related to an application.

The basics for the creation of all the three types of users are the same.

The following query can be run in the source Oracle database to create a list of users that have privileges on any object in a specific schema. The query is constrained to only a specific schema and its users. This aids in situations where only a subset of the schemas and the related users are being migrated:

SELECT grantee FROM dba_tab_privs WHERE owner = username

UNION

SELECT grantee FROM dba_col_privs WHERE owner = username;

The grantee could be a user or a role. Obtain the characteristics of user accounts in Oracle to be migrated:

SELECT du.username,

DECODE(du.password,'EXTERNAL','EXTERNAL','DB') "AUTHENTICATION MODE",

du.default_tablespace, du.temporary_tablespace,

dp.resource_name, dp.limit

FROM dba_users du, dba_profiles dp

WHERE du.profile = dp.profile

AND dp.resource_type = 'PASSWORD'

AND du.username = 'OE';

where OE is the name of the user that is being migrated.

Create SQL Server login accounts that provide access to the SQL Server instance, and Create a user account in each of the databases in which the schema's objects have been migrated.

The system stored procedure sp_grantlogin is used to create a SQL Server login for a domain-authenticated account. sp_addlogin is used to create a SQL Server authenticated account. The procedure sp_grantdbaccess is used to create user accounts in the individual databases for these logins. User accounts should be created in a database only if there are objects in the database the user needs to access.

T-SQL DDL commands:

To create login

CREATE LOGIN AbolrousHazem WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';

Following creates a database user for the login created above: CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;

To retrieve all Logins in SQL Server, you can execute the following SQL statement:

SELECT * FROM master.sys.sql_logins;

For a list of SQL Users:

SELECT * FROM sys.database_principals

After user migration is done, make sure to reproduce the privileges they possess in the Oracle database.

References

https://msdn.microsoft.com/en-us/library/aa337545.aspx

https://www.techonthenet.com/sql_server/users/create_login.php

Feature ID

60

Feature

Row-Level Security

Description

Protect data privacy by ensuring the right access across rows

Fine-grained access control over specific rows in a database table

Help prevent unauthorized access when multiple users share the same tables, or to implement connection filtering in multitenant applications.

Oracle Label Security (OLS) enables you to enforce row-level security for your tables. Hides rows and data depending on user access grants. You can accomplish this by assigning one or more security labels that define the level of security you want for the data rows of the table.

You then create a security authorization for users based on the OLS labels.

For example, rows that contain highly sensitive data can be assigned a label entitled HIGHLY SENSITIVE; rows that are less sensitive can be labeled as SENSITIVE, and so on. Rows that all users can have access to can be labeled PUBLIC. You can create as many labels as you need, to fit your site's security requirements. In a multitenant environment, the labels apply to the local pluggable database (PDB) and the session labels apply to local users.

After you create and assign the labels, you can use Oracle Label Security to assign specific users authorization for specific rows, based on these labels. Afterward, Oracle Label Security automatically compares the label of the data row with the security clearance of the user to determine whether the user is allowed access to the data in the row.

You can create Oracle Label Security labels and policies in Enterprise Manager, or you can create them using the SA_SYSDBA, SA_COMPONENTS, and SA_LABEL_ADMIN PL/SQL packages.

Category

Security

To Find Feature Enablement

Check if Oracle Label Security is enabled:

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';

Recommendation

Feature Description:

In SQL Server, Implement RLS by using the CREATE SECURITY POLICY Transact-SQL statement, and predicates created as inline table valued functions.

It is highly recommended to create a separate schema for the RLS objects (predicate function and security policy).

RLS supports two types of security predicates.

FILTER silently filters the rows available to read operations (SELECT, UPDATE, and DELETE).

BLOCK explicitly blocks write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. The function is then invoked and enforced by a security policy. For filter predicates, there is no indication to the application that rows have been filtered from the result set; if all rows are filtered, then a null set will be returned. For block predicates, any operations that violate the predicate will fail with an error.

Administer via SQL Server Management Studio or SQL Server Data Tools

Enforcement logic inside the database and schema bound to the table.

Feature Comparison:

RLS feature is supported by SQL Server as well.

The access restriction logic is located in the database tier rather than away from the data in another application tier. The database system applies the access restrictions every time that data access is attempted from any tier.

Migration Approach

SSMA can't migrate Row Level Security directly.

In SQL Server, Row-Level Security can be implemented manually by using the CREATE SECURITY POLICY Transact-SQL statement, and predicates defining filtering criteria created as inline table valued functions.

Step 1: Create a new inline table valued function. The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = 'Manager').

CREATE TABLE Sales

(

OrderID int,

SalesRep sysname,

Product varchar(10),

Qty int

);

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS fn_securitypredicate_result

WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

Step 2: Create a security policy adding the function as a filter predicate. The state must be set to ON to enable the policy.

CREATE SECURITY POLICY SalesFilter

ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)

ON dbo.Sales

WITH (STATE = ON);

References

https://docs.oracle.com/database/121/TDPSG/GUID-72D524FF-5A86-495A-9D12-14CB13819D42.htm#TDPSG94446 (Enforcing Row-Level Security with Oracle Label Security)

https://msdn.microsoft.com/en-us/library/dn765131.aspx (Row-Level Security)

https://www.datavail.com/blog/row-level-security-never-got-this-easy-with-sql-server-2016/

http://searchsqlserver.techtarget.com/feature/Put-row-level-security-to-work-in-Azure-SQL-databases

Feature ID

111

Feature

Data Masking

Description

Data masking (also known as data scrambling and data anonymization) is the process of replacing sensitive information copied from production databases to test non-production databases with realistic, but scrubbed, data based on masking rules.

To mask data, the Data Masking Pack provides two main features:

Masking format library- The format library contains a collection of ready-to-use masking formats.

Masking definitions- A masking definition defines a data masking operation to be implemented on one or more tables in a database. Masking definitions associate table columns with formats to use for masking the data.

Category

Security

To Find Feature Enablement

Recommendation

Feature Description:

Dynamic data masking limits (DDM) sensitive data exposure by masking it to non-privileged users. It can be used to greatly simplify the design and coding of security in your application.

DDM can be configured on the database to hide sensitive data in the result sets of queries over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

DDM features full masking and partial masking functions, as well as a random mask for numeric data.

Dynamic Data Masking is applied when running SQL Server Import and Export. A database containing masked columns will result in a backup file with masked data (assuming it is exported by a user without UNMASK privileges), and the imported database will contain statically masked data.

Dynamic data masking is available in SQL Server 2016 and Azure SQL Database, and is configured by using Transact-SQL commands.

Feature Comparison:

Like Oracle, both full and partial Data Masking is supported in SQL Server.

Migration Approach

SSMA does not support migrating Data Masking directly.

Based on Masking requirements, SQL Server Dynamic Data Masking can be configured manually by using CREATE or ALTER Transact-SQL commands:

CREATE TABLE Membership

(MemberID int IDENTITY PRIMARY KEY,

FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,

LastName varchar(100) NOT NULL,

Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,

Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL

ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

Use the sys.masked_columns view to query for table-columns that have a masking function applied to them:

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function

FROM sys.masked_columns AS c

JOIN sys.tables AS tbl

ON c.[object_id] = tbl.[object_id]

WHERE is_masked = 1;         

Dropping a Dynamic Data Mask:

ALTER TABLE Membership

ALTER COLUMN LastName DROP MASKED;

GRANT UNMASK TO TestUser;

-- Removing the UNMASK permission

REVOKE UNMASK TO TestUser;

References

https://docs.oracle.com/database/121/DMKSB/GUID-E3C164DC-0004-4857-A038-54EC7B1A5118.htm (Oracle Data Masking)

http://www.oracle.com/technetwork/database/options/data-masking-subsetting/overview/ds-security-dms-2245926.pdf

https://msdn.microsoft.com/en-us/library/mt130841.aspx (Dynamic Data Masking)

Feature ID

Case Sensitive Password

Feature

104

Description

Case sensitive user passwords in Oracle: Oracle by default force case sensitivity of user passwords. The users must provide passwords in the same case (upper, lower or mixed) they created the password with. This behavior is controlled with an initialization parameter SEC_CASE_SENSITIVE_LOGON. By default, it has a value TRUE. Oracle releases before 11g didn't have case sensitivity on password

Case sensitive password in Password File in Oracle

ignorecase=n is the default with the orapwd command in oracle 11g i.e. you mention it or not it will force the password to be case sensitive when users log in as SYSDBA remotely.

To turn off password case sensitivity in password file we need to explicitly mention ignorecase=y while creating the password file.

Category

Security

Find Feature Enablement

show parameter sec_case_sensitive_logon;

Recommendation

Feature Description:

If you selected a case-sensitive collation when you installed SQL Server, your SQL Server login is also case sensitive.

Since SQL server is not case sensitive. By default, SELECT * FROM SomeTable is the same as SeLeCT * frOM soMetaBLe.

Feature Comparison:

case-sensitive password is not configurable option in SQL Server but can be implemented by applying case-sensitive collation.

Migration Approach

SSMA doesn't support automated migration for case-sensitive migration.

To enable password case-sensitivity, Select a case-sensitive collation when you install SQL Server, your SQL Server login will then become case sensitive.

For case sensitive passwords you need to use a case-sensitive collation:

SELECT * FROM dbo.TableName WHERE Password = @ password COLLATE SQL_Latin1_General_CP1_CS_AS;

ALTER DATABASE { database_name | CURRENT } COLLATE Latin1_General_100_CI_AS;

The Oracle RDBMS also provides password management functions, such as account locking, password lifetime and expiration, password history, and password complexity verification. The SQL Server RDBMS does not provide these services, and Windows security is used to provide these features.

References

http://www.oracleflash.com/37/Oracle-11g-Case-Sensitive-Passwords.html

https://www.mindstick.com/blog/360/check-case-sensitive-password-in-sql-server-using-collate-clause

http://stackoverflow.com/questions/1411161/sql-server-check-case-sensitivity

https://www.webucator.com/how-to/how-check-case-sensitivity-sql-server.cfm

Feature ID

4

Feature

Total Database size

Description

• DB size = the size of (data files + temp files + online/offline redo log files + control files)- Overall DB size includes used space and free space.
• Maximum DB Size in Oracle: There are limits, which vary depending on operating system. Example: if you have 8k bigfile tablespaces and 65,533 files the upper limit is somewhere around 2,047 petabytes!

Category

Performance

Find Feature Enablement

select

( select sum(bytes)/1024/1024/1024 data_size from sys.dba_data_files ) +

( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from sys.dba_temp_files ) +

( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"

from

dual

Recommendation

Feature Description:

SQL Server supports a maximum single database size of nearly 525 petabytes. SQL Server database can be further expanded by either increasing the size of an existing data or log file or by adding a new file to the database.

Please follow referred links below for actions recommended for expanding size of database.

Feature Comparison:

Like Oracle, SQL Server size can be expanded.

Migration Approach

SQL Server supports a maximum single DB size of nearly 525 petabytes. If required to migrate bigger data size, it can easily be expanded by adding additional data files. Size is effectively limited only by disk size or windows limitations.

The database is expanded by either increasing the size of an existing data or log file or by adding a new file to the database.

To increase the size of a database using T-SQL:

USE master;

GO

ALTER DATABASE AdventureWorks2012

MODIFY FILE

(NAME = test1dat3,

SIZE = 20MB);

GO

To add data or log files to a database using T-SQL:

USE master

GO

ALTER DATABASE AdventureWorks2012

ADD FILEGROUP Test1FG1;

GO

ALTER DATABASE AdventureWorks2012

ADD FILE

(

NAME = test1dat3,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

),

(

NAME = test1dat4,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',

SIZE = 5MB,

MAXSIZE = 100MB,

FILEGROWTH = 5MB

)

TO FILEGROUP Test1FG1;

GO

To increase the size of a database using SQL Server Management Studio:

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand Databases, right-click the database to increase, and then click Properties.
3. In Database Properties, select the Files page.
4. To increase the size of an existing file, increase the value in the Initial Size (MB) column for the file. You must increase the size of the database by at least 1 megabyte.
5. To increase the size of the database by adding a new file, click Add and then enter the values for the new file. For more information, see Add Data or Log Files to a Database.
6. Click OK.

References

https://msdn.microsoft.com/en-us/library/ms175890.aspx (Increase the Size of a Database)

https://msdn.microsoft.com/en-us/library/ms143432.aspx

http://docs.oracle.com/cd/B19306_01/server.102/b14237/limits002.htm#i287915 (physical DB limits)

http://docs.oracle.com/cd/B19306_01/server.102/b14237/limits003.htm#i288032 (logical DB limits)

http://awads.net/wp/2010/02/15/oracle-database-limits-you-may-not-know-about/ (data type limits)

Feature ID

9

Feature

Oracle Database Version

Description

The version information is retrieved in a table called v$version. It returns detailed version number of the database components.

Category

General

Find Feature Enablement

SELECT * FROM SYS.PRODUCT_COMPONENT_VERSION;

SELECT * from V$VERSION;

Recommendation

Feature Description:

The most up-to-date version of Microsoft's RDBMS is SQL Server 2016, released in June 2016.

Microsoft offers four different editions of SQL Server 2016, plus a web edition for web hosting providers.

Express Edition is a lightweight SQL Server database that can support up to 10 GB of data, while Developer Edition is licensed exclusively for development and test environments.

The other SQL Server versions include Enterprise, Standard and Web.

Enterprise Edition comes with the full suite of features suitable for mission-critical databases and advanced analytics workloads, while Standard Edition comes with a more limited set of features suited to a smaller-scale setup.

Web Edition is for use with public websites and is available exclusively to third-party hosting service providers, who set the price.

Migration Approach

You can find version of SQL Server running by T-SQL query:

SELECT @@VERSION

SQL Server 2016

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)

Apr 29 2016 23:23:58

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows 10 Pro 6.3 <x64> (Build 14393: )

In SQL Server Management Studio, right click on the instance name and selecting properties. The "Product version" or "Version" gives you a number of the version that is installed:

The first digits refer to the version of SQL Server such as:

8.0 for SQL Server 2000

9.0 for SQL Server 2005

10.0 for SQL Server 2008

10.5 for SQL Server 2008 R2

11.0 for SQL Server 2012

12.0 for SQL Server 2014

13.0 for SQL Server 2016

References

https://www.techonthenet.com/oracle/questions/version.php

https://www.mssqltips.com/sqlservertip/1140/how-to-tell-what-sql-server-version-you-are-running/

https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version,-edition-and-update-level-of-sql-server-and-its-components

http://sqlserverbuilds.blogspot.com/

Feature ID    

34

Feature

Set Schema

Description

• A "database" in Oracle typically refers to the complete instance.
• You can consider that a user is the account you use to connect to a database, and A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The CREATE USER command automatically creates a schema for same name. A USER owns its SCHEMA. A user may be given access to schema objects owned by different Users as well.
• The SET SCHEMA statement sets the default schema for a connection's session to the designated schema. The default schema is used as the target schema for all statements issued from the connection that do not explicitly specify a schema name.

Category

General

Find Feature Enablement

• Query database names
  • select * from v$database;
  • select ora_database_name from dual;
• use select instance_name from v$instance; to find out which instance are you currently connected to
• TNSNAMES.ora also has the details about which database you are connecting to

Recommendation

Feature Description:

• When a login connects to SQL Server
  • the login is automatically connected to its default database and acquires the security context of a database user.
  • If no database user has been created for the SQL Server login, the login connects as guest.
  • If no default database has been assigned to the login, its default database will be set to master.
• USE is executed at both compile and execution time and takes effect immediately. Therefore, statements that appear in a batch after the USE statement are executed in the specified database. If the database user does not have CONNECT permission on the database, the USE statement will fail.

Feature Comparison:

• As there is typically only a single instance/installation there is no sense in "switching a database" in Oracle. The closest thing to switch the current schema in Oracle is to "USE mydatabase" in SQL Server.

  Show databases;

  Use databaseName;

Migration Approach

You need to choose how to map the Oracle schemas to the target. In SQL Server, schemas are not necessarily linked to a specific user or a login, and one server contains multiple databases.

Using SSMA tool for migration, you can follow one of two typical approaches to schema mapping:

• By default, in SSMA, every Oracle schema becomes a separate SQL Server database. The target SQL Server schema in each of these databases is set to dbo—the predefined name for the database owner. Use this method if there are few references between Oracle schemas.
• Another approach is to map all Oracle schemas to one SQL Server database. In this case, an Oracle schema becomes a SQL Server schema with the same name. To use this method, you change the SSMA default settings. Use this method if different source schemas are deeply linked with each other (for instance if there are cross-references between Oracle tables in different schemas, when trigger is on the table and the tables itself are in different schemas…).

SSMA applies the selected schema-mapping method consistently when it converts both database objects and the references to them.

A schema is separate entity within the database. It is created by using the CREATE SCHEMA statement. A schema can be owned by a user, a role, or a group (for more information about possible schema owners, see the "Principals" section in this document). A user executing CREATE SCHEMA can be the owner of the schema or it can allocate another user as the schema owner (with appropriate IMPERSONATE permissions). A schema only has one owner, but a user can own many schemas.

Default Schema

Users can be defined with a default schema. The default schema is the first schema that is searched when it resolves the names of objects it references.

The default schema for a user can be defined by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema. It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user. If the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema.

References

http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj32268.html

https://msdn.microsoft.com/en-us/library/ms188366.aspx

http://www.baezaconsulting.com/index.php?option=com_content&view=article&id=46:use-database-command-in-oracle&catid=7:oracle-answers&Itemid=12

Feature ID    

113

Feature

Admin Accounts

Description

The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. EM Express allows you to log in as user SYS and connect as SYSDBA or SYSOPER. SYS or SYSTEM, are Oracle's internal data dictionary accounts. You set the SYS account password upon installation (Windows) or configuration (Linux).

CONNECT SYSTEM/<password>

You set the SYS and SYSTEM account password upon installation (Windows) or configuration (Linux).

CONNECT SYS/<password> AS SYSDBA

To connect as SYSDBA you must supply the SYS user name and password.

CONNECT / AS SYSDBA

The slash (/) indicates that the database should authenticate you with operating system (OS) authentication. when you connect with OS authentication, you are effectively logging in to the database as user SYS. An administrator who is authenticated through OS authentication does not need to know the SYS or SYSTEM account password.

SYSTEM user is also automatically created when Oracle database is installed & is automatically granted the DBA role

It's used to create additional tables and views that display administrative information. This account can perform all administrative functions except Backup and recovery, and Database upgrade.

While this account can be used to perform day-to-day administrative tasks, Oracle strongly recommends creating named users account for administering the Oracle database to enable monitoring of database activity.

When you connect with the SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC. The SYSDBA role is like "root" on Unix or "Administrator" on Windows.

SYSDBA and SYSOPER are administrative privileges to perform high-level admin operations such as creating, starting up, shutting down, backing up, or recovering the database.

The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks, but without the ability to look at user data. The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open, and allow to connect to the database instance to start the database.

Category

Admin

Find Feature Enablement

Recommendation

Feature Description:

SA is a SQL login administrator account that can be used if mixed authentication is enabled on SQL Server.

Like sa, dbo is the most powerful user in a database and no permissions can be denied to him. dbo is a member of the db_owner database role. mapping of dbo is not done at user creation – dbo always exists since the database was created; instead, the login to which dbo maps is determined by what login is the owner of the database – dbo will always map to the login that is marked as the database owner.

Feature Comparison:

Like Oracle, root account for SQL server gets created at time of installation, and is assigned all admin privileges.

Migration Approach

Admin Accounts for SQL Server are set up independently. There is no migration of Admin accounts from Oracle database.

The SA account gets automatically created on every new SQL Server installation. But this account is disabled by default if you select Windows Authentication during setup. sa is the primary admin login name, which is, by default, mapped to the dbo user in all the databases. The sa login is hardcoded to be a member of the sysadmin server role. sa/sysadmin pair represent ownership of the server system. "sa" is a SQL Server login, and "BUILTIN\Administrators" is an Integrated Windows Group login.

sa admin is well-known, so target for hackers. Our recommendation would be to disable sa admin created as part of installation; instead create a separate user & assign admin privileges.

References

https://docs.oracle.com/database/121/ADMQS/GUID-2033E766-8FE6-4FBA-97E0-2607B083FA2C.htm#ADMQS12004

https://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/users_secure.htm#CHDJIEBA

https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm#TDPSG20303

https://blogs.msdn.microsoft.com/lcris/2007/09/12/basic-sql-server-security-concepts-permissions-and-special-principals-sa-dbo-guest/

https://www.mssqltips.com/sqlservertip/3695/best-practices-to-secure-the-sql-server-sa-account/

Feature ID

38

Feature

Data Dictionary

Description

The data dictionary is structured in tables and views & store information about the database.

• In Oracle, the data dictionary is stored in the SYSTEM tablespace. The Oracle user SYS owns all base tables and user-accessible views of the data dictionary. When you connect as user SYS, although you have unlimited privileges on data dictionary tables; but you don't modify any data dictionary tables.
• A data dictionary contains:
  • The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
  • How much space has been allocated for, and is currently used by, the schema objects
  • Default values for columns
  • Integrity constraint information
  • The names of Oracle users
  • Privileges and roles each user has been granted
  • Auditing information, such as who has accessed or updated various schema objects
• Oracle creates public synonyms for many data dictionary views so users can access them conveniently.
• In Oracle the instance and the database are closely related, there are not system databases like in SQL Server.

Category

Admin

To Find Feature Enablement

View Data dictionary:SELECT * from DICT;

this query returns all the objects contained in your schema:

SELECT object_name, object_type FROM USER_OBJECTS;

this query returns all the objects to which you have access:

SELECT owner, object_name, object_type FROM ALL_OBJECTS;

to query the DBA views, administrators must prefix the view name with its owner, SYS, as in the following:

SELECT owner, object_name, object_type FROM SYS.DBA_OBJECTS;

Oracle recommends that you protect the data dictionary to prevent users that have the ANY system privilege from using those privileges on the data dictionary. To enable data dictionary protection, following initialization parameter set to FALSE (which is default) in the initsid.ora control file: O7_DICTIONARY_ACCESSIBILITY = FALSE. This restricts access to objects in the SYS schema (dictionary objects) to users with the SYS schema. These users are SYS and those who connect as SYSDBA.

SELECT * from DICTIONARY;

Recommendation

Feature Description:

• SQL Server System Catalog contain information about all the objects, data types, constraints, configuration options, and resources available to SQL Server. Each database has a system catalog and the structure of the database is defined by this catalog.
• System catalog is stored in the system tables. All SQL Server system tables are under sys schema so have names prefixed with "sys".
• System catalog consists of the following:
  • Catalog View: This is the best way to access system metadata.
  • Backward Compatibility Views: This contains all system tables from previous releases
  • Dynamic Management Views: These enable insight into the current state of the SQL Server system and provide real-time snapshots of internal memory structures indicating the server state.
  • INFORMATION_SCHEMA Views: For SQL-99 standards compliance; allow to view system metadata.
  • In SQL Server, each instance has the system databases which includes:
    • the master database which stores the system information,
    • the model database which contains a configuration template for new databases created,
    • the tempdb database used for temporary storage or temporary results,
    • the msdb database which contains the SQL Server Agent configuration and
    • the resource database which contains system objects included in SQL Server.

Feature Comparison:

Similar to Oracle, SQL Server provides system views and table for metadata on database objects.

Migration Approach

SSMA for Oracle V6.0 can convert Oracle system views, which are frequently used. It does not convert columns that are too closely linked with Oracle physical structures or have no equivalent in SQL Server 2014.

Please refer SSMA Guide- Emulating Oracle System Objects page 21

SQL Server's resource database contains the metadata for system stored procedures

SELECT * FROM sys.columns WHERE object_id = object_id('myTable');

SELECT * FROM mydb.INFORMATION_SCHEMA.TABLES;

The following table maps the system tables to their corresponding system views or functions in SQL Server 2016.

References

https://docs.oracle.com/database/121/CNCPT/datadict.htm#CNCPT002 (Oracle 12c Data Dictionary Views)

http://www.dummies.com/programming/databases/how-to-use-oracle-12cs-data-dictionary/

https://msdn.microsoft.com/en-us/library/ms345522.aspx (Querying the SQL Server System Catalog FAQ)

https://docs.oracle.com/cd/B19306_01/gateways.102/b14270/apc.htm

Feature ID

112

Feature

Diagnostics and Performance views

Description

• When Oracle is running, set of tables is continually updated with current system metrics. Access is limited to DBA users by default These are in-memory virtual tables, read only views used for performance tuning, session monitoring, etc.
• The prefix used is V$. Also known as Oracle Dynamic Performance Views (V$ Views).
• It shows current state of database. If you are running clusters, you have V$ views, but you also have GV$ views (global views).- you will have Instance_Id for each node in cluster.
• Its system generated views, DBA can't change, remove or modify them. These views are used internally by DB or can be used for monitoring by users. SYS owns V$ views. Oracle's V$ objects are actually public synonyms

Category

Admin

To Find Feature Enablement

select * from v$sql_plan_statistics

select * from v$session_wait_class

Recommendation

Feature Description:

• SQL Server provides Dynamic Management Views (DMVs), and Dynamic management functions (DMFs). They are prefixed with 'dm_'.

Feature Comparison:

• Like Oracle, SQL Server also has system views and functions that give insight into the current state of the system and provide real-time snapshots of internal memory structures indicating the server state.

Migration Approach

Dynamic Management Views and Functions are available in SQL Server, and are enabled by default.

There are two types of dynamic management views and functions: Server-scoped dynamic management views and functions.

To access them a user requires SELECT permission on object and require VIEW SERVER STATE permission on the server. Database-scoped dynamic management views and functions require VIEW DATABASE STATE permission on the database.

• All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema.
• Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names. Dynamic management functions on the other hand can be referenced in Transact-SQL statements by using either two-part or three-part names. Dynamic management views and functions cannot be referenced in Transact-SQL statements by using one-part names.
• You could Monitor database and instance activity, using DMVs and, monitor performance and scalability using DMFs

  select * from sys.dm_exec_query_stats

  select * from sys.dm_exec_session_wait_stats

• Retrieving connection information: Use sys.dm_exec_connections to view information about the current connections to SQL Server. It helps you find which processes are currently connected to the instance. The following are the columns commonly used by sys.dm_exec_connections: session_id, most_recent_session_id, connection_time, client_net_address, last_read, last_write, auth_scheme, and most_recent_sql_handle. For example, the following query shows the most recent SQL text executed for each session connected to the SQL Server:

  SELECT ec.[session_id]

  ,ec.[connect_time]

  ,ec.[client_net_address]

  ,ec.[last_read]

  ,ec.[last_write]

  ,ec.[auth_scheme]

  ,qt.[text]

  FROM [sys].[dm_exec_connections] ec

  CROSS APPLY [sys].[dm_exec_sql_text](ec.[most_recent_sql_handle]) AS qt

• Retrieving currently executing query and blocking information. It can be useful to find out what requests are currently executing on SQL Server at any given time. For this you can use the sys.dm_exec_requests dynamic management view. This SQL DMV includes detailed information about the query and query plan, the status of the request and information about the amount of time the query has been executing. The columns you are most likely to use are:
  • blocking_session_id: The Service Profile Identifier of the blocking session
  • wait_type: type of wait
  • wait_time: length of time request has been waiting (in milliseconds)
  • last_wait_type: if a wait has ended, its type is listed here
  • wait_resource: name of resource the request is waiting for
  • lock_timeout: length of time a lock can exist before timing out

The DMV is ideal for troubleshooting blocking, as the following example shows:

SELECT [session_id]

,[blocking_session_id]

,[status]

,[wait_time]

,[wait_type]

,[wait_resource]

,[transaction_id]

,[lock_timeout]

FROM [sys].[dm_exec_requests]

WHERE [blocking_session_id] <> 0

The following figure shows example results for blocking sessions:

References

https://docs.oracle.com/database/121/CNCPT/datadict.htm#CNCPT88897

https://msdn.microsoft.com/en-us/library/ms188754.aspx

http://searchsqlserver.techtarget.com/tip/Manage-your-SQL-Server-databases-with-SQL-DMVs

Feature ID

109

Feature

Feature Usage Statistics

Description

DBA_FEATURE_USAGE_STATISTICS view is to display information about database feature usage statistics.

Some of the information tracked are:

• Name of the feature
• # of times the system has detected usage for the feature
• First sample time the system detected usage of the feature
• Last sample time the system detected usage of the feat

Category

To Find Feature Enablement

• Functions: select name c1, detected_usages c2, first_usage_date c3, currently_used c4 from dba_feature_usage_statistics where first_usage_date is not null;
• Below sql gives the detail on oracle services being used:

  select NAME, VERSION, DETECTED_USAGES, CURRENTLY_USED, FIRST_USAGE_DATE, LAST_USAGE_DATE from DBA_FEATURE_USAGE_STATISTICS where CURRENTLY_USED  = 'TRUE' order by 1,2;

Recommendation

Feature Description:

Tracking SQL Server object usage can be done with the Audit feature. To track object use with the SQL Server Audit feature, it's necessary to set up the auditing. To do this, an audit object must be created first. This can be done using SQL Server Management Studio or T-SQL.

To continue setting up the auditing, it's required to create a database level audit specification. Such database level audit specification will belong to the audit object previously created.

Although SQL Server provides a built-in feature (the View Audit Logs context menu option of an audit object) to view captured information, this is not a convenient way for creating comprehensive reports, and it provides basic filtering only. So, in order to provide tracked information for any deeper analysis or documenting, use the fn_get_file_audit SQL Server function to read repository .sqlaudit files used by the audit object.

Feature Comparison:

Feature usage tracking via System view is NOT available in SQL Server; but tracking database object usage can be done with the Audit feature in SQL Server.

Migration Approach

Feature Usage Statistics support can't be migrated through SSMA tool.

Tracking SQL Server object usage with the Audit feature

The database level auditing is available in SQL Server Enterprise and Developer editions only.

To track object use with the SQL Server Audit feature, it's necessary to set up the auditing. In order to do so, an audit object must be created first. This can be done using SQL Server Management Studio or T-SQL

The following T-SQL creates and enables the AuditObjectUsage audit object:

USE [master];

GO

CREATE SERVER AUDIT [AuditObjectUsage] TO FILE (

FILEPATH = N'C:\AUDITs\'

, MAXSIZE = 15 MB

, MAX_FILES = 10

, RESERVE_DISK_SPACE = OFF

)

WITH (

QUEUE_DELAY = 1000

, ON_FAILURE = CONTINUE

);

ALTER SERVER AUDIT [AuditObjectUsage]

WITH (STATE = ON);

GO

With above T-SQL audited info will be stored in maximum 10 files (each 15 MB in size), located in the AUDITs sub-folder on the local drive. This can be modified per requirements. The next step is to set up the auditing in the particular database on specific objects.

To continue setting up the auditing, it's required to create a database level audit specification. Such database level audit specification will belong to the audit object (AuditObjectUsage) we previously created. The following T-SQL creates and enables the database level audit specification:

USE [ACMEDBNEW];

GO

CREATE DATABASE AUDIT SPECIFICATION [ObjectUseSpecification]

FOR SERVER AUDIT [AuditObjectUsage]

ADD (DELETE ON OBJECT::dbo.Customers BY [public]),

ADD (INSERT ON OBJECT::dbo.Customers BY [public]),

ADD (SELECT ON OBJECT::dbo.Customers BY [public]),

ADD (UPDATE ON OBJECT::dbo.Customers BY [public]),

ADD (EXECUTE ON OBJECT::dbo.Customers BY [db_owner]),

ADD (EXECUTE ON OBJECT::dbo.Invoices BY [db_securityadmin])

WITH (STATE = ON);

GO

Within the T-SQL we specified that the Customers table will be audited for particular actions (SELECT/INSERT/UPDATE/DELETE/EXECUTE), while the Invoices table will be audited for EXECUTE operations only. Note that it's possible to specify only one object and one principal per event.

Although SQL Server provides a built-in feature (the View Audit Logs context menu option of an audit object) to view captured information, this is not a convenient way for creating comprehensive reports, and it provides basic filtering only.

So, in order to provide tracked information for any deeper analysis or documenting, use the fn_get_file_audit SQL Server function to read repository .sqlaudit files used by the audit object. The following T-SQL script queries the information tracked by the AuditObjectUsage server level audit object:

SELECT

event_time AS [Event time],

session_server_principal_name AS [User name] ,

server_instance_name AS [Server name],

database_name AS [Database name],

object_name AS [Audited object],

statement AS [T-SQL statement]

FROM sys.fn_get_audit_file('C:\AUDITs\AuditObjectUsage*.sqlaudit', DEFAULT,

DEFAULT);

The SQL Server Audit feature is native, but when it comes to tracking database level objects, it is supported by SQL Server Enterprise and Developer editions only.

ApexSQL Audit is a compliance tool for SQL Server that features a range of auditing and documenting captured information options via a user-friendly GUI. It helps ensuring SQL Server security and requirements for compliance regulations by tracking changes and access to objects on one or more SQL Server instances

References

https://oracle-base.com/articles/misc/tracking-database-feature-usage

http://solutioncenter.apexsql.com/tracking-sql-server-object-usage/

http://dba.stackexchange.com/questions/24294/tracking-object-use-using-sql-auditing

Editions and Supported Features for SQL Server 2016

https://msdn.microsoft.com/en-us/library/cc645993.aspx

Feature ID

35

Feature

Oracle Component Installed

Description

Oracle provides several views (dba_registry and v$option) that display the installed features within the database.

Category

General

To Find Feature Enablement

• select comp_name, version from dba_registry where status = 'VALID';
• select parameter from v$option where value = 'TRUE' order by parameter;

Recommendation

Feature Description:

The Discovery Report feature is included in the SQL Server Installation Center under Configuration Tools. this feature can be launched from the Start menu. This will produce the report of all discovered versions/components of SQL Server that exist on your machine. The SQL Server Discovery Report is saved to %ProgramFiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\<last Setup Session>\SqlDiscoveryReport.htm.

Feature Comparison:

Like Oracle, SQL Server supports finding out Installed database components.

Migration Approach

SSMA tool doesn't support migrating installed components information.

However, In SQL Server, licensing is simple, because every feature and capability is already built into edition itself. There's no extra add-ons to run. SQL Server Discovery Report will produce the report of all discovered versions/components of SQL Server that exist on your machine.

The SQL Server discovery report can be used to verify the version of SQL Server and the SQL Server features installed on the computer. The Installed SQL Server features discovery report displays a report of all SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016 products and features that are installed on the local server. The SQL Server features discovery report is available on the Tools page on the SQL Server Installation center.

The SQL Server discovery report is saved to %ProgramFiles%\MicrosoftSQL Server\130\Setup Bootstrap\Log\<last Setup Session>

You can also generate the discovery report through the command line. Run "Setup.exe /Action=RunDiscovery" from a command prompt If you add "/q" to the command line above no UI will be shown, but the report will still be created in %ProgramFiles%\MicrosoftSQL Server\130\Setup Bootstrap\Log\<last Setup Session>

References

http://www.dba-oracle.com/t_list_installed_components.htm

Editions and Supported Features for SQL Server 2016

https://msdn.microsoft.com/en-us/library/cc645993.aspx

https://technet.microsoft.com/en-us/library/bb510455(v=sql.130).aspx

Feature ID    

18

Feature

Shut Down

Description

To shut down Oracle database and instance, you must first connect as SYSOPER or SYSDBA. modes for shutting down a database:

• shutting down with the NORMAL clause (default): Oracle will close all sessions, close the database, un-mount the data files and then shut down the instance in two steps, first issuing a "free" the SGA RAM heap and finally, terminating the background processes. Since it waits for all in-flight work to be complete, this could take hours.
• IMMEDIATE clause: terminates all sessions and does a rollback on all uncommitted transactions
• TRANSACTIONAL clause,
• ABORT clause.

Some shutdown modes wait for certain events to occur (such as transactions completing or users disconnecting) before actually bringing down the database. There is a one-hour timeout period for these events

SQL> shutdown

SQL> shutdown immediate

SQL> shutdown abort

Category

Admin

Find Feature Enablement

This query shows current instance info- time instance was started, current status, & if any shutdown is pending:

SELECT TO_CHAR(STARTUP_TIME,'MON-DD-RR HH24:MI:SS') AS "Inst Start Time",

SHUTDOWN_PENDING,

DATABASE_STATUS

FROM V$INSTANCE;

Recommendation

Feature Description:

SQL Server is among the most reliable database systems; you may still occasionally need to shut it down or stop SQL Server for a planned maintenance or relocation.

SQL Server supports Shutdown in multiple modes via T-SQL or stopping Windows Services with dba privileges.

Feature Comparison:

Like Oracle, SQL Server supports Shutdown in multiple modes with dba privileges.

Migration Approach

SHUTDOWN permissions are assigned to members of the sysadmin and serveradmin fixed server roles, and they are not transferable. SHUTDOWN can be performed by following methods:

using T-SQL commands:

SHUTDOWN

Immediately stops SQL Server. performs an orderly shutdown of the server, with SQL Server checkpointing all databases and flushing all committed data to disk.

SHUTDOWN WITH NOWAIT

Shuts down SQL Server without performing checkpoints in every database. SQL Server exits after attempting to terminate all user processes. When the server restarts, a rollback operation occurs for uncompleted transactions.

using the Windows Services from Control Panel:

stop the MSSQLServer service (or the MSSQL$InstanceName service, if you have a named instance), to stop the instance that you've selected.

using the SQL Server Configuration Manager:

This issues a checkpoint in all databases. You can flush committed data from the data cache and stop the server.

using command prompt:

run net stop mssqlserver for a default instance,

run net stop mssql$instancename for a named instance.

If sqlservr.exe was started from the command prompt, pressing CTRL+C shuts down SQL Server. However, pressing CTRL+C does not insert a checkpoint.

Using any of above methods to stop SQL Server sends the SERVICE_CONTROL_STOP message to SQL Server.

References

https://docs.oracle.com/cd/B28359_01/server.111/b28310/start003.htm#ADMIN11156

http://www.dba-oracle.com/t_oracle_shutdown_immediate_abort.htm

http://sqlmag.com/t-sql/shutting-down-sql-server

https://msdn.microsoft.com/en-us/library/ms188767.aspx

http://codingsight.com/database-checkpoints-in-sql-server

Feature ID

28

Feature

Constraints

Description

• Constraint is a way of enforcing rules in the database, and it maintains the integrity of the database.
• Constraints are defined on the columns of a table or table itself to enforce certain business rules.
• ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
• ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

Category

SQL

To Find Feature Enablement

• select * from all_constraints;
• select * from user_constraints;

Recommendation

Feature Comparison:

Both Oracle & SQL Server support same six constraints-

• PRIMARY KEY
• UNIQUE
• FOREIGN KEY
• CHECK
• NOT NULL
• DEFAULT

Migration Approach

SSMA tool performs Constraints Migration as part of converting Oracle Tables to SQL Server Tables. (Refer Feature ID 42 Migration Approach section)

References

http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj13590.html

https://www.techonthenet.com/oracle/check.php

https://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx

Feature ID    

50

Feature

Column-level check constraint

Description

Oracle check constraint insures that updated or inserted values meet a specific condition. The Oracle check constraint check condition must return a TRUE or FALSE, much Like the WHERE clause. If the Oracle check constraint condition returns as TRUE when you use Oracle check constraint, the value is accepted by the constraint. If Oracle check constraint returns the condition as FALSE, the value is rejected. Any column level constraint (exception: not null) can be expressed at the table level - but the opposite is not true. Column Level constraint is checked when the value of the column changed.

Oracle check constraint has some limitations.  For one, subqueries cannot be used within your Oracle check constraints.  Also, an Oracle check constraint is able to reference another column. Sysdate, currval, nextval, level, rowid, uid, user or userenv cannot be referenced with Oracle check constraint.

Oracle check constraint does have some limitations in its ability to validate data.  If more than one Oracle check constraint is needed, triggers must be implemented.

Category

SQL

Find Feature Enablement

SELECT constraint_name,

constraint_type,

search_condition

FROM DBA_CONSTRAINTS where constraint_type='C';

Recommendation

Feature Description:

You don't have to create constraints that only check the values of a single column. You can create constraints that check values in multiple columns at the same time.

For instance, if I wanted to create a single constraint that checked both the Salary, and SalaryType constraints, I could use the following code:

ALTER TABLE dbo.Payroll

WITH NOCHECK ADD CONSTRAINT CK_Payroll_Salary_N_SalaryType CHECK (SalaryType IN ('Hourly','Monthly','Annual')

AND Salary > 10.00

AND Salary < 150000.00);

While migrating, keep in mind:

• CHECK constraint causes validation logic overhead. The amount of overhead is determined by its complexity of evaluation (comparisons good; function calls not so much)
• Disabling unneeded CHECK constraints will reduce the load time.
• CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column specifying that MyColumn can contain only the value 10 (MyColumn=10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error. A CHECK constraint returns TRUE when the condition it is checking is not FALSE
• CHECK constraints are not validated during DELETE statements.

Feature Comparison:

CHECK constraints are supported in SQL Server as well.

Migration Approach

SSMA tool performs column-level check constraints Migration as part of converting Oracle Tables to SQL Server Tables. (Refer Feature ID 42 Migration Approach section)

While migrating, keep in mind:

• CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column specifying that MyColumn can contain only the value 10 (MyColumn=10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error. A CHECK constraint returns TRUE when the condition it is checking is not FALSE
• CHECK constraints are not validated during DELETE statements.

References

http://www.databasejournal.com/features/mssql/article.php/3811831/Using-Check-Constraints-to-Validate-Data-in-SQL-Server.htm

https://msdn.microsoft.com/en-us/library/ms187550.aspx

Feature ID

20

Feature

Views

Description

Supported View Types in Oracle & SQL Server:

Category

SQL

To Find Feature Enablement

for all views (you need dba privileges for this query):

select view_name from sys.dba_views

for all accessible views (accessible by logged user):

select view_name from sys.all_views

for views owned by logged user:

select view_name from sys.user_views

SELECT view_name, owner

FROM sys.all_views

ORDER BY owner, view_name

Recommendation

Feature Comparison:

• Both Oracle and SQL Server offer views based on simple queries involving a single table and complex queries based on multiple tables.
• Indexed views in SQL Server are the only type of view that actually stores data, similar to materialized views in Oracle. (Please refer Feature ID 82 for details on Materialized View) Everything else is run on top of the tables.
• Oracle and SQL Server offer updatable views with INSTEAD OF triggers and WITH CHECK OPTION constraints.

Migration Approach

SSMA for Oracle V6.0 converts Oracle system objects including views

It does not convert columns that are too closely linked with Oracle physical structures or have no equivalent in SQL Server 2014. The following views can be migrated automatically to SQL Server views:

• ALL_INDEXES
• DBA_INDEXES
• ALL_OBJECTS
• DBA_OBJECTS
• ALL_SYNONYMS
• DBA_SYNONYMS
• ALL_TAB_COLUMNS
• DBA_TAB_COLUMNS
• ALL_TABLES
• DBA_TABLES
• ALL_CONSTRAINTS
• DBA_ CONSTRAINTS
• ALL_SEQUENCES
• DBA_SEQUENCES
• ALL_VIEWS
• DBA_VIEWS
• ALL_USERS
• DBA _USERS
• ALL_SOURCE
• DBA_SOURCE
• GLOBAL_NAME
• ALL_JOBS
• DBA_ JOBS
• V$SESSION

There are ways to manually convert the following views: (Please Refer SSMA Migration Guide V6.0 Page 21)

• ALL_EXTENTS
• V$LOCKED_OBJECT
• DBA_FREE_SPACE

DBA_SEGMENTS

References

http://www.dba-oracle.com/concepts/views.htm

http://www.oratable.com/oracle-views-features/

http://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-server-views

https://www.mssqltips.com/sql-server-tip-category/50/views/

Feature ID    

37

Feature

Triggers

Description

A trigger is an exceptional sort of stored procedure which functions when we try to amend the data in a table like inserting, deleting or updating data.  It is a database object, executed automatically and is bound to a table.

Trigger types supported in both Oracle and SQL Server:

• DML – Insert
• DML – Update
• DML – Delete
• Timing – Before
• Timing – After
• Level
• Views – INSTEAD OF
• Multiple triggers per actions
• DDL triggers
• Login triggers
• Single trigger for multiple actions

Category

SQL

Find Feature Enablement

select * from all_triggers;

select * from DBA_TRIGGERS

select * from USER_TRIGGERS

Recommendation

Feature Comparison:

• Note that SQL Server does not have an exact equivalent of Oracle's 'Before' trigger. During migrations, Oracle's 'Before' trigger is replaced by SQL Server's "Instead Of" trigger.
• Also note that many triggers in Oracle databases assign only default values – this is not efficient and overcomplicates the implementation. For default values use SQL Server's built-in database default value capability. Don't use (or continue to use) triggers to assign default values.

Migration Approach

SSMA tool performs Trigger Migration as part of converting Oracle Tables to SQL Server Tables.

Refer Feature ID 42 Migration Approach section, Also Refer Migrating Oracle Triggers section in SSMA Guide for specific detailed information on migrating different types of Trigger.

Using SSMA, you can migrate Oracle Row-level triggers, if SSMA generates a special ROWID column for the SQL Server table. Therefore, if you are converting tables with UPDATE triggers, we recommend setting the Generate ROWID column option to Yes or Add ROWID column for tables with triggers in the SSMA project settings (See Figure below). To emulate row-level triggers, SSMA processes each row in a cursor loop.

Since, SQL Server does not have an exact equivalent of Oracle's 'Before' trigger. To emulate this in SQL Server, you must create INSTEAD OF triggers. That means you must incorporate the triggering statement into the target trigger's body. Because multiple rows can be affected, SSMA puts the statement in a separate cursor loop.

In some cases, you cannot convert Oracle triggers to SQL Server triggers with one-to-one correspondence. If an Oracle trigger is defined for several events at once (for example, INSERT or UPDATE), you must create two separate target triggers, one for INSERT and one for UPDATE. In addition, because SQL Server supports only one INSTEAD OF trigger per table, SSMA combines the logic of all BEFORE triggers on that table into a single target trigger. This means that triggers are not converted independently of each other; SSMA takes the entire set of triggers belonging to a table and converts them into another set of SQL Server triggers so that the general relation is many-to-many.

In brief, the conversion rules are:

• BEFORE triggers for a table are converted into one INSTEAD OF trigger.
• AFTER triggers remain AFTER triggers in SQL Server.
• INSTEAD OF triggers are converted to INSTEAD OF triggers. Multiple INSTEAD OF triggers defined on the same operation are combined into one trigger.
• Row-level triggers are emulated using cursors.
• Cascading triggers are converted into multiple individual triggers.

Triggers that are defined for multiple events are split into separate target triggers.

References

https://technet.microsoft.com/en-us/library/ms179288(v=sql.105).aspx

http://www.way2tutorial.com/plsql/plsql_triggers.php

http://blog.sqlauthority.com/2013/01/24/sql-server-use-instead-trigger/

Feature ID    

21

Feature

Indexes

Description

Oracle uses indexes for query performance.

Category

SQL

To Find Feature Enablement

select dbms_metadata.get_ddl('INDEX', index_name, owner)

from all_indexes;

Recommendation

Feature Description:

• During migration, make sure you understand the index capabilities of SQL Server and use those capabilities.
• Before migrating indexes it is important to ensure that they are actually used. Do not migrate unused indexes as this can result in a lot of unnecessary processing.
• Also note that since SQL is more "set-based" than Oracle, it may need fewer indexes due to advanced query optimizations.
• We recommend that you use the index tuning wizard after migration to recommend new indexes. Do not run this wizard during peak load times, however.
• Oracle's invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level.
• Disabled indexes in SQL Server prevent user access to the index, and for clustered indexes, to the underlying table data. The index definition remains in metadata and index statistics are kept on nonclustered indexes. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for DML operations until the index is dropped or rebuilt.
• Clustered Columnstore indexes in the SQL Server Database Engine, with improved data compression, can be used to significantly speed-up the processing time of common data warehousing workloads that primarily perform bulk loads and read-only queries. If most of your queries are small lookup queries, seeking into a B-tree index may be faster and you may not find a columnstore index to be beneficial. If you test a columnstore index and it does not benefit your workload, you can drop or disable the index.

Feature Comparison:

Following table highlights comparative support:

Migration Approach

References

https://www.techonthenet.com/oracle/indexes.php

http://viralpatel.net/blogs/invisible-indexes-in-oracle-11g/

https://www.simple-talk.com/sql/performance/14-sql-server-indexing-questions-you-were-too-shy-to-ask/

https://www.brentozar.com/sql/index-all-about-sql-server-indexes/

http://logicalread.solarwinds.com/sql-server-2016-columnstore-pd01/#.WCcA_vorKM8

Feature ID

26

Feature

Trace Files

Description

• Trace File are trace (or dump) file that Oracle Database creates to help you diagnose and troubleshoot operating problems, and are a useful mechanism for developers and DBAs to performance tune applications. With trace data, you can track the execution of a given set of SQL statements of a session.
• Each server and background process writes to a trace file. When a process detects an internal error, it writes information about the error to its trace file.
• The file name format of a trace file is sid_processname_unixpid.trc.
• The following is a sample trace file name: $ORACLE_BASE/diag/rdbms/mydb/mydb/trace/test_lgwr_1237.trc where ORACLE_BASE is the Oracle Base Directory. For security reasons and to prevent unauthorized access to the ORACLE_HOME area, the trace file area (identified by the USER_DUMP_DEST init.ora parameter) is separate from the ORACLE_HOME area.
• MAX_DUMP_FILE initialization parameter is used to set the size of trace file.
• Use SQL Developer to see a formatted display of a SQL Trace file (*.trc file) in Oracle. You can also use The Oracle Enterprise Manager to monitor the active sessions, with the the query that are being executed, its execution plan, locks, some statistics and even a progress bar for the longer tasks.
• To gather trace at your own session, you can issue the following commands:

  ALTER SESSION SET SQL_TRACE TRUE;

  ALTER SESSION SET timed_statistics = TRUE;

  ALTER SESSION SET statistics_level=ALL;

  ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';

Category

Admin

To Find Feature Enablement

To find the trace file for your current session:

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

The full path to the trace file is returned.

To find all trace files for the current instance:

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

The path to the ADR trace directory for the current instance is returned.

To determine the trace file for each Oracle Database process, submit the following query:

SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

Recommendation

Feature Description:

In SQL Server, use Profiler tool in SQL Server to view trace files.

For best practices, to use SQL Server Profiler, pls refer below link.

Feature Comparison:

Similar to Oracle, Tracing is available in SQL Server as well. Tracing can be disabled or enabled as needed.

Migration Approach

Tracing configurations can't be migrated directly via SSMA.

In SQL Server, tracing can be configured easily. Use the default trace enabled option in SQL Server to enable or disable the default trace log files.

Default Trace in SQL Server can be enabled or disabled using the sp_configure system stored procedure.

Set the 'default trace enabled' advanced option to 1 (which is default setting for this option) to enable the default trace or set it to 0 to disable the default trace.

To change the settings for any of the advanced options (like 'default trace enabled' option) using the sp_configure system stored procedure, 'show advanced options' must be set to 1.

Default location of trace files: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\LOG\log.trc

Use the default trace enabled option in SQL Server to enable or disable the default trace log files.

To enable the default trace

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'default trace enabled', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

GO

To disable the default trace

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'default trace enabled', 0;

GO

RECONFIGURE;

GO

EXEC sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

GO

To check whether the default trace is ON (1), or OFF (0)

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'default trace enabled';

GO

EXEC sp_configure 'show advanced options', 0;

GO

RECONFIGURE;

GO

To get information for all traces in the instance of SQL Server

SELECT * FROM :: fn_trace_getinfo(default)

This will give you a list of all of the traces that are running on the server.

The property of the trace as represented by the following integers:

1. Trace Options (@options in sp_trace_create)
2. FileName
3. MaxSize
4. StopTime
5. Current Trace status (1 = On and 0 = Off)

References

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

https://msdn.microsoft.com/en-us/library/ms187929.aspx

https://www.mssqltips.com/sqlservertutorial/3501/sql-server-profiler-best-practices/

Feature ID

42

Feature

Tables

Description

Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.

You can specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.

Category

Platform

To Find Feature Enablement

Select * from dba_tables where owner in ('select user from dual');

Feature Usage

To list all tables owned by the current user, type

Select tablespace_name, table_name from user_tables;

To list all tables in a database

Select tablespace_name, table_name from dba_tables;

To list all tables accessible to the current user, type:

Select tablespace_name, table_name from all_tables;

Relational Tables:

Select * FROM all_tables

Temporary Tables

Select * from dba_tables where temporary='Y'

Select * from SYS.TTABLES

Select * from SYS.TTBL_STATS

Recommendation

Feature Description - In SQL Server Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format like a spreadsheet. Each row represents a unique record, and each column represents a field in the record

• The number of tables in a database is limited only by the number of objects allowed in a database (2,147,483,647).
• You can assign properties to the table and to each column in the table to control the data that is allowed and other properties.
• The data in the table can be compressed either by row or by page. Data compression can allow more rows to be stored on a page.

Feature Comparison -

Below tables lists the tables present in Oracle and SQL Server.

Migration Approach

In SSMA, each Oracle table is converted to a SQL Server table. During the conversion, all indexes, constraints, and triggers defined for a table are also converted. When determining the target table's structure, SSMA uses type mapping definitions.

Below are the steps to migrate your Oracle Schema to SQL Server

• Start SSMA
• Change Default Project Setting - By default, SSMA loads only basic Oracle system schemas and packages. You need to customize project settings to allow loading of the schema you want to migrate. Click on Tools from the menu and select Default Project Settings. On the Default Project Setting menu, click on Loading System Objects and check '<Schema Name You Want To Migrate>' system object.
• Create a new project- Once the program is running, click on the New Project icon in the upper left corner to get started. Specify the name of the project and the location of the file to save the project information:
• Connect to Oracle - Click on the Connect to Oracle icon from the menu toolbar and provide connection information to your Oracle database.
• Create a schema migration report - Select the schema, then right-click the schema then select Create Report:
• Connect to SQL Server - Click on the Connect to SQL Server icon from the File Menu. Specify the server name (e.g. localhost if SSMA is running on the SQL server machine) and port number (if using other than default 1433 SQL Server port number). Type the name of the database you are migrating to (e.g. HR). If the database does not exist, SSMA will create a new database using the default setting. Specify authentication information and click Connect to continue.
• Map Schema and Type - In the Oracle Metadata Explorer, check the schema and expand. You can select (or deselect) objects to be migrated as well as map schema. Schema mapping can be done at the Oracle schema level or at the individual object (such as specific table in Oracle) to SQL Server schema. 
•  Convert the schema - In the Oracle Metadata Explorer, right-click the schema and select Convert Schema:
• Review conversion report and resolve error as necessary.
• Synchronize the SQL Server database. To deploy the changes to the SQL server, right-click the database in the SQL Server metadata explorer and select Synchronize with Database.
• Migrate the data. From Oracle Metadata Explorer window, right-click on the schema and select Migrate Data. Provide connection information to both the Oracle source database and the target SQL server.
• Review Migration Report.  After the data is migrated, a report will be displayed with migration statistics

Migrating tables to Memory Optimized Tables

• SQL Server 2014 introduced In-Memory OLTP database concept which improves OLTP database performance.
• The In-Memory OLTP feature includes memory-optimized tables, table types and native compilation of stored procedures for efficient access to these tables.
• AS memory-optimized tables reside in memory, rows in the table are read from and written to memory.
• A second copy of the table data is maintained on disk, but only for durability purposes. Each row in the table potentially has multiple versions. This row versioning is used to allow concurrent reads and writes on the same row.

DDL syntax for creating memory-optimized table is as follows:

CREATE TABLE database_name.schema_name.table_name

(

column_name data_type

[COLLATE collation_name] [NOT] NULL

[DEFAULT constant_expression]

[IDENTITY]

[PRIMARY KEY NONCLUSTERED [HASH WITH (BUCKET_COUNT = bucket_count)]]

[INDEX index_name

[NONCLUSTERED [HASH WITH (BUCKET_COUNT = bucket_count)]]]

[,…]

[PRIMARY KEY

{

NONCLUSTERED HASH (column [,…]) WITH (BUCKET_COUNT = bucket_count) |

NONCLUSTERED (column [ASC|DESC] [,…] ) }

}]

[INDEX index_name

{

NONCLUSTERED HASH (column [,…]) WITH (BUCKET_COUNT = bucket_count) |

NONCLUSTERED (column [ASC|DESC] [,…] ) }

}] [,…]

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Restrinctions for conversion to memory-optimized tables:

• When converting an Oracle table that uses sequence to SQL Server table using IDENTITY property, it must be created only with SEED equal to 1 and INCREMENT equal to 1.
• If this condition is not met, SSMA generates a warning message like this: "Cannot create identity with seed 1000 and increment 5 for Memory optimized table. Allowed only Identity(1,1)". There are two ways to solve this issue.

First one is to convert the table with IDENTITY (1, 1) and add a corresponding seed to the identity column value and multiplying this value into the corresponding increment. For example, if Oracle sequence has seed value equal to 10 and increment value equal to 2:

SQL Server

CREATE TABLE imt(

id INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,

name VARCHAR(50) NOT NULL)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

SELECT 10 + (id – 1) * 2

FROM imt;

The second way is to use SQL Server SEQUENCE objects instead of IDENTITY property when inserting new records:

SQL Server

CREATE TABLE imt(

id INT NOT NULL PRIMARY KEY NONCLUSTERED,

name VARCHAR(50) NOT NULL)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

GO

CREATE SEQUENCE imt_seq AS INT START WITH 10 INCREMENT BY 2

GO

INSERT INTO imt(id, name)

SELECT NEXT VALUE FOR imt_seq, 'New Name';

The next restriction is that uniqueidentifier column default is not supported for memory-optimized tables. Besides, column defaults support only constant expressions. SSMA issues warning about that and removes the column default.

A workaround for this can be defining the column that uses uniqueidentifier default as varchar column that can contain at least 36 characters (this is the length of uniqueidentifier value in SQL Server). Insert the value to this column explicitly every time when inserts to the table are performed:

Oracle

CREATE TABLE IMT

(

ID RAW(32) DEFAULT sys_guid(),

NAME VARCHAR2(50)

);

SQL Server

CREATE TABLE [dbo].[IMT]

(

[ID] varchar(36) NULL,

[NAME] nvarchar(50) NULL,

[PKCol] int IDENTITY(1, 1) NOT NULL,

PRIMARY KEY NONCLUSTERED

(

[PKCol] ASC

)

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

GO

Conversion to memory-optimized tables is not supported on Azure SQL DB.

Performance Recommendation

• Create user tables on a non-primary filegroup; reserve the primary file group for system objects. This way the system supplied and user-defined objects do not compete for disk resources
• Create commonly accessed tables on the same filegroup. You can expect performance benefits if the data of commonly joined tables resides on the same disk.
• Create a clustered index on every table. Each table can only have a single clustered index. If a table has a clustered index, its data is physically sorted as per the clustered index key. Clustered indexes in SQL Server have numerous benefits. For example, if you retrieve data from a table using an ORDER BY clause referencing the clustered index key, the data does not need to be sorted at query execution time.
• Ensure the clustered index is built on a column that contains distinct values in each row. This makes the clustered index also a unique index. If the clustered index key(s) contains non-unique values, SQL Server will add a hidden column to your table to make clustered index keys unique.
• In addition to the clustered index, create non-clustered indexes, particularly on those columns used for joining the table to other tables or for filtering the data set to be retrieved.
• If you have numerous "lookup" tables with very few rows in each, consider combining them into a single "master lookup" table. For example, you could have numerous "type_lookup" and "category_lookup" tables, each with a dozen of rows. Instead of having to maintain 30 different lookup tables, you can combine them in a single table that has row_identifier, row_type and row_value columns.
• If a table contains millions of rows and you have multiple disks (or disk arrays) at your disposal, take advantage of table and index partitioning. Partitioning can provide considerable query performance improvements. It can also make loading and purging large data sets from a table very fast.
• If the table is partitioned, make sure its indexes are aligned; this means indexes are using the same partitioning scheme as the table.

References

https://docs.oracle.com/database/121/CNCPT/tablecls.htm#GUID-F845B1A7-71E3-4312-B66D-BC16C198ECE5

https://msdn.microsoft.com/en-us/library/ms189084.aspx

https://www.dbbest.com/blog/ssma-oracle-7-0-whats-new-using-memory-oltp-sql-server-2016/

Feature ID

49

Feature

Cluster

Description

A cluster provides an optional method of storing table data. A cluster is made up of a group of tables that share the same data blocks. The tables are grouped together because they share common columns and are often used together.

Because clusters store related rows of different tables together in the same data blocks, properly used clusters offer two primary benefits:

• Disk I/O is reduced and access time improves for joins of clustered tables.
• The cluster key is the column, or group of columns, that the clustered tables have in common. You specify the columns of the cluster key when creating the cluster. You subsequently specify the same columns when creating every table added to the cluster. Each cluster key value is stored only once each in the cluster and the cluster index, no matter how many rows of different tables contain the value.

Category

Platform

To Find Feature Enablement

Select count(*) from DBA_CLUSTERS

Feature Usage

Select * from USER_CLUSTERS

Select * from DBA_CLUSTERS

Select * from ALL_CLUSTERS

Select * from V_$ACTIVE_INSTANCES

Recommendation

Feature Description:

• To provide high availability for your SQL Server databases, it is recommended that you store them on server clusters using Cluster Service.
• Servers configured using Cluster Service share common data and work as a single system.
• Each server can operate independently of other servers in the cluster. So, if one server fails, another server takes over its functions.
• If the current server is a clustered server, fn_virtualservernodes returns a list of failover clustered instance nodes on which this instance of SQL Server has been defined. If the current server instance is not a clustered server, fn_virtualservernodes returns an empty rowset.
• As part of the SQL Server Always On offering, Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

Feature Comparison:

• Oracle Clusters do not have an equivalent in SQL Server.
• The tables in the cluster must be created as regular heaps.
• If rows are frequently accessed using a range search, a clustered index may be created on such column(s). Only one clustered index can be created on a table.
• This migration of clustered tables in Oracle to heaps in SQL Server will be transparent to the application and users.
• Always On Failover Cluster Instance and Mirroring can be used as a workaround for Oracle clusters.

Migration Approach

As migrating from Oracle Clusters to SQL Server Clustering is not a straight forward migration, we need to study the clusters present on the Oracle applications and accordingly we can create Clusters using the workaround of Always On Failover Cluster Instances or Database Mirroring.

This application consists of 11 clusters which are created under the SYSTEM tablespace.

We can configure Always on Failover Cluster Instances which leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI).

Windows Server Failover Clustering (WSFC) is a feature of the Windows Server platform for improving the high availability (HA) of applications and services.

With Windows Server Failover Clustering, each active server has another server identified as its standby server. For a failover cluster to work, each server's hardware specifications must be the same and the servers must share storage.

The two servers communicate through a series of "heartbeat" signals over a dedicated network.

SQL Server 2016 takes advantage of WSFC services and capabilities to support Always On Availability Groups and SQL Server Failover Cluster Instances.

Windows Server Failover Clustering provides infrastructure features that support the high-availability and disaster recovery scenarios of hosted server applications such as Microsoft SQL Server and Microsoft Exchange. If a cluster node or service fails, the services that were hosted on that node can be automatically or manually transferred to another available node in a process known as failover.

Performance Recommendation

• A two-node Active/Passive configuration is preferred over an Active/Active configuration.
• If you build a 3-node or larger cluster, one node should be dedicated to failover, while the rest of the nodes run a single instance of SQL Server.
• Running multiple instances of SQL Server on the same node is not recommended.
• SQL Server clusters should be dedicated to SQL Server. Don't run other software on these nodes.
• Cluster nodes should be member servers, not domain controllers.
• Cluster nodes must be all in the same domain and have access to multiple domain controllers.
• Before you begin to install Cluster Services or SQL Server Clustering, determine your virtual names and IP addresses.
• After each major step, check all logs to look for potential problems, before moving onto the next step.
• Before installing SQL Server Clustering, ensure that the Windows Cryptographic Service Provider and the Task Scheduler are on.
• Once Clustering Services and SQL Server Clustering have been installed, give the domain controllers some time to replicate the virtual server names before clients try to access them.

References

https://docs.oracle.com/cd/B28359_01/rac.111/b28255/intro.htm#BABCHEEE

https://docs.oracle.com/cd/B28359_01/server.111/b28310/clustrs001.htm#ADMIN11739

https://msdn.microsoft.com/en-us/library/ee784936(v=cs.20).aspx

https://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters.

https://blogs.msdn.microsoft.com/clustering/2014/05/08/deploying-sql-server-2014-with-cluster-shared-volumes/

https://msdn.microsoft.com/en-us/library/ms189134.aspx

Feature ID

51

Feature

Packages

Description

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. Oracle supports encapsulating variables, types, stored procedures, and functions into a package.

A package is compiled and stored in the database, where many applications can share its contents.

A package always has a specification, which declares the public items that can be referenced from outside the package.

If the public items include cursors or subprograms, then the package must also have a body. The body must define queries for public cursors and code for public subprograms.

Category

Platform

To Find Feature Enablement

SELECT count(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE')

Feature Usage

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE')

Recommendation

Feature Description: SQL Server does not support objects with functionality like that of a ORACLE Packages.

Feature Comparison:

Some Oracle object categories, such as packages, do not have direct SQL Server equivalents. SSMA converts each packaged procedure or function into separate target subroutines and applies rules for stand-alone procedures or functions.

When you convert Oracle packages, you need to convert:

• Packaged procedures and functions (both public and private).
• Packaged variables.
• Packaged cursors.
• Package initialization routines.

Migration Approach

As studied above, SQL Server does not provide packages as a direct feature, but the below workaround can be used to convert Oracle packages to its SQL Server equivalent.

In SQL Server 2014, you can group procedures and functions by their names. Suppose that you have the following Oracle package:

CREATE OR REPLACE PACKAGE MY_PACKAGE

IS

space varchar(1) := ' ';

unitname varchar(128) := 'My Simple Package';

curd date := sysdate;

procedure MySimpleProcedure;

procedure MySimpleProcedure(s in varchar);

function MyFunction return varchar2;

END;

CREATE OR REPLACE PACKAGE BODY MY_PACKAGE

IS

procedure MySimpleProcedure

is begin

dbms_output.put_line(MyFunction);

end;

procedure MySimpleProcedure(s in varchar)

is begin

dbms_output.put_line(s);

end;

function MyFunction return varchar2

is begin

return 'Hello, World!';

end;

In SQL Server we can emulate Oracle Packages, you can group procedures and functions by their names. such as:

Scott.MY_PACKAGE$MySimpleProcedure and Scott.MY_PACKAGE$MyFunction.

The naming pattern is:

<schema name>.<package name>$<procedure or function name>

References

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6006.htm

Feature ID

53

Feature

Sequences

Description

An ORACLE sequence is a user-defined object that generates a series of numeric values based on the specification with which the sequence was created. The most common purpose of a sequence is to provide unique values for the primary key column of a table. ORACLE sequences are not associated with any tables. Applications refer to a sequence object to get the current or next value of that sequence. ORACLE keeps the set of generated values of a sequence in a cache, and a unique set of cached values is created for each session.

In ORACLE, the NEXTVAL expression generates and returns the next value for the specified sequence. The ORACLE CURRVAL expression returns the most recently generated value of the previous NEXTVAL expression for the same sequence within the current application process. In ORACLE, the value of the CURRVAL expression persists until the next value is generated for the sequence, the sequence is dropped, or the application session ends.

Category

Platform

To Find Feature Enablement

Select count(*) from DBA_SEQUENCES

Feature Usage

Select * from DBA_SEQUENCES;

Recommendation

Feature Description:

• A sequence is a user-defined schema bound object that generates a sequence of numeric values according the specification with which the sequence was created.
• The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted. Sequences, unlike identity columns, are not associated with specific tables.
• Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.

Feature Comparison:

SQL Server 2014 supports objects with functionality similar as ORACLE sequence.

Migration Approach

Below are the steps to migrate your Oracle Sequences to SQL Server

• Start SSMA
• Connect to Oracle - Click on the Connect to Oracle icon from the menu toolbar and provide connection information to your Oracle database.
• Create a schema migration report - Select the schema, then right-click the schema then select Create Report:
• Connect to SQL Server - Click on the Connect to SQL Server icon from the File Menu. Specify the server name (e.g. localhost if SSMA is running on the SQL server machine) and port number (if using other than default 1433 SQL Server port number). Type the name of the database you are migrating to (e.g. HR). If the database does not exist, SSMA will create a new database using the default setting. Specify authentication information and click Connect to continue.
• Map Schema and Type - In the Oracle Metadata Explorer, check the schema and expand. You can select (or deselect) objects to be migrated as well as map schema. Select Sequences. Schema mapping can be done at the Oracle schema level or at the individual object (such as specific table in Oracle) to SQL Server schema. 
•  Convert the schema - In the Oracle Metadata Explorer, right-click the schema and select Convert Schema:
• Review conversion report and resolve error as necessary.
• Synchronize the SQL Server database. To deploy the changes to the SQL server, right-click the database in the SQL Server metadata explorer and select Synchronize with Database.
• Migrate the data. From Oracle Metadata Explorer window, right-click on the schema and select Migrate Data. Provide connection information to both the Oracle source database and the target SQL server.
• Review Migration Report.  After the data is migrated, a report will be displayed with migration statistics

In many cases if you use sequence only for getting NEXTVAL you can convert it to SQL Server sequence.

ORACLE

CREATE SEQUENCE seq1;

...

INSERT INTO t1 (id, name)

VALUES (seq1.NEXTVAL, 'name');

INSERT INTO t2 (id, name)

VALUES (seq1.CURRVAL, 'name');

...

SQL Server

CREATE SEQUENCE seq1

...

declare @newid int;

select @newid = NEXT VALUE FOR seq1;

INSERT INTO t1 (id, name)

VALUES (@newid, 'name');

INSERT INTO t2 (id, name)

VALUES (@newid, 'name');

However, some features of ORACLE sequences (e.g. CURRVAL) are not supported in SQL Server.

Workaround for Resolving the issue

Two distinct scenarios of ORACLE sequence CURRVAL usage exist: a variable that saves sequence value, and an auxiliary table that represents an ORACLE sequence.

In this scenario, an ORACLE sequence is used in a way that is incompatible with SQL Server sequence. For example, NEXTVAL and CURRVAL of sequence can be used in different procedures or application modules.

In this case, you can create an auxiliary table to represent the ORACLE sequence object. This table contains a single column declared as IDENTITY. When you need to get a new sequence value, you insert a row in this auxiliary table and then retrieve the automatically assigned value from the new row.

create table MY_SEQUENCE (

id int IDENTITY(1 /* seed */, 1 /* increment*/ )

)

go

To maintain such emulation of NEXTVAL, you must clean up the added rows to avoid unrestricted growth of the auxiliary table. The fastest way to do this in SQL Server is to use a transactional approach.

declare @tran bit,

@nextval int

set @tran = 0

if @@trancount > 0

begin

save transaction seq

set @tran = 1

end

else begin transaction

insert into MY_SEQUENCE default values

set @nextval = SCOPE_IDENTITY()

if @tran=1

rollback transaction seq

else rollback

In SQL Server, IDENTITY is generated in a transaction-independent way and, as in ORACLE, rolling back the transaction does not affect the current IDENTITY value.

In this scenario, we can emulate CURRVAL by using SQL Server @@IDENTITY or SCOPE_IDENTITY() functions.

@@IDENTITY returns the value for the last INSERT statement in the session, and SCOPE_IDENTITY() gets the last IDENTITY value assigned within the scope of current Transact-SQL module.

Note that the values returned by these two functions can be overwritten by next INSERT statement in the current session, so we highly recommend that you save the value in an intermediate variable, if CURRVAL is used afterwards in the source code. Both @@IDENTITY and SCOPE_IDENTITY() are limited to the current session scope, which means that as in ORACLE, the identities generated by concurrent processes are not visible.

References

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314

https://msdn.microsoft.com/en-us/library/ff878091.aspx

https://blogs.msdn.microsoft.com/ssma/2011/07/12/converting-oracle-sequence-to-sql-server-denali/

Feature ID

54

Feature

Snapshot

Description

A snapshot is a replica of a target master table from a single point-in-time. Whereas in multimaster replication tables are continuously being updated by other master sites, snapshots are updated by one or more master tables via individual batch updates, known as a refresh, from a single master site.

Oracle offers a variety of snapshots to meet the needs of many different replication (and non-replication) situations. You might use a snapshot to achieve one or more of the following:

• Ease Network Loads
• Mass Deployment
• Data Subsetting
• Disconnected Computing 

Category

Platform

To Find Feature Enablement

Select count(*) from DBA_HIST_SNAPSHOT

Feature Usage

Select * from DBA_HIST_SNAPSHOT

Recommendation

Feature Description:

• A database snapshot is a read-only, static view of a SQL Server database (the source database).
• Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time. To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.
• The database snapshot is transitionally consistent with the source database as the moment of the snapshot's creation.
• A database snapshot always resides on the same server instance as its source database.
• As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.
• Multiple snapshots can exist on a given source database. Each database snapshot persists until it is explicitly dropped by the database owner.
• Database snapshots are unrelated to snapshot backups, snapshot isolation of transactions, or snapshot replication.
• Snapshots can be used for reporting purposes.
• In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
• Database snapshots are dependent on the source database. Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.
• sparse file- A file provided by the NTFS file system that requires much less disk space than would otherwise be needed. A sparse file is used to store pages copied to a database snapshot. When first created, a sparse file takes up little disk space. As data is written to a database snapshot, NTFS allocates disk space gradually to the corresponding sparse file.

Feature Comparison:

Oracle and SQL Server has defined Snapshots differently, and hence we can migrate or rewrite Snapshots in SQL Server. We can use Snapshot Replication, Transactional Replication or Merge replication to solve the purpose of Oracle Snapshots.

Migration Approach

We need to rewrite the Oracle Snapshots in SQL Server and look for a workaround depending on the type of Snapshot we are migrating.

Creating a snapshot on the AdventureWorks database

This example creates a database snapshot on the AdventureWorks database. The snapshot name, AdventureWorks_dbss_1800, and the file name of its sparse file, AdventureWorks_data_1800.ss, indicate the creation time, 6 P.M (1800 hours).

CREATE DATABASE AdventureWorks_dbss1800 ON

( NAME = AdventureWorks_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )

AS SNAPSHOT OF AdventureWorks;

GO

To drop database snapshot

DROP DATABASE SalesSnapshot0600 ;

Reverting a snapshot on the AdventureWorks database

USE master;

-- Reverting AdventureWorks to AdventureWorks_dbss1800

RESTORE DATABASE AdventureWorks from

DATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';

GO

References

https://docs.oracle.com/cd/F49540_01/DOC/server.815/a67791/mview.htm

https://msdn.microsoft.com/en-us/library/ms175158.aspx

https://blogs.msdn.microsoft.com/dbrowne/2013/07/25/how-to-move-a-database-that-has-database-snapshots/

Feature ID

56

Feature

Built-In Functions

Description

A built-in function is an expression in which an SQL keyword or special operator executes some operation. Built-in functions use keywords or special built-in operators. Built-ins are SQL92Identifiers and are case-insensitive.

Category

Platform

To Find Feature Enablement

select count(*)

from

all_arguments

where

package_name = 'STANDARD';

Feature Usage

select distinct object_name from all_arguments

where package_name = 'STANDARD';

Recommendation

Feature Description:

• SQL Server provides many built-in functions and lets you create user-defined functions
• SQL Server built-in functions are either deterministic or nondeterministic.
• Functions are deterministic when they always return the same result any time they are called by using a specific set of input values.
• Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values.

Feature Comparison:

SQL Server supports all the built-in functions which are present in Oracle.

Below table will list the Oracle built in functions and their equivalent SQL Server function.

Arithmetic Functions

String Functions

Datetime Functions:

Conversion and Format Functions:

Case and Decode Functions:

NULL Functions:

LOB Functions