MartW's questions

We have a server on which SQL Server 2008 is running. When we connect to it with SSMS with some logins (from SSMS 2008), Intellisense is available. For others it isn't. The logins in question connect to the same database, and nothing else is changed between SSMS sessions apart from the login used to connect.

If I connect as a login which is a member of db_owner, Intellisense works. If I connect as a login which is not in db_owner, but which has 'View Definition' privileges, Intellisense works. If I connect as a login which is neither in db_owner or has 'View Definition' privileges, Intellisense does not work.

So I tried granting the third login 'View Definition' privileges to that database, thinking they'd get Intellisense. No luck. The third login has access to two other databases. In all three databases, this login is a member of the db_datareader role but no other roles (and no 'View Definition' privilege either). In those two other databases, this login gets Intellisense. In the main database, it doesn't.

It seems to me that there's some per-database and per-login setting/permission that allows Intellisense, but so far I've been unable to track it down.

Any ideas?

We're about to get our first sysadmin to look after a multitude of SQL Servers which have previously been awkwardly looked after by a mixture of the developers and IT support. It's long overdue, and we've been trying to persuade the higher-ups to agree to one for years.
Well, finally they did but the salary we were able to offer wasn't exactly inspiring to say the least. Nevertheless, we've snagged one somehow.
What I'd like to know is what are the early signs to look out for that a new sysadmin doesn't really know what they're doing or what dangerous habits to look for, with particular focus on SQL Server. I'm a little nervous that our bargain-basement hunting may not work out too well, which has been the case with other roles.

Any thoughts, please?

We're in the process of planning the migration of some databases from an old doddery server (running SQL Server 2000) to a new one running SQL Server 2008. As not everything is going to the same new server, we're intending to 'retire' the name of the old server and use a new one more in keeping with our current naming conventions. But, due to various reasons of bad practice and auditing, I don't believe we'll be able to definitively identify pre-migration absolutely everything that may still refer to the old server name. This means that for a while it's likely we'd have to redirect any reference to the old name to the new name and hope nothing breaks (most migrated DBs will be going to this server, so we'll likely play the odds).

With this in mind, I was wondering what would be the best way to detect any connection attempts that have used the old name? Is this something that can be done via SQL Server, or only via the internal DNS servers? The former is preferable to me as I can monitor this myself. Would the latter be able to detect the exact connection string used (which would help identify the application using it)? I think it's likely that many of the redirected connections will involve either Crystal Reports or Access ADPs.

A possible complication is that our network is Novell, not MS, although the new SQL Server will be on Windows Server 2008.

Any advice is welcome.

We have a SQL Server 2000 server that was formerly running on SQL Server 7, with the result that the default collation is not what seems to be the default set later for SQL 2000 and up (it is SQL_Latin1_General_CP1_CI_AS rather than Latin1_General_CI_AS).

Anyway, we're now planning to upgrade to SQL Server 2008 at last. While the upgrade advisor shows no issues apart from one maintenance sp referring to some legacy system tables, I was wondering how to do a 'full' upgrade of the DB, rather than simply leaving the database in compatibility mode which what seems to happen if we either do an upgrade in place, or restore/attach the SQL2000 database to a SQL2008 instance.

So I have various questions :

1. Would we lose any functionality by leaving the DB in compatibility mode for 2000?
2. If we change the compatibility mode to 2008, what could break? Would there be other settings we'd need to change to make it fully a SQL 2008 DB?
3. Is there any way to make the upgrade disregard the existing collation references and use the server default throughout?
4. Or is it just better to script the database again from scratch and import the data afterwards?

Basically we want to upgrade, but make sure that this legacy DB/server can fully gain from the functionality of the newer versions of SQL Server.

We have an overworked server currently running a single SQL Server 2000 instance on physical hardware, and about 40 different apps interact with it on a daily basis. Last year, the RAID controller failed and we had no spare, so IT Support hurriedly migrated it overnight to a copy running on a VMWare Server. While it was on that server everything ran much quicker due to it being a big improvement in spec. However, the biggest app using it had occasional serious errors which never occurred on physical hardware.

Specifically, several times a week it would disconnect batches of users - anywhere from just ten to hundreds at once, and all at the same time. It didn't affect any particular users or PCs or offices - all were affected equally.

The only common thing was the app, which is a VB6 app using ADO 2.8 to connect. The other apps connecting to that virtualised instance of SQL Server seemingly had no problems, although they were (and are) responsible for only a tiny fraction of the work involving this server.

The upshot is that after about two weeks of loving the speed and hating the random mass disconnections (which we were never able to find a cause for), we sadly took the decision to return to physical hardware and the disconnections vanished.

Now we've reached the point where the old server just can't handle all that's being asked of it, and we're intending to migrate everything to 2 or more other servers. The snag is that there's a good chance they'll have to be virtual ones again. Given what happened last time, I'm trying to find out what possible reasons there could be for these mass disconnections. We were running VMWare ESX, but the network is Novell-based. Also, the server had a linked server setup to connect to an Informix server using a known-to-be-buggy ODBC driver, and this is used throughout the day.

Any ideas on the cause(s)?