So far my experience is that
getting a SQL query correct is
fairly easy and usually fun
but that the real difficulty,
e.g., for SQL Server, is for the
rest:
(1) Installation.
Grim. Barbed wire enema.
Wiped out my Windows boot
partition requiring that I
reinstall everything starting
with the Windows DVD.
(2) Security.
The stuff with users,
logins, etc. and nearly
everything in SQL Server
Management Studio was
clear as mud. In the end:
(A) Assume that basically what
is there is essentially old
capabilities and access
control lists, imagine how
they might work, and make that
a first guess. It helped.
(B) Use SQL Server Management
Studio only for passively reading
but never for actual management
where change things.
(C) For each of the important
steps in setting up
security, do searches on the
Internet, find standard
text SQL statements, and
try those. Save and profusely
document the statements that do work.
(D) Do system management
only with SQL statements in
simple text files and executed
with the SQL Server
command line utility
SQLCMD.EXE.
(3) Connection String.
The last time I had to get
a SQL Server
connection string to work
took a solid week of
throwing things against
a wall to see if they would stick.
The effort was an
unanesthetized upper molar
root canal procedure.
I haven't found that stuff overly difficult so far.
http://downloadsqlserverexpress.com/ is a nice source to install SQL Server and Management Studio from, which also has a link to a blog post kinda mocking how ridiculously hard it is to find the installer on the Microsoft website.
I also find it odd just how long it takes to install, versus Postgresql on Windows installing in like 5 minutes I think.
I'm not sure what you're trying to do with users and permissions, but I haven't had much trouble with that in Management Studio. Add a new login in the server Logins folder, set either a username/password or domain authentication, select databases and set permissions. Not that I've tried anything tricker like assigning multiple users to a role, or doing stuff with AD groups.
https://www.connectionstrings.com/ is a decent resource for connection strings too. It seems to be pretty easy, as long as you're connecting from other Microsoft tools, like Management Studio, sqlcmd, ADO.NET, etc. I'm pretty sure I once spent like a day or so trying to figure out how to get ActiveRecord to connect to SQL Server before I got it working.
That said, the usual lessons could stand to pay more attention to this kind of management stuff.
I've got SQL Server working
now with notes on how I did it
and with some simple text files
with SQL commands to be run
with the Microsoft SQLCMD.EXE
for setting security.
For the connection string,
I found something that is working
and, of course, have it documented,
saved, etc. To define the
tables and columns, I have that also
as just simple text in a file
run with SQLCMD.EXE.
To keep SQL Server installation
problems from ruining my boot main
partition, finally I am good
at using NTBACKUP to get a
clean backup and know how to
restore it. So, before doing
anything like a SQL Server install,
I will use NTBACKUP to save
the main boot partition.
If SQL Server messes up, then,
boom, I will just restore the
boot partition and start over
with the SQL Server install.
The worst time with SQL Server
installs it wasn't clear what
the heck to click on, ended
up with two versions installed
side by side, tried to uninstall
one, and ended up with a sick
SQL Server that wouldn't do
even the simplest things.
Another time I wanted SQL Server
to use a database from
an earlier install, and trying
to do this got SQL Server sick.
Trying to uninstall and reinstall
ruined my boot partition.
But, as soon as I can get the
other work done, I will get
the first server computer
for my "server farm", install
some recent versions of Windows
Server, SQL Server, IIS, etc.
and go live. Then, with
Windows Server, etc., I will
be back into system management
again.
Today I am fixing a bug in
my Web site: I have two
Web pages where they use
server.transfer
to send a user from one to the
other, and I have a fairly
elaborate session state to be
saved in one page and restored
in the next one (instead of
Redis, wrote my own session
state store with just
de/serialization from/to
byte strings of instances of
my session state class and
two collection classes). But
going between the two pages
I must have coded when
half asleep and didn't get
quite right. So fixing that now.
Also putting in lots of documentation
to make it clear just what
is going on.
My wisdom in building a Web
site with ASP.NET: Start with
the class for the session state
you want for your site.
That is, concentrate on this
data, with essentially global
scope. The first crucial work
is just being really clear on
just what the session state data
is to be.
The second most
important thing is to be clear
on just how ViewState works.
ViewState is useful and
can save some coding.
Third, in positioning things
on the Web page, maybe
make good use
of instances of the class
Place Holder.
Fourth, have clearly in mind
all the data that is available
from a post back -- some of that
data can be useful.
Fifth, make good use of the
ability to write data to the
log file.
Last, have
clearly in mind when
routines
Page_Init
Page_Load
the button click routines
Page_Unload
etc. run.
After that, just keep it
all simple and clear for the
user.
Oh god, no more Access. Access has a weird semi-dialect of SQL where it's insanely picky about how you position parenthesis in multiple join statements. I found it impossible to get a query right without the graphical query generator, and that's after routinely writing 5-10 table joins in T-SQL. No thanks, I'd recommend Sqlite over it anyday for a lightweight database. Works great on command line or in any of dozens of helper tools on any platform, and it's Free.
That was easy. Think of it
as close to set theory!
I needed to get through all the
overhead stuff since I'm using
SQL Server in the server farm
for my startup. So, I have
code for Web pages
with ADO.NET and also some
stand alone programs that
do SQL Server queries.
Again, the only difficult
stuff was the overhead stuff,
and it would appear that the
Khan course would not cover that.
(1) Installation.
Grim. Barbed wire enema.
Wiped out my Windows boot partition requiring that I reinstall everything starting with the Windows DVD.
(2) Security.
The stuff with users, logins, etc. and nearly everything in SQL Server Management Studio was clear as mud. In the end:
(A) Assume that basically what is there is essentially old capabilities and access control lists, imagine how they might work, and make that a first guess. It helped.
(B) Use SQL Server Management Studio only for passively reading but never for actual management where change things.
(C) For each of the important steps in setting up security, do searches on the Internet, find standard text SQL statements, and try those. Save and profusely document the statements that do work.
(D) Do system management only with SQL statements in simple text files and executed with the SQL Server command line utility SQLCMD.EXE.
(3) Connection String.
The last time I had to get a SQL Server connection string to work took a solid week of throwing things against a wall to see if they would stick. The effort was an unanesthetized upper molar root canal procedure.