Would you rather… ? (2)

Another simple choice this time 🙂

Assume you have 6 web services running off the same server (I\’m not a Web Service guy, so humour me a little).

4 of these Web Services tick over nicely, the 5th does not play nice with the others at times and the 6th is critical to line-of-business. When the 5th one decides not to play nice, it seriously affects all the other services on the box.

You have budget for a new server (this IS hypothetical after all).

Would you rather:

Protect the line-of-business web service by moving it onto the new server, leaving the 4 simple services and one bad boy ?

Or

Move the unfriendly web service to the new server, and leave the l-o-b service with the other 4 ?

Personally, option 2 gets my vote. If the unfriendly one decided to throw it\’s toys out of the pram, then at least it\’s the only one in the pram… It\’s kind of like moving the naughty kid to another class all by himself instead of taking smartypants out of school altogether.

Again, I\’d be interested in your comments !

Would you rather… ? (1)

OK, short one on partitioning this time 🙂

Kind of an odd situation, but we\’ve been having quite a serious discussion about this. Assume you want to partition by day (yes, 2day\”, we are quite mad), and you have to keep records in the partitioned table for a year.

Would you rather :

Create a partition function and scheme based on the date (2010-09-03), creating a new one whilst ageing the oldest each day

Or


Create a partition function and scheme using the DATEPART (DayOfYear) function, and reuse the partitions when next year comes around


And yes, this is in part related to the last post !


Because I\’m primarily a lazy DBA, I\’d go for option 2. It just seems a little more elegant – set it up once and let it roll. You get all the benefits of having the table partitioned without the headache of constantly maintaining it.


Comments are invited !

DBAs are from Mars, Developers are from Venus

Before you start, this is NOT a post about Multiple Active Result Sets, so don\’t get too excited (but don\’t go away yet either – hear me out).

A perennial challenge between the DBA and his/her development colleagues is that they speak a different language and some things are bound to get lost in translation. A good team will use many different strategies for coping with this, other people will constantly be at loggerheads. The DBA wants to use native tSQL, the developer C# CLR stored procedures. The DBA wants to use out-of-the-box functions in SSIS, the developer .net code. We\’ve all seen this.

Unfortunately, Microsoft don\’t make it any easier for teams to begin to understand each other, because they use differing constructs in different tools to accomplish the same thing.

I\’ll explain:

Situation arose today where it was needed to get the day number for a given date. Simple enough to do in tSQL – 

SELECT DATEPART (DayOfYear, \’2010-08-26\’);

Nice and simple. What really irritated was that to accomplish the same thing to add a derived column to a pipeline in SSIS, you\’ve got a different syntax:

DATEPART ( \”DayOfYear\”, [MyDateColumn])

(Note the requirement for the double quotes around the DATEPART specification)

Is this really a problem ? Not so much, but it certainly doesn\’t help when you\’re trying to convince a dyed-in-the-wool tSQL guy that they should really take a look at this pipeline ETL stuff called SSIS because it\’s really easy….

All I\’m pointing out here is that maybe things could be a little better integrated. SQL2005 introduced CLR and .net scripts inside stored procedures, but I don\’t see anything allowing tSQL to drive things the other way round.

Please could someone prove me wrong about this ? Is there such a \’killer app\’ that will let DBAs and Developers perhaps not talk the same language, but at least allow them to use a common thesaurus ? 

True. Or False. Whatever….

Most of the time, there\’s an understanding between DBAs and their developers. Sure, we all do things a little differently, but we get along fine.
However, when developers don\’t even agree amongst themselves… well, everyone\’s job gets a little harder.
Take a recent experience of mine, for instance. 
Reports were coming in thick and fast about database performance being sub-optimal (a post for another time), but whilst all around were doing heroic things keeping the app afloat, something caught my eye – a function that was being called a few thousand times a minute. Surely something a bit fishy there, I thought.
The function was defined as follows:
create function dbo.ConvertToBoolean
(
   @flagToConvert  char(1) = null
)
returns varchar(5)
as

begin
   return( select case
               when @flagToConvert in (\’Y\’, \’1\’, \’T\’) then \’true\’
               else \’false\’
           end)

Notwithstanding the irony in the name of the function and it\’s output, it\’s typical of what can happen when teams don\’t talk to each other. It would appear that to represent TRUE or FALSE conditions in the database, different people used different things, with 1, Y, and T all being used to express something that has one of two states (like Yes and No, On and Off, Salt and Pepper). This course of action requires something to standardise the meaning.
The moral of this particular story is that a few minutes agreeing how to handle Yes/No-type scenarios would have saved several thousand calls every minute to a function.
Oh, and if they\’d agreed to use a BIT column, that would have been ace, too.

SQL2008 Installation on Windows 2008 R2

 One issue, several \”solutions\”
