:::: MENU ::::

Posts Categorized / Upgrade SQL Server

  • May 29 / 2009
  • 1
dbDigger, Upgrade SQL Server

SQL BPA command line has encountered a problem and needs to close.

I got following error message while performing upgrade from SQL Server 2000 to SQL Server 2005.

SQL BPA command line has encountered a problem and needs to close

SQL BPA command line has encountered a problem and needs to close.

After little googling i found a solution that

  • Go to C:Program FilesMicrosoft SQL Server90Setup BootstrapBPA
  • Make folder BPAClient
  • Go to C:Program FilesMicrosoft SQL Server90Setup BootstrapBPAbin
  • Copy BPAClient.dll
  • Paste it in to the newly created directory BPAClient on following path C:Program FilesMicrosoft SQL Server90Setup BootstrapBPABPAClient

To accomplish the above task I have created a cmd script in .bat file. On double click the batch script will run and above task will be performed. After these small steps upgrade process will go on without any problem. As a follow up to this post i will try to list here some reasons and background for this error message.

  • Oct 17 / 2008
  • 0
dbDigger, Upgrade SQL Server

SQL Server 2005 Licensing modes

Basically there are three options that you can use in licensing your SQL 2005 server for either the Enterprise, Standard or Workgroup editions. Each of the three licensing modes is listed below with brief descriptions of each.

Processor License
With this licensing mode each processor whether they are physical or virtual that the SQL server uses for SQL must have a license. It does not require a Device or User Client access license (CAL) each of which will be explained below.

Device Client Access License (CAL)
This licensing mode requires a license for each of the Network Operating Systems (NOS) where the NOS is running a SQL instance rather it is a named instance or the default instance. It also requires you to have a CAL for each client machine that accesses the SQL server.


User Client Access License (CAL)

With this licensing mode a license is needed for each NOS that is running a SQL instance rather it is a named instance or the default instance in addition to a CAL for each user that accesses the SQL server.

 

SQL Server 2005 Licensing

  • Aug 09 / 2008
  • 0
DBA best practices, dbDigger, Upgrade SQL Server

DBA Best Practices for Upgrading

DBA Best Practices for Upgrading

  1. Run the Upgrade Advisor before upgrading. Make any necessary changes before performing the upgrade.
  2. Perform a test upgrade of your test SQL Servers before you upgrade your production servers. And don’t forget to test your applications with the new version also.
  3. Before you upgrade, be sure you have a plan in place to fall back to in case the upgrade is problematic.
  4. Don’t upgrade SQL Server clusters in place. Instead, rebuild them on new hardware.
  5. If you upgrade from a previous version of SQL Server, you should update all of the statistics in all your databases using either UPDATE STATISTICS or sp_updatestats. This is because statistics are not automatically updated during the upgrade process.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

  • Aug 07 / 2008
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Upgrade SQL Server

change compatibility level of a SQL Server database

I have a database with compatibility level 80 (upgraded from SQL Server 2000 to SQL Server 2005). Today when i tried to disable the trigger through following command

 Use DBNameHere  
 GO  
 DISABLE TRIGGER triggerName  
 ON tablename  
 GO  

Following error occurred

Msg 156, Level 15, State 1, Line 1
 
Incorrect syntax near the keyword ‘TRIGGER’.

It was surprised as the command looked valid and used many times. It was unusual and i had no way but to disable it through SSMS for time being. Later i tried again in a different way and it worked through following command.

 
USE DBNameHere  
 GO  
 ALTER TABLE tableName  
 DISABLE TRIGGER TriggerName  
 GO  

So if you ever get the above mentioned error on enable/disable command then it may be compatibility level issue. Because disable trigger is SQL Server 2005 command. As compatibility level 80 is of SQL Server 2000 so it will not work. If you do not have any problem then you may easily update compatibility level to 90 for upgraded databases.

To change compatibility level of a database to 90 (SQL Server 2005) use following script

 
ALTER DATABASE AdventureWorks  
 SET SINGLE_USER  
 GO  
 EXEC sp_dbcmptlevel AdventureWorks, 90;  
 GO  
 ALTER DATABASE AdventureWorks  
 SET MULTI_USER  
 GO  

Through SSMS you may change the compatibility level by right clicking the database and click properties. Following screen will appear and you may change the level there.

SQL Server dt properties

By the way compatibility levels of SQL Server databases are as followings

* 60 = SQL Server 6.0

* 65 = SQL Server 6.5

* 70 = SQL Server 7.0

* 80 = SQL Server 2000

* 90 = SQL Server 2005

Consult us to explore the Databases. Contact us