Author: Haidong Ji

SQLCMD will be fun

SQLCMD is a command line utility for Sql Server 2005. Like osql and isql, it can be used interactively. It can also be used as a scripting tool to automate a lot of routine tasks. It is the later part that I find exciting and fun. (By the way, isql is not available in Sql Server 2005. osql is there, but probably won’t be there in the next release of Sql Server, shall I say, Sql Server 2010;)

One big improvement I see SQLCMD over osql is the ability to set your own customized variables. This may seem trival but actually provides great flexibility and versatility in doing your day to day tasks.

For example, in a typical development cycle, you will be asked to promote sql code from dev environment to QA, Cert, and/or Prod. With the setvar command, all these can be done in a single script, if you define your environment variables properly.

I will write more on this in future posts.

In other news, I was able to finish silent / command line install of Sql Server 2005, but I had to choose to install ALL components. I will see if I can find a good sample of ADDLOCAL and test that. I will blog that when I make progress.

SQL Server Management Studio Reports — Potential Management and Permission Issues

By now, you probably know that in SQL Server 2005, a new tool that replaces SQL Server Enterprise Manager and SQL Server Query Analyzer is SQL Server Management Studio (SSMS). It has a lot of good stuff in it, one of them being the reports.

SSMS reports provide a snapshot of your server status and database status, like cpu, memory, io, cursor usage, schema changes, index usage, user statistics, etc. All these are great information available at your fingertips. MVP and PASS director Randy Dyess has a great intro post on them recently.

When I saw the demos at PASS of those reports, I was really impressed and excited about them. They definitely provide the DBAs with tons of information to proactively monitor and manage db server and databases. One thing bugged me a little during the sessions is execution permission on those reports. I could be wrong, but I believe most of the data for those reports come from DMVs or SQL Server 2005’s internal resource database. So, if you make SSMS available to developers or some regular database users, the reports will be available to them, but they cannot use them, unless they have system privileges.

To prove my theory, I created a login for AdventureWorks, a replacement for Northwind and Pubs. I gave this login db_reader and db_writer rights, a common practice for a regular user. I then logged into this database with this login, using SSMS. Sure enough, under this regular user’s login, when I clicked on any server reports, I got a permission error. I haven’t checked database status reports yet, because I’ve been playing with silent / command line install and just removed all my installations. I will test that when I get it installed again.

I think this opens a can of worms for your average DBA. Since SSMS is the only client tool available for SQL Server 2005, you don’t have a choice of whether to install this for your developers and average users. (We will have to see if Query Analyzer works with SQL Server 2005, if yes, install that for them, but that is really lame). It’s like showing a candy jar full of goodies to your child but won’t allow him to open it. The average user will constantly bug you on this and make you feel denfensive and waste your valuable time while doing it.

I then thought, geez, maybe the available reports are stored in the registry or a configuration file. If that is the case, I can then delete them after SSMS is installed on a client machine. So I did a string search in the registry, nothing came up. I then did another string search using MSN tool bar on the hard drive. Nothing came up either.

I am all for transparency and information sharing, but it seems to me that if you don’t want people to use certain feature, don’t let them know they exist in the first place. It will be great if the available reports are configurable.

What do you think?

Silent install / Command line install of SQL Server 2005 Part 3

I blogged about my 2 failed attempts of silent / command line install of SQL Server 2005 September CTP. So I filed a bug here on MSDN. I am impressed that Microsoft responded very quickly, saying this is not a bug. Thanks to whoever closed the bug. That was a real quick response:) I will redo my test asap to find out.

The reason that it is not a bug, according to the report, is that, Quote:

The message you’re seeing in the Summary.txt file is not why setup is failing. Your missing parameters for the services. From the template.ini:
;——————————————————————–
; The services for SQL Server and Analysis Server are set auto start. To use the *ACCOUNT settings
; make sure to specify the DOMAIN, e.g. SQLACCOUNT=DOMAINNAME\ACCOUNT
; NOTE: When installing SQL_Engine 3 accounts are REQUIRED: SQLACCOUNT, AGTACCOUNT and SQLBROWSERACCOUNT.

; SQLACCOUNT Examples:
; SQLACCOUNT=<domain\user>
; SQLACCOUNT=”NT AUTHORITY\SYSTEM”
; SQLACCOUNT=”NT AUTHORITY\NETWORK SERVICE”
; SQLACCOUNT=”NT AUTHORITY\LOCAL SERVICE”

; Note: To install localized versions of SQL Server, refer to the Localized Service Names table in the SQL Server Books Online topic “Setting Up Windows Service Accounts”

; Note that if SQLBrowser is already installed, SQLBROWSERACCOUNT and SQLBROWSERPASSWORD are ignored.

