Poll: Would you be interested in a "Microsoft SQL best practices 101 for schools" event?

+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
MIS Systems Thread, Microsoft SQL best practices 101 in Technical; I was recently at a Microsoft SQL user group which got me thing about schools and how we tackle training. ...
  1. #1

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,433
    Thank Post
    368
    Thanked 646 Times in 528 Posts
    Rep Power
    159

    Microsoft SQL best practices 101

    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?

  2. #2

    Join Date
    Mar 2008
    Location
    Colchester
    Posts
    194
    Thank Post
    34
    Thanked 40 Times in 34 Posts
    Rep Power
    24
    I would be interested so keep me updated

  3. #3
    TheScarfedOne's Avatar
    Join Date
    Apr 2007
    Location
    Plymouth, Devon
    Posts
    1,143
    Thank Post
    677
    Thanked 169 Times in 154 Posts
    Blog Entries
    78
    Rep Power
    85
    Good shout. Well worth looking In to, and I'm sure you will get interest

  4. #4

    Join Date
    Jul 2009
    Posts
    273
    Thank Post
    6
    Thanked 43 Times in 37 Posts
    Rep Power
    16
    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

  5. #5
    jdoyle's Avatar
    Join Date
    Mar 2008
    Location
    Republic of Swindon
    Posts
    394
    Thank Post
    70
    Thanked 49 Times in 42 Posts
    Rep Power
    57
    would be interested.

  6. #6
    Marshall_IT's Avatar
    Join Date
    Jul 2011
    Location
    Leeds
    Posts
    520
    Thank Post
    77
    Thanked 66 Times in 57 Posts
    Blog Entries
    1
    Rep Power
    19
    I'd be in! Always wanted to know more about SQL but the task is so large!

  7. #7
    JonThompson's Avatar
    Join Date
    Nov 2011
    Location
    Leicester
    Posts
    177
    Thank Post
    18
    Thanked 26 Times in 19 Posts
    Rep Power
    10
    Sounds interesting, keep me posted.

  8. #8

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,433
    Thank Post
    368
    Thanked 646 Times in 528 Posts
    Rep Power
    159
    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.

    Quote Originally Posted by andydis View Post
    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.

  9. #9

    Join Date
    Sep 2010
    Posts
    566
    Thank Post
    26
    Thanked 63 Times in 59 Posts
    Rep Power
    20
    this would interest me as well, be nice to be more comfortable with things like migration and disaster recovery etc

  10. #10

    Join Date
    Sep 2007
    Location
    Somerset
    Posts
    62
    Thank Post
    5
    Thanked 15 Times in 12 Posts
    Rep Power
    17
    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

  11. #11
    jonawd's Avatar
    Join Date
    Dec 2009
    Location
    Hayfield, High Peak
    Posts
    45
    Thank Post
    3
    Thanked 7 Times in 7 Posts
    Rep Power
    11
    Quote Originally Posted by andydis View Post
    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.

  12. #12
    Sam_Brown's Avatar
    Join Date
    Sep 2009
    Location
    Northampton
    Posts
    574
    Thank Post
    97
    Thanked 40 Times in 38 Posts
    Rep Power
    18
    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.

  13. #13

    Join Date
    Sep 2014
    Posts
    1
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by splattthecat View Post
    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.

  14. #14

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,433
    Thank Post
    368
    Thanked 646 Times in 528 Posts
    Rep Power
    159
    Quote Originally Posted by DBMSGuru View Post
    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 <module_name> BY CERTIFICATE <key_name>
    [{ 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
    Last edited by matt40k; 16th September 2014 at 11:23 PM.

  15. Thanks to matt40k from:

    Sam_Brown (17th September 2014)

  16. #15
    Sam_Brown's Avatar
    Join Date
    Sep 2009
    Location
    Northampton
    Posts
    574
    Thank Post
    97
    Thanked 40 Times in 38 Posts
    Rep Power
    18
    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.

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 2
    Last Post: 4th July 2014, 09:08 AM
  2. Replies: 2
    Last Post: 23rd February 2011, 03:43 PM
  3. SQL Server best practices
    By sidewinder in forum How do you do....it?
    Replies: 6
    Last Post: 21st May 2010, 03:53 PM
  4. DHCP best practice and advice again
    By tosca925 in forum Windows
    Replies: 11
    Last Post: 23rd November 2006, 08:49 PM
  5. Implementing best practice ICT management and support
    By FITS in forum Courses and Training
    Replies: 16
    Last Post: 8th September 2005, 02:24 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •