List of Differences between SQL Server and Oracle

List of Differences between SQL Server and Oracle

1. In SQL Server, only one instance runs per platform.

2. SQL Server tablespaces are called databases, and lie half way between an oracle tablespace and an oracle instance.

3. Five databases always exist: master (=system); tempdb (=temp); distribution (for distributed transactions); model (the template for all new database/tablespaces); and msdb (automatic scheduled jobs information). Three sample databases are also created on install, but can be safely deleted: pubs; northwind; user1.

4. SQL Server databases comprise one or more datafiles, as in oracle, but also have one or more online redo log files. This is the principal difference between SQL Server and oracle; the idea of the temporary tablespace, system tablespace, and each schema tablespace having their own set of online redo logs takes some getting used to.

5. SQL Server has logins and users. Logins are specific for a SQL Server instance. Users are specific to a database. A login will have one user for every database it has access to. Logins have systemic privileges (create database, etc.), Users have DDL and DML privileges. Standard practice is to name users the as their parent login - this is done by default.

6. Users with execute permission on a procedure / select permission on a view will fail to use this if there is broken ownership with referenced objects, unless they are explicitly given access to the referenced table.

7. Revoke (neutral) and Deny (strong) are both available in place of Oracle Revoke. The difference is whether or not they can be overridden by role or user privileges. Deny appears as a red cross, Grant as a green tick, and Revoke does not appear in Enterprise Manager privilege box. [p109]

8. Objects can have their schema changed in SQL Server without being rebuilt, using sp_changeobjectowner object, owner. But SQL has to be rewritten to specify the right user, especially with broken ownership chains, so probably this is more trouble than it is worth.

9. SQL Server does not have synonyms.

10. Standard Practice on SQL Server is to have all objects, even views and stored procedures, owned by the dbo user. Other schemas would tend to have their own database/tablespace. However, this is just standard practice, and does not have to be followed. But problems could arise be with lack of synonyms and broken ownership chains.

11. SQL Syntax is slightly different. SQL Server books online has full syntax with a search capability. However DDL and DBA operations should be done by the Enterprise Manager GUI, which has wizards available. SQL Server Enterprise Manager is vastly superior to the equivalent Oracle GUIs. A few operations cannot be done through Enterprise Manager: Column permissions have to be modified through SQL; Filegroups have to be created through SQL.

12. In DDL, whitespace and special characters must be covered with []. E.g.: REVOKE ALL ON [order details] FROM PUBLIC; DENY CREATE DATABASE TO Eva, [Corporate\ErikB], Ivan.

13. SQL Server has its equivalent of v$ and dba_ views. It also has a collection of system stored procedures which return v$ information or actually perform system DDL.

14. SQL Server has application roles in addition to standard roles. Application roles are password identified while standard roles are always enabled for a user. When an application role is enabled, no other privileges are apparent, with the exception of public privileges. Application roles would typically be enabled and disabled through a VB front end script. Syntax is: exec sp_setapprole 'approle_name', 'password'

15. Databases have a primary datafile (*.mdf), possibly some secondary datafiles (*.ndf); and one or more on line redo log files (*.ldf).

16. There is no reason to give a database more than one datafile, except for backup/recovery streamlining for very large database/tablespaces.

17. SQL Server has a fixed block size of 8k, and a fixed extent size of 64k. This may not mean an end to defragmentation requests, since there is still a reorganise utility. The reorganise utility can be scheduled to run after backup, and does hot reorgs. Small tables can, apparently, share an extent.

18. Rows cannot span blocks, so the maximum row size is 8k. This means chaining does not happen in SQL Server, but problems will occur if a row physically cannot fit into 8k.

19. The default size of on line redo log files is 25% of the total size of all datafiles. It is also recommended that autoextend be switched on on online redo log files.

20. A maintenance wizard will decide whether or not to grow or shrink on line redo log files, among other things.

21. Autoextend can be in extensions of a fixed size or a percentage of current size.

22. Autoshrink is available, but recommend that it is switched off and shrinking is done by maintenance jobs after backups.

23. Databases can be dbo use only (in development phase) (=restricted session); and can be in single user mode (when doing restores, etc.).

24. Truncate log on checkpoint is equivalent to noarchivelog mode in Oracle. Truncating the log on backup is equivalent to archivelog mode in Oracle.

25. On line redo logs are not archived, except on backup. They will therefore grow to much larger sizes than oracle On line redo logs.

26. By default, a database is created with one filegroup, named default. Microsoft recommend that filegroups should only be used for backup purposes. Performance should be handled by striping, even with tables and indexes.

27. Unlike our practice with Oracle on NT, but like our practice with Oracle on Sun: Microsoft recommend on line redo log files should be on separate physical disks, with separate disk controllers, from the datafiles. This is recommended both for performance and for fault tolerance. [p160]

28. During an online backup: cannot create or alter databases; create indexes; perform nonlogged operations such as bulk copy and writetext. These will be failed if attempted after backup is started, or cause backup to stop these are already running.

