Posts Tagged ‘database’

Domain models and the database schema

May 24th, 2013


A domain model does not have to look like the database schema.

They can resemble each other but they don’t have to.


A domain model is for handling domain problems while a database is for storing and retrieving data. Let each tool do what it is best at; a hammer for nails and a screw driver for screws.

Gratis sql client tools to put on server

May 18th, 2011

( There are probably many but I haven’t bothered to do a deep search since I normally have Sqlserver and its tools on my machines. )

I avoid installing query tools on the servers to keep them as clean as possible but sometimes I need to and then there are two that spring to mind.

Microsoft Sql server 2008 management studio express which requires an install.

JAlbaharis  Query express which comes as a folder.  No installation and no risk of contaminating the server.  As is mentioned on the page there is a F/OSS version with more functionality here.  The latters seems to in active development at writing time.

Even though I wouldn’t say Management studio has a well thought through user interface it is better than Query express.  On the other hand – the source code of the latter is free (someone has already branched it but I haven’t tried the fork yet, written in C#, visual studio and seems to be active)

First time I tried Query express I threw it away since there is a bug that kicks in if you make the new document in the MDI window maximized and then run the query; the splitter between query and result is set to the bottom om the document and it looks like there is no result.  Workararound: either run the query first with non-maximized document or simply drag the splitter upwards.

Are there more gratis tools anyone would recommend?


Do annotate fields in the database

June 8th, 2010

It is, at least in MSSqlserver, possible to annotate fields.  Please do.

There are people coming after you to maintain the code base.  One day it might be you who come after someone.  Start paying it forward.

The field explanation might be in the Documentation but the Documentation is too often faulty, not accessible and almost always not up to date.  The database is closer and probably easer to keep in sync with reality.

Don’t think a field name is self explanatory.  The field CustomerID in table Customer might be.  But the same field in Region is not.  Is it the region master or the primary customer or what?  By the time the field was created the meaning of this field was clear but not a year afterwards.

The reason I write this is that I presently work in a project where the habile predecessor has written down the meaning of the fields and saved me lots of time and lots more of confidence of what the figures mean and tons of debugging time.

Login failed for user ‘IIS APPPOOL\DefaultAppPool’.

October 18th, 2009

If one uses trusted connection to a sqlserver database under Windows 7 and II7 one might get the error message “Login failed for user ‘IIS APPPOOL\DefaultAppPool’.” when trying to run a web application.  There are several articles for this but all points to a different GUI than mine (earlier versions of Windows7/IIS7?).

Update: as I lightly noted, to give the apppool SystemLocal rights is Not the right way to go.

Set up the application pool in IIS

Fire up Internet Information Services Manager.  (windowbutton-iis-return)

Find the Application Pools item in the Connections toolbar.  One can reuse the default application pool or create a new one.  Go for the latter since it is slightly more complicated and hence more fun.

or in Win8:

Add application pool in Win8

(I don’t grok what Managed pipleline mode does.)

Select your newly created application pool and the advanced settings.  Change the Process model to LocalSystem.
(I had planned to create a “better” account with the proper limited rights but ran out of time.  Someone else…?)

Update: Don’t change to LocalSystem, instead add the user to allowed SQLServer logins as noted in the bottom here.  I suggest “IIS AppPool\MySite” for now.

The recommended Microsoft solution is to create a separate account. However, if your solution is small, you can instead add the ‘IIS APPPOOL\DefaultAppPool’ user as a database user in your SQL instance, then providing the proper ‘User Mappings’ to the databases you need the DefaultAppPool user to access. You may not necessarily be able to search for this user, but you can still enter it in the ‘Login name’ field in the “Login – New” window as “IIS APPPOOL\DefaultAppPool” (without the quotes).

Follow this link as a reference and pay attention to the last post: http://social.msdn.micro…-4a71-a448-3e3eef9ee404/

Application pool settings

Getting to advanced settings.

Getting to advanced settings.


Application pool advanced settings

Application pool advanced settings

Site settings

Now change your web application to use this application pool.  This could be made simpler by moving the menu for this to the same place as the other menus.  To make the story short – select your site and look to the right of the IIS manager.

Site advanced settings

Site advanced settings.

Site advanced setting IIS7

Site advanced setting IIS7.

Application pool

Application pool.

Application pool in IIS7

Application pool in IIS7.


Set up Sqlserver

Honour those who should.

To add a new user to SQLServer go to your database->security and add user connected to IIS APPPOOL\DefaultAppPool.  This way the user can log in to the database.

Sqlserver -> new login

Sqlserver -> new login.

New login to database

New login to database. Don’t forget to change default database to appropriate.

Then go to the SQLServer->security and add this user to be allowed to login to the server through the user and properties.

The users of a database

The users of a database.


That is all there is to it.

Visual studio database edition – how to think to get started

July 13th, 2009

Visual studio database edition is part of the “normal” Visual studio.  One cannot be totally sure of this since Microsofts licences are as comprehensible as scientology.

I made two tries at it before I got it to work.  Then it failed me anyway, but that is another posting.

The way to think is to think about the database as source code.  You make changes, compare them to the checked in/commited code and let the machine do its magic merge and check in the result.

Note: it does not handle databases with data in them but truncates everything.  My presently preferred solution is here.

Select Distinct is considered bad

September 15th, 2008

Correction; Considered a warning.

Here is a good rule-of-thumb: If you have to write an SQL with Select Distinct, do adjust your chair, get a cup of coffee, walk around the office but above all talk to someone else because Select Distinct is a symptom of something being wrong, either in the database or in your solution.

/* Select Distinct is normally not needed when your database is lagom normalized. But if you do need a Select Distinct then this is a very good example of a situation that needs to be documented.*/