As a DBA, how would I go about transitioning from Oracle to SQL Server?

I've swapped between working on Oracle and SQL Server over the past few years, and wrote a blurb on going the other way here. There are a number of idiomatic and architectural differences, and various bits of terminology get used differently by the vendor and developer/DBA communities surrounding each product.

Physical architecture

SQL Server organises various things a bit differently to Oracle and has one or two key concepts that have no direct analogues in Oracle.

• A 'Database' is a separate item in SQL Server, with its own user permissions, schemas/name spaces and storage. If you're familiar with Sybase, they work much the same as databases in Sybase, due to the common origins of the product.

• Filegroups are roughly equivalent to table spaces, although they are local to a database.

• A schema is a distinct concept from a database user in SQL Server, although users can have a default schema.

• MVCC works somewhat differently in SQL Server. It's a relatively recent feature, maintaining different copies of a row until the locks on the old version are released. SQL Server has no direct equivalent to a rollback segment. It is not active by default on SQL Server databases.

• Tempdb is much more heavily used in SQL Server. The system uses it for temporary tables and intermediate join results. More on tempdb later.

• Table partitioning is somewhat clumsier than Oracle. You need to set up a partition function that creates a partition key from whatever you are supplying it, then a partition scheme over that partition function. The partition scheme behaves a bit like a filegroup in that you then create the table on the partition scheme.
  
  Swapping partitions in and out requires you to set up a constraint on an empty table in the right structure. The constraint guarantees that the partition key values are appropriate to the partition you intend to swap into it.

• Materialised views are called indexed views in SQL Server. The GROUP BY clause does have a CUBE operator, and the documentation alludes to a query rewrite feature. However, this functionality is not well documented and may not be terribly mature. YMMV.

• SQL Server does not support autonomous transactions, although it does support two-phase commit through XA or OLEDB transaction protocols.

• Clustered indexes are slightly different from index-ordered tables in Oracle, as they don't require all of the columns in the table to participate in the clustered index. They're much more widely used in SQL Server architecture than IOTs are in Oracle.

• SQL Server does support covering indexes, but doesn't have join indexes. Bitmap indexes are not supported, although it does have an index intersection/star transform operator that can calculate intersections without hitting the fact table.

• Sequences are a relatively recent addition to SQL Server. Traditionally autoincrementing keys are done through identity columns. You can load values into an identity column through set identity_insert on.

Programming

Idiomatic T-SQL has some differences to idiomatic PL/SQL. It works differently enough that some of the paradigmatic differences merit explaining in more depth.

• T-SQL has no concept of a package. All stored procedures and functions in a database live in a common namespace, although schemas can be used to break this up, and the namespace is local to a database.

• Get a feel for how to use temporary tables, and SELECT INTO. It's pretty rare to encounter T-SQL code that actually needs a cursor; temporary tables allow operations to be broken up into steps that can be done with set operations. SELECT INTO in tempdb is minimally logged, and it is minimally logged in certain recovery modes on user databases as well, so it is just as fast as query operator that persists an intermediate join result.
  
  Idiomatic T-SQL will use temp tables in the sort of roles that you would see cursor variables in PL/SQL, but will make much more use of set operations. Temp tables can, however make for fairly obtuse code, so use with care.

• The system data dictionary was much more obtuse than Oracle's one on older versions, but it got much better with SQL Server 2005. Although the tools supplied by Microsoft have quite a lot of introspection stuff built in in the SSMS explorer, it's still worth knowing your way around the data dictionary. It doesn't differentiate between ALL, USER and DBA views of the DB objects, though.

• SSMS has a query plan viewer built in.

• Identifiers in T-SQL code can be quoted with [], and can contain all sorts of rubbish if quoted. However, if we catch you calling a column 'Direct/Transfer', we will rip your intestines out.

• SQL Server does have window functions (since 2005 IIRC), so you can do ordering, running sums and suchlike within groups now.

• T-SQL has no direct equivalent to CONNECT BY, although recursion can be done through recursive CTEs.

• If you need to write code that hops across databases (as opposed to schemas within a database), consider using public synonyms to alias the objects to something local and refer to the aliases in the code. This avoids hard-coded dependencies on database names.

• If you avoid hard coded dependencies on database names, databases make it quite easy to maintain multiple environments on the same server.