SQLBROWSERACCOUNT=
SQLBROWSERPASSWORD=

SQLACCOUNT=
SQLPASSWORD=

AGTACCOUNT=
AGTPASSWORD=

End Quote

Now I have 3 suggestions for the Installation help file, located at D:\SQL Server x86\Servers\Setup\help\1033\setupsql9.chm:

1. In the section titled How to: Install SQL Server 2005 from the Command Prompt, it has this to say about accounts:

[SQLACCOUNT], [SQLPASSWORD], [AGTACCOUNT], [AGTPASSWORD], [ASACCOUNT], [ASPASSWORD], [RSACCOUNT], and [RSPASSWORD]
This is information for the Service Accounts dialog box. If these parameters are not specified, Setup defaults to the LocalSystem account.

This is where it thrown me off. I intended to use LocalSystem account for startup, that is why I didn’t specify them in my config .ini file in the first place. This part of the documentation needs to be corrected, if the person who closed the bug is correct;

2. In the first post, I suggested that it would be helpful to have a template.ini file as a starting point. It turned out the setup DVD does have them, but it is nowhere mentioned in the section help file mentioned above. One would think that would be a logical place to mention them. By the way, I did visually check if I could found them on the DVD, however, I checked the wrong folder (D:\SQL Server x86\Servers\Setup). There are actually 2 copies of template.ini and they appear identical. They are located at D:\SQL Server x86\Servers and D:\SQL Server x86\Tools, respectively.

3. In the same section of the file, one command line example given was:

Setup.EXE /settings C:/set.ini /qn

This is incorrect. It should be a back slash \ after C: Unless, of course, this is Microsoft’s first step of porting SQL Server to *nix;)

All right, I will start over again. I will report back my results in a separate post;)

Vista, SQL Server 2005, AJAX, and Web 2.0

Ken’s question made me think about Vista’s impact. Virtually all Microsoft’s products depend on the win32 API, so I wonder how much of change can Vista make? In other words, if Vista breaks win32 API calls, the cascading effects it has on other products like SQL Server, Exchange, BizTalk, Office, etc., would be tremendous. It may not come down to rewrite everything, but suffice it to say that the changes and additional development work and QA regression testing effort will be significant. So is it safe to say that Vista would be an evolutionary process of Windows, not a revoluntionary change that some people are led to believe? It will be interesting to see what unfolds, as far as Vista’s impact on server applications is concerned.

To extend the thinking a little further, once the foundation is set, the rest of stuff has to build on top of it and you are limited in ways to innovate and extend your stuff. Minor modification on the framework can be done but it is going to be costly. I guess that kind of explains why AJAX and Web 2.0 is all the rage recently. The idea is that you will do most of stuff through a web interface, no custom installation and setup is needed on your workstation. Google is a pioneer in this arena. The famous AJAX/Web 2.0 products are Gmail, Google Maps. Yahoo is not too far behind. Flickr and Yahoo Mail beta are good examples.

It will be interesting to see what comes out of the recent alliance between Sun and Google. If Google can deliver OpenOffice, a competitor of Microsoft Office Suite, through a web interface, much like how it does with GMail and Google Maps, that will give Microsoft Office Suite a run for its money.

A couple of years ago, Sun’s CEO Scott McNealy mentioned that “Network is the computer”; all we need is a dumb terminal to access it. I don’t necessarily think the terminal will be dumb, because now we do need it for local storage, text editing, some file processing, especially media files like music, image, and video. But hey, I cannot pretict the future. Maybe the dumb terminal 10 years from now is 10 times smarter than the ones we have today.

Mutt questions

Originally posted at SqlServerCentral.com’s blog site:

I am working on my own website, http://www.HaidongJi.com. It is Apache on Linux, ran by a hosting company. I got access to SSH, MySql, and most of Linux utilities. I am planning to use WordPress and host a blog there too. That will be my primary blogging site, including my thoughts on Linux, MySql, personal musings, and whatnot (cannot believe I used the word). It is still a work in progress. Hopefully I can get it up and running within a month.

Anyway, the real reason I post this is to seek advice / pointers on Mutt. I am planning to install Mutt on this Linux machine and use it as my primary email client. I want to use it primarily because it allows me to use my favorite editor, VI / VIM. I also heard from people that it is keyboard driven, which I like too. The fact it is light weight also helps.

Here are my questions:
1. Installation: anything advice, how easy or difficult it is. It looks like that something special needs to be done if you are going to use SSL. Since I don’t have root access to the Linux box, will I encounter problems when install Mutt?
2. Configuration: what do I need to do to configure it so I can have access to multiple POP or IMAP accounts? It looks like I need to play with .muttrc file.

That’s it for now. Email or comment below if you have ideas/suggestions. Thanks a lot.