Microsoft SQL best practices 101

[matt40k]

I was recently at a Microsoft SQL user group which got me thing about schools and how we tackle training. We often are driven by the suppliers when it comes to training, especially when it comes SIMS and MS-SQL server. This in general is ok, but when we look at secondary schools we find we aren't getting good value for money - we buy a Microsoft SQL Server standard license simply to remove the memory and cpu limit but it is very rare we look at what else we have as part of that license. The main two reasons this happens is supportability, if you leave, can the school realistic recruit someone to replace you, will it make it difficult or even impossible for your third party support team or even the supplier to support you. The other is the lack of resources to specialize in a particular area - there aren't enough hours in day to learn everything! Yet you still have to worry about being a DBA, a network engineer, a helpdesk analysis, a desktop engineer, a server engineer, a web designer and a million other things.

 

What I am looking at doing is getting one those lovely DBA to put together a crash course session in setting up SQL 2014 and doing a bit of DBA work - this won't be MIS specific, it'll be MS-SQL specific so IF you decide to become a DBA you have some actual knowledge of how things work in the real world rather then some tool a vendor made that only one small area of a wider community uses. With that said everything discussed won't result in your MIS system becoming unsupported. From your employers point of view, this will be away of getting the full potential of your investment, ie SIMS will go faster if you give me the day off to go.

 

So... interested? Shall I being nagging?

[xenonive]

I would be interested so keep me updated

[TheScarfedOne]

Good shout. Well worth looking In to, and I'm sure you will get interest

[andydis]

Always wanted to learn how to write SQL Querys ! Could of saved myself a lot of time exporting to excel if I could query SQL directly

 

Count me in

[jdoyle]

would be interested.

[Marshall_IT]

I'd be in! Always wanted to know more about SQL but the task is so large!

[JonThompson]

Sounds interesting, keep me posted.

[matt40k]

Cool, glad people are generally liking the idea. If you could also vote on the poll above as well please guys and girls. Would also be interesting in any reasons why not - other then database are boring etc.

 

Always wanted to learn how to write SQL Querys ! Could of saved myself a lot of time exporting to excel if I could query SQL directly

 

I was thinking of was more aimed at being followed on to Exam 70-462 - Administering Microsoft SQL Server 2012 Databases. Might have to have this as part of phase 2 - I don't like the idea of querying the SIMS database directly - we could look at building a data warehouse. This will at least give you a safe environment to play around with, just be a question of right way of doing it.

[SHimmer45]

this would interest me as well, be nice to be more comfortable with things like migration and disaster recovery etc

[splattthecat]

Good thinking Matt

 

Direct querying of the SIMS databases is not a good idea, but querying a copy of it can be useful. I have started looking at the Microsoft Virtual Academy which has several SQL courses - http://www.microsoftvirtualacademy.com/product-training/sql-server

[jonawd]

Always wanted to learn how to write SQL Querys ! Could of saved myself a lot of time exporting to excel if I could query SQL directly

 

There are some good video courses out there. As a business we use Pluralsight and I can recommend their 'Introduction to SQL' course as a starting point. Very useful if you just want the basics, and you'll be able to get it on the free trial too.

[Sam_Brown]

Would love this. Recently moved to SQL 2012 but no idea on how to optimize the server for best performance. A section on optimizing the server would be great.

[DBMSGuru]

Direct querying of the SIMS databases is not a good idea, but querying a copy of it can be useful.

 

Why not query the SIMS data directly?

 

A 'SELECT' statement is a read. The underlying data is not affected.

 

Querying a copy involves creating extra steps to maintain the copy, for no obvious benefit.

[matt40k]

Why not query the SIMS data directly?

 

 

A 'SELECT' statement is a read. The underlying data is not affected.

 

 

Querying a copy involves creating extra steps to maintain the copy, for no obvious benefit.

 

 

It's a difficult question to answer to what looks like a simple Q&A, the simple answer is, it's just bad a idea that can lead to a large bill. Now I appreciate this is a rubbish answer, personally I would take that as a challenge if it wasn't being said by someone I respect as my elder in terms of experience. The other answer I hear is, it will break if Capita (the supplier) changes the tables or views - they don't publish any documentation regarding data structures - so although this is a valid answer, it's not ideal - just confirm, if your going to commit to such customisation your school should be committing to hiring someone skilled in the art of database design and should be paying as such, it's not a question of if you can do it (and willing to do it for your salary), it's a question of if they can hire someone else to replace you for when you leave.

 

 

My main reason for not doing it is two things - auditing and security. So using the SA account is a bad idea, right? No one disagrees that running a "select" statement as the super administrator user is a bad idea? Or do I need to borrow a quote from my buddy Yoda -

"Everything! SA is the path to the dark side. Select leads to update. Update leads to delete. Delete leads to no data. I sense much permissions in you."

So lets explain a little how a user is created in SIMS .net - the "Data Manager" goes into System Manager and creates a user - this can either link to a Windows User or use SQL auth - then selects from a list of security groups. So lets pick SQL auth - that being the most common - so great we've got our user, created in the correct manner (for SIMS .net), we've been limited to say, pupil attendance data - we can't see staff bank details and we can't see pupils home details and so on. Happy days. Or is it?

 

 

OK, so the "data manager" has given you a username and password but you can't login in, you can via SIMS .net however, you change the password, still can't login via SQL Management Studio. OK, let me give you a few tips, the SQL user is prefixed with the database name - [db]_[user] - eg sims.mattsmith - makes sense. Still can't get in, password wrong. OK, let me help again, the password is salted (or whatever you call it) by the application, there is a routine to do it - lets say I tell you what a particular password is salted or tell you how to salt your password using the routine. Bingo your in. But wait, you've got no access. Why, because the signature has all the access and you need to get it to execute your command. Suddenly your in a world of pain and Yoda's words are being starting to fade into the background - sod it, lets just create a normal SQL user account and limit their access to particular tables\views - problem, how does your non-SQL educated data manager \ headteacher \ whoever, validate you've not just slipped in access to something you shouldn't? Well they can't really - even if your SIMS support (just remember ratios are like 100:1 - schools to support staff) does go into SQL Management studio and confirm - how do they know you haven't just changed it from selecting from all tables? Or your going to change it after they hang up the phone? They can't, least using the official route of setting permissions you get a bit of auditing - even if lot of it is hidden away in the depths of the database.

 

So yer, if you can figure out how Capita has setup signatures and use it then I tip my cap to you dear sir and ask you do share how you do it as I'd love to know, however I suspect the overhead of just using the API and doing data extracts, even using the reporting engine, would make it easier - even in terms of development .

 

Bit of guff on why they've done it (don't even comment on if it was cloud based, it wouldn't be needed):

Using Signatures on Procedures to Grant Permissions

A new—and, in my experience, little known—feature in SQL Server 2005 is the use of a signing certificate to sign stored procedures, assemblies, views, and functions. Administrators can assign permissions to the signing certificate itself and can then be sure that the permissions granted cannot be inadvertently modified by changing the stored procedure, assembly, and so on. Signed executable code cannot be changed without breaking the signature, which consequently invalidates any permissions granted to the signature—that is, unless the changed procedure, assembly, view, or function is re-signed with that signature.

 

The syntax for adding a signature is simple and includes the ability to add an existing signature (presumably created on another server) with a certificate containing only a public key and to specify a password if the certificate is password protected:

 

ADD SIGNATURE TO BY CERTIFICATE

[{ WITH PASSWORD = ‘password’ | WITH SIGNATURE=binary_signature}]

 

 

The need for signing code may seem rather exotic. In most production environments, administrators do not need to worry about someone other than administrators changing procedures. However, there are important scenarios where this could be very useful:

· A signature enables a low-privileged account to execute a specific, pre-approved task that the account would not otherwise have permissions to perform.

· ISVs may find signing certificates useful for ensuring that the stored procedures, assemblies, and so on, that are released with their product are not changed.

· Signatures solve a difficult problem with cross-database authentication.

 

The ability to deploy signed stored procedures, assemblies, functions, and views can allow an ISV to effectively prevent code tampering by a customer’s database administrator. This can help prevent local changes that would otherwise become support issues. Signed procedures are easy to include in any deployment scenario: the signatures can be backed up and restored, attached, or scripted as a blob with the ADD SIGNATURE Transact-SQL statement. Technically, a system administrator may be able to temporarily work around this by implementing his or her own certificate of the same name and signing all of the same procedures, but this would not be trivial. It would also be detectable by the ISV.

from: Cryptography in SQL Server

Edited September 16, 2014 by matt40k

[Sam_Brown]

I'll also add that by creating your own queries you're running the risk of bringing the SQL server grinding to a halt unless you're adapt in optimizing your queries.

[CAM]

I'll say yes to this! The idea of a secure data warehouse that exports nightly so I can do some proper high level stuff with SQL is up my street.

 

Time should probably be during holidays though? Good chance for all to attend just like conf.

[matt40k]

I'll also add that by creating your own queries you're running the risk of bringing the SQL server grinding to a halt unless you're adapt in optimizing your queries.

 

Yer, there is that too

 

I'll say yes to this! The idea of a secure data warehouse that exports nightly so I can do some proper high level stuff with SQL is up my street.

 

Time should probably be during holidays though? Good chance for all to attend just like conf.

 

Yer, not sure how logically to do it, few ideas I had was to tack it onto a conference - might give you a better chance of getting work to let you go conference which is a bonus. Will see, still an idea at the moment. Be good to get it done in time for (realistic rollout of) SQL 2014.

[CAM]

Maybe add it to the Edugeek conference at first? I'm sure non data managers could use it too.