SQL Server Version Listing

Last week we got into a strange issue regarding some SQL Server 2008 post-installation error messages. Technet mentioned that such occurrences are reported when we are using a version prior to Cumulative Update (CU6).  But it wasnt getting clear if a particular build came prior or before CU6 (yes, sounds insane, but we landed there!).

Anyways, I came across a web-resource from SQLSecurity.com available here, which is quite helpful as it lists down complete history of SQL Server builds ever published (public/private, CU, SP, Patches – everything). Now, there is another web-resource from SQLTeam available here, but it enlists only major Releases, CU, SP etc. I used to reference SQLTeam link till sometime back, but given the level of depth on SQLSecurity site, I think that too is quite a useful reference point going forward.

Identifying SQL Server Version

DECLARE @SQLRelease VARCHAR(15);

SET @SQLRelease = 'UNKNOWN'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='8'

SET @SQLRelease = 'SQLServer2000'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='9'

SET @SQLRelease = 'SQLServer2005'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),2)='10'

SET @SQLRelease = 'SQLServer2008'

SELECT @SQLRelease as ReleaseLevel

, SERVERPROPERTY('ProductLevel') AS ProductLevel

, SERVERPROPERTY('Edition') AS Edition;

We are working upon a central Server Inventory solution and needed to capture for SQLServer details. Surprisingly, there are many script-lets out there to capture Product Version, but not much details around what it maps to (unless you want to keep a table running with you to interpret numbers again SQL Server Product version). Hence the quick script above which can tell you Product Version in more humanly manner, along with SP and Edition details.

Oracle Files Poster

I recently collated details on different Oracle Files, and allied information on each of them, in a pictorial format! It lists down different generally required, and option oracle files, and more details or commentary for each of the files.

Here it is for general use and reference.

 

