SQL Server Aliases

When you develop applications that use databases you usually need to work with several database servers. i.e. Your local computer and a test server. If you are the only developer and have only one application that’s not a big problem, but if you are in a team  is quite annoying when someone messes up the connection strings to point to different servers depending on the task he wants to accomplish.

A common approach I’ve seen to deal with this issue is to modify the host file, so you can point to the right server without having to change all your connection strings. Unfortunately this does not work when the server you want to target is a SQL Express version or any other SQL Server with a named instance.

A better approach is to use SQL Server Aliases, with them you can indicate the name that is going to be used to access a SQL Server, no matter whether this server is your localhost or a remote server. To do that you only need to open the SQL Server Configuration Manager, expand the node of the SQL Native Client Configuration and go to the alias section as shown in the image below.

SQL Server Configuration Manager

Right click on the node, select New Alias and introduce the name of the alias and the connection details of the target server. The image below shows how we have configured our local version of SQL Express to be accessed with the name “TestDBServer”.

SQL Server Alias

When you introduce the connection details, be sure to verify they match with what is set in the protocols configuration of the target server.

That’s all, the new alias is set at machine level, so all the applications residing in the computer where the alias is configured can make use of it.

I hope this helps.

Window Server 2008 + TFS 2008 SP1 + SQL Server 2008 SP1 + SSL

We have been using TFS at Payvision for many years, but these days I've been playing again with Team Foundation Server 2008 ... let's say for fun. I wanted to install from scratch TFS 2008 single server installation, on top of Windows Server 2008 and SQL Server 2008 SP1 and, configure it to be accessed via SSL, since I got lot of fun I decided to write down all the things I needed to do to have all up and running, basically because I doubt I will be able to remember all if I need to do it again. So, here is the list with the steps:

- Step 1: Get the latest version of the installation guide and read it before start installing.

- Step 2: Read the installation guide.

- Step 3: I really mean it, RTFM.

- Step 4: Follow all the steps described in the guide to create accounts, open ports, install prerequisites...

- Step 5: If you followed all the steps, you are now probably wondering why after do the "Integrated Installation of Team Foundation Server and Service Pack 1" the installer stills complaining about not having a supported SQL Server version for TFS. If this is the case, follow this workaround. If it is not the case, it means you didn't follow the recommendation of check for updates after install SQL Server and the SQL Server 2008 SP1 is not installed yet.

- Step 6: After complete the installation of TFS. Install the Team Explorer and check all works as expected: Create a project, bug, work items, check in, check out, reports, team site ...

- Step 7: Not a real step but I always suggest to install it in the same server: the TFS Administration Tool and the Team Foundation Power Tools. They became good friends of mine long time ago.

- Step 8: What? Really? So, when you open Team Explorer you see a red cross in the Reports, but the reports are working when you access them directly and in addition it does not happen on all computers...don't panic, it is something with an easy solution, you need to be sure Visual Studio SP1 is installed after Team Explorer.

- Step 9: After SP1 is installed you can already access the reports from Team Explorer, you can open the Reports but ... the Event Viewer shows a nice error saying that "OLE DB error: OLE DB or ODBC error: Cannot open database "TfsWarehouse" requested by the login. The login failed.; 42000.". You need to assign the role TfsWarehouseDataReader to the user Network Service, I found the solution here and also the solution to problem 8, sorry step 8, it was pity I didn't see problem 9 before problem 8 it would have saved me some time.

- Step 10: We also use scrum so I installed the template made by Conchango Scrum for Team System. Yeah you know why I add this point here, it doesn't work by default with SQL Server 2008, but we have the solution here thanks to Stuart Preston.

- Step 11: Great all is working now.  It's time to break it again,  it's time to configure the FQDN and SSL. To do it, you should check this guide from the Team Foundation Server global support team, probably one of the best docs I read about it, but we are not going to follow it exactly so I give hints on what/why I did different. Some are quite evident others no.

- Step 12: Before you configure the SharePoint Central Admin to have SSL required in IIS, it is important that you Configure Alternate Access Mappings in SharePoint. If you do the way around the mappings will be changed and you will not be able to open SharePoint 3.0 Central Administration.

- Step 13: When you do the step to configure the connections with TfsAdminUtil be sure to use the full url for the option /ReportServer because there is a bug that configures a wrong URL, therefore instead of   "/ReportServer:https://www.site.com:port/ReportServer" you should do "/ReportServer:https://www.site.com:port/ReportServer/ReportService.asmx". Sigh, this one really drove me crazy I wasted more time trying to figure out why all was failing than all the other steps together. So, I highly suggest you run "TfsAdminUtil ConfigureConnections /view" to get the list of urls, double check they are configured as expected and you can access them with your web browser.

- Step 14: When you configure the Report Server with "Reporting Services Configuration Manager", instead of just adding the SSL configuration I removed also the standard http one. Even if I was able to access the report site via SSL the internal links were still pointing to the HTTP version and as I said before, I just wanted to have all via SSL.

- Step 15: You need to give permissions to the machine keys in order the user network service can access the certificate we use for SSL encryption. To do it go to \ProgramData\Microsoft\Crypto\RSA, add the user NETWORK SERVICE, and assign the permissions read and write. Be very careful with this step you don't want to mess up anything here.

- Step 16: All seems to be done now, check all works as expected: Create a project, bug, work items, check in, check out, reports, team site ... and yes all looks good...but we are not done yet, "Team Project Administration" of the Conchango template still does not work, when I try to access the page it gives me a 403 error...sigh...but I found a non related post with my error that gave me the hint to solve it. To make it work I just needed to update the registry key with the new SSL url for TFS.

- Step 17: Have fun with your fresh installation of TFS.

This is pretty much all the (extra) steps you need to know for the errors you might face during the installation. I hope this post can safe some of your time if you do something similar.

NDepend

People who knows me also knows that I'm a big fan of static analysis tools. Thanks to Patrick Smacchia I had the chance to test NDepend, probably the most complete tool for static code analysis. It is not worthy to enumerate all the features the tool offers, because the documentation and the web site already do a wonderful job with it. Just to mention some of them with NDepend you will be able to measure up to 82 predefined metrics to manage things like coupling, instability, abstractness, dependencies, naming conventions ...

Aside all the different, some of them unique, metrics the tool incorporates there are many features that make of NDepend a great tool like:

  • Possibility to incorporate new custom rules or modify the existing ones via the Code Query Language, a language with a syntax similar to any SQL language that allows you writing queries against your code structure. CQL makes so easy to create custom rules that there is no fair comparison with FxCop or StyleCop.
  • Possibility to introduce CQL constraints in the code that will allow you building really creative rules embedded in your code.
  • MSBuild and NAnt tasks to integrate with your TFS or CruiseControl.
  • Integration with Visual Studio and Reflector.
  • Different graphs that allow you seeing your code in a visual manner.

It is also remarkable the job Patrick has done by creating several demos on how to perform different operations with NDepend to take more profit of it. You can find them online, together with the description of the different metrics and links to technical articles to his blog. It is just pity that you cannot link easier to it from the tool itself. It would be fantastic if in next versions you could just right click in the failing rule and provide an option to reach the help.

Regardless I think the tool is great I must say that I didn't like some of the naming conventions rules do not follow the guidelines provided by MSDN and one of the greatest books about the subject: Framework Design Guidelines. I also found some of the metrics a little bit arbitrary, like code should have more than 20% of comments, I completely understand you need to put a value to break a rule, but I would love to see more detailed information on how he arrived to the conclusion that some rules should have a specific value instead of another one. Of course, this is part of his .NET knowledge and programming experience. Anyway, this shouldn't be a limitation because if for any reason you do not agree with a rule, just modify it, disable it ... you can do it if you have good reasons for it!!

I like to see the tool not only as a bunch of rules, but as the infrastructure that will allow to build and manage your corporate standards.

I want to finish the post saying that I like NDepend, I think this is one of the must-have developer tools, so if you haven't tried yet go the website and get an evaluation copy to check it.

Presenting at large events such as TechEd

INETA APAC is organizing a series of best practices webcasts of interest to the community. The webcasts apply to all members of the community, leaders, user group members and MVP’s, who might not yet be at the stage where they speak at large events. The topic would be of interest to all, and would help more people to aspire to be speakers, and get an understanding of what is involved in being a good speaker.

Here are the details of the first webcast:

Best Practices Webcast: Presenting at large events such as TechEd