• Some things, such as custom aggregate functions, can only be implemented using CLR sprocs. Also, if you want to escape from a transaction context (e.g. to fake an autonomous transaction for rollback-proof error logging) you can use a CLR sproc, as it can create a local connection outside of the current transaction context.

Security

Logins are defined at the SQL Server instance level, but each login maps to zero or more databases as a 'database user'. Permissions cam be assigned to both 'logins' (server) and 'users' (database) but in a database 'roles' are normally used. Users belong to roles, permissions are assigned to roles. SQL Server 2012 adds 'server roles'.

• SQL Server 2012 introduces a concept called 'partially contained databases', which allows user and role information to be held local to that database.

• Within a database, the concept of user and schema is separated. A user or role can be assigned to a schema and a schema owns database objects.

• Windows authentication uses login information behind the scenes to authenticate a user on a machine or domain to a SQL Server login. IIRC support for this is an optional extra on Oracle.

• A special role, 'dbo' (short for 'database owner') has a sort of super-user privilege within a specific database. Each database has a 'dbo' role and users can be assigned to the 'dbo' role on a given database.

• There is also a default 'dbo' schema. Objects may be owned by the dbo schema - Objects created by users with the 'dbo' role (or system-wide admin permissions) will default to being owned by the 'dbo' schema unless another schema is supplied explicitly.

• Security information is not kept with a backup of a single database. Users must and roles must be configured explicitly on the server the backup is restored to. SQL Server 2012 allows user and role data to be kept locally to a database with a new 'partially contained databases' feature.

• From SQL Server 2005, stored procedures can be executed in the security context of the caller, the creator, the owning schema or a specified user.

• In a view on SQL Server, permissions on underlying tables are based on the permissions of the schema that owns the view. User permissions on the underlying tables do not participate in the security, although a view definition can include filters that get information from the session. In Oracle, user permissions on the underlying tables may affect the view, depending on the configuration of the grants.

Monitoring and tuning

TBA - memory architecture vs. SGA etc. in oracle

Backup and Recovery

TBA

Tooling

Microsoft bundles a set of surrounding tools with SQL Server. Some of the major items supplied are:

• SQL Server Management Studio (SSMS): This does something similar to SQL Developer on Oracle - it provides an editor and code execution facility. Some useful features include a database object browser and a query plan viewer.

• SQL Server Analysis Services (SSAS): This is an OLAP server that is distinct from the database server. It uses its own query language (MDX) and API (XML/A) for client-server communication. It cannot be queried with SQL. SSMS has a facility for editing MDX and raw XMLA queries and displaying the results. A command line query tool called ASCMD.EXE is also supplied.

• SQL Server Reporting Services (SSRS): This is a web-based reporting tool for publishing reports. Reports can be built through BI Development Studio (BIDS) or Report Builder, and published to a web portal. The SSRS server itself has a web service API for programatically managing the server. Note that SSRS reports can consume data from a variety of sources, not just SQL Server. A command line tool called RS.EXE is supplied for programatically managing SSRS servers.

• SQL Server Integration Services (SSIS): This is an ETL tool supplied with SQL Server. Architecturally, it is quite different to OWB or ODI in that it is not a code generation tool. The runtime sits client-side and can be on a separate machine to the database server. SSIS packages can be developed with BIDS and executed independently with a command line tool called DTEXEC.EXE.

• B.I. Development Studio (BIDS): This is a visual studio based environnemt for developing reports, SSIS packages and SSAS cubes. If other VS based development tooling is installed (e.g. VS Professional) the tooling can be integrated into a single environment and a common project grouping.

• Bulk copy (BCP): A command line bulk insert/extract tool similar to SQL*Loader

• SQLCMD: A command line query tool similar to SQL*plus

• SQL Profiler: A tracing and profiling tool that can capture and evaluate trace information from SQL Server, SSAS and other tools in the suite.

• SQL Server Agent: A job scheduling utility that can run periodic jobs of one sort of another.

Our main product works on both SQL Server and Oracle, here are some other differences we had to work around and it might be good to keep in mind:

• Date-time handling is very different: different precisions, different set of functions to work with

• Empty strings are NULLs in Oracle, not in SQL Server

• The handling of character encoding and Unicode is very different. In SQL Server you can have normal (varchar) or Unicode (nvarchar) columns mixed in the same database, in Oracle you decide at the database-level which kind of encoding to use.