Vikas Rajput (http://vikasrajput.wordpress.com)

Using Command Prompt during Windows 2008 R2 Installation

You would have heard of Command Line based installation of Windows. Though in Windows 2008 R2, you can even use command prompt to a great extent right from the moment you see “Where do you want to install” screen. This is enabled by something called WINMINPC environment which is used by Setup itself. Complete list of commands and when you shall use them can be referenced at http://technet.microsoft.com/hi-in/magazine/gg491396%28en-us%29.aspx.

 

Vikas Rajput (http://vikasrajput.wordpress.com)

Configuring Oracle Database Console Manually

This article tells about configuring Oracle Enterprise Manager, OEM.
In order to manually configure your Enterprise Manager, connect to database instance, you must complete these tasks:
Verify that the user you are authenticating to the Solaris or Linux system has the Oracle Environment variables set correctly.
env | grep ORACLE*

[oracle@SERVERNAME ~]$ env | grep ORACLE*
ORACLE_SID=XXXXXX
ORACLE_BASE=/app/apps/oracle
ORACLE_HOME=/app/apps/oracle/product/10.2.0/db_1

If the above environment variables are set correctly, we are ready to continue. If the variables are not set correctly, we should modify the users profile to include them before continuing. If we prefer to continue without modifying your profile, you will need to export the variable for this Terminal Session as well as any future sessions.

After verifying that the environment variables are set correctly, we have one more step to verify before configuring the database control repository: make sure that your netservices files are configured correctly.

To verify, you need to check your tnsnames.ora, listener.ora and sqlnet.ora files to verify that XXXXXX is listed.

Creating and Configuring the Database Control Repository
To create and configure your database control repository, run the following commands in your terminal session:

[oracle@SERVERNAME ~]$ emca -repos create

This command will prompt you for your Database SID, Listener port number, Password for your SYS user, and the Password for your SYSMAN user. All of this information should be known prior to running the command. The password for the SYS and SYSMAN users were configured during the database install.

[oracle@SERVERNAME ~]$ emca -repos create
STARTED EMCA at
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Enter the following information:
Database SID: XXXXXX
Listener port number: 1521
Password for SYS user: xxxxxxxx
Password for SYSMAN user: xxxxxxxx
Do you wish to continue? [yes(Y)/no(N)]: y
oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/oracle/product/10.2/db_1/cfgtoollogs/emca/XXXXXX/emca_-PM.log.
oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) …
Once your repository is created, you must configure your database control as follows:

[oracle@SERVERNAME ~]$ emca –config dbcontrol db

This command will again prompt you for your Database SID, Listener port number, Password for SYS, DBSNMP, and SYSMAN users. It will also ask you for data to configure email for notifications from the EM.

Starting the Console
Now that you have created your repository and database control, you are ready to start the console. To start the console, initiate the following command at your terminal console:

[oracle@SERVERNAME ~]$ emctl start dbconsole

By launching a browser and going (http://SERVERNAME.DOMAIN.com:5500/em) to this page, I will now be prompted to login to OEM with the Oracle database instance of XXXXXX.
Automatically start and stop the DB-Console:
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole
You now have a working repository and database control to access OEM for XXXXXX instance.

Vikas Rajput (http://vikasrajput.wordpress.com)

"Denali” CTP1 Released

MS has released the CTP1 for code named “Denali” which is going to be the next SQL Server version. There isn’t any timeline definition as yet on formal release. More details can be referenced at: http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx.

 

Vikas Rajput (http://vikasrajput.wordpress.com)

What SQL Server Data Access Driver you using?

There are times when team runs into issues and we try to find what version of SQL Server data access driver is application using. This maps back to something called SNAC (SQL Server Native Client). The reason for this unawareness can also be because of the reason that every SNAC version supports current SQL Server version, two previous SQL Server versions, and two new SQL Server versions. For example, SQL Server 2005 had SNAC9, which supports SQL Server 2005, SQL Server 2000, SQL Server 7.0, SQL Server 2008 and would support SQL Server 2011 (if thats the to be version for same). Current SQL Server 2008 has SNAC10.More details on SNAC, WDAC, MDAC can be referenced here.

So, to determine what SNAC version your application is using (which can be different from whats on your database server – leading to issues), following script can be used:

SELECT session_id, protocol_type, driver_version = CASE CONVERT(CHAR(4), CAST(protocol_version AS BINARY(4)), 1)
WHEN ’0×70′ THEN ‘SQL Server 7.0′
WHEN ’0×71′ THEN ‘SQLServer2000′
WHEN ’0×72′ THEN ‘SQLServer2005′
WHEN ’0×73′ THEN ‘SQLServer2008′
ELSE  ‘Unknown Version’
END
FROM sys.dm_exec_connections

The binary conversion (to hex) is for the reason that sys.dm_exec_connections store the protocol_version info in integer form for TDS version. The hex value is mapped already to respective SQL Server versions in query above.

Rebuilding System Databases

Step#3 : Post Rebuild Steps
1. Apply the latest service pack and any applicable hot fixes.

2. Restore your current full backups of the master, model, and msdb databases.
NOTE: If you have changed the server collation, do not restore the system databases as it will replace the new collation with the previous setting.

3. If a backup is not available or if the restored backup is not current, re-create any missing entries. For example, re-create all missing entries for your user databases, backup devices, SQL Server logins, end points, and so on. The best way to re-create entries is to run the original scripts that created them (sole reason why change management is of great essence).

4. If the instance of SQL Server is configured as a replication Distributor, then restore the distribution database as well.

5. If required, move the system databases to the locations you recorded previously.

6. Verify the server-wide configuration values match the values you recorded previously.

Rebuilding System Databases

Step#2[b] : Rebuild the resource Database Procedure

When you rebuild the resource database, all service packs and hot fixes are lost, and therefore steps below must be followed.

1. Launch the SQL Server Setup program (setup.exe) from SQL Server 2008 distribution media.

2. In the left navigation area, click Maintenance, and then click Repair.

3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.

4. On the Select Instance page, select the instance to repair, and then click Next.

5. The repair rules will run to validate the operation. To continue, click Next.

6. From the Ready to Repair page, click Repair. The Complete page message indicates that the operation is finished.

Rebuilding System Databases

Step#2[a] : Rebuild Procedure (for all except resource database)
1. Execute C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe within installation media using command prompt.

2. From a command prompt window, enter the following command:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

PS: Use SAPWD switch only when server was using Mixed Authentication mode. If it was using OS Authentication, then you don’t need to use it.

3. When Setup has completed rebuilding the system databases, examine the C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Logs\Summary.txt log file to verify that the process completed successfully.