
-- -
/********1*********2*********3*********4*********5*********6*********7**
Configur.CQL             1996/03/05 17:12

   This file upgrades sysconfigures from 4.2 or 6.0 to 6.5, only
as part of a 6.5 setup.
   This file also ensures some minimum-maximum values for certain
configurations.
   Do not run this file against a MS SQL version earlier than 4.2.
   Origin of this file was 6.00.121 InstProc.SQL.

Copyright Microsoft, Inc. 1994, 1995, 1996
All Rights Reserved.
Use, duplication, or disclosure by the United States Government
is subject to restrictions as set forth in subdivision (c) (1) (ii)
of the Rights in Technical Data and Computer Software clause
at CFR 252.227-7013. Microsoft, Inc. One Microsoft Way, Redmond WA
98052.
*********1*********2*********3*********4*********5*********6*********7*/

Go
use master
set nocount on
Go
dump transaction master with no_log
Go

declare @vdt varchar(99)
select  @vdt = convert(varchar,getdate(),113)
raiserror('Starting Install\Configur.SQL at %s',0,1 ,@vdt)
raiserror('This file upgrades sysconfigures from MS SQL Server 4.2x to 6.0.',0,1)
Go


--------------  Only for 6.5 sqlservr.exe  --------------------

if (@@version like '%6.50.%')
   begin
   raiserror('The @@version is like  6.50.  , for which this file is designed.   Will proceed.',0,1)
   end
else
   begin
   raiserror('The @@version is NOT like  6.50.  , for which this file is designed. Will NOT proceed.',22,127) with log
   end
Go



declare @int1 integer

raiserror('Making sure that updates to system tables are allowed.',0,1)

if (     exists (select * from sysobjects where name='sp_configure')
   AND   1 <> (select value from syscurconfigs where config = 102)
   )
        begin                                          -- Query tree compatible
        exec @int1 = sp_configure 'allow updates',1    -- config=102
        if @@error <> 0 or @int1 <> 0
                raiserror('Bad sp_configure exec at top of Configur.SQL, killing spid.'
                        ,22,127) with log
        reconfigure with override
        raiserror('sp_configure & reconfigure just now turned on ''allow updates''.',0,1)
        end
Go

/***
   Make sure server was started in single user mode or that sp_configure
was used to enable updates to system tables.
***/

if (select value from syscurconfigs where config = 102) <> 1
        raiserror('Cannot run Configur.SQL unless updates to system tables are enabled. Shutdown server and restart with the ''-m'' option (or use sp_configure?) to enable updates to system tables.'
                        ,22,127) with log
Go

dump transaction master with no_log
Go


---- 118 ('remote logins') is obsolete in 6.5.
delete from sysconfigures where config in (118)
Go


/***********
      We need to know whether we are in the process of upgrading
a 4.2x or a 6.0+ MS SQL Server.   Most of the remaining logic
in this file will be bypassed unless we are upgrading a 4.2x.
      Our technique will be to query for the presence of
certain config rows which we know exist in 4.2x but not in 6.0+,
plus for some rows we know are new in 6.0, plus for some rows
we know are new in 6.5.


IN 4.2 BUT NOT IN 6.0:
------------------------
116   devices
133   infect ticks



IN 6.0 BUT NOT IN 6.5:
------------------------
.



NEW IN 6.0 (sample):
------------------------
1510  RA pre-fetches
1522  LE threshold minimum



NEW IN 6.5:
------------------------
 542  remote proc trans                -- (DTC Viper)
 543  remote conn timeout
1110  time slice                       -- (Issue 4.2x & 6.0)
1119  remote sites
1534  user options                     -- (@@options)
1535  affinity mask
1536  max text repl size

***********/


if exists (select * from sysconfigures where config in (542,1535))
   begin
   raiserror('sysconfigures rows are already at the 6.5+ version, so most of the remaining 6.0 logic in this file will be bypassed.',0,1)

   GOTO LABEL_60BYPASS

   end
else
   raiserror('sysconfigures rows are not yet at the 6.5+ version.   Will proceed.',0,1)



if exists (select * from sysconfigures where config in (1510,1522))
   begin
   raiserror('sysconfigures rows are already at the 6.0+ version, so most of the remaining 6.0 logic in this file will be bypassed.',0,1)

   GOTO LABEL_60BYPASS

   end
