Using SQL on Development Machine

[Jonathan Wood]

My Web application will eventually be hosted on someplace like GoDaddy.com.

NOTE: I am not asking anything about hosting my application on the Internet.
Rather, I'm asking about testing the application here on my desktop during
development.

On a new Vista system with Visual Studio 2005 installed, do I need to
install anything else in order to test it?

Specifically, I'm wondering about My SQL. With all the tons of stuff
included with VS, do I need to download and install this in order to connect
to a database on my test machine?

All comments welcome--I'm lost.

 

[Norman Yuan]

My Web application will eventually be hosted on someplace like
GoDaddy.com.

NOTE: I am not asking anything about hosting my application on the
Internet.
Rather, I'm asking about testing the application here on my desktop during
development.

On a new Vista system with Visual Studio 2005 installed, do I need to
install anything else in order to test it?

You only need to install what you need, and do not need to install what you
do not need. I know this is not anwsering anything, but no one knows what
your project does/includes, hence the answer.

Specifically, I'm wondering about My SQL. With all the tons of stuff
included with VS, do I need to download and install this in order to
connect
to a database on my test machine?

"The tons of stuff" in VS does not include MySQL. So, if your web app uses
it, you must have it available for developing/testing somewhere either on
your computer or on the reachable network.

VS2005 comes with SQL Server Express. However, in ADO.NET, you would use
different data access namespace, which would results in possible different
coding, so that code may not run on both MySQL and SQL Server/Express.

 

[Cowboy \(Gregory A. Beamer\)]

Download the MySQL bits for your OS. Then, you need a provider if you are
going to use OLEDB instead of ODBC (speed primarily). Have not touched MySQL
in quite some time, but a quick google search will find open source
providers.

Vista is a bigger problem, as you have to make sure you have VS 2005
properly patched and you have to deal with the IIS compatibility (if a web
app) to get things rolling in IIS. I am waiting for VS 2008 for my final
foray into Vista.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************

 

[Jonathan Wood]

Gregory,

Download the MySQL bits for your OS. Then, you need a provider if you are
going to use OLEDB instead of ODBC (speed primarily). Have not touched
MySQL in quite some time, but a quick google search will find open source
providers.

Okay, so VS does not include an SQL server then?

I will use native .NET database access. I do not know when or if OLEDB or
ODBC are involved or not. I'm pretty new to this area.

Vista is a bigger problem, as you have to make sure you have VS 2005
properly patched and you have to deal with the IIS compatibility (if a web
app) to get things rolling in IIS. I am waiting for VS 2008 for my final
foray into Vista.

Probably a good point, although a bit late for me now.

Thanks.

 

[Jonathan Wood]

Norman,

"The tons of stuff" in VS does not include MySQL. So, if your web app uses
it, you must have it available for developing/testing somewhere either on
your computer or on the reachable network.

VS2005 comes with SQL Server Express. However, in ADO.NET, you would use
different data access namespace, which would results in possible different
coding, so that code may not run on both MySQL and SQL Server/Express.

I'm a long-time develop who is fairly new to .NET and very new to SQL-type
stuff.

So VS *does* come with an SQL server. Well, this is what I've been driving
at.

But why on Earth would VS include a server that requires a different
namespace than what many people are using? I just don't get the logic there.

So if the company hosting my site only provides MySQL, then I need to
download it in order to develop and test on my computer?

What if the company hosting my site provides SQL Server? Is that compatible
with the one that comes with VS2005, or MySQL?

Thanks.

 

[William Vaughn]

Ah no. See >>>

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Jonathan Wood]

William,

In another thread, you suggested I download MySQL from somewhere. Perhaps
because I had mentioned it.

If my host supports both MySQL and SQL Server, is there any reason not to
just use SQL Express that comes with VS2005 instead of downloading MySQL? At
this stage, I have no idea which one is best, so I am certainly not looking
to use one over the other. I'm just trying to make this as simple as
absolutely possible.

Thanks.

 

[William Vaughn]

It's not as simple as that. As I discuss in my book, SQL Server is a host
database engine. It would require that the ISP permit you to install your
database into their instance of SQL Server. Would it be the same version as
you're using? Maybe, but only maybe. Would it support the functionality
you're using? Maybe. SQL Express has features not exposed on other upscale
versions and the upscale versions support features not exposed on the
Express version. Would you connect to the host instance in the same way as
you connect to your local SQL Server instance? Probably not.

So, how do you solve this problem? Well, you start with getting to know more
about your ISP and the engine(s) they support and installing the target
engine on your development system (or another local system). You'll also
need to devise a connect strategy that permits your application to
accommodate the security scheme installed on the ISP host.

Another approach is to choose a DBMS platform that does not require the host
to do anything to support it--like SQL Server Compact Edition (see my EBook
for details). This engine can be imbedded in the application deployment file
and accessed like other DLLs.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Jonathan Wood]

Unfortunately, while I have many years of programming experience, it's all
been on the desktop using proprietary file and data formats written from
scratch. I just don't have any experience with this sort of stuff. And I'm
just not able to get what I need from your description.

So I'm still not clear on if I even have any SQL server installed on my
system, what it might be, which ones it might be compatible with, etc. It
seems the more I dig, the deeper the hole I find myself in. All I want to do
is create a database in ASP.NET. Parts of ASP.NET are pretty straight
forward. Apparently, databases are not.

I'll just look around for another avenue to approach this.

Thanks anyway.

Jonathan

 

[William Vaughn]

Don't feel alone. I did most of my work in proprietary systems at EDS and
elsewhere. I think reading my book might get you off on the right foot. The
architectures chapter and "How SQL Server Works" were written specifically
for those with your kind of experience. While I don't get into ASP designs
much, I show where that approach fits.
Use the Services.msc snapin to look for SQL Server instance services. That
should tell you whether or not it's installed.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Jonathan Wood]

William,

Don't feel alone. I did most of my work in proprietary systems at EDS and
elsewhere. I think reading my book might get you off on the right foot.
The architectures chapter and "How SQL Server Works" were written
specifically for those with your kind of experience. While I don't get
into ASP designs much, I show where that approach fits.
Use the Services.msc snapin to look for SQL Server instance services. That
should tell you whether or not it's installed.

Thanks for your comments. I guess I really need a focused path to get
something up and running RELIABLY, and then I can expand out from there.
Instead, I don't even know which SQL server I have, which one I need, how
best to connect to it, so I can't even run the simplest of database
applications.

The fact is that your book is a bit overwhelming for me right now. It's a
ton of pages and at this stage, I would not be able to understand many of
those pages. It looks like a great resource that I hope to make good use of.
Hopefully, I can do so before it becomes outdated. But since I'm also trying
to learn ASP.NET overall (and trying to sell a house, and dealing with
health issues, etc, etc.), I'm not sure your book is the best one to start
with. I picked up a couple of ASP.NET ones and my start with those.

I will search around to see if I can figure out what Services.msc is.

Thanks again.

Jonathan

 

[Jonathan Wood]

William,

Use the Services.msc snapin to look for SQL Server instance services. That
should tell you whether or not it's installed.

Although a little tricky to find on Vista, I was able to. It contains the
following SQL-related entries.

SQL Server (SQLEXPRESS)
SQL Server Active Directory Helper
SQL Server Browser
SQL Server VSS Writer

So I would say I have SQL Express installed and ready to go. So if I went
with a host such as GoDaddy.com or Brinkster, who both state they support
MySQL and SQL Server, it would seem that SQL Express should work just find
for testing on my production machine. And should, for the most part, be
compatible with SQL Server on these hosts.

Jonathan

 

[William Vaughn]

I've been where you are (many years ago) so I understand your frustration.
That's why I wrote the books the way I did.
Okay, you do seem to have SQL Express installed. However, as I said, there
can be issues. Let's say the ISP does support SQL Server. It still must
permit you to install (attach) your database to their server instance. I
would not assume they are running SQL Server Express (although if the ISP is
small, they might be). I would write your application using SQL Server as if
it was on the local system, but not using the User Instance = True option
(that Visual Studio tends to do by default).

Yes, there are some great ASP.NET books out there and I depend on them too
as I don't do much ASP.NET work (I create Windows Forms applications and XML
services.) However, not many of them focus on the special issues associated
with SQL Server. This can be a bit daunting at first.

Good luck and keep asking questions. We'll help whenever we can.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Jonathan Wood]

William,

I've been where you are (many years ago) so I understand your frustration.

Well, I got the impression you had probably been working with servers and
databases before then, but maybe not.

Okay, you do seem to have SQL Express installed. However, as I said, there
can be issues. Let's say the ISP does support SQL Server. It still must
permit you to install (attach) your database to their server instance.

Reading some docs at GoDaddy.com, they said I needed a script in order to
create and populate a database on their server. So I don't know what the
ramifications are there but I assumed that was the approach I'd need to
take. Does that sound right?

