sql-server-2008visual studio
Ok, I've installed SQL Server 2008 R2.
I'm trying to add a "SQL Server Database" to an ASP MVC project.
But it keeps telling me I need SQL Server for it to work.
What do I need to do to setup the SQL Server properly for it to work with VS2010 ?
I've tried messing with the SQL Server Management Studio, and can't seem to connect to anything through there.
What am I doing wrong ? How can I create a working SQL service ?
If you are licensed to use the full Management Studio, I would install that. It has more features that will come in handy if you ever need to manage other SQL Server instances other than your local development/Express version. If you're not licensed or you know this is not a requirement, you can download Management Studio Express from here:
http://msdn.microsoft.com/en-us/library/ms365247.aspx
Yes, that's for 2008 R2; probably better to get the most recent release, which will work just fine with 2008.
Whether you end up using the full version or the Express version, can't hurt to install Service Pack 1 once SSMS is installed:
http://www.microsoft.com/download/en/details.aspx?id=26727
(Probably more appropriate for the full version, since there is a greater chance that issues are fixed since it includes more features.)
Actually I was a bit underwhelmed with VS2010, to be honest. I think an old-school create table script and files for stored procedures are easier to work with. If you need schema management then you can get Redgate SQL Compare Pro for a few hundred dollars.
If you really need a database modelling tool then Powerdesigner or even Erwin does a much better job, although they're not particularly cheap.
Although I prefer SSMS I've used both. Some pros and cons:
SSMS has a user interface that 'just works' well for SQL development. Just building and using creation scripts is much more convenient than the hoops VS2010 forces you to jump through. Much, much more flexible (+ SSMS).
VS2010 has basic schema management (i.e. difference/patch script generation) (+ VS2010). However it's not all that good and has some flaws. For example it matches constraints on name. If you have check or default constraints on a column without naming them, SQL Server generates a random name behind the scenes. This will confuse VS2010 if you install the script on a different machine as the constraints may have different names. Redgate is cheaper and better. (+ VS2010, but flawed).
VS2010 is really clumsy - you need to have one file for each table or other DB object. Essentially you have to do things the VS2010 way, which is quite cumbersome. (- VS2010)
VS2010 Is also somewhat fragile and source control integration is flaky. Even on a simple database every table, constraint, stored procedure, index and other database object is its own file. Files get added to the project all the time, much faster than a typical programming project with (say) C#. With optimistic concurrency it has a tendency to silently drop files from the project as check-ins get out of sync. Even with good team discipline the feedback from the UI about status is very poor. This would be a disaster on a complex model. (-VS2010 - I'd almost consider that a show-stopping flaw for a large project).
SSMS comes with SQL Server - can't beat the price (+ SSMS).
VS2010 still does not have a proper repository like (say) PowerDesigner or Oracle Designer. You can't easily query the data model without installing it into a database. (- VS2010).
On the whole, I would rate VS2010 about a B-. It was clumsy on a relatively simple database project with two fact tables and around 15 dimensions.
The largest data model I ever did was for a court case management system, which had around 560 tables. I would not recommend VS2010 for a project that size (I did that on Oracle Designer). Essentially it's trying to be clever and the paradigm doesn't really work all that well. You're better off with a best-of-breed modelling tool like PowerDesigner or just using create table scripts by hand.
SSMS is simple and reliable but manual. You have pretty much infinite control over how you want to manage the model. Combine it with a schema manager such as Redgate SQL Compare and maybe a decent modelling tool such as PowerDesigner and you have a much better package than VS2010.
Summary I'm not sure I could quote any killer features or benefits apart from (perhaps) integration with VS solutions containing other projects. If you already have VS2010 premium or ultimate then you get a somewhat flawed database development tool thrown in with your .Net tool chain. It will integrate DB projects into your VS solution, so you can use it to make sproc deployment scripts at least.
However, VS has no modelling tool to speak of, so PowerDesigner or even Erwin is better on that count. Redgate's schema management is much better and SQL Compare Pro is quite cheap (about £400 IIRC). IMHO SSMS works much better for T-SQL development but you can certainly do it with VS2010.
VS2010 premium isn't much cheaper than a best-of-breed database modelling tool and VS2010 ultimate is at least as expensive. At the expense of tight integration with your VS project you could probably do better with third party tools.
An alternative
I guess one shouldn't slag off VS2010 too much without suggesting at least one alternative and outlining its pros and cons. For the purposes of this I'll assume a large project. Although I mainly do A/P work these days I have been involved in a 100+ staff year project where I did the data model (and some development work) and a couple of others in the 10 staff-year range, where I mainly worked as an analyst or a developer. Mostly I work on data warehouse systems these days but the larger projects were mainly applications. Based on my experiences with various tools, here are some suggestions for an alternative tool chain:
Pros: Better database modelling and schema management than VS2010, better version control system, easier to customise the build and project work flow, better management of specs and documentation.
Cons: More effort to integrate tools, limited DB integration into build process.
Assumptions: Assumes that control of change/release process in more important than automated or tightly integrated release management for DB schemas. Also assumes that automated DB schema management is not 100% reliable.
Not terribly high tech or slickly integrated, but for a complex project you're probably more interested in control than cute automated features. I'd argue that you're better off with a set of best of breed tools and whatever home brew build and test scripting is necessary to integrate them. Just try to keep the build (a) relatively simple to understand and (b) fully automated.
QA on DB patches. On a large schema you may want to have a manual patching process for live systems, particularly if the patches involve data migration. For example, it may be desirable to have roll-forward and roll-back scripts to support backing out a change if necessary. In this case you will want to have a facility to test that the patch actually works properly. If you manage the database schema in a repository you can test the scripts by setting up before databases and generating a reference database from the repository. Running the patch script on the before database should synchronise it with the repositiry model. A schema compare tool can be used to test this.
I've done a lot more data warehouse and integration work than bespoke application development lately, so I encounter this more often than a development team will in most cases. However, project management tools and methodologies do a very poor job of managing external stakeholders. In an integration project such as a data warehouse I really want to see a project management tool that really pushes external dependencies (i.e. ones I don't control) in the face of programme management. On any non-trivial integration project the external dependencies are by far the biggest drivers of wasted time.
Best Answer
Go to your SQL Server Configuration Manager. You will find it in the SQL Server folder, under Configuration Tools.
Check the list of services that are currently running. At the minimum you should have started "SQL Server (MSSQLSERVER)" (if it's the default instance).
After you check that, go to the client and to the server protocols and see if the TCP/IP and Shared Memory protocols are enabled. If yes, then you should be set for the first time use of SQL Server.
Open Management Studio and try to connect to the local instance: use local machine name or IP. If it won't work, please tell us any error that you might get.