david wrote:
Dunno, haven't tried that one. I guess that includes Visual Studio
Express? Otherwise you will be stuck using SQLCMD to do even
the most basic tasks.

Visual Studio does include SSE, but SSE is also a standalone download.

Another option would be to download the full version of SQL Server
to experiment on. Last I looked, the full version is FREE for you, a
DEVELOPER, to do DEVELOPMENT on. You just use Express
when your company starts using it for real data.

I believe it actually costs $50 to have the developer version. Besides
it's not strictly necessary because the SQL Server Management Studio
(SSMS) is already available. In all versions it's bundled but I believe
for Express, you have to separately download the SSMS but it's free as
well.

The advantage of the full version is that it comes with the complete
client tools and documentation, including SQL Server Enterprise
Manager, which is (was?) the primary administration tool for SQL
Server. Dunno how Visual Studio/SQL Server Express compares
with that now.

It should be noted that the complete documentation is online at MSDN. If
you google "Transact-SQL whatever", you'll find many good
documentation there.

I'm struggling with the fundamentals of SQL Server databases. Is it a
single file like an .mdb file?

Yes, it is a couple of files, like Access requires MDB, LDB and MDW
files, and yes the data is in just one MDF file, like Access data is in just
one MDB file.

While this is technically accurate, this is conceptually misleading
because in client's interaction with server, the question of "where is
the .mdf?" will never be asked by the client. In fact, a properly set up
server would _deny_ access to the users to those files. Only the daemon
(aka SQLSERVER service) or the administrator would have access to those
files.

Thus, it's important to understand that you do not "browse and open a
file" as you would with other applications such as documents or
spreadsheet, but rather "connect to" a server using one of many
protocols... TCP/IP, Named Pipes, Named Memory, and then some more. Thus
the question of "where is the file" is totally nonissue because that's
the daemon's job to manage it and you communicate with the daemon.

Where does it get reside?

Like Access, they reside where you put them. If you want to share them,
you put them somewhere where they can be shared. Then you set up the
shares so the user can use them.

No. As I explained, daemon does all file handling here. Therefore a
proper setup will _deny_ the users access to the files. All
communication should go through the daemon. So you never put .mdf and
..ldf files in a share folder but rather merely open the port 1433 for
TCP/IP or use Named Pipes or whatever for the clients to connect to.
This is why if you look at various ODBC connection strings, the only
time you do see a filepath is when you're working with a serverless
source (e.g. a .mdb will have its filepath in the ODBC connection, but
you'll never see that for a Oracle, MySQL, DB/2, SQL Server!)

connectionstrings.com

To be crystal clear: The users do not need and should not have access to
the .mdf and .ldf files!!

For Access, creating new databases is done from Access, and setting
up network database shares in done from Windows Explorer: For SQL
Server, it is done from Enterprise Manger, or Visual Studio, or using
OSQL or SQLCMD

Is it creating an SQL Server on the machine which is a different thing
to an SQL server database?

Creating a SQL Server on a machine means installing and starting
the SQL Server service. For Access, the "SERVER" service is
required (plus some other things). For SQL Server, a "SQL SERVER"
service is required (plus some other things). The only difference is
that the "SERVER" service is included and started by default on
Windows: the "SQL SERVER" service you had to download and
install.

I do believe that "Server" service is not actually germane to the
discussion here. If you read its description, it says it enables file,
print and named piped sharing over the internet. That what it does. SQL
Server services, OTOH, enables clients to connect to this machine and
issue requests for data.

In fact, if I turn off "Server" service, I still can connect to SQL
Server on my SSMS, and reach a Sharepoint site remotely... IOW, "SQL
Server" service and "Sharepoint" service are not dependent on the
"Server" service. There is of course the notable exception that if I
wanted to use Named Pipes, then yes, I do need "Server" service. But
with TCP/IP, then no. They have nothing to do with each other.

As I explained in my previous reply to Jon22, the term "server" actually
refers to a role, rather than some kind of program. A server usually has
a daemon (aka Windows service) that listens on a specific protocol (e.g.
a port on TCP/IP, a region of address for Named Pipes/Memory, etc.) and
react to the requests through there. It goes back to why it's important
to not be so concerned with the questions such as "where is the file?"
because those are wrong questions to ask.

You will have to completely re-write that stuff in TSQL.

It's not a lot more difficult, but it is different.

I would question that. A well-written Access application usually will
run on any backend, be it Oracle, MySQL, DB/2, SQL Server with minimum
change (e.g. using linked tables). It all depends on whether one has
written the application specifically for server-client architecture in
mind, of which also benefits even ACE engine.

There are times and places where re-writing a piece of functionality
into T-SQL, stored procedure, views or other SQL Server objects does
make sense, but I really don't believe it requires a wholesale rewrite.
But that is largely a function of how well written the application was
to begin with.