I\’ve had the rare luxury of being away from work this week, and boy did I need that !
Apart from the opportunity to reconnect with parts of life that had been pushed to the side somewhat (and hack down the wild Eucalyptus tree in the garden), I\’ve also had the chance to be a bit philosophical about SQL.
Does that make me sound like a basket-case ? (Don\’t answer that !)
Here\’s the nugget that I\’ve come up with for your consideration:
\”It depends.\”
As I\’m sure you know, this is the DBAs answer to a straight yes/no question, as in:
\”Would using a CTE and a join improve performance ?\”
\”It depends….\”
The philosophical part was wondering if there were any other walks of life where ambiguity was baked-in in the same way. So, I picked up my guitar, and went in search of enlightenment. And a cold one (I\’m on holiday, after all).
Little did I realise, but the answer was right in front of me. Literally.
Guitar chords are almost exactly like SQL, in that the chord you use depends on the sound you want to make at that point in the tune. And I\’m not talking about different chords (like a G or an A Minor), but variations on the SAME chord.
As evidence of this, open your favourite search engine. and look for the chord patterns for E/G# (or look at this site, if you need instant gratification).
E/G# sounds like quite a specific chord – it\’s an E, with an added G#. But there are over 50 ways of making that sound. And which one you use is predicated on several things:
Does it make the right sound ?
How hard is it to play ?
What is are the preceding and subsequent chords ?
I could go on about how to place your fingers on the fretboard to play the chords (barre, half-barre, double-jointed, etc), but I won\’t. I\’m sure you get the point.
So, when faced with the question about CTEs, it\’s perfectly valid to say \”It depends…\” as long as you\’ve got a list of things to take into consideration when making the choice.
For the record, I\’d look at what is actually being attempted, indexes, aggregations, table-valued parameters, CROSS APPLY,…..
Maybe I should just stick to \’Hallelujah\’ by Leonard Cohen. The version I\’m playing has an E/G#. Other versions don\’t. Which one is right ? I kind of think it depends….
Back soon !
Month: Feb 2011
As one door closes, another one opens.
As one door closes, another one opens.
Further to yesterday\’s post about remembering to tidy up database users, I thought I should mention something else.
It is entirely possible to create a user in a database that is associated with a Windows login WITHOUT adding the Windows login into master. And this DOES look a bit like a security hole in SQL Server (or possibly something that DBAs should be aware of, just in case).
Anyone who has \’dbo\’ rights to a database can create a user in that database, as we know, but they don\’t have rights to create logins in SQL Server. There is, however, a way of circumventing this.And here\’s how to do it….
In SMSS object explorer, choose any non-system database you like, and expand \’Security\’ and then \’Users\’. Bring up the dialog box to create a new user….
In the UserName field, pick a valid Windows user (perhaps someone you know). Or, if you\’re into keeping database users and logins distinct, type your own style of user name in the box.
OK, moving on to the LoginName field, here you would normally choose the […] box to pick and existing login to associate with your sparkly new database user.
You don\’t have to, though. You can just type in any valid Windows login. And that\’s the hole.
When you create the user, SQL will query Active Directory for the SID of the domain user that you entered. If it\’s valid, the new database user is created as normal, and the correct entry is added sysusers.
If you check the properties of the newly created user in SMSS, you\’ll see that there is a mapping between the UserName and the LoginName as normal (please excuse the crappy redaction – there\’s a recession on):
…even though there is no corresponding login in master (again, sorry for the redaction):
If you go back to the database you created your new user in, and SELECT * FROM sysusers, you\’ll see your Windows login in there, as well as the SID that SQL helpfully picked up from AD.
Going back to the post the other day, this now means that if the Windows user you used is a member of a group that has access to your server (but not the database you\’ve just \’created\’ them in), they will be able to access your database as well.
….which looks like a bit of a loophole to me…
So, I guess the morals of this story are \”Be careful who gets \’dbo\’ rights to any database\”, and \”Keep a close eye on all your database users AT ALL TIMES\”.
But I suppose you\’re already thinking of writing a script or policy that would remove any explicit database users who don\’t have a corresponding login. 🙂
Good luck with that….
Back soon !
Sharepoint – many data files make light work
Sharepoint – many data files make light work
I recently had the pleasure of attending a course on Sharepoint. Specifically, WSS 3.0 and MOSS 2007, if you must know, but that\’s not really important right now.
The important bit was this: The tutor asked the group, \”How many people are here because you company is thinking of implementing MOSS 2007 / 2010, and you\’d like some more information on how to do it ?\”.
Cue tumbleweed moment – in a group of some 14 delegated. No-one was there for that reason…..
The next question the tutor asked was, \”How many people are here because your company bought a point-solution from a 3rd party that uses Sharepoint, and now you\’ve got performance problems ?\”
What\’s the opposite of \’tumbleweed moment\’ ? I\’m not sure, but that\’s the response he got. There were a few rueful grins, a few nodded heads, and I think I heard someone say \”Thank you, <> !\”
I recently had the pleasure of implementing some of the performance tweaks gleaned from that course (with an exceptionally talented colleague), and thought I would share one of them with you – Splitting the ContentDBs onto multiple filegroups with multiple files.
Having plumped for moving all user-generated content into it\’s own 4-file filegroup, this is what we did:
The process is actually quite simple, but you have to interrupt the normal process flow when creating a Content Database. The default is to let Sharepoint create the database and add all the required objects. Here\’s how do it the DBA way….
First, you need to create the database for Sharepoint to attach to.
CREATE DATABASE [<>] ON PRIMARY
COLLATE Latin1_General_CI_AS_KS_WS
The important point here is the collation, so don\’t leave this line out – Sharepoint will tell you soon enough if you\’ve got it wrong.
And you\’ll also need to map the Sharepoint Admin account to \’dbo\’:
USE <<contentDBName>>;
GO
if exists (select name from master.sys.databases sd where name = N\'<>\’
and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [<>].dbo.sp_changedbowner @loginame=N\’DOMAIN\\YourSPAdminServiceAccount\’, @map=false;
GO
Next, ask the SP admin to attach the database to Sharepoint. This will create all the required database objects, including those you want to move. It doesn\’t take long, so resist the urge to go walkabout…
Once these have been done, the new database will need to be detached from Sharepoint (not SQL Server) so you can make your amendments. So ask the Admin to that. If it\’s you, ask yourself nicely.
Now you have exclusive use of the database, you can add a new filegroup and data files, and then move (in this case) all the user-generated content tables to the new filegroup, in this case called [UserData]. Please note that Microsoft recommend having multiple files in the PRIMARY filegroup and leaving it at that (but, hey, it\’s only a recommendation). Note that this part of the scripting process is in SQLCMD mode, so you\’ll need to change variables as appropriate, including those associated with SIZE , MAXSIZE and FILEGROWTH , to fit your needs:
— USE SQLCMD mode !
— and change the DataPath and LogPath accordingly
:SETVAR SPContentDBName <>
:SETVAR DataPath Drive:\\DataPath\\
:SETVAR LogPath Drive:\\LogPath\\
–Do not change anything below this line –>
——————————————————
USE [master];
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILEGROUP [UserData]\’);
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILE ( NAME = \’\’\’ + \’$(SPContentDBName)\’ + \’_001\’\’ , FILENAME = \’\’\’ + \’$(DataPath)\’ + \’$(SPContentDBName)\’ + \’_001.NDF\’\’ , SIZE = 102400KB , MAXSIZE = 12058624KB , FILEGROWTH = 102400KB ) TO FILEGROUP [UserData]\’);
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILE ( NAME = \’\’\’ + \’$(SPContentDBName)\’ + \’_002\’\’ , FILENAME = \’\’\’ + \’$(DataPath)\’ + \’$(SPContentDBName)\’ + \’_002.NDF\’\’ , SIZE = 102400KB , MAXSIZE = 12058624KB , FILEGROWTH = 102400KB ) TO FILEGROUP [UserData]\’);
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILE ( NAME = \’\’\’ + \’$(SPContentDBName)\’ + \’_003\’\’ , FILENAME = \’\’\’ + \’$(DataPath)\’ + \’$(SPContentDBName)\’ + \’_003.NDF\’\’ , SIZE = 102400KB , MAXSIZE = 12058624KB , FILEGROWTH = 102400KB ) TO FILEGROUP [UserData]\’);
EXEC (\’ALTER DATABASE \’ + \’$(SPContentDBName)\’ + \’ ADD FILE ( NAME = \’\’\’ + \’$(SPContentDBName)\’ + \’_004\’\’ , FILENAME = \’\’\’ + \’$(DataPath)\’ + \’$(SPContentDBName)\’ + \’_004.NDF\’\’ , SIZE = 102400KB , MAXSIZE = 12058624KB , FILEGROWTH = 102400KB ) TO FILEGROUP [UserData]\’);
USE $(SPContentDBName);
Next, you\’ll need to script out all the tables that you want to move to the new filegroup. I did prepare a script by way of demonstration, but it\’s a long one and a little off-putting. Suffice it to say that you need to script out dropping all the constraints on your target tables, dropping the tables you wish to move (in this case AllDocs, AllUserData, AllLists, AllLinks, AllDocVersions and AllDocStreams), and another script to recreate those tables which is modified to place them on the [UserData] filegroup, not [PRIMARY]. Don\’t worry, the process to create the database objects that\’s run in Sharepoint doesn\’t put any data in these tables.
Having done that, you can then ask the SP Admin to reattach the content database to Sharepoint. Any you can finally go walkabout, because what people put in Sharepoint isn\’t really up to you, all you want to do is make sure you\’ve got optimal performance. Isn\’t it ? 🙂
In case you\’re interested, along with the other performance changes we made, we calculated that over 200 man-hours per day were saved by not having to wait for Sharepoint to deliver content to the end-user. OK, so it\’s a second or so per page, but there\’s over 450GB of data in > 25 content databases running of a single WebApp and a few Web Front Ends, and over 4 million documents… and a user base of over 15K !
Back soon !
Please close the door behind you…
Please close the door behind you…
Sometimes, you come across a situation that you know you should know the answer to, but can\’t actually put your finger on. But something tells you that its fundamental knowledge. And something else then starts kicking you until you re-learn that fundamental knowledge.
Like the other day, when someone asked me \”If someone is only a member of a Windows group that has access to Database A, but they are defined as a User in Database B, can they access data in Database B ?\”
And the answer is \”yes, they can\” (but I\’m guessing that they probably shouldn\’t be able to). And I had trouble remembering that, since it\’s not something I get asked very often 🙂
Presumably, this happens in the development / early production stages, when nervous developers insist on having \’dbo\’ permissions to their baby database in their own right (rather than having a proper NT group for support), and then their login gets dropped when the service has been in for a while and is stable.
I\’m sure this happens quite a lot (and from a bit of digging, a bit more often than I originally would have thought), and those thoughtful guys at Microsoft even put a dialog box up when you delete a login advising you that you still need to delete any database user associated with the Windows login. What\’s not 100% clear from that message it that database permissions for the user will remain, and the end-user can still get access to the data even if their login does not explicitly exist on the SQL server.
Is this a bit of a security hole ? Perhaps it is. But then again (as the helpful message intimates), SQL Server cannot take arbitrary decisions about who owns what object in the database (or indeed whether any object should be removed). It\’s down to the DBA to make those decisions according to each circumstance, and with careful consultation with the end-users – you don\’t really want to drop a table that other people or services rely on day-to-day….
So, next time you get asked to drop a login for a Windows user or NT Group, remember to tidy up all the permissions in all the databases that the login had access to before you get your next latte / mocha / cold one. It\’ll save embarrasing questions about unauthorised data access later on, and it\’ll stop things kicking you in the head when you can\’t remember fundamental knowledge. Which is a win on both counts !
Certainly, from now on I\’ll be double checking that all permissions are revoked when a login is dropped. Especially on the HR and Financial systems.
Back soon !
The Modern Dataist 