29. Three types of backup: full backup (=online backup); online redo log backup (=archive); and differential backup. The last type is completely new to SQL Server. It backs up just those blocks which have been modified since last full backup.

30. Create Index statements force the data and index filegroups to be backed up simultaneously.

31. There are no rollback segments in SQL Server. Rollback information is obtained from the online redo logs. This should improve performance.

32. SQL Server is read inconsistent. This will improve performance for some SQL jobs, but produce inconsistent results.

33. Standby databases can be up and available read-only on SQL Server. On Oracle7 they are unavailable.

34. SQL Server creates snapshot disk files, rather than snapshot log tables.

35. SQL Server has transactional replication, which replicates data by monitoring redo logs. block changes for replication are specifically marked in the redo logs. This is like snapshot replication, but with constant data copying. So it provides the same functionality as distributed database triggers, but with fewer potential problems.

36. A distribution database stores distribution history, and in transactional replication, also keeps the information culled from the redo logs for propagation.

37. SQL Server does not have parallel Server, although this may be possible via NT operating system.

38. SQL Server does not have sequences. Instead columns can be given the identity property.

39. By default, autocommits instantly, rollback unavailable.

40. With begin transaction...commit/rollback transaction statements, get commit, rollback, endpoint functionality. However, because SQL Server does not have rollback segments, modified rows in a transaction are locked. Users cannot even select from the entire table in most cases.

Best Practices Every DBA Must Know

Best Practices Every DBA Must Know
By Brad M. McGehee

Installing & Upgrading SQL Server
* Generally, when installing a new SQL Server instance:
• Use the newest hardware firmware and OS drivers.
• Use the newest OS version with latest SP and patches.
• Use the newest SQL Server version with latest SP & Hot Fixes.
• Test, and once stable, put into production & be wary of making changes.
* Generally, when upgrading an existing SQL Server instance:
• Don’t upgrade unless you have a good reason to upgrade. If your instance is working well, don’t mess with it.
• For example, upgrade if you need new features, or have problems with an old installation, or need to upgrade hardware.
• It is always safer to upgrade to a new server with a fresh installation of the OS and SQL Server than to upgrade in place. This allows you to test more effectively, and also gives you an easy “back out” option.


Security Basics
• Don’t give users more permissions than they need to perform their job. (Critical. Sounds simple, often hard.)
• Don’t use the SA account for anything. Assign it a complex password, and keep it handy just in case. Instead, use a domain account that is a member of the sysadmin role.
• Don’t allow an application to use the SA or a sysadmin account to access SQL Server.
• Use Windows Authentication security whenever possible. (Applicable for in-house development).
• Don’t give vendors sysadmin access to your servers.
• Log off or lock your SQL Server (or workstation) when done.


General Server Configuration
• Ideally, SQL Server instances should run on a stand-alone server (physical or virtual) with no other major apps running on it. Small monitoring apps or utilities are generally OK.
• Avoid multiple instances unless you have a really good reason to use them. Consider virtualization instead.
• Unnecessary SQL Server services should be uninstalled or turned off.
• Ideally, don’t run antivirus/antispyware software locally. If your organization’s policy requires running antivirus/antispyware software locally, exclude MDF, NDF, LDF, BAK, TRN and BCP files.


SQL Server Property Settings
• SQL Server includes many instance-wide property settings. (e.g. sp_configure)
• Don’t change any of these defaults unless you know exactly what the effect of the change is.
• There are a few of these settings that are commonly changed from their defaults, and generally you need to perform
baselines before and after the change to see if performance and HA increased or decreased because of the change.
• If you make a change, only make one change at a time so that you can see its effect independent of other variables.

Memory Configuration
• Ideally, use the 64-bit version of the OS and SQL Server.
• If using the 32-bit version of SQL Server, and if using 4 GB or more of RAM, ensure that /3GB switch and AWE memory are correctly configured. Correct settings depend on available RAM.


Data and Log File Management
• Remove physical file fragmentation before creating new or expanding existing MDF or LDF files.
• When creating new MDFs and LDFs, pre-size them to eliminate/minimize autogrowth events.
• MDF files should be located on their own disks.
• LDF files should be located on their own disks.
• BAK and TRN backup files should be located on their own disks.


Instant File Initialization
• Enable instant file initialization, which prevents MDF files from being zeroed out when they are grown, allows MDF files to be created quickly. LDF files are not affected.
• Speeds up CREATE DATABASE, ALTER DATABASE, RESTORE DATABASE, Autogrowth.
• Requires SQL Server 2005/2008, and Windows Server 2003/2008 (or higher version).
• Instant file initialization is turned on if the SQL Server (MSSQLSERVER) service account has been granted the SE_MANAGE_VOLUME_NAME permission by adding the service account to the Perform Volume Maintenance Tasks security policy. Members of the local Windows Administrator group automatically have this right.

Don’t Shrink Files
• If you properly size your MDFs and LDFs, then you should very rarely have to shrink a file.
• Don’t schedule periodic database or file shrinking operations.
• If you must shrink a database (MDF file):
– Do so manually
– Rebuild the indexes after the shrink is complete
– Schedule these steps during the slow time of the day
• Benefits of not automatically shrinking files:
– Eliminates grow and shrink syndrome
– Reduces physical file fragmentation
– Reduces resources used for these operations, allowing more important tasks to use them


Tempdb Management
• Pre-size tempdb so autogrowth doesn’t have to happen often (8MB is default, which is very low).
• Set autogrowth to avoid many growth spurts, use a fixed amount that minimizes autogrowth use. (10% is default,
which causes lots of autogrowth).
• If tempdb is very active, locate it on its own disks.
• If very active, consider dividing the tempdb into multiple physical files so that the number of files is 1/4 to 1/2 the number of CPU cores, up to 8 files. Each physical file must be the same size.


Database Property Settings
* Don’t change database property settings unless you have a very good reason. Some key ones:
• Auto Create Statistics: On
• Auto Update Statistics: On
• Auto Shrink: Off
• Autogrowth: Leave on. Use mainly for catching mistakes. File growth should be managed manually. Use fixed amount that minimizes autogrowth occurrences.
• Recovery Mode: Set to full for all production databases so transaction log backups can be made.
• Page Verify: Use Checksum (2005/2008), don’t turn off.
• Compatibility Level: Should be set to match current server version, unless there are compatibility problems.

Configuring Jobs—General
• If your production servers don’t have any jobs, then there is a problem, as all servers need jobs.
• Try to schedule jobs so they don’t interfere with production.
• Try to prevent jobs from overlapping.
• Set alerts on jobs so you are notified if they fail.
• Check jobs daily to verify that they have run correctly (not hung, not run abnormally long, etc).
• If you use the Maintenance Plan Wizard, be careful to use it properly. If misused, it can create maintenance jobs that hurt performance.


Create Index Rebuilding/Reorganize Job
• Indexes need to be rebuilt or reorganized regularly to minimize fragmentation and reduce wasted space.
• Only Reorganize or Rebuild indexes that need it, don’t defrag all indexes all the time (unless perhaps a database is small).
• Some general recommendations (from BOL). Use as a starting point to determine what is best for your server.
– If fragmentation is less <5 alone.="alone." leave="leave" p="p" then="then">– If fragmentation is >5% and <30 consider="consider" p="p" reorganize.="reorganize.">– If fragmentation >30%, consider Rebuild.
• Use sys.dm_db_index_physical_stats to help you determine if an index should be rebuilt or not.
• Reorganize or Rebuild jobs should ideally be scheduled as a SQL Server Agent job using a custom T-SQL script you create to meet your environment’s specific needs.
• Perform as needed to minimize negative effect of index fragmentation, but not more than required.


Create Data Corruption Detection Job
• Ideally, run DBCC CHECKDB as frequently as practical.
• If you have a problem, you want to find it as soon as possible to reduce the risk of data loss.
• Create an appropriate job to run this (or similar) command:
DBCC CHECKDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS;
Note: Consider using PHYSICAL_ONLY option for large or busy production servers to reduce run time.
• Don’t use the DBCC CHECKDB repair option unless you fully understand its implications, as data loss will most likely occur.


Implement a Backup/Restore Strategy
• Create a job to perform full backups daily on all system and user production databases, plus log backups hourly (or similar variation). Include differential backups if appropriate.
• If a database uses the bulk or full recovery model, you must back up the transaction log to keep it from growing uncontrollably.
• Backup using RESTORE WITH VERIFYONLY to help verify backup integrity. (Does not guarantee good backups.)
• Periodically test backups to see if they can be restored.
• Set up an appropriate backup retention policy.
• Store backups securely and off-site (not on same disk array or SAN).
• If you have a limited backup window, or have limited disk space, use backup compression. Can be a big time saver.

Set Up Alerts for Critical Errors
• Create a SQL Server Event Alert for all events with a severity of 19 [fatal] and higher.
• Have alerts sent to you or whoever is responsible for day-to-day monitoring.
• Consider a third-party alerting tool if SQL Server Alerts doesn’t meet all of your needs.


Create a Disaster Recovery Plan
• You must create a document that outlines, step-by-step, in detail, how you will recover your SQL Servers in the case of any problem, small or large.
• You need to practice using the plan so you are familiar with it and can easily implement it.
• Keep Microsoft SQL Server’s Product Support phone number handy. Paste it near your computer.
• Remember: Most “disasters” are small, such as a corrupted database. Big “disasters” occur very rarely, if ever. But you need to be prepared for both.


Document Everything
* Yes, documentation is very boring, but it is very critical to being a successful DBA. Be sure to document:
• The installation and configuration of each instance.
• The installation and configuration of any application that uses SQL Server as its back end (as related to SQL Server).
• Troubleshooting tasks, as the same problem may reoccur, and you don’t want to reinvent the wheel.
• Any time any change is made to any instance for any reason.
* Be sure that documentation is easily available to everyone who needs access to it.

Test Everything
• Before you make any change on a production SQL Server, be sure you test it first in a test environment.