else
   raiserror('sysconfigures rows are not yet at the 6.0+ version.   Will proceed.',0,1)


raiserror('Making changes to sysconfigures rows up to 6.0 .....',0,1)


-------- Caution re 123/1123

raiserror('Delete 4.2x rows that are obsolete in 6.0.',0,1)

delete
      from
             sysconfigures
      where
            (config in ( 110,114,116,132,133 )

            or    config between  118 and  122
            )

raiserror('Number of rows deleted as obsolete in 6.0 was %d'
,0,1,@@rowcount)




raiserror('Delete rows that are to be added in 6.0.',0,1)

delete
      from
             sysconfigures
      where
            (config in ( 505,508,518,521,523,540,541
                        ,1522)

            or    config between  502 and  503
            or    config between 1504 and 1506
            or    config between 1509 and 1520
            or    config between 1530 and 1533
            )

raiserror('Number of rows deleted as to-be-added in 6.0 was %d'
,0,1,@@rowcount)




if exists (select * from sysconfigures where config = 123)
   begin
   update sysconfigures set config = 1123 where config = 123
   raiserror('config value=123 (sortorder) was Updated to 1123.',0,1)
   end
else
   raiserror('There is already NO row where value=123 (sortorder), so 1123 must already exist.',0,1)



raiserror('Updating a few statuses to Dynamic.',0,1)

update sysconfigures set status = status | 1
where config in (101,102,115)
and   status & 1 = 0

raiserror('Number of rows updated for status dynamic bit was %d'
,0,1,@@rowcount)



/***
   If sysconfigures for TempDB in RAM was 1 (Mb), this was a 4.2 database.
We will keep TempDB in RAM, but it will just be 2 Mb and will
need to manually be altered later!   If it is a value higher than 1,
we will not touch it.
   First, we add a config=501 row if none is present.
***/

-------- Add a 501 row if none is present; but not "in RAM".

if not exists (select * from sysconfigures where config = 501)
   begin
   insert into   sysconfigures
          values (501,0,'TempDB in RAM option',0)
   raiserror('Just finished Insert of 501 ''TempDB in RAM''.',0,1)
   end
else
   raiserror('501 ''TempDB in RAM'' row already present; fine.',0,1)


update   sysconfigures
   set   value=2 ,comment='TempDB in RAM option' ,status=0
   where config=501 and value=1

if @@rowcount=1
   raiserror('The pre-existing 501 ''TempDB in RAM'' row was found to be in need of an Update, and was Updated.',0,1)
else
   raiserror('The pre-existing 501 ''TempDB in RAM'' row does not need Updating.',0,1)



/***
Insert new configuration options.   These comments are sometimes not
exactly equal to their corresponding spt_values.name strings.
   Status Bits:   1=dynamic ,2=advanced
***/

raiserror('
Begin approx 28 Inserts up to 6.0.
Will report only failed or bypassed Inserts....
',0,1)




if not exists (select * from sysconfigures
                         where value=502)
   insert into sysconfigures values (502,8,'Maximum outstanding async IOs',0)
else raiserror('Insert failed for    502 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=503)
   insert into sysconfigures values (503,255,'Maximum worker threads',1)
else raiserror('Insert failed for    503 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=505)
   insert into sysconfigures values (505,4096, 'Default network packet size',1)
else raiserror('Insert failed for    505 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=508)
   insert into sysconfigures values (508,3,'RA worker threads',0)
else raiserror('Insert failed for    508 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=518)
   insert into sysconfigures values (518,0,'show advanced options',1)
else raiserror('Insert failed for    518 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=521)
   insert into sysconfigures values (521,0,'LE threshold percent',1)
else raiserror('Insert failed for    521 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=523)
   insert into sysconfigures values (523,200,'LE threshold maximum',1)
else raiserror('Insert failed for    523 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=540)
   insert into sysconfigures values (540,5,'backup threads',0)
else raiserror('Insert failed for    540 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=541)
   insert into sysconfigures values (541,1,'backup buffer size',1)
else raiserror('Insert failed for    541 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1504)
   insert into sysconfigures values (1504,7993,'Hash buckets',2)
else raiserror('Insert failed for    1504 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1505)
   insert into sysconfigures values (1505,64,'Number of sort pages',3)
else raiserror('Insert failed for    1505 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1506)
   insert into sysconfigures values (1506,8,'Maximum lazywrite IO',3)
else raiserror('Insert failed for    1506 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1509)
   insert into sysconfigures values (1509,5,'RA slots per thread',2)
else raiserror('Insert failed for    1509 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1510)
   insert into sysconfigures values (1510,3,'RA pre-fetches',3)
else raiserror('Insert failed for    1510 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1511)
   insert into sysconfigures values (1511,15,'RA delay',3)
else raiserror('Insert failed for    1511 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1512)
   insert into sysconfigures values (1512,3,'RA cache miss limit',3)
else raiserror('Insert failed for    1512 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1513)
   insert into sysconfigures values (1513,4,'RA cache hit limit',3)
else raiserror('Insert failed for    1513 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1514)
   insert into sysconfigures values (1514,10000,'spin counter',3)
else raiserror('Insert failed for    1514 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1515)
   insert into sysconfigures values (1515,20,'Free buffers',3)
else raiserror('Insert failed for    1515 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1516)
   insert into sysconfigures values (1516,0,'SMP concurrency',2)
else raiserror('Insert failed for    1516 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1517)
   insert into sysconfigures values (1517,0,'Priority boost',2)
else raiserror('Insert failed for    1517 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1519)
   insert into sysconfigures values (1519,5,'remote login timeout',3)
else raiserror('Insert failed for    1519 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1520)
   insert into sysconfigures values (1520,0,'remote query timeout',3)
else raiserror('Insert failed for    1520 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1522)
   insert into sysconfigures values (1522,20,'LE threshold minimum',3)
else raiserror('Insert failed for    1522 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1530)
   insert into sysconfigures values (1530,0,'logwrite sleep (ms)',1)
else raiserror('Insert failed for    1530 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1531)
   insert into sysconfigures values (1531,-1,'cursor threshold',3)
else raiserror('Insert failed for    1531 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1532)
   insert into sysconfigures values (1532,0,'set working set size',2)
else raiserror('Insert failed for    1532 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1533)
   insert into sysconfigures values (1533,10,'resource timeout',3)
else raiserror('Insert failed for    1533 config!',22,127) with log



LABEL_60BYPASS:


raiserror('Logic is now passed the Inserts for 6.0.',0,1)

Go


----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8


if object_id('tempdb..#new65') is not null
           drop table #new65
Go

create table #new65
   (new65_config smallint not null)
Go

insert into #new65 (new65_config) values ( 542)
insert into #new65 (new65_config) values ( 543)
insert into #new65 (new65_config) values (1110)
insert into #new65 (new65_config) values (1119)
insert into #new65 (new65_config) values (1534)
insert into #new65 (new65_config) values (1535)
insert into #new65 (new65_config) values (1536)
Go

set nocount off
Go

print ' '

select    t1.new65_config ,'New in 6.5.' as 'New in 6.5.'
   from   #new65                 t1
   order by t1.new65_config


print ' '

select    t1.new65_config ,'Already Present.' as 'Already Present.'
   from   master..sysconfigures  cfg
         ,#new65                 t1
   where  t1.new65_config        = cfg.config
   order by t1.new65_config


print ' '
Go

set nocount on


Go
if exists (select * from sysconfigures where config in (1536)) --Latest addition
   begin
   raiserror('sysconfigures rows are already at the 6.5+ version, so most of the remaining 6.5 logic in this file will be bypassed.',0,1)

   GOTO LABEL_65BYPASS

   end
else
   raiserror('sysconfigures rows are not yet at the 6.5+ version.   Will proceed.',0,1)


raiserror('Making changes to sysconfigures rows up to 6.5 ....',0,1)



raiserror('Delete 6.0 rows that are obsolete in 6.5.',0,1)

delete
      from
             sysconfigures
      where
            (config in (  118)
            )

raiserror('Number of rows deleted as obsolete in 6.5 was %d'
,0,1,@@rowcount)


raiserror('Delete rows that are new to-be-added in 6.5.',0,1)


delete    sysconfigures
   where  config in
            (select t1.new65_config from #new65 t1
            )

raiserror('Number of rows deleted as new to-be-added in 6.5 was %d'
,0,1,@@rowcount)



raiserror('Begin approx 6 Inserts up to 6.5. Will report only failed or bypassed Inserts....',0,1)


----(config ,value ,comment ,status)


if not exists (select * from sysconfigures
                         where value=542)
   insert into sysconfigures values (542, 0, 'Create DTC transaction for remote procedures', 0)
else raiserror('Insert failed for    542 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=543)
   insert into sysconfigures values (543, 10, 'Remote Connection Timeout period (minutes)', 0)
else raiserror('Insert failed for    543 config!',22,127) with log



if not exists (select * from sysconfigures
                         where value=1110)
   insert into sysconfigures values (1110,100,'Average time slice per process in milliseconds',3)
else raiserror('Insert failed for    1110 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1119)
   insert into sysconfigures values (1119,10,'Number of remote sites',2)
else raiserror('Insert failed for    1119 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1534)
   insert into sysconfigures values (1534,0,'user options',1)
else raiserror('Insert failed for    1534 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1535)
   insert into sysconfigures values (1535,0,'affinity mask',2)
else raiserror('Insert failed for    1535 config!',22,127) with log


if not exists (select * from sysconfigures
                         where value=1536)
   insert into sysconfigures values (1536,65536,'Maximum size of a text field in replication.',1)
else raiserror('Insert failed for    1536 config!',22,127) with log



LABEL_65BYPASS:


raiserror('Now passed all of the 6.5 Inserts.',0,1)

Go


if object_id('tempdb..#new65') is not null
           drop table #new65
Go


----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8

---- (Old .C gen Config.SQL)


use master
Go

raiserror('Handle NewInstall/Upgrade configuration value issues....',0,1)

raiserror('Any changes made to pre-existing configuration values will be reported ....',0,1) with nowait
Go

declare @OldValue   integer
       ,@NewValue   integer
       ,@OKMin      integer
       ,@OKMax      integer
       ,@ConfigName varchar(35)
       ,@ConfigNum  integer

declare @Registry_SetupStatus varchar(255) ,@RegistryOrig_SetupStatus varchar(255)

---- Upgrade1.SQL ensures xp_regread is ready.
execute master..xp_regread 'HKEY_LOCAL_MACHINE'
      ,'SOFTWARE\Microsoft\MSSQLServer\Setup' ,'SetupStatus'
      ,@RegistryOrig_SetupStatus OUTPUT


select @Registry_SetupStatus = Isnull(@RegistryOrig_SetupStatus,'NewInstall')

raiserror('(Registry SetupStatus=''%s'', will be treated as ''%s''.)'
,0,1,@RegistryOrig_SetupStatus,@Registry_SetupStatus)

IF @Registry_SetupStatus = 'Installed'
   begin

   GOTO LABEL_25AFTERINSTALL

   --raiserror('The value ''%s'' is an invalid RegistryStatus value at this phase, ABORTING SetUp!',22,127,@Registry_SetupStatus) with log
   end

IF @Registry_SetupStatus NOT IN ('Tweaked','Upgrade','NewInstall')
   begin
   raiserror('The value ''%s'' is an unrecognized RegistryStatus, ABORTING SetUp!',22,127,@Registry_SetupStatus) with log
   end



IF @Registry_SetupStatus IN ('Upgrade') -- Then no need to install these initial values.
   GOTO LABEL_25AFTERINSTALL

raiserror('Now installing initial configuration values...',0,1)


-------------------------------------------------------
SELECT @NewValue=5 ,@ConfigName='recovery interval' ,@ConfigNum=101 -----------

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=30 ,@ConfigName='procedure cache' ,@ConfigNum=108

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=0 ,@ConfigName='fill factor' ,@ConfigNum=109

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=2 ,@ConfigName='database size' ,@ConfigNum=111

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=0 ,@ConfigName='media retention' ,@ConfigNum=112

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=0 ,@ConfigName='recovery flags' ,@ConfigNum=113

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=20 ,@ConfigName='open databases' ,@ConfigNum=105

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=5000 ,@ConfigName='locks' ,@ConfigNum=106

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=500 ,@ConfigName='open objects' ,@ConfigNum=107

update sysconfigures set value=@NewValue where config=@ConfigNum

-------------------------------------------------------
SELECT @NewValue=15 ,@ConfigName='user connections' ,@ConfigNum=103

update sysconfigures set value=@NewValue where config=@ConfigNum



-----------------------------
LABEL_25AFTERINSTALL:
-----------------------------


raiserror('At  LABEL_25AFTERUPGRADE  ....',0,1)



IF @Registry_SetupStatus IN ('Tweaked','NewInstall') -- Then no need to adjust user's unsafe values.
   GOTO LABEL_36AFTERUPGRADE

raiserror('Now ensuring minimum values for certain configurations....',0,1)


-------------------------------------------------------
SELECT @NewValue=20 ,@ConfigName='open databases' ,@ConfigNum=105 -------------

select @OldValue = min(cf.value) from sysconfigures cf ,spt_values val
   where val.name=@ConfigName and val.type='C  ' and cf.config=val.number
   and   cf.value < @NewValue

if @OldValue is not null begin
   raiserror('Will now change your existing ''%s'' from %d to our recommendation of %d.',0,1 ,@ConfigName,@OldValue,@NewValue)
   update sysconfigures set value=@NewValue where config=@ConfigNum
                         end


-------------------------------------------------------
SELECT @NewValue=5000 ,@ConfigName='locks' ,@ConfigNum=106 ------------------

select @OldValue = min(cf.value) from sysconfigures cf ,spt_values val
   where val.name=@ConfigName and val.type='C  ' and cf.config=val.number
   and   cf.value < @NewValue

if @OldValue is not null begin
   raiserror('Will now change your existing ''%s'' from %d to our recommendation of %d.',0,1 ,@ConfigName,@OldValue,@NewValue)
   update sysconfigures set value=@NewValue where config=@ConfigNum
                         end


-------------------------------------------------------
SELECT @NewValue=500 ,@ConfigName='open objects' ,@ConfigNum=107 --------------

select @OldValue = min(cf.value) from sysconfigures cf ,spt_values val
   where val.name=@ConfigName and val.type='C  ' and cf.config=val.number
   and   cf.value < @NewValue

if @OldValue is not null begin
   raiserror('Will now change your existing ''%s'' from %d to our recommendation of %d.',0,1 ,@ConfigName,@OldValue,@NewValue)
   update sysconfigures set value=@NewValue where config=@ConfigNum
                         end


-------------------------------------------------------
SELECT @NewValue=15 ,@ConfigName='user connections' ,@ConfigNum=103 --@@max_connections 6.5 W.S.

select @OldValue = min(cf.value) from sysconfigures cf ,spt_values val
   where val.name=@ConfigName and val.type='C  ' and cf.config=val.number
   and   cf.value < @NewValue

if @OldValue is not null begin
   raiserror('Will now change your existing ''%s'' from %d to our recommendation of %d.',0,1 ,@ConfigName,@OldValue,@NewValue)
   update sysconfigures set value=@NewValue where config=@ConfigNum
                         end


--------------------------------
LABEL_36AFTERUPGRADE:
--------------------------------

update sysconfigures set status=2
	where config=1123 --def sortorder
	and status<>2

raiserror('At  LABEL_36AFTERUPGRADE  ....',0,1)


Go



raiserror('Will now run the finalization logic for this file.',0,1)

use master
Go

if exists (select * from sysobjects where name = 'sp_configure'
         and sysstat & 0xf = 4)
   begin
      exec sp_configure 'allow updates',0   --  102
      reconfigure with override
      raiserror('sp_configure just now turned off ''allow updates'', and reconfigure with override was run.',0,1)
   end
else
   raiserror('(sp_configure not present to turn off ''allow updates''.)',0,1)
Go

---- The configuration changes made by this file might not be fully installed until sqlservr.exe is stopped & restarted.
Go
----(still need -m effect) reconfigure with override
Go


declare @vdt varchar(99)
select  @vdt = convert(varchar,getdate(),113)
raiserror('Finishing Configur.SQL at %s',0,1,@vdt)
Go

dump transaction master with no_log
Go
checkpoint
Go
-- -

