MVA – SQL Server Essentials for Oracle DBAs

The Microsoft Virtual Academy has just released a free course covering SQL Server Essentials for Oracle DBA’s.

This is a Level 200 course containing 15 modules (each consisting of a video presentation, demos and a slide-deck) and while it’s intended to be delivered as a four-day workshop for Oracle DBA’s, the content in its entirety is a reference guide (rather than a mere guideline) and contains valuable information for any SQL Server professional.

Course Description

This course is to help Oracle database administrators (DBAs) leverage their skills and experience as an Oracle DBA to manage a SQL Server 2012 system.  It provides a quick start for the Oracle DBA to map, compare and contrast the realm of Oracle database management to SQL Server 2012 database management.

The content is a comprehensive course with descriptive reference material while the course itself focuses on introducing the concepts and providing hands-on learning. This course has been developed to extend the terminologies familiar to the Oracle DBA and relate those terminologies to the SQL Server world.

Course Presenter

Maciej Pilecki (@DrHouseOfSQL)

Maciej is a Microsoft Certified Master in SQL Server 2008, Microsoft Certified Trainer and former SQL Server Most Valuable Professional (MVP) who is an expert in cross-platform integration and interoperability between Oracle, MySQL and Microsoft SQL Server.

Course Modules

Module 1 – Database and Instance

Provides an understanding of the two major components of a database system.  Contains the following sections:

  • Schema and Data Structures
  • Storage Architecture
  • SQL Server Data Files
  • Logging model

Module 2 – Database Architecture

Goes in-depth into structure, components and contents of the files that constitute the database.  Contains the following sections:

  • SQL Server Instance Environment
  • Client interaction with Database and Instance
  • Database capacities

Module 3 – Instance Architecture

Discusses the memory and process architectures that are key to a database’s performance.  Contains the following sections:

  • Memory Architecture
  • Memory Address Space – Comparison
  • Monitoring memory usage
  • Process and Thread-Based architecture
  • Using the Resource Governor
  • Oracle client-database interaction
  • Comparing background processes

Module 4 – Data Objects

Examines in greater detail the schema objects introduced in Module 2.   Contains the following sections:

  • Reviewing large allocation units
  • Views comparison
  • Creating a table
  • Non-native data types

Module 5 – Data Access

Focuses on how data is accessed and manipulated by the clients.  Contains the following sections:

  • Structured query language
  • Control statements
  • Integrated full-text searching
  • Query optimization
  • Transaction management

Module 6 – Data Protection

Fulfils the twin tasks of protecting data against unauthorized access (database security) and also from the destructive interaction between authorized users working concurrently (concurrency control).   Contains the following sections:

  • Locking
  • Isolation level behaviors
  • Database security
  • Instance security

Module 7 – Basic Administration

Contains discussion on planning and installation of SQL Server.   Contains the following sections:

  • Installing SQL Server 2012
  • Server configuration
  • Database system
  • Database maintenance

Module 8 – Server Management

Discusses the administration of various types of resources such as system resources (such as memory, processes, storage, and so on) as well as low-level database resources (such as lock, latches, queues, and so on).   Contains the following sections:

  • Using the performance monitor
  • Managing storage
  • Locks and latches
  • Capturing deadlock information

Module 9 – Schema Management

Provides the administrative aspect of schema objects described in Module 4.   Contains the following sections:

  • Referential integrity via constraints
  • Managing triggers
  • Managing indexes
  • Managing views

Module 10 – Database Security

Continues the discussion of data protection initiated in Module 6.  Contains the following sections:

  • Managing privileges
  • Managing roles
  • Create logins and users

Module 11 – Data Movement

Examines the non-transactional mechanisms for moving data into and out of a database.  Contains the following sections:

  • Data transport
  • SQL Server Integration Services
  • SSIS data flow
  • Import/export wizard

Module 12 – Backup and Recovery

Lists the types of errors encountered in a database and the various mechanisms that are available to safeguard against these errors.  Contains the following sections:

  • Backup methods
  • Recovery model
  • Recovering from database errors
  • Maintenance plan backup

Module 13 – Performance Tuning

Is divided into two sections: tuning an instance and tuning an application.  Contains the following sections:

  • Application tuning
  • Instance tuning
  • Data compression
  • T-SQL hints
  • Plan guides

Module 14 – Scalability and High Availability

Provides a high-level overview of the scalability and high availability features available in each RDBMS.  Contains the following sections:

  • Clustering
  • Database mirroring
  • AlwaysOn
  • Replication – objects
  • Scalability

Module 15 – Monitoring

Shows the mechanisms by which the database can be monitored for availability, errors, and performance.  Contains the following sections:

  • Error logs
  • Error messages
  • Monitoring performance
  • Activity monitor

A New Era in Microsoft Certification

Today Microsoft released details of it’s new certification program which is designed to address the growing need for IT Pros and Developers to have skill sets that run both broad and deep.