Session abstract: Presenting at large events such as TechEd involves a lot of preparation. Too many speakers leave far too much to chance. Doing a good job does not happen by accident. In this session, Greg will share experience and tips related to the process of getting selected, preparing content and delivering that content. Many of the tips can also be applied to improving the delivery of technical material in classroom or user group situations.

Presenter Bio: Greg Low is an internationally recognized consultant, developer and trainer. He is the country lead for Solid Quality, a SQL Server MVP and one of only three Microsoft Regional Directors for Australia. Greg also hosts the popular SQL Down Under podcast (www.sqldownunder.com), is a board member of PASS (the Professional Association for SQL Server) and an author with Microsoft Press and Rational Press. He regularly speaks at large events around the world.

INETA APAC invites you to select a convenient time of your choice for this webcast, fill the survey(takes less than 3 minutes) and we will notify you of the final survey results and the exact webcast timing.
http://www.surveymonkey.com/s.aspx?sm=AdhgSxdrRqvyUP9KZQRWpA_3d_3d
Please note that Survey closing date is : May 4, 2009

Static Code Analysis

These days I've been updating the CA settings on some old projects, I wanted to modify the configuration of certain rules that were removed with the shipping of Visual Studio 2008. You can obtain detailed information about what rules are shipped with each version of CA in this post of the FxCop blog.

As you can see in the post, some of the rules were removed because they do not apply anymore or because they were too noisy compared with the benefit introduced. One of the rules that make feel more upset when I knew it was removed was "CA1818 - Do not concatenate strings inside loops". This rule threw an error (I will continue in my imaginary world where everybody sets warnings as errors in production code) with code like:

string s = string.Empty;
for (int i = 0; i < 5; i++)
{
   s += i.ToString();
}

One of the first things you learn in .NET is about the immutability of the strings, you can find lot of literature talking about how to handle properly strings. Even Improving .NET Application Performance and Scalability, one of the best papers I've seen about .NET performance, makes an explicit reference to do not concatenate strings when the number of concatenations is unknown.

So, today that I've been working again with the rule, I had the curiosity (hope) to verify the rule was not removed because it was too noisy but because the CLR was improved to avoid the issue. I know this is again living in my imaginary world, but I'm a bit stubborn...what I've done is to compile a console project with the code above with 2.0 (VS 2005) and 3.5 (VS 2008), first one fires the error, second one doesn't. First step has been to look for differences in the IL generated, both cases have:

   1:  .entrypoint
   2:  // Code size       39 (0x27)
   3:  .maxstack  2
   4:  .locals init ([0] string s,
   5:         [1] int32 i)
   6:  IL_0000:  ldsfld   string [mscorlib]System.String::Empty
   7:  IL_0005:  stloc.0
   8:  IL_0006:  ldc.i4.0
   9:  IL_0007:  stloc.1
  10:  IL_0008:  br.s     IL_001c
  11:  IL_000a:  ldloc.0
  12:  IL_000b:  ldloca.s i
  13:  IL_000d:  call     instance string 
  14:                     [mscorlib]System.Int32::ToString()
  15:  IL_0012:  call     string [mscorlib]System.String::Concat(string,
  16:                                                            string)
  17:  IL_0017:  stloc.0
  18:  IL_0018:  ldloc.1
  19:  IL_0019:  ldc.i4.1
  20:  IL_001a:  add
  21:  IL_001b:  stloc.1
  22:  IL_001c:  ldloc.1
  23:  IL_001d:  ldc.i4.5
  24:  IL_001e:  blt.s    IL_000a
  25:  IL_0020:  call     valuetype [mscorlib]System.ConsoleKeyInfo 
  26:                     [mscorlib]System.Console::ReadKey()
  27:  IL_0025:  pop
  28:  IL_0026:  ret

No improvements on the compiler side. Next step has been to verify the native code generated, for that I used the command !u at WinDbg (in the post Inline Methods you can see how to obtain the native code after the method is jitted). The code for both projects looks like (note that some memory addresses will be different):

   1:  CA1818.Program.Main(System.String[])
   2:  Begin 009d0070, size 55
   3:  009d0070 55           push ebp
   4:  009d0071 8bec         mov  ebp,esp
   5:  009d0073 57           push edi
   6:  009d0074 56           push esi
   7:  009d0075 83ec10       sub  esp,10h
   8:  009d0078 33c0         xor  eax,eax
   9:  009d007a 8945f4       mov  dword ptr [ebp-0Ch],eax
  10:  009d007d 8b3d2c10d602 mov  edi,dword ptr ds:[2D6102Ch] ("")
  11:  009d0083 33d2         xor  edx,edx
  12:  009d0085 8955f4       mov  dword ptr [ebp-0Ch],edx
  13:  009d0088 837df405     cmp  dword ptr [ebp-0Ch],5
  14:  009d008c 7d26         jge  009d00b4
  15:  009d008e 8b75f4       mov  esi,dword ptr [ebp-0Ch]
  16:  009d0091 e80a0cca6f   call mscorlib_ni+0x220ca0 (70670ca0) 
  17:  009d0096 50           push eax
  18:  009d0097 8bce         mov  ecx,esi
  19:  009d0099 33d2         xor  edx,edx
  20:  009d009b e8bad00971   call 
  21:                        mscorwks!LogHelp_TerminateOnAssert+0xb82 
  22:                        (71a6d15a) 
  23:  009d00a0 8bd0         mov  edx,eax
  24:  009d00a2 8bcf         mov  ecx,edi
  25:  009d00a4 e8a7ebc36f   call mscorlib_ni+0x1bec50 (7060ec50)
  26:  009d00a9 8bf8         mov  edi,eax
  27:  009d00ab ff45f4       inc  dword ptr [ebp-0Ch]
  28:  009d00ae 837df405     cmp  dword ptr [ebp-0Ch],5
  29:  009d00b2 7cda         jl   009d008e
  30:  009d00b4 8d4de8       lea  ecx,[ebp-18h]
  31:  009d00b7 33d2         xor  edx,edx
  32:  009d00b9 e8f6381570   call mscorlib_ni+0x6d39b4 (70b239b4)
  33:  009d00be 8d65f8       lea  esp,[ebp-8]
  34:  009d00c1 5e           pop  esi
  35:  009d00c2 5f           pop  edi
  36:  009d00c3 5d           pop  ebp
  37:  009d00c4 c3           ret

We see there are no improvements either on the jitter side. The next to verify is if the strings are being discarded on each iteration creating a new one or not. To do that I used some good profilers that are on the market but in the end I decided to show how I did it with WinDbg because anybody can download it for free.

With !DumpHeap -type System.String we can see all the string instances of our application. This returns a list with the memory address of the string instances, to view the contents of the object we just need to do a !do (dump object) of the address we want to check. So, just taking some samples from the instances with higher memory addresses we can already see the next:

   1:  0:003> !do 01c83ae0 
   2:  Name: System.String
   3:  MethodTable: 706c0a00
   4:  EEClass: 7047d64c
   5:  Size: 24(0x18) bytes
   6:  String: 012
   7:   
   8:  0:003> !do 01c83af8 
   9:  Name: System.String
  10:  MethodTable: 706c0a00
  11:  EEClass: 7047d64c
  12:  Size: 20(0x14) bytes
  13:  String: 3
  14:   
  15:  0:003> !do 01c83b0c
  16:  Name: System.String
  17:  MethodTable: 706c0a00
  18:  EEClass: 7047d64c
  19:  Size: 26(0x1a) bytes
  20:  String: 0123
  21:   
  22:  0:003> !do 01c83b28
  23:  Name: System.String
  24:  MethodTable: 706c0a00
  25:  EEClass: 7047d64c
  26:  Size: 20(0x14) bytes
  27:  String: 4
  28:   
  29:  0:003> !do 01c83b3c 
  30:  Name: System.String
  31:  MethodTable: 706c0a00
  32:  EEClass: 7047d64c
  33:  Size: 28(0x1c) bytes
  34:  String: 01234

From the results above we can see how on each iteration we have two strings, the resulting of i.ToString() and the resulting of concatenation.

Conclusion, we still creating new strings and discarding the previous version for GC on each manipulation of the string. Therefore, I suppose the rule was removed just because it was too noisy.

Once I stopped playing with WinDbg I come back to the earth to say what I wanted to say from the beginning. It's pity to see that a performance issue that has been repeated so many times, now is ignored just because the rule is too noisy. I know lot of people could argue that using the StringBuilder we also discard old versions of strings when the size of the string becomes bigger than the buffer, but still better than discarding all modifications.

I don't understand why a good string handling was that important before and now is just ignored by the main CA tool used by .NET developers.