So the big decision has been made – your team decided to use SQL Server. But stop. There are few other things need to be finalized before actual design process begins

SQL Server Version

SQL Server 2005 or 2008? If you can use SQL 2008 – use it. There are quite a few things which make you happy. I would mention only one of them – Table-Valued Parameters ( http://msdn.microsoft.com/en-us/library/bb510489.aspx ). It’s not only simplifying your life, it also increases performance. We’ve tested the pattern of inserting 5000 records (about 250 bytes each) with TVP versus individual inserts from client side, TVP approach was about 7 times faster.

SQL Server Edition

Obviously in most part of the cases decision is not driven by developers. There is the project budget which can include cost of the licenses; existing SQL Server instances; legacy issues and dozen of other factors. It also depends on if you develop hosted solution or box product where you should provide customer clear upgrade path from SQL Express to Data Center edition. In any rate, let’s assume that you have a choice.

There are ton of useful features in the Enterprise edition which make DBA happy. Although from development standpoint there are only 2 major benefits there: Table Partition and Data Compression (SQL Server 2008 only). Usefulness depends on the nature of the system. You need to have those features if you plan to have millions of the records in the transactional tables and plan to implement sliding window type functional and/or data archiving. Of course you can always do those things manually although it would greatly simplify your life if you can use Enterprise edition for those tasks.

Data Access Layer

Everybody agrees that good system needs it. Surprisingly a lot of teams do not implement that. Especially if there are no dedicated database and/or middle-tier developers. It’s time consuming, especially on initial stage. It introduces additional rules and restrict “the creativity’ of the developers, requires better collaboration between team members and so on, so on..

In any case, think about price of database refactoring at the future. It’s complicated process by itself. In the case if you don’t have data access code localized in one place, you will also spend a lot of time looking through thousands lines of code.

Architect DAL up front. It will save you a lot of time later.

SQL generators/ORM

Great thing.. Really simplifies life of middle-tier developers. Everything is simpler, development is faster, stakeholders are happier. Only minor downside – you often don’t have any control over SQL nor even understand what happens below the hood.

I don’t want to say that those frameworks are bad. In any rate, how many people are using assembler or even plain C today? We’re living in commercial development era. But for future sake, do the research. Check how good is SQL generated. And make sure that you can put custom, highly optimized code when needed. In practice, you rarely need to do that but when you need it, you should have this ability. There are dozen frameworks and solutions available on the market – you would be able to find the right one which suit your needs

How Agile is your team?

Agile is popular. Agile is beneficial. Stakeholders love it. Developers love it. Only problem that major Agile principle – “Do not overarchitect and spend time on features you don’t need now” does not work very well with database design and development. Especially if you deploy system to production after each iteration/sprint. The price of refactoring on the database level is far higher than on application level. Especially if system is in production already.

So if your team is using Agile based development process and you expect to have complex database backend with a lot of data and concurrent users – reserve good amount of time for database design. Create user stories like: “As an user I want to be able to use the system with 50 concurrent users querying orders table”. Put those stories to backlog with high priority. And convince the stakeholders that it worth it.

At bare minimum create data access layer. Otherwise you will spend iterations just to accomplish basic refactoring.

My first acquaintance with SQL Server was in 2002. Granted I had a couple projects where we utilized MSSQL 6.5 but I did not pay much attention there. So in 2002 we decided to use SQL Server 2000 as the backend database for our GPS Tracking system. Why SQL Server? Frankly, I don’t know. We did not care much – we thought about Oracle but it was more expensive, harder to maintain and last, but not least – neither of us had any PL/SQL knowledge. So we made the decision and our journey began.

As I said we did not know PL/SQL but the problem was that we did not know T-SQL either. Granted we knew how to write select/insert/update/delete statements; how to create tables but I would say that’s almost everything we knew. Surprisingly our initial design worked. Well, at least while we had less than 50 customers. At the day when every 5^th customer called and asked why the system is so slow, we spent a couple days in research and decided that we need to change “a few things”. It was our first refactoring.

I think one of the biggest benefits and same time disadvantages of SQL Server is simplicity. Everybody can install it and build the database which will work. It does not force you to think ahead. You pay great price and suffer huge pain later but everything looks simple. You will have to refactor a system; then refactor it again, and again, and again..

We are on the same boat. Now we are handling 2000 TPS with servers working on 1/10^th of capacity but in order to achieve that we accomplished 5 major refactoring in the last 8 years. And I believe it’s not the final number.

My goal is to share experience. I’m not going to talk about analysis or system architecture in general. Nor tell you how to correctly map business objects into the database tables. I will try to uncover some of SQL Server internals so you will be able to predict how SQL Server behaves. I’ll show you some tricks how to design the database, how to write queries and T-SQL code in the way which makes your system more efficient. I will try to be practical even if it’s not always possible.

If you find anything useful in this blog, I would be happy.