At first glance it appears that Microsoft has simply reintroduced acronyms used in previous certification streams (such as MCSD , MCSA and MCSE) however this isn’t so and it’s important to understand that these acronyms now represent new certification structures and requirements, not just a renaming of terminology.

So What Has Changed?

As you can see from the diagram below, the new certification program consists of three core levels:

  • Microsoft Certified Solutions Associate (MCSA)
  • Microsoft Certified Solutions Expert (MCSE) / Microsoft Certified Solutions Developer (MCSD)
  • Microsoft Certified Solutions Master (MCSM)

You will also notice that Microsoft has introduced recertification as a requirement of MCSD/MCSE level certifications.


Microsoft Certified Solutions Associate (MCSA)

Associate level certifications validate the core skills required to work with a technology at a beginners level.  They also represent the prerequisite certifications required to obtain Expert level certification. 

Microsoft Certified Solutions Expert (MCSE) & Microsoft Certified Solutions Developer (MCSD)

The Expert level is Microsoft’s flagship set of certifications validating that skills obtained are relevant in the constantly changing tech environment. The Microsoft Certified Solutions Expert (MCSE) is the destination for established IT Professionals who have expertise working with Microsoft technology solutions.  The Microsoft Certified Solutions Developer (MCSD) is the destination for established Developers who have expertise developing solutions with Microsoft tools.

As previously stated, Microsoft has also introduced recertification as a requirement of this certification level.

Microsoft Certified Solutions Master (MCSM)

This certification is for the select few who wish to further differentiate themselves from their peers and achieve the highest level of skills validation.

Example: SQL Server 2012 Certification Path


Microsoft Certified Solutions Associate (MCSA)

Prerequisite: No certification prerequisites exist however Microsoft recommends that candidates have at least 2 years experience with the technology being tested.

There are three exams required to obtain the MCSA: SQL Server 2012 certification:

SQL Server 2008 Upgrade Path

Please Note: SQL Server 2008 certification exams will be retired in July 2013 and exams 70-457 and 70-458 are expected to be available by August 2012.

Candidates who have achieved an MCTS on SQL Server 2008 will be able to earn the MCSA: SQL Server 2012 certification by passing two exams:

Microsoft Certified Solutions Expert (MCSE)

Prerequisite: MCSA: SQL Server 2012

The MCSE level consist of two platforms (Data and Business Intelligence) and there are two exams (per platform) required to achieve MCSE certification:

MCSE: Data Platform: 

MCSE: Business Intelligence Platform:

SQL Server 2008 Upgrade Path

Please Note: SQL Server 2008 certification exams will be retired in July 2013 and exams 70-457, 70-458, 70-459, and 70-460 are expected to be available by August 2012.

Candidates who have achieved an MCITP on SQL Server 2008 will be able to earn an MCSE: Data Platform or MCSE: Business Intelligence Platform certification by passing three exams.  This upgrade path will also earn the candidate the MCSA: SQL Server 2012 certification.

MCITP: Database Administrator 2008 / MCITP: Database Developer 2008

MCITP: Business Intelligence 2008

Microsoft Certified Solutions Master

The MCSM level consist of two platforms (SQL Server and Intelligence):

MCSM: Data Platform

Prerequisite: MCSE: Data Platform

  • Exam 986: MCSM Data Platform Knowledge
  • Exam 986: MCSM Data Platform Lab

Full requirements are still being finalised.  More information can be found at

MCSM: Intelligence

Prerequisite: MCSE: Business Intelligence Platform

  • Full requirements are still being finalised however this level of certification will require the completion of Lab-based and Technical exams

Microsoft Certified Architect

Prerequisite: MCSM: SQL Server and MCSM: Intelligence

MCA: SQL Server

  • Full requirements are still being finalised

Offers and Promotions

Microsoft, in conjunction with Prometric, has released the following offer and promotion to assist candidates with achieving MCSA and/or MCSE certification.

Certification SKU Offer

If you purchase a set of exams to achieve MCSA or MCSE certification, you will receive a 15-20% discount (depending on the number of exams in the set) off the purchase price.

Two-for-One Promotion

Purchase and take a full-priced exam before 30 June 2012 and you will receive, at no cost, a voucher valid for a MCSA or MCSE exam in the same technology.  More information about this offer can be found here.

Additional Information

Microsoft Certification overview page:

Microsoft Certification overview video:

MCSE information page:

MCSE video on YouTube:

MCSE Data Platform:

MCSE Business Intelligence:

The New MCSAs and MCSEs, Your Questions Answered:


SQL Server 2012 Certification Nearly Here

UPDATE: As per this thread on Born to Learn (SQL Server 2012 Certifications), beta exams will go live mid-March 2012.

While reviewing threads on the MSDN Training and Certification Forum, I read a reply from Nilkamal0007 in relation to a question on SQL Server 2012 certification. What caught my eye about this particular reply was that Nilkamal0007 provided links to exam content published in Microsoft’s exam catalogue; something I didn’t know had been published.  So what does this mean?  It means that the Beta cycle for these exams is close to going live.

I don’t know the exact release dates for the new SQL Server 2012 certifications (or when the beta period actually begins) however the exam catalogue does show the following:

Published: June 8, 2012 (In development)

70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012

Published: June 11, 2012 (In development)

70-464: Developing a Microsoft SQL Server 2012 Database
70-465: Designing Database Solutions for SQL Server 2012

Published: June 12, 2012 (In development)

70-461: Querying Microsoft SQL Server
70-462: Administering a Microsoft SQL Server 2012 Database
70-463: Building Data Warehouses with Microsoft SQL Server 2012
70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012 Platform

Master-Level Certifications

The first exam for the new SQL Server 2012 Master-level certification is scheduled to be available in beta version in June.

Looks like I better get my study cap on Smile

OPENROWSET (T-SQL) – Sometimes You Have to Stop and Smell the Roses

Ever been in the situation where you’re so fixated on debugging an error that you fail to see the solution which has been staring you in the face the whole time?  Me too.

The other day I was trying to import data from an Excel spreadsheet into a SQL Server table using OPENROWSET (something I’ve done numerous times before) but I just couldn’t get it to work on our development server (I could get it to work on my development machine though).  My code was quite simple:

INSERT INTO Cruise.Pricing
SELECT CruiseCode, Rate, Fare
     'EXCEL 12.0;Database=d:\ozdata\PricingData.xlsx; 
     HDR=YES;IMEX=1','SELECT CruiseCode, Rate, Fare FROM [sheet1$]');

Yet SQL Server kept throwing the following error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". 
Msg 7303, Level 16, State 1, Line 1 
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Now I really do love developing with Microsoft products but what the heck does “Unspecified Error” mean?  Come on! Really!  How difficult can it be to provided decent error messages.  Anyway, I digress.

Like any good developer, I launched Google and searched for the above error which resulted in a number of hits; The first of which was the article Microsoft.ACE.OLEDB.12.0″ for linked server “(null)” returned message “Unspecified error” (dated 11 September 2011) which was indeed related to my search however the solution was to open SSMS with Administrator rights (Hint #1).  Just as I was about to close the article, I saw the following comment posted by Yves Forget at the bottom (Hint #2):

Were fixed a similar problem on Windows 2003 server by allowing read/write access to the users executing the query to the temp directory of the user running SQL Server (on Win 2003, it would look like c:\Documents and settings\SQLDBA\Temp). This is because SQL Server copies the file to that directory before opening it (probably to ensure exclusive access to the file).

Not been able to do the same on Windows Server 2008, however.

I found the comment interesting however didn’t pay much attention to it as we’re running Windows Server 2008 and the comment stated this fix worked for 2003 but not 2008.

Over the next hour or so I read countless articles, most of which suggested to run the following commands in SQL Server (which despite already being run on the server, were run again):

sp_configure 'show advanced options', 1;

sp_configure 'Ad Hoc Distributed Queries', 1;

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'
	, N'AllowInProcess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'
	, N'DynamicParameters', 1

Some even suggested that the TEMP directory permissions (discussed in the 1st article I read) can cause problems on x86 machines however I dismissed these comments as we are running x64 (Hint #3).  Besides, it works on my 64bit development machine.  Fed up with playing this game, I went to lunch.

BTW, some of you are probably already thinking “Why didn’t he just use SQL Server’s Import and Export Wizard?”. Well, the wizard uses SSIS and SSIS is a 32bit application which means that the 64bit Microsoft Office 12.0 Access Database Engine OLE DB Provider (a.k.a. Microsoft ACE OLE DB 12.0 Provider) is not available in the wizard (refer to my previous post on Consuming Microsoft Access or Microsoft Excel Data Sources in 64-bit Environments).  That’s why I couldn’t use the wizard.

Anyway, during my break I convinced myself that the only plausible explanation as to why I was experiencing these problems was that the provider must have become corrupt and all I needed to do was reinstall it.  Well, I was wrong.  I still got the same unhelpful error message.

(Insert long break here)

I bet you’ve already guessed what the problem was.  Yep, it was a permissions problem. 

During a tidy-up of the accounts on the development server, I had changed the SQL Server service to use a local account instead of my domain account (Yes I know).  This meant that when I executed the OPENROWSET statement in SSMS, it was executing under the context of my domain account which did not have write access to the TEMP directory of the SQL Server service account (BTW, the instance of SQL Server on my development machine was still running under my domain account.  That’s why it didn’t fail there i.e. The service account and the account SSMS was launched under were the same therefore the required permissions were already in place). 

I understand why SQL Server needs to use a temporary directory however I don’t understand why there’s a requirement to set permissions on the SQL Server service accounts TEMP directory manually. Surely there’s a better way to handle this requirement/limitation (and surely there’s a better error message other than “Unspecified Error”!)

– Sometimes you have to stop and smell the roses (or get smacked in the face with a cricket bat!)


%d bloggers like this: