Fremus.co.za

One of the other things I felt the esteemed devs at the previous company had problems with was fundamentally understanding SQL Server from a technical and ground-level understanding. I don’t think they understood things like indexes very well, because in the database design they only ever used primary keys, and when they used foreign keys, they referred to doing things the ‘relational way’. SQL Server 2008 has certain types of indexes and understanding them, in my view, is central to understanding how your database should be designed. What is a btree?

In computer science, a B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic amortized time. The B-tree is a generalization of a binary search tree in that a node can have more than two children. (Comer, p. 123) Unlike self-balancing binary search trees, the B-tree is optimized for systems that read and write large blocks of data. It is commonly used in databases and filesystems.

I also recall these ‘DBA’s’ categorically stating that they didn’t like views – without a reasonable argument, which they expected everyone just to accept. Its the same argument style they used when comparing Dell to HP – they expected everyone to accept their argument (or lack thereof) that HP was worse than Dell. Back to the point of why they didn’t like views, which was based solely on some over hyped personal opinion and not some real proof. I just did a quick Google for Views vs Stored Procedures and one of the first links I cam across was an article by Frans Bouma where he lists quite a few points on why stored procedures are bad, and why using dynamic SQL is a better approach. I also found an article by Jeff Attwood where he clearly states that stored procedures should not be used for simplistic needs. Within the previous environment I worked in the devs wrote stored procedures for everything, including basic scaffolding.

I am still frustrated and angry with my previous employer, simply because they did not offer me anything new to learn at all. I don’t know why I still carry this frustration, but I do, so maybe I need to use this energy and write about the things that bothered me so much and propose a better way.

Their programming techniques were questionable to say the least, from having upper case method naming conventions right through to having no real clue about things, and claiming they did. The thing that was the most surprising was that through their cluelessness, they actually thought that what they were doing was right. What gives me the right to say I am right? Not much really, apart from the fact that I did study some and I feel that what I was taught was completely ignored by these ‘experts’. Some are claiming to be Senior Web Developers with 6 years experience – yet they have only worked with Microsoft Technologies, and they have only worked for one organisation their entire career, so who really measures their seniority, apart from them? Claiming to be a Senior Web Developer using VB.NET and not knowing a quarter of the language’s features does not entitle you to seniority either. The DBA’s also know little outside of SQL Server and when asked about MySQL they tend to be smart about it as if SQL Server is the only piece of RDBMS software written. I have written before that these people talked about “doing things the relational way” when they used SQL Server, and if that is not a red light to anyone who has done some computer science, then not much will be. The fact is they pretty much did self appointed ‘DBA-mastery’ when what they really did was write little more than messy T-SQL Scripts, which included generating HTML in some cases (shocking!). The T-SQL often used concatenation to generate results and was difficult to read, to say the least, and because they were able to write and execute these scripts, they thought they were awesome and wise. Yet data often leaked to clients that were not suppose to see it, which makes me wonder.

In my first attempt at dealing with the frustration I want to list the things a SQL Server DBA should now, which I know they did not. First and foremost they relied almost entirely on learning by Google, in other words whenever an error occurred, Google was their first source. They had no real system for breaking the errors down, which ultimately meant they did not have enough knowledge or expertise of SQL Server, yet they called themselves DBA’s. Asked what CLR stood for none knew either. I know normalization can be both good and bad – but throughout their database design(s) they did not do any normalization and I am willing to bet that if you asked them to normalize they would a) not be able to recite the normalization theory b) apply normalization, primarily because they tend to think that a certain way of feeling or thinking is going to result in a better ERD design. I saw in at least one instance where their ‘way of feeling’ failed, and where normalization would have helped a great deal. Other things that seemed strange to me was that they used the ‘sa’ user as login, as well as “views” simply being deemed “bad” with no real good reason.

So what are some of the better practices? I did a Google for some SQL Server best practices and came across this MSDN article and relating back to the point I made about the ‘sa’ user, on Page 18, of this document:

• Have distinct owners for databases; not all databases should be owned by sa.

Why is the sa login such a vulnerability though? I bet none of the guys at the previous job can answer that since they use sa as a login for everything.

Ok so I have written my first CLR project, but there are a few things I didnt do right and which I know requires a bit more knowledge and effort. Firstly, the context-connection seems to be only applicable to the SqlPipe class. I tried using an SqlConnection that used a regular connection string, but I got an error stating that SqlPipe.Context can only be used with context-connection = true. Allrighty then. Coming to think of it I did not do much wrong in terms of data access, because I used stored procedures that already existed. Anyway…

My main problem was that I needed a connection string and I didnt know how to get it. To get a connection string into a CLR project you have these choices:

• Use an App.config file. This is not really recommended in a production environment because you will have to restart SQL Services for SQL Server to read the values in the config file. If you plan on using a config file, you will have to place the config file in the root folder of the SQL server instance. You get this by looking at the properties of the database (look for Root folder), and naming the file something like sqlsrv.exe.config.
• Hardcode the connection strings. This is hardly a good thing to do
• Use context connection = true. Again though you need to be aware that the usage for this connection type differs from a regular connection string. Read the MSDN article here
• Use some external file, plain text, xml, etc. This means however knowing a part of the directory structure the machine/server resides on.
• Could you use a web service? Haven’t really thought this one through yet. But could you hypothetically speaking use a web service to initialise a connection string? I’m not sure
• Use a scalar user defined function and pass the database details as parameters, and run the function as a Job. This is the method I chose.

So during the week I had a situation where I had to take an existing web application, that used SQL Server Express, and had to make it work with SQL Server 2005 Standard Edition. Both versions of SQL Server form part of the SQL Server SKU line of products, but their implementation in certain scenarios differ. The providers that are used to connect through your applications are the same though.

I used Visual Studio 2005 to develop the initial application, and in many ways Visual Studio is awesome but in other ways its not, and specifically with regards to SQL Server databases its not that great. Visual Studio allows you to simply drag and drop databases into your web application project, which is great for productivity, but it may cause you some headaches later on. If you have SQL Server Express running on a local machine you will note that a database created in Visual Studio will not show up in SQL Server Management Studio Express. Visual Studio creates the database for you, but it does not attach it to the SQL Server instance that you are running in SQL Server Management Studio Express. SQL Server Express uses a special mode of operation called user instancing, which means it uses accounts such as NETWORK SERVICE and ASPNET to give it rights to connect to the SQL Server Express Instance. SQL Server Express Instancing also adds a 45-75MB memory overhead.

During the week I have been working with SQL Server 2005 Standard Edition and 2005 Express Edition for a website. I have specifically been looking at using SQL Server 2005 for Authentication as opposed to using the standard authentication that comes with ASP.NET when you configure an ASP.NET application in Visual Studio 2005. When you enable authentication for your website an SQL Server Express database (MDF file) is automatically created and the connection string in your web.config file is also changed to point to that SQL Server Express database.

Why is the standard authentication model not appropriate for all uses?

A couple of reasons I can think of:

1. If you wanted your development environment and your application environment to work together seamlessly then you might want to consider using SQL Server Authentication, or at least get both environments to use connection strings that are similiar or the same.
2. If you wanted to change or add to the fields provided by the regular membership model provided in ASP.NET and in particular have those fields stored in a proper database table.
3. If you wanted to use a Database Management System other than SQL Server. Perhaps you want to use MySQL or MS Access. You can also use Active Directory and XML as an authentication model.