I really would like to achieve Test driven development using visual studio 2012.
i want to run automated unittests using http://tfs.visualstudio.com as they offer a build server to run my test on.
When i uploaded my code , all tests failed as they were looking for my database.
Whats the best practice for test automation ?
should i use sql server compact or a real database ?
I'd suggest that you have a look at the Repository design pattern to make your tests independent of your storage mechanism. That way you will be able to run your tests without an actual database.
The Repository design pattern allow to switch easily between storage mechanism. That way you will be able to use a real Database when your code is deployed in production and you will be able to use a mock or an in memory storage for testing purposes. I encourage you to look at IoC frameworks to easily switch between the different repositories. IoC (Inversion of Control or Dependency Inversion) is a technique often learned while learning TDD because it makes your code easier to test.
This will have many benefits:
Your tests will run faster since you won't need to connect to a database
They will be easier to maintain since you won't have to worry about schemas, tear down the database between tests, creating test data....
The Repository pattern will allow you to apply some separation of concern in your code(See the Single Responsibility Principle), because I assume that right now your code is tightly coupled to Sql Server (since you can't run your tests without a DB).
You will be able to change your storage mechanism easily.
The inner goal of TDD is to encourage good code design. I'll suggest that you have a look to the SOLID principles for guidelines and to the F.I.R.S.T principles to have well designed unit tests.