Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Changing database serve, minimizing downtime

7 posts in General Discussion Last posting was on 2009-10-15 13:52:50.0Z
Kon T. Amusse Posted on 2009-10-14 14:32:00.0Z
From: "Kon T. Amusse" <kon.t@amusse.invalid>
Newsgroups: sybase.public.ase.general
Subject: Changing database serve, minimizing downtime
Date: Wed, 14 Oct 2009 16:32:00 +0200
Organization: A noiseless patient Spider
Lines: 19
Message-ID: <hb4nd0$ck3$1@news.eternal-september.org>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Trace: news.eternal-september.org U2FsdGVkX192VFYohAGxorJGZ+N/3iSP9tT2oQa4pENWYAoUuLTx301W5/VIKAM5dCXiZ/nuTFpgH6F6Z4kynXTxbdFrn+Sc9eqqPVGG4XNdXRWQd+UrcOX95TfARAQuv6/G9BEepotoqCR2XxuDXFhr7xjJKgRv
X-Complaints-To: abuse@eternal-september.org
NNTP-Posting-Date: Wed, 14 Oct 2009 14:32:00 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-Auth-Sender: U2FsdGVkX18FDfUd/UibtOUI3m++urBOiI1550OyeHk5dgj2rMjRfA==
Cancel-Lock: sha1:97Iua9Lf1IkK0Sbcjr9rYu7VabM=
X-Priority: 3
X-MSMail-Priority: Normal
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!news-raspail.gip.net!news.gsl.net!gip.net!aotearoa.belnet.be!news.belnet.be!news-out1.kabelfoon.nl!newsfeed.kabelfoon.nl!xindi.nntp.kabelfoon.nl!feeder.news-service.com!feeder.erje.net!feeder.eternal-september.org!eternal-september.org!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28470
Article PK: 77712

I want to migrate a production database from one server (A) to another (B),
minimizing downtime.
This is my plan:

- While database A is running, dump database MAIN to "/dump/MAIN.dump"
- Copy the file MAIN.dump from server A to server B
- On B, load database MAIN from "/dump/MAIN.dump"
*** Start of downtime ***
- On A, set database in "dbo use only", kill all processes, set database in
"single user"
- On A, dump transaction MAIN to "/dump/MAIN.trans"
- Copy the file MAIN.trans from server A to server B
- On B, load transaction MAIN from "/dump/MAIN.trans"
- On B, online database MAIN
- Set applications to point to the new server
*** End of downtime ***

Is the plan correct? Am I forgetting anything?


"Mark A. Parsons" <iron_horse Posted on 2009-10-14 14:48:58.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Changing database serve, minimizing downtime
References: <hb4nd0$ck3$1@news.eternal-september.org>
In-Reply-To: <hb4nd0$ck3$1@news.eternal-september.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091012-0, 10/12/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad5e4da@forums-1-dub>
Date: 14 Oct 2009 07:48:58 -0700
X-Trace: forums-1-dub 1255531738 10.22.241.152 (14 Oct 2009 07:48:58 -0700)
X-Original-Trace: 14 Oct 2009 07:48:58 -0700, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28471
Article PK: 77713

I'd like to make sure no one is accessing server A once server B has been brought up ...

Kon T. Amusse wrote:
> I want to migrate a production database from one server (A) to another
> (B), minimizing downtime.
> This is my plan:
>
> - While database A is running, dump database MAIN to "/dump/MAIN.dump"
> - Copy the file MAIN.dump from server A to server B
> - On B, load database MAIN from "/dump/MAIN.dump"
> *** Start of downtime ***

- lock all logins but your own
!!!! make sure you don't lock your own login
!!!! if your login is also used by applications, setup a special
login to handle the the final backup and make sure you
lock your own login

- bounce server A to make sure all user connections have been killed and all transactions rolled back

> - On A, set database in "dbo use only", kill all processes, set database in "single user"
> - On A, dump transaction MAIN to "/dump/MAIN.trans"

- (re)sync various tables in the master databases (eg, syslogins, sysloginroles)
!!!! objective is to make sure you have the latest set of logins/passwords
on server B

- shutdown server A
!!!! objective is to make sure no one (including DBA/sa maintenance and
monitoring processes) is logging into server A once the switchover
has been performed.


> - Copy the file MAIN.trans from server A to server B
> - On B, load transaction MAIN from "/dump/MAIN.trans"
> - On B, online database MAIN
> - Set applications to point to the new server
> *** End of downtime ***
>
> Is the plan correct? Am I forgetting anything?

I'm also assuming:

- if using RepServer you have addressed the issue of (basically) rebuilding those parts of the replication system that
used to interact with server A and which now need to interact with server B

- all client interfaces/sql.ini files have been updated

- server B has the same name (@@servername) as server A (in case any applications/processes utilize @@servername)

- all maintenance and monitoring processes have been switched to server B


Kon T. Amusse Posted on 2009-10-14 15:46:31.0Z
From: "Kon T. Amusse" <kon.t@amusse.invalid>
Newsgroups: sybase.public.ase.general
Subject: Re: Changing database serve, minimizing downtime
Date: Wed, 14 Oct 2009 17:46:31 +0200
Organization: A noiseless patient Spider
Lines: 9
Message-ID: <hb4roo$hpc$1@news.eternal-september.org>
References: <hb4nd0$ck3$1@news.eternal-september.org> <4ad5e4da@forums-1-dub>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Trace: news.eternal-september.org U2FsdGVkX19hLv7AAJ2QgE5xY+N/3YJdHh0Kz3hJ2w000+3QB86Wd+x0Mubo9uhYE7VN/n+2AiSy7EvZS7PbZi99eGNm97KKR8YFpfDgaxWjLoazaf8ORxYnPvyQkwBDOlJHnTfnJe8REUCW9ox+67VQifXzlYAb
X-Complaints-To: abuse@eternal-september.org
NNTP-Posting-Date: Wed, 14 Oct 2009 15:46:32 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
In-Reply-To: <4ad5e4da@forums-1-dub>
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-Auth-Sender: U2FsdGVkX1+wKiCH/xSmjKINVXi+bnniQYsyzKUp+ktW3tsxqTTv2g==
Cancel-Lock: sha1:dfoX+GEgjcFB5S0IDiUSXk3VIg4=
X-Priority: 3
X-MSMail-Priority: Normal
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!news-raspail.gip.net!news.gsl.net!gip.net!aotearoa.belnet.be!news.belnet.be!plix.pl!newsfeed1.plix.pl!news-out1.kabelfoon.nl!newsfeed.kabelfoon.nl!bandi.nntp.kabelfoon.nl!feeder.erje.net!feeder.eternal-september.org!eternal-september.org!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28473
Article PK: 77714


"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4ad5e4da@forums-1-dub...
>
> - bounce server A to make sure all user connections have been killed and
> all transactions rolled back
>

What do you mean by "bounce server"?


"Mark A. Parsons" <iron_horse Posted on 2009-10-14 16:46:03.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Changing database serve, minimizing downtime
References: <hb4nd0$ck3$1@news.eternal-september.org> <4ad5e4da@forums-1-dub> <hb4roo$hpc$1@news.eternal-september.org>
In-Reply-To: <hb4roo$hpc$1@news.eternal-september.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091012-0, 10/12/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad6004b@forums-1-dub>
Date: 14 Oct 2009 09:46:03 -0700
X-Trace: forums-1-dub 1255538763 10.22.241.152 (14 Oct 2009 09:46:03 -0700)
X-Original-Trace: 14 Oct 2009 09:46:03 -0700, vip152.sybase.com
Lines: 11
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28474
Article PK: 77715

shutdown and restart

Kon T. Amusse wrote:
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in
> message news:4ad5e4da@forums-1-dub...
>>
>> - bounce server A to make sure all user connections have been killed
>> and all transactions rolled back
>>
>
> What do you mean by "bounce server"?


Kon T. Amusse Posted on 2009-10-15 13:08:10.0Z
From: "Kon T. Amusse" <kon.t@amusse.invalid>
Newsgroups: sybase.public.ase.general
Subject: Re: Changing database serve, minimizing downtime
Date: Thu, 15 Oct 2009 15:08:10 +0200
Organization: A noiseless patient Spider
Lines: 22
Message-ID: <hb76rr$3er$1@news.eternal-september.org>
References: <hb4nd0$ck3$1@news.eternal-september.org> <4ad5e4da@forums-1-dub> <hb4roo$hpc$1@news.eternal-september.org> <4ad6004b@forums-1-dub>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Trace: news.eternal-september.org U2FsdGVkX1/7ptosoMRTeVm8Z/iAq+tBP5KVByv/MXw4iKcc8YXFAIPgg2s8bVE0CBzJElx05m+q57kK0INUq7G7ZqQc12j21PmMW/P43vvOQJ9HV/qR7Q8P/Q68+xu1h3N8ZR+Q41nEfXvVuTcnu1tJYNsOvbPW
X-Complaints-To: abuse@eternal-september.org
NNTP-Posting-Date: Thu, 15 Oct 2009 13:08:11 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18005
In-Reply-To: <4ad6004b@forums-1-dub>
X-Newsreader: Microsoft Windows Mail 6.0.6002.18005
X-Auth-Sender: U2FsdGVkX18GL57DWF5kCdv+ydHFv7zk0b+ZBPv4H++cZZYLwSHpAw==
Cancel-Lock: sha1:TNswklKKB+j7n+aHzoWuwvI2oHs=
X-Priority: 3
X-MSMail-Priority: Normal
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!news-raspail.gip.net!news.gsl.net!gip.net!aotearoa.belnet.be!news.belnet.be!goblin1!goblin2!goblin.stu.neva.ru!feeder.erje.net!feeder.eternal-september.org!eternal-september.org!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28478
Article PK: 77720

Why should I bounce server then?
Once I set "dbo use only", no new user can login.
Then I kill all tasks.
Once I set "single user", I am sure there are no other users.
I consider a shutdown unnecessary here, am I missing something?
[btw, thanks for all the other considerations]

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4ad6004b@forums-1-dub...
> shutdown and restart
>
> Kon T. Amusse wrote:
>> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
>> news:4ad5e4da@forums-1-dub...
>>>
>>> - bounce server A to make sure all user connections have been killed and
>>> all transactions rolled back
>>>
>>
>> What do you mean by "bounce server"?


"Mark A. Parsons" <iron_horse Posted on 2009-10-15 13:52:50.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Changing database serve, minimizing downtime
References: <hb4nd0$ck3$1@news.eternal-september.org> <4ad5e4da@forums-1-dub> <hb4roo$hpc$1@news.eternal-september.org> <4ad6004b@forums-1-dub> <hb76rr$3er$1@news.eternal-september.org>
In-Reply-To: <hb76rr$3er$1@news.eternal-september.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091012-0, 10/12/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad72932$1@forums-1-dub>
Date: 15 Oct 2009 06:52:50 -0700
X-Trace: forums-1-dub 1255614770 10.22.241.152 (15 Oct 2009 06:52:50 -0700)
X-Original-Trace: 15 Oct 2009 06:52:50 -0700, vip152.sybase.com
Lines: 58
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28481
Article PK: 77721

Bouncing the dataserver is a general, catch-all step that addresses issues you haven't mentioned, eg,

- what happens if you can't kill a spid?

- what happens if someone else logs into the database while you've got it set to 'single user' or 'dbo use only' mode?
(this happens a lot more often than you'd believe!!)

- what happens if you have a phantom spid holding open a transaction?

What I didn't mention, and which you'd probably think is (also) paranoid, is shutting down server_A and bringing it up
on a different port while utilizing a new/separate interfaces file; perhaps even changing the name of server_A in the
interfaces file (and obviously using a new RUNSERVER file).

By bringing the dataserver up in *TRULY* standalone mode, no one ... local/remote applications, local/remote developers,
local/remote maintenance scripts, repserver DSI connections ... can access the dataserver and make any unwanted changes
to the data.

Obviously if an application/developer/maintenance-script/repserver-DSI-connection gets an error due to inability to
access the dataserver, this would tend to imply that someone forgot to switch said data modifier process to the new
dataserver ... it's better for them to error out than to continue making changes to the wrong database.

NOTE: I would also disable/hobble the old RUNSERVER file for the old dataserver so that no one can accidentally startup
the old server (and thus allow some process to connect to it).

-------------------

I've seen several of these 'simple moves' of a dataserver go bad because at some point someone failed to block out *ALL*
incoming data to the old dataserver. Net result is that they switch over to the new dataserver but end up leaving some
data on the old server ... or worse yet, they leave some process connecting to the old server.

Paranoid? Perhaps, but I'd rather be paranoid and insure that *NO* data modifications are made to the old dataserver
... than to have to explain how I let data get routed to 2 different dataservers ... not to mention the *BIG* hassles to
clean up said mess.

Kon T. Amusse wrote:
> Why should I bounce server then?
> Once I set "dbo use only", no new user can login.
> Then I kill all tasks.
> Once I set "single user", I am sure there are no other users.
> I consider a shutdown unnecessary here, am I missing something?
> [btw, thanks for all the other considerations]
>
>
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in
> message news:4ad6004b@forums-1-dub...
>> shutdown and restart
>>
>> Kon T. Amusse wrote:
>>> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in
>>> message news:4ad5e4da@forums-1-dub...
>>>>
>>>> - bounce server A to make sure all user connections have been killed
>>>> and all transactions rolled back
>>>>
>>>
>>> What do you mean by "bounce server"?
>


"Mark A. Parsons" <iron_horse Posted on 2009-10-14 14:52:43.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Changing database serve, minimizing downtime
References: <hb4nd0$ck3$1@news.eternal-september.org> <4ad5e4da@forums-1-dub>
In-Reply-To: <4ad5e4da@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 091012-0, 10/12/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad5e5bb$1@forums-1-dub>
Date: 14 Oct 2009 07:52:43 -0700
X-Trace: forums-1-dub 1255531963 10.22.241.152 (14 Oct 2009 07:52:43 -0700)
X-Original-Trace: 14 Oct 2009 07:52:43 -0700, vip152.sybase.com
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28472
Article PK: 77717

One more assumption ...

- you've run a successful test of the dump-n-load-n-online steps to make sure they work and that the database(s) on
server B are accessible by test copies of all end-user/maintenance/monitoring applications; this is especially important
if doing a cross-platform dump-n-load where you may need to perform additional steps beyond the 'online database' command

Mark A. Parsons wrote:
> I'd like to make sure no one is accessing server A once server B has
> been brought up ...
>
>
> Kon T. Amusse wrote:
>> I want to migrate a production database from one server (A) to another
>> (B), minimizing downtime.
>> This is my plan:
>>
>> - While database A is running, dump database MAIN to "/dump/MAIN.dump"
>> - Copy the file MAIN.dump from server A to server B
>> - On B, load database MAIN from "/dump/MAIN.dump"
>> *** Start of downtime ***
>
> - lock all logins but your own
> !!!! make sure you don't lock your own login
> !!!! if your login is also used by applications, setup a special
> login to handle the the final backup and make sure you
> lock your own login
>
> - bounce server A to make sure all user connections have been killed and
> all transactions rolled back
>
>> - On A, set database in "dbo use only", kill all processes, set
>> database in "single user"
>> - On A, dump transaction MAIN to "/dump/MAIN.trans"
>
> - (re)sync various tables in the master databases (eg, syslogins,
> sysloginroles)
> !!!! objective is to make sure you have the latest set of
> logins/passwords
> on server B
>
> - shutdown server A
> !!!! objective is to make sure no one (including DBA/sa maintenance and
> monitoring processes) is logging into server A once the switchover
> has been performed.
>
>
>> - Copy the file MAIN.trans from server A to server B
>> - On B, load transaction MAIN from "/dump/MAIN.trans"
>> - On B, online database MAIN
>> - Set applications to point to the new server
>> *** End of downtime ***
>>
>> Is the plan correct? Am I forgetting anything?
>
> I'm also assuming:
>
> - if using RepServer you have addressed the issue of (basically)
> rebuilding those parts of the replication system that used to interact
> with server A and which now need to interact with server B
>
> - all client interfaces/sql.ini files have been updated
>
> - server B has the same name (@@servername) as server A (in case any
> applications/processes utilize @@servername)
>
> - all maintenance and monitoring processes have been switched to server B