Reply to: Learning SQL Server

[Spacecadet]

I spent the first 4-5 years of my IT career working on Oracle as a PL/SQL developer (8i), the rest (6 years or so) I've worked almost exclusively on MS SQL Server but have still done a bit of Oracle, Informix, Access, MySQL.
Not having had a huge programming background meant that I have avoided many of the pitfalls that you speak of, i.e. VB developers approaching database development from a procedural point of view rather than a set based view.

When blaming the inclusion of a procedural language in a database product there seems to be a blanket dismal of any use of T-SQL or PL/SQL.
Both have their uses, their limitations and the misuses.
There also seems to be the arrogant assumption that any scripting done on the database is wrong and it should be "elsewhere".
It doesn't matter where the scripting is done. Bad database development is a system wide issue. Whether it's a crap set of queries being invoked by an application or an SQL procedure using row-by-row cursors rather than a well written DML statement.

Going back to my roots as an Oracle PL/SQL developer.
The first system I developed on, involved a database that had 2 main tables and 4 or 5 very small lookup tables. The contents of the database were a complete mess, as was the development procedure. Document everything first, then develop. Problem is, signed off development documents could contain fundamental flaws which even a company purporting to be an Oracle specialist would miss.

My second job worked on a more relational database model. Unfortunately most of the developers were crap. Processes which should take a couple of seconds to run took minutes and in some cases hours. Cursors all over the place and so much stuff hardcoded that at least 2 weekends a month were spent trying to fox reports so that management could actually get some sales figures out. It was so bad that I almost left after 3 months. Instead I stayed and decided to force a rethink through and push the standard up. Thankfully I managed to get through to the rest of the team and performance improved.

When I switched to SQL Server, I was initially appalled. Like you I hated the point and click attitude. On the surface it appeared as if it was a real second rate system compared to Oracle.

I persevered again, and after a couple of years (yes, it wasn't overnight) and being forced by my manager at the time into a DBA role, I came to love the system. Reading up on the literature, both MS and from other sources, I could see that the form based management style was just another way of doing things. It does not absolve the DBA of any responsibilities in terms of checking system performance, monitoring, compatibilities etc.
SQL Server might try and smooth those over for you if you happen to be implementing a smaller stand alone system. But as soon as you move to multiple servers for multiple companies in multiple countries you have to know what your doing.

Having left the full time job to move into contracting I decided to specialise in SQL Server T-SQL development. DBA work is ok, but (on any system) uses a lot less imagination and thought then development (I'm not saying that being a DBA isn't easy). Its involves a completely different mindset to development and as you say, the two should not be mixed lightly.

Now that I'm a contract SQL Server developer I still get asked from time to time to have a look at Oracle PL/SQL scripts which aren't working/performing/behaving as expected or simply need to be migrated to a SQL Server platform. I see exactly the same mistakes being made in Oracle PL/SQL that I do in SQL Server T-SQL. Cursors instead of well written DML, bad use of sub queries, Dynamic SQL being used out of ignorance or laziness and nobody ever seems to know efficient ways of querying linked databases.

Now to get to the issue of MS propaganda. I'm a keen reader of technical documents. I hate not knowing something and I also believe that the best way of getting the optimal performance out of any system is to read up on the official literature, white papers, documentation etc... to make sure that "the wheel" isn't being reinvented. At no point have I ever got the impression from Microsoft that SQL Server was a "self healing" system. They supply tools-a-plenty to help you monitor its health, get alerts when something isn't right and a lot of advice on best practices. But for anything other than the smallest implementation a knowledgeable professional is required to get the best out of the system.
As well as the official literature, there are also the SQL Server MVP's. Go in to any online SQL Server community and these people stand out. They know a lot about the system, the best practices and how to keep it running well.
http://www.odinjobs.com/blogs/career...perts_part_iii
Going back on to google, searching on "sql server best practices" (without the quotes) brings up as first result this:
http://technet.microsoft.com/en-us/s.../bb671430.aspx
A wealth of best practice papers direct from microsoft all of which impart proper database theory knowledge specifially applied to SQL Server, although in many cases also applicable to other database systems. It is not simply a lot of what to click and what not click guides.


So where is this "self healing" myth coming from? It comes from the bottom up. From people with very little experience of SQL Server and see it, as I initially did, as a point and click environment which doesn't need any administration.
Unfortunately these masses can appear to shout the loudest. Especially when you have the feeling of still finding your feet.
As a professional, it is imperative that you do your best to champion best practices. Educate those who aren't doing it right and don't sit back and let them develop "crap" on a "crap" system.


I did have a look for any propagande which may exist from Micrsoft about "self healing" databases.
http://www.microsoft.com/presspass/f...18sql2000.mspx
I'll leave it up to you to read it. But make sure you read this one as well:
http://www.oracle.com/technology/ora...310gcover.html

[Sir_Edward_Matheson]

The issue is not necessarily one only of Microsoft against Oracle the other big gorilla in the RDBMS market (besides IBM with DB2 of which I have no experience) who is equally culpable in regard to some of the issues raised, rather the issue is of the quality and sorts of people that qualify as being SQL Server DBA's whether permies or contractors.

Most SQL Server DBA's were developers who started out creating maintenance plans and went from there; they are in the main developers both in terms of mindset and in regard to their main duties. It is quite common to hear "DBA work is not that interesting because you are just using somebody else's program" or some other such nonsense. There is little appreciation for many of the things a DBA will do. As an example prior to installing SQL Server on a brand new server a simple check that the DBA should make is verification of the default OS language and locale as the install picks a default collation based on that. These developer-DBA's never make this check IME. They then wonder why some queries throw errors on the new environment when a little planning and forethought would lead to a common environment. This type of thing is harder to do with Oracle as the install is much more involved and the checks are required.

A further gripe is T-SQL and the notion of the database developer in SQL Server. The database developer in SQL Server is usually a developer coming from a VB or lately .NET background with little appreciation of the nuances of set theory and is prone to using a cursor with little regard for execution plans and the like. While this is possible in Oracle, experience indicates that as PL/SQL is much more powerful and complete as compared to T-SQL (until recently anyway) and with the average Oracle developer being an almost exclusive PL/SQL developer there is at least some appreciation for what works and what does not in regard to query performance. Furthermore, there is a blurring of the lines between database developer and DBA in the eyes of clients some of which is from the MS propaganda.

Yet another issue, which again is possible with other RDBMS' is non-relational design but you are more likely to see it in MS shops mainly because these are more than likely to have databases designed by those with a procedural understanding of how systems work. IMO, (and yes I am a little dogmatic) design should always be relational. Denormalisation means you want BI which means firing MDX at the server and not SQL. This is better than the crappy idea of parent-child tables which is denormalisation. In really crappy environments you will see these types of tables grow exponentially, i.e the number of these tables as you can always think of something else to denormalise rather than create BI cubes and fire MDX to get your results. One of the jobs of a DBA is to know what tools are on offer, in this case SSAS yet how many developers have heard of SSAS let alone set it up or developed against it.

Some of the aforementioned is due to a lack of skills but really it has its roots in the MS propaganda that their system is self healing and does not really require those expensive DBA's like Oracle. I agree that it by and large is self healing as compared to Oracle and means you can be productive doing something else but this is not the be all and end all of DBA tasks. This then means that the developers design, manage and develop for SQL Server usually with the most disastrous results in some cases. Example: a quad quad-core 16 GB system which is massive massive massive overkill for the system and yet they are surprised when it still averages 35% of CPU resources. If there had been DBA's on board from the beginning to manage the environment, design or provide advice about database design, manage ETL processes and the like the situation would not deteriorate to the point that the system will likely fall over in 18 months time. It would also prevent certain poor habits amongst developers like updating target tables from source tables and then updating the same source tables from aforementioned target tables to update other target tables and so on and on. Tools like SSIS are of little or no use in thid situation and in fact get in the way when systems have been designed like this as it is usually a case of packages calling other packages ad-infitum. I should add that I have little respect for OWB in this regard either.

Finally, IME MS environment developers, and especially .NET developers with special emphasis on ASP.NET developers, are usually not particulary good at anything much except point and click. These are the sorts of people that will send you and SSIS package to insert six rows (one coulmn only) in to a table. Of course, as this was developed against the local install on their development machine it is my job to change connection strings when it would probably be quicker for me to type those six rows in using SQL Server EM. This is also something these types are prone to doing and I have heard of them saying "BEGIN TRAN XYZ....ROLLBACK TRAN XYZ" just slows things down. These are the primary people that end up being DBA's and database developers in SQL Server environments.

To be sure, I am not particularly critical of MS or SQL Server rather of the way they push their system which is leading to it being poorly configured in the vast majority of case, poorly designed for in the vast majority of cases and poorly developed against in the vast majority of cases. Again, this is also possible with RDBMS but it is less likely to happen as there are likely to be present those with the correct skills and background in these cases. Very sadly, this is not the case with SQL Server.

[Spacecadet]

For the most part, "WHS" is all that needs to be said.

However, there's one phrase in the rant that has important implications in another area of technology:

Developers tend to think proceduraly rather than in a set-based fashion

That, right there, is the reason so many people find it hard to get along with XSLT.

SQL is a declarative language intended for the purpose of manipulating sets.

XSLT is a declarative language intended for the purpose of manipulating sets.

Every single objection, misunderstanding, and silly rant that I have ever encountered in relation to XSLT has been down to the fact that programmers used solely to procedural modes of programming (which includes OOP in all current manifestations) simply cannot understand the nature of declarative programming.

Totally agree with you there

And to get back on topic, this is why SQL implementation vendors such as Microsoft keep on sticking support for procedural programming concepts into their proprietary implementations of SQL (though not just MS - others also introduce procedural concepts into their versions of SQL).

Nice of you to give a foot note to other DB vendors in that anti-MS rant.

There is nothing wrong with vendors supplying and supporting procedural programming in the databases.
There is something wrong with programmers using it badly, as you say, where set based methods should be applied
There is also something wrong with the database doing processing which should be handled by an external application (in the case of the application - backend DB model).
However there is still a strong need for procedural programming within the database environment.

Interestingly, Microsoft have strengthened their support for taking the ETL processing out of the database. SSIS is a huge improvement over the old DTS and MS made sure that the performance benefits of using an external ETL tool were known when 2005 were launched.

They want to make it easy for people to be superficially productive without imposing the requirement that said people grasp the underlying concepts.

And why do they want to do that?

Procedural programming within the database has a long history and support for it cannot suddenly be dropped.

There are many valid uses for procedural programming which need supporting

Bad workmen use the wrong tools... MS are not to blame for companies employing crap database professionals

To increase sales into the enterprise. Marketing a reduction of TCO is everything.

It comes down to "Relational Database theory is hard, and you need to pay for smart people who can make it work... so now we've made a version that anybody who can get their head around BASIC can use! You get cheaper staff, we impose our elision of the declarative paradigm! N.B. CHEAPER STAFF! CHEAPER STAFF!"

And then, when it turns out that people who don't understand Set Theory aren't very good at the theoretical underpinnings of RDB implementation, Lo! there is a solution: Microsoft has a consultancy branch that will teach all your tame VB programmers how to use SQL Server well enough to get along... until the next time, when you need to pay the "experts" to teach them how to get along with the next version... and this carries on, and at the end, none of them understand the first thing about the relational model, and your database is fscked.

And the TCO? Well, that was gauged on the previous version...

No idea where you get this crap from (must be an apple blog somewhere)
Microsoft have the MVP program...(MVPs are independant and not on the MS payroll) all of the SQL Server MVPs who I've ever read up on have strongly advocated set based theory

You really need to calm down with all this anti MS stuff old boy you'll have a stroke at this rate

[Bob Dalek]

<rant>
</rant>

The gap twixt MS-SQL and Oracle closes by the month: both will be pointy-clicky toot eventually.

Bob Dalek's Prediction of The Week:
Oracle's CBO will be eventually tweaked to the demands of procedural code, rather than declarative code, as more and more apps. become moron-written drivel. Too many sites now believe that SSDs, RAC and in-memory everthing is better than "proper" code and design. I see their dollar-driven POV, but it still makes me puke.

[NickFitz]

<rant>
WHS.
</rant>

For the most part, "WHS" is all that needs to be said.

However, there's one phrase in the rant that has important implications in another area of technology:

Developers tend to think proceduraly rather than in a set-based fashion

That, right there, is the reason so many people find it hard to get along with XSLT.

SQL is a declarative language intended for the purpose of manipulating sets.

XSLT is a declarative language intended for the purpose of manipulating sets.

Every single objection, misunderstanding, and silly rant that I have ever encountered in relation to XSLT has been down to the fact that programmers used solely to procedural modes of programming (which includes OOP in all current manifestations) simply cannot understand the nature of declarative programming.

And to get back on topic, this is why SQL implementation vendors such as Microsoft keep on sticking support for procedural programming concepts into their proprietary implementations of SQL (though not just MS - others also introduce procedural concepts into their versions of SQL).

They want to make it easy for people to be superficially productive without imposing the requirement that said people grasp the underlying concepts.

And why do they want to do that?

To increase sales into the enterprise. Marketing a reduction of TCO is everything.

It comes down to "Relational Database theory is hard, and you need to pay for smart people who can make it work... so now we've made a version that anybody who can get their head around BASIC can use! You get cheaper staff, we impose our elision of the declarative paradigm! N.B. CHEAPER STAFF! CHEAPER STAFF!"

And then, when it turns out that people who don't understand Set Theory aren't very good at the theoretical underpinnings of RDB implementation, Lo! there is a solution: Microsoft has a consultancy branch that will teach all your tame VB programmers how to use SQL Server well enough to get along... until the next time, when you need to pay the "experts" to teach them how to get along with the next version... and this carries on, and at the end, none of them understand the first thing about the relational model, and your database is fscked.

And the TCO? Well, that was gauged on the previous version...

[Sir_Edward_Matheson]

<rant>
I have recently come off a spate of ill configured instances setup by developers whose attitude was "SQL Server is just another program." The install process is too simple which means a lot of developers think this is just another click-click-click install without doing the requisite checks beforehand. An example was the developer/DBA who asked "What is a collation?" or the developers who had indexed only one side of a primary-foreign relationship and wondered why things were slow.

My favorite is the contract just gone where I was not renewed because they wanted a developer who had some SQL Server skills.
This is a place where the x64 instance is not releasing memory on a quad quad-core 16GB system and the developers and SA's are non-plussed but it is another of those things that are just ignored like the completely tulip non-relational design of all their databases. In case you are wondering the solution is relatively trivial but not particularly obvious.

Another skill sorely lacking in a lot of places is proper relational design skills as opposed to surrogate keys here there and everywhere. This though is possibly not only a SQL Server issue but it is more prevalent in SQL Server shops as developers tend to think proceduraly rather than in a set-based fashion.

The major cause of the devaluation of SQL Server DBA skills is the Microsoft propaganda/sales material which makes out that there is no need for DBA's. There is an implicit undercurrent that SQL Server takes care of itself unlike that nasty Oracle that requires those expensive DBA's to take care of it.

The issue might not be so bad if the install process consisted of something more involved than brainlessly clicking next. It is fashionable to knock the Oracle install process but the prerequiste checks on say RHEL are for the long term benefit of the system and they take little time especially when using a script to make the required changes.

Rates are not particularly clever for SQL DBA work when compared to Oracle or DB2 for that matter as the marketplace is awash with SQL "DBA's" who are really developers that once created a maintenance plan. A lot of shops now want "developer/DBA" with the worst culprits asking for "C# and .NET experience." A lot of this relates to the tulip known as SSIS. Another pointy-clicky piece of tulip that is okay for small processes but when they get big it is just cumbersome and slow.

Personally I will be moving back to Oracle in 2009.

Good luck with your SQL install BTW. I would go with Windows 2003 on VMWare Server with a bridged connection.
</rant>

[BamBam]

Depends what you're actually trying to learn (DBA-stuff or developer stuff?).

But for most things, consider this... If you've already got Visual Studio 2005 installed, you've already got SQL Server 2005 Express installed too. So, just download the SQL Server Management Studio and you're up and running! You can even use SQL Profiler on Express.

Of course if you want to do more complex stuff (eg Reporting Services, Distributed views, etc, etc) then you will need a proper SQL Server 2005 instance.

[Ardesco]

VMWare ESXi is free, although you need a spare box to run it:

https://www.vmware.com/tryvmware/log...FQ-mQwodSzC9XQ

I'm impressed with it, you can build yourself a whole domain if you want to so that you can simulate all sorts of real world situations. Well worth a punt at the price

[voodooflux]

Nice link. And if it's a proper self-contained image you'll be able to run another one once the 30 days are up. So as long as you can learn enough to do a full db export in 30 days, you're sorted!

Good point - learn that first!

[thunderlizard]

Nice link. And if it's a proper self-contained image you'll be able to run another one once the 30 days are up. So as long as you can learn enough to do a full db export in 30 days, you're sorted!

[voodooflux]

Another vote for using a VPC here - if you end up trashing things as you learn then you can just start over again.

You can grab a pre-configured SQL Server 2005 VPC image from Microsoft that'll work for 30 days.

[thunderlizard]

I did a similar thing a couple of years ago and my PC's still got various bits of SQL Server on it that I couldn't get rid of - so maybe VPC for that reason.

[Bob Dalek]

no need for the Virtual PC - adds an extra layer of complexity and unless you're planning to go into DBA roles then unnesseccary IMHO.

Install it straight onto the OS, you can install more than one instance if you want multiple database environments.

As an Oracle bod, I naturally abhore SQL Server. That said, I think if you want to learn it properly, you need Windows Server, or whatever it's called - so maybe VMware/whatever would be the way forward, after all.

Windows Server is free to download and use for (I think) 90 days. I recommend CBT Nuggets CD-ROM courses. I trained myself in SQL Server for about £50 using their stuff & a couple of books.

[London75]

What are you trying to "learn". It's not relevant to the spec as it's fine.

Development, DBA or Reporting?

[VectraMan]

One good thing about using Virtual PC is you can close it down and abandon all changes to the virtual disk. Pretty handy when you're experimenting with something and screw it up completely.

Never tried with SQL server though.