Advanced features in SQL that an Architect should familiar with

Easwaran Paramasivam
Easwaran Paramasivam used Ask the Experts™
Hi Experts,

   I have 7 yrs of experience in SQL (As a developer as because it is used as back end of my projects). I would like to become an architect. For that what are all the features /concepts that I must be familiar with in SQL. Please do share if any document/resource/link that you followed to become familiar with the same.

  NOTE: The list could include advanced features in SQL 2008, Management studio, Advanced Features/concepts with examples and so on.

   Thanks in advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ted BouskillSenior Software Developer
Top Expert 2009
You should learn the following:

- OLAP, Cubes and advanced dataset analysis
- SQL Reporting Services
- Mirroring
- Clustering
- All the choices for performance analysis
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Depends a bit on what you really mean about being an "Architect"...

A normal DBA gets involved with a lot more than designing tables, and all things T-SQL...

Primarily, need to know all about configuring Servers (how memory is managed, use of disks / physical IO and filespaces, High Availability, Security) - all with a basic understanding of Hardware up to a point...

To be honest, there are soooo many things that comprise the life of an Architect that it is hard to simply list them. Entire books have been written about it.

In that regard, you might like to check out MS Press and any DB Architecture books at amazon (easy to search for) especially those used for certifications.

Now, if you want to become a Certified Architect, then you also need project management skills as well (for the board exam).

In that case, there is a progression from the certifications :

1) Admin :
or : (scroll down to SQl Server)

2) Master : (scroll down to SQL Server)

3) Architect :

If you successfully get through the Admin Certifications then you are well on your way. At least go through the requisites and consider the training materials. It is a very good heads up as to what is expected.

e.g. aim to get 70-432 ( ) and 70-450 ( ) and you will have the knowledge, then it is "simply" a matter of putting it into real life practice...

( for course material or books, search for those exams numbers )

And, just to show how far those certifcations are NOW heading, check out Pinal Dave :

Mind blowing isnt it...
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Might also be worth checking out the SQL Resource Centre :

And if you do go to Amazon, one of the redeeming features are "box sets" and "what others are reading" and those types of things.  e.g.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ted BouskillSenior Software Developer
Top Expert 2009
Ah, in my interpretation an Architect isn't an SME (Subject Matter Expert)!  Let me explain.  I have a decade's experience in Civil Engineering.  In that field an Architect is definitely NOT an SME on any subject.  What they do though is have high level knowledge in all the related fields to talk to the SME's and help make choices to design a great building.  In fact, they often are perceived as being artists however even in that regard Architects delegate to in house graphic SME's

In my career I've had the Architect role and even been interviewed for that role in large organizations.  The role is always a leadership one that requires enough knowledge to make technology choices and ensure that the technology choices are implemented properly.

No offense to Mark, however, Microsoft's view of what "Certified" is isn't the be all and end all.  My company has many funny stories of Microsoft Certified Experts that were huge disappoints as employees and contractors.  We don't care about that certification although some companies do.

The reason I didn't get an Architect position with one company is because I didn't have enough high level experience with other non-Microsoft solutions.  I'm 100% Mac at home but at work I'm almost exclusively on the MS platform.

As I said they wanted somebody that had high level experience with the implementation of a wide array of technology to provide a holistic high level view.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

The main thrust was :

"At least go through the requisites and consider the training materials. It is a very good heads up as to what is expected."

Followed with : will have the knowledge, then it is "simply" a matter of putting it into real life practice...

I wasnt necessarily advocating that you must get all the way to MCA certification, though was suggesting MCSA is a good starting point, and certainly reading the prerequisites should provide a good list to work with...

I believe some of the old problems with certifications have been largely addressed by MS and are regaining credibility.

But you do raise an interesting angle... Checking out job descriptions as to what employers see as important like :

(or monster, or whatever job search engine is available).

With regard those certifications, even MS dont mention the qualifications in their own ads for DBA's
But, I happen to know they do ask :)

Apologies if my previous post seemed to indicate that you must get certified...
Senior Software Developer
Top Expert 2009
@mark_wills: Apology not necessary and I should have made you aware that the asker asked this same question in ASP.NET which you probably didn't see.  So actually a Database Architect should have deeper knowledge than I'm recommending and is more inline with what you are describing.  However, because I had seen both questions I answered differently.

I'd guess about 80% of your points are all in database related topics, substantially MS SQL.  In my mind you'd qualify as an MS SQL Architect.

If you look at my profile, I have about 700,000 points on DB questions (less than 20%) and about 60% on SharePoint. with the other points on a wide variety of topics like AD, SBS, IIS, ASP.NET et cetera.  However, because SharePoint actually is a platform built on the MS web application stack all my general knowledge of the MS Web stack bubbles up into that product.

So in my experience the wider the array of technology required for the Architect role the less detailed knowledge of each is required and/or possible to acquire.  There are people with exceptional memory capacity that can gather substantial in-depth knowledge however that doesn't mean they have all the soft skills to excel as an Architect and it's also rare.  Look how few on EE have that capacity.

Therefore, my knowledge of MS SQL is a good balance as a MS Web Architect but your level of knowledge on MS SQL is what I think makes you a MS SQL Architect (as an educated guess)

Thanks for your comments.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial