SSMS Review

1) The tabs labels are useless.  They are not wide enough to show anything exciting, for example, I see “server01.databa_…Query1.sql”  The tab label should be wider or we should just have the file name.

2) F5 doesn’t always behave, sometimes it start “Open Project” instead of execute.  Fortunately ctrl-E works the same as F5 did in query analyzer.

3) Object explorer is just about useless because it takes 50% of the screen.  25% of the space object explorer now takes up is white space.  The object explorer heirarchy is 6 folders deep, so with all folders open, this becomes full page of diagram.

4) I can’t sort views by name in the object explorer.

5)  I have to register servers twice, once in the “Register Servers” section and once in the “Object Explorer” section

Front Page Extentions: What are they?

0) vti_XXX directories are evidence of Front Page extensions.  VTI was the company who intially developed Front Page Extensions. 

1) They are an API of server side code components.  This is quickly becoming irrelevant unless you are actually using Front Page to write your website and are using Front Page the way it was ‘meant to be used’ instead of what I’d do, which would be to ignore all the FP features.

2) It is a primitive type of source control or an FTP accellerator.  When opening a website by FPE, you get a local copy, then when you upload the pages, FP overwrites what you changed.  There is some sort of detection of things changing on the server, because Visual Studio will tell (and optionally automatically reload) any changes it detects.  There is no merge, the person who saves their file first, wins.  There is no visible history or tool for comparing what you just did to what is on the server.  Since FPE write to the server only what you changed, it is faster than FTP’ing the entire website.

3) When you check a FPE website into VSS, VS warns that FPE aren’t going to be used anymore.

JScript.NET + SMO

This could be a cool combination– Javascript, that language we all so painfully learned just before we all realized that Javascript code can only be expected to run on one browser, plus SMO, the object oriented way to talk to SQL server. It beats some of the options, such as batch or sqlcmd. Well I don’t know about enough about sqlcmd. Or JScript.NET.

Jscript.net works at the file level, although files can now “import” a .NET assembly.

With the help of “external commands” and a customized tool bar, I quickly was able to get away from the command line and do most things in Visual Studio 2005.
For a Jscript.NET program, the .exe is the project. This is important to understand. If that .exe has a .pdb file (debugging symbols) in the same directory and if you open the original source code page for the .exe, you can debug that file with breakpoints, step through and all the other things you get when debugging something like VB.NET.

Not being able to do that, in my opinion was javascript greatest weakness. Just writing a bunch of alert(x) commands doesn’t cut it for a debugging experience.

Unfortunately, the greatest weakness of JScript.NET is that it doesn’t have intellisense. Might as well use C# if you are using more than a few objects and properties.

If the code snippet manager is hidden…

The code snippet manager sometimes fails to show up on the “Tools” menu of Visual Studio 2005.  If it does, go to Tools/Customize, select the tools category, find “Snippet Manager” drag out of dialog window and up into the menu.  The operation is a bit like the MS-Access menu designer if you’ve ever used that.

Random learning links for VSS

A good introductory article on using Visual Source Safe 6.0.
Someone not happy with the thought of Visual Source Safe.  We get what we pay for. VSS is free with MSDN.  CVS is free to everyone. I think the Visual Studio team has made up for some of the worst VSS usability issues. I wouldn’t think of using VSS with anything less than VS 2005.  Code repositories that use SQL as the back end would be better, but as far as I can tell, they are all commercial and/or expensive.

Red Gate SQL Tools

Red Gate SQL Compare: It does a better job than Apex SQLDiff, although I liked the UI of Apex SQLDiff better. In particular, Red Gate is more likely to generate a data preserving change script that will run without modification, however, it will choke on hard things like changing the data type of a column in a primary key.

Red Gate SQL Data Compare: On large databases, it is a bit slow. No matter how you write this code, the computer needs to read in 20GB of data at least twice, and that is a lot of IO and network chatter. Expect to find yourself resorting to alternative solutions, like replication, table copies, etc. The typical use case would be refreshing the data on a test or development server without blowing away the new stored procedures, indexes, etc on the development server.

Red Gate DTS Compare: I got a ‘Library not loaded’ error. Couldn’t test it. (update: it was DSO objects becoming unregisterd, had to reregister with regsrv32)

Red Gate SQL Packager: This is pretty cool, although I’m not sure when I’d use it. The typical use case would be selling an packaged application that depends on a SQL database and you don’t expect an SQL expert to be at the client’s location to set up the database.

What developers would like to know about their new job…

- Time tracking, productivity tracking
- Email respone time
- Radio, head phones, closed door rules

- What is explicity forbidden (cell phones, etc)
- Clearance strategy
- Security policies
- Rules on installing software

- CMM compliance & competing policies & process methodologies
- Tools available, tools request (write/buy policies, if any)
–Code Repository?
–Unit testing tools?
–IDE?
–Documentation templates?
–Coding Standards
—-Variable name conventions
—-Commenting conventions

# Do you use source control?
# Can you make a build in one step?
# Do you make daily builds?
# Do you have a bug database?
# Do you fix bugs before writing new code?
# Do you have an up-to-date schedule?
# Do you have a spec?
# Do programmers have quiet working conditions?
# Do you use the best tools money can buy?
# Do you have testers?
# Do new candidates write code during their interview?
# Do you do hallway usability testing?

I wish this was in BOL (SQL2000)

Choosing Net Libraries For SQL-Server 2000

Fastest: TCP/IP
Can talk to multiple instances on same machine: Everything but Multiprotocol, AppleTalk, and Banyan Vines Net Libraries
Security reasons:
Named Pipes and Multiprotocol Net Libraries, will only work over trusted connection, i.e. on a windows network.
Shared memory. No network access.
Support Delegation/Impersonation: TCP/IP
Supports Encryptions: Multiprotocol Net Library and use the “Force encryption” check box, or use SSL (although SSL takes a lot more effort) Encryption also will require putting “;Encrypt=YES” or “Use Encryption for Data=True” into your connection string, depending.

Best practice:
Use shared memory for random SQL servers (eg. MS-Access adp projects and the like)
Use TCP/IP + firewall for intranet applications.
Use Multiprotocol for secure intranet applications, unless you need to support impersonation.
Use TCP/IP + SSL for secure connections through the firewall.

SQL DBA Check List- Draft

I’m in the mood for lists.  Today’s list is the light touch DBA to-do list, all the stuff a DBA can do without knowing what is in the database or having much time to co-ordinate with the application developers

Admin Stuff
- Check for weird server and database settings.  Variances from default should have a plausible reason.
- Check for absence of  a clever server/database setting. Stuff like turning on ‘check for torn pages’ should be a no-brainer.
- Check growth rate of database. Resizing is expensive.
- Check to see how transaction logs are being handled.  Uncontrolled transaction log growth causes headaches, often when you don’t give a damn about the transaction log.
- Check for a maintenance plan.  Make it clean everything up even if it is just a development/test box precisely because it is just a development test box.  Do you you want to trouble shoot issues related to re-indexing on a test box?
- What is the reliability/availability needs? Pick a backup restore strategy.
- Set up notification emails.  You look silly when a scheduled task has been failing for the last 4 months and you’ve just found out.

Security Stuff
- Check for blank SA password (check all servers on network while you are at it for blank sa)
- Check for user id = password

Data Modeling Stuff
- Does everything have a meaningless numeric primary key?
- Are alternate natural PKs set up to be unique?

Performance Stuff
- Are select column indexed?
- Are foreign keys indexed?
- Are you using lots of disks? The more you can split the various files that SQL uses among different disks the faster SQL goes. This is all transparent to the developers.