I would not assume they are running SQL Server Express (although if the
ISP is small, they might be). I would write your application using SQL
Server as if it was on the local system, but not using the User Instance =
True option (that Visual Studio tends to do by default).

No, both hosting companies support MySQL and SQL Server (not SQL Server
Express). My comment was expressing my impression that SQL Server would be
largely compatible with SQL Server Express. So, unless I was doing any
unusual stuff, I should be able to test using SQL Express, and then upload
to the host and run SQL Server. Does that seem like it could work?

Yes, there are some great ASP.NET books out there and I depend on them too
as I don't do much ASP.NET work (I create Windows Forms applications and
XML services.) However, not many of them focus on the special issues
associated with SQL Server. This can be a bit daunting at first.

I guess I don't want to wait until after reading your entire book before my
first application. I need to get up and running because the best way I learn
is by doing. So, yeah, I need a more direct approach. Then I could hopefully
use your book to go back and get more detailed information as I'm able to
absorb it.

Thanks.

 

[Cowboy \(Gregory A. Beamer\)]

First a few notes.

You can develop locally with MS SQL (Microsoft SQL Server Express 2005). You
can install it when you install VS.

Realize, however, that MySQL has different features, so you will have to
remain VERY ansi compliant with your SQL statements if you want to go to
MySQL later and not have issues.

To be safe, you should download MySQL (http://dev.mysql.com/downloads/)
Community Edition and an open source .NET Provider (something like
http://www.mysql.com/products/connector/net/). This will guarantee you can
script and install your database on your production machine.


REST INLINE

Gregory,


Okay, so VS does not include an SQL server then?

VS includes SQL Server Express, but it is different from MySQL.

I will use native .NET database access. I do not know when or if OLEDB or
ODBC are involved or not. I'm pretty new to this area.

Use OLEDB, if you can.

Probably a good point, although a bit late for me now.

Have fun!

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************

 

[Cowboy \(Gregory A. Beamer\)]

Yes, you can use SQL Express and then move to a full SQL Server.

There are two ways to accomplish the move. One is to have them attach the
databse you create locally. This is generally harder than it appears, as
most ISPs do not cooperate.

The other is to script the databse, which you should be able to do from the
Express Manager. YOu then run the script through the SQL console they
provide. You will also have to seed any tables that act as enumerations
("type" tables).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************

 

[William Vaughn]

I've been working with data and computers that manage it since the early
70's. I taught myself 8080 and Z80 assembly language when the first PC kits
came out. Yes, I've been a novice too--partly self-taught, partly
college-trained, but most of my experience comes from working in the
industry for almost 35 years.

Mr. Beamer is right. You can script the database. SQL Server Management
Studio can do this for you. This does not populate the database tables with
data. Using SQL Server is very different in many respects than Oracle, MySQL
or JET or any other brand of DBMS. The way you approach problems is going to
leverage the strengths, features and bugs in SQL Server. These approaches
will not map 1:1 to other DBMS engines. Creating a functional and
well-performing generic application that can run on a variety (even two)
backends is a challenge for the best developers--not something that should
be attempted for a novice.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Jonathan Wood]

Gregory,

You can develop locally with MS SQL (Microsoft SQL Server Express 2005).
You can install it when you install VS.

Okay, thank you. Is it optional? Does there happen to be an easy way to
determine if I installed it?

Realize, however, that MySQL has different features, so you will have to
remain VERY ansi compliant with your SQL statements if you want to go to
MySQL later and not have issues.

My mantra is that I don't want to "do" database administration. I want to
write applications, like I did before. So I can't for the life of me imagine
why would I not just stay with SQL Server instead of bouncing between
servers?

Use OLEDB, if you can.

As indicated above, I do not know when or if OLEDB or ODBC are involved or
not. I will use native .NET database access and will have no idea if OLEDB
is involved, unless I read about that or someone explains it to me.

Have fun!

Too late now for that too.

 

[Jonathan Wood]

BTW, the connection string "Server=(localhost)\SQLEXPRESS;Integrated
security=SSPI;Initial Catalog=BlackBelt" fails with the error:

"An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)"

Otherwise, I'd now be developing my app instead of looking into database
issues (which I don't like and am beginning to dispise).

Wouldn't that a valid connection string if SQL Express was installed?

Thanks.

 

[William Vaughn]

Change the "(localhost)" to "." or "(local)" to refer to the local system.
This also assumes that you have rights to the default database "BlackBelt".
Is the service started? Start | SQL Server Configuration Manager to check.

See Chapter 9 (and make sure to download the new PDF version).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------