I had the unbounded joy of being able to install SQL2008 on Windows 2008 R2 this week.
Joy ? Is that too strong a word ?
Yes, it is, because what should have taken a day ended up taking 3 !! During my trials, I came across one problem, but the solution was different in each case.
note: servers were sub-strength VMs – I recommend physical tin if you\’ve got the budget, or the beefiest VM you can muster on the quietest host you\’ve got.
In each case, SQL2008 Setup bailed out with an error:
Cannot invoke window control until it has been created.
Scenario 1 
Installing SQL 2008 RTM
Solution 1 
Take it very slowly. Very VERY slowly. At the end of the information gathering section of the install, it will actually create a Configuration.ini file to do the install itself. Once the first install is complete, you can use this file (and the Summary.txt file in the same folder) to do an automated install on other servers (From the Installation Center, choose \’Advanced\’, then \’Install from configuration file\’)
Scenario 2
Installing SQL2008 Service Pack 1
Solution 2
Boot the server. Try it again a couple of times before going to Solution 3.
 
Solution 3
Make sure that you have not tweaked your RTM install for language settings for the SQL Server and your installation account. If you have, untweak them before having another go – remember to restart SQL Server beforehand.
Solution 4
Wait for the error message. If your installing from the self-extracting exe, you can copy the extracted files from their extracted location before you press OK on the error box. You can then run the service pack setup from the unpacked files. Try it and you\’ll see what I mean.
Solution 5 
Murphy puts his coat on and leaves the building. True – the last time I got this error on the last server I was building, it failed 8 times and then just worked !

Hope this helps !

SQL2000 MSDE – Adding new users

I don\’t know about you, but I\’m constantly surprised that anyone could accomplish great things using SQL2000, but accomplish them they did. There\’s a wealth of \’lost\’ knowledge about the product – I say \’lost\’ because we now have DMVs to empirically show us what we previously just had to \’understand\’ and \’accept\’.
One such example of this \’lost\’ knowledge was presented to me recently – I was asked to add a user to an instance of SQL2000 – a simple enough task. The challenge was that the instance was MSDE (a freely distributed desktop SQLServer engine from the SQL2000 family), but given that there was no Management Console shipped with the product, you\’ve kind of got to go back to first principles and use the good old OSQL utility.
Say you want to add NT AUTHORITY\\System to the SysAdmins. MSDE will only listen for connection on Shared Memory (as far as I remember), so you\’ll have to use your favourite remote support tool to run these commands.
(Note: you can check which protocols MSDE is listening on by looking at the ERRORLOG file  in <>\\Program Files\\Microsoft SQL
Server\\MSSQL\\LOG\\. This is a plain text file, so notepad or your text editior of choice will let you read it.)
Start a command window.
Type \”OSQL -S<> -E\” and press return. MSDE will only respond when poked with the full server name. For OSQL command line help, type \”OSQL -?\” at the command prompt and press return.
The OSQL command prompt is kind of odd in that you get to input code one a line–by-line basis, but it won\’t execute anything until you press return to submit the query AND THEN \’GO\’ to execute it.
So, the completed set of commands to add a new user to MSDE would look like:
1>EXEC sp_grantlogin [NT Authority\\System]
2>GO
3>EXEC sp_addsrvrolemember \’NT Authority\\System\’, \’sysadmin\’
4>GO
5>SELECT name, sysadmin FROM syslogins
6>GO
7>QUIT
(or whatever user you needed to add).
Hope this helps (or jogs your memory a bit)

SQL Server and Active Directory – How did I get here ?

I\’ve recently been asked to determine which groups give a user access to a database.

 \”Simple.\” I hear you cry. \”Just look at the logins on the server, and compare those with the groups the user is in in Active Directory.\”

 But what if there\’s no match ? For us DBAs, it\’ll usually be  a group mapped to SysAdmin, so no challenges there. But what if it\’s someone else who has several levels of nested AD groups, only one of which actually grants SQL access way down the tree ?

 I found a few security DMVs to help out here, sys.login_token and sys.server_principals.

 I think it works like this:

 When you add a login to SQL Server FROM WINDOWS , it will check the name given against AD, get the SID associated with the group, and add that (as well as some other stuff) to sys.server_principals

 When you connect to SQLServer as a user, your request contains all of the names (and SIDs) that you\’re a member of. All of them. Even the nested ones.

 (You can try this out by connecting to any instance of SQL using Windows Authentication, and executing SELECT [name] FROM sys.login_token  – you\’ll get more rows back than groups that you\’re a direct member of in AD)

 SQL Server then has to match up any of the SIDs in your request with those in sys.server_principals. If it finds one, you\’re in – if not, you can\’t authenticate any further and you get access denied.

 So, going back to my original request, I got the end-user to execute the following query:

 SELECT DISTINCT lt.[name] FROM sys.login_token lt
       INNER JOIN sys.server_principals sp
               ON lt.[sid] = sp.[sid]

 They then mailed me the results of this, and I could immediately see that they were a member of 2 groups that had access to the required instance. Their issue was that they appeared to be entering dates in both British and US formats in different databases – a problem easily diagnosed to the language settings of the groups that they were in
 (one was British English, the other English, and I don\’t need to tell you what that means.), but to track that down using SQL and AD alone would have taken quite some time.

 If you need to find out which groups grant you access to SQL Server, use the script above. No special permissions are required to use these DMVs, so anyone with access to Management Studio can use it to find our which groups grant them access. If the user doesn\’t have access to Management Studio, then it\’s not a big job to create either a vbScript or exe that executes the statement above (but you\’re on your own here – unless you\’re really stuck, in which case I\’ll help out if I can)