Advice on where to start for a new SQL administrator/developer

Hi all,

I have a project which involves me taking 98-364, 70-461/2/3/4 & 5, I have completed 98-364 and 70-461 but looking forward my aim is as follows...

  1. Learn about SQL Admin
  2. Learn about SQL Development
  3. Create a new database and a bespoke application to enable my employer to store property information including assets.

I need to start with a login and a menu, which i hope the courses will lead me in the right direction, but I am looking for some guidance into what I should be doing to start off with and any specific avenues I can pursue to achieve the above aims.

I am a developer in OpenEdge which although not common, it is a 4GL language which should lend me some good foundations to build upon.

Can anyone offer any advice or guidance on how I should be approaching this with regards to training and materials. and how best to learn and apply these skills to achieve the aims above?

Just my two cents... If you really want to work with relational databases and actually be good at it... Start with the two fundamental ideas that all of this is built on. Relational Algebra/Set Theory and Predicate Logic. Not to worry, you don't have to be a math genius to do this type work (it wouldn't though either), but it is important to understand that the relational model is based on actual mathematical models and the better you understand these models the better you'll understand proper database design (and vise versa). This one of those areas where abstract mathematical theory has direct application to the real world.

So... First and foremost... Learn the Normal Forms! I'm not saying you need to need to be able to write a PhD thesis on transitive functional dependencies... But you absolutely should know that that putting 3 different phone number columns on the Customer table IS NOT the proper way to deal with the fact that people tend to have more than one phone number.

Same for learning Predicate Logic... Aka, the fancy way of saying that any given proposition is either entirely true or entirely false... and of course the ACID model...

Personally, I got my start creating reports... I discovered that had a knack for it and I enjoyed it... It didn't take long before the limitations of GUI based SQL editors forced me to start messing around directly in the SQL code itself. That, eventually led to a job as a full blown SQL developer and from there to a DBA. As far as learning development first or the administration first... I think it's a false distinction, if you want to be a great SQL developer, you have to have an understanding of how the internals of the database engine works... and from the other direction, unless your idea of being a DBA is baby sitting backups and staring at wait stats, you're going to need to have development skills. So, try different things, take on different kinds of projects and figure out, for your self, what aspects you enjoy most and put your focus there but continue to learn the other aspects as well.

Absolutely agree. Very sound advice.

My other "Absolutely Essential" suggestion on normalisation is "Don't store the same data in two places".

So you have a Phone Number on the Customer Table, and you also have a Phone number on some other record which is storing the same data value, or a "copy of it", or the Operator is entering it in two different places. Some days/months/years from now the phone numbers become different - which one do you trust? how much time do you waste trying to validate, and synchronise, them? IME that would be "a lot", along with users cursing and losing confidence in the system ...

I came at it from the "doing" end too. I haven't got a single qualification to my name (some here may well say "that's the problem" :slight_smile: ) so I, too, strongly recommend finding a means of "doing" as much as just studying for exams. Whilst maybe not the norm? I would employ someone with experience over someone with qualifications. 80%?? of the syllabus for the qualification's examination we don't need in real-life, here, as it effects us, and for the rest we know where to ask, but the bit that we do know we are very very good at because "that's what we do" every day.

If you want a project here's my suggestion:

Write a scheduled routine that makes backups. Here's a spec, based on what our in-house tool does:

A Procedure to make a backup - pass it the Database Name and the type of Backup (Full, Diff, Log). Optional comment. Optional "retention period" (days). ALWAYS use this when you want to make a one-off backup - e.g. just before you do something "that has risk".

That procedure will ALWAYS store your backup file in a predictable location, and add it to some Metadata that you store about your backups (such as when they can be deleted).

Then create a scheduled backup. Have a table of Database names, and when the last (Full, Diff, Log) backup was made, along with what type of backup you need for that database (basically "Mission Critical, frequent" or "Once a day will do", and maybe some other "flavours"). Your scheduled task then needs to figure out which backups overdue, need running, and then run them.

Then add a scheduled task that Adds any newly created databases to your Database Table - with a default backup method (perhaps choose "Mission Critical" for that so that, worst case, any newly created database gets the highest quality backups). We explicitly exclude any database name that starts with "RESTORE_" - so that we can make a test restore WITHOUT clogging up the backup system. You might have other nuances - e.g. if the Database name ends in "_TEMP" or "_TEST" ...

Then figure out a list of things that could go wrong, and make sure your backup routines take care of them:

  • Database changed to Read Only / Offline / something else
  • Database changed from FULL Recovery model to Simple (or the other way)
  • Database no longer exists (is that something you should Report/Alert/Notify about?)
  • Disk full / Target Drive does not exist / all the other horrendous possibilities!
  • Database is corrupted - figure that the DBA would like to know about that sooner rather than later!

Possible enhancements:

  • Backup file security - we copy Backup Files to an alternative disk/server as soon as they are completed. (They also get copy to Tape/Cloud, but "not immediately"). Our Database Meta Table has info about "where" to make that duplicate copy.
  • Automatically restore to DEV or similar - and then run a DBCC CHECKDB on the restored copy - this proves that your Backup did actually work!
  • Check that YOUR backup has ALL the appropraite files - nothing is missing from the set - it is common for "someone else" to set up a backup, which then means that your "perfect" backup chain may be reliant on the other people's backup too. Getting to know about this ONLY when you have a disaster (and then trying to figure out where the heck they stored THEIR backups ... particularly if they are only on Tape, which you cannot quickly get restored) is much too late! We get this all the time from some other "well meaning" company that adds some Backup Tool to the server, and detecting it the moment that the Backup Chain was broken has saved our bacon on more than one occasions (the 3rd party can make a COPY_ONLY backup, that doesn't break the chain at all, so once you tell them :slight_smile: they can do that)

A Restore Procedure would be handy too. I want to restore to Friday at 14:56 - what backup files do I need restoring? Automatically generating the script for that saves time (usually at a time when everyone is screaming and time is of the essence). That needs to work both from your meta data about backups (or the data that SQL keeps in MSDB, if you like), but also from the physical backup files that are able to be found on disk

Blimey, I'm exhausted typing that! Our backup routine has evolved over the years, as we've needed new features. There are good products out there, so you could use those as a reference. I don't think that the Microsoft provided Maintenance Plans are suitable for ... well "anything" really. They do the job, but they do it in a way that I think solves the wrong problem :slight_smile:

Take a look at:

Minion Backup (I like this one, nice and simple to set up)
Ola Hallengren's backup (I find this one very complex)

Thanks for the replies, very much appreciated. I should explain that I am a developer in a 4GL language so I am comfortable with Normalisation, etc. My problem with SQL is where to begin...

So I learn SQL DB Admin, ok.. on it..

Next, What language should I be learning to write the apps, i will need a login screen, menu structure, then the user apps, but I don't (yet) know whether I should be learning C++ C# or something else!

I presume Visual Studio and C++ but this is just a guess, so I am asking the experts & current users on What I will need, why, and how best to get there.

In SQL you can create a Stored Procedure. The whole thing is written in SQL and is usually a building-block such as:
"Get a record from Table-X with ID=y"

"Save a record to Table-X with all these Parameters" - and that could do data validation / sensibility checking (and return Error Code / Message which APP can then display to user)

"Find all records that match these Criteria Parameters" and possibly also calculate some additional values - running totals, grand totals, or allow "Paging" ("Only return rows for Page-X of the results")

So it could be that the language that you need is SQL (properly called T-SQL for MS SQL Server).

Speaking for myself, 99% or more of the Application Code we write is SQL. But a long time ago we took the decision that we did not want logic split in two places, so we built the application "dumb" to just do screen input stuff and pass that to SQL for "handling", and in the reverse for anything that comes back from SQL "just display it". Our resultsets (from SQL) can contain "display hints" which the APP acts upon, which enables us to have the flexibility to control the output solely from the SQL end.

That's not a conventional route though! ... at the other end of the spectrum you could just embed some SQL commands in the APP code (which could be in C# or any other language you like the look of ...) and not have any APP logic at the server end at all. That tends to have security/permission and performance issues. If you want to tune something (because it is slow) you have to change (and deploy) the application, whereas changing a SQL Stored Procedure just requires deployment of that one Proc. Very easy to "mess around" with that Proc, in a DEV environment, testing different parameters, analysing performance, and so on, until it is improved/fixed/tested to the point where you can then deploy it.