Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Hello,
Is it possible in SQL Anywhere 12 to temporarily disable connections to SA12 for non-DBA users (without simply deleting users)? The goal is to disable connections other than the one who is unloading/reloading database.

asked 15 Mar '12, 08:59

Arthoor's gravatar image

Arthoor
1.3k355266
accept rate: 11%

edited 22 May '13, 07:37

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297


The easiest way to do this is

  1. install a login procedure ( see this question or this question or the docs ) that selectively disables new connection - your condition within the login procedure will need to be revised (from what is posted in the pages that I have listed) to suit your requirements, and then

  2. drop all of the users that you don't want connected while you do the operation

When you are finished your unload/reload you will want to reenable connections in your login procedure.

permanent link

answered 15 Mar '12, 09:21

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

edited 15 Mar '12, 09:22

Comment Text Removed

Thank you all for the answers. This approach seems to be the most applicable for us.

(16 Mar '12, 07:15) Arthoor

Confine the FAQ, that deals with a similar problem - though I do not know what exactly do you mean by "disable connections" without dropping them?

How to get full control over a SQL Anywhere server?


Basically, you will need to call

call sa_server_option('ConnsDisabled', 'YES')

or

call sa_server_option('ConnsDisabledForDB', 'YES')

to disable new connections temporarily to the server (or database), and will have to drop existing connections (I don't think there's a way to simply "disable" them - however, you might set their priority to "Background".)

permanent link

answered 15 Mar '12, 09:11

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

edited 15 Mar '12, 09:17

I had in mind "without simply deleting users from the database".

(15 Mar '12, 09:23) Arthoor
Replies hidden

Ah, I should have read better...then I guess the cited solutions are "simpler/better" than than approach:)

(15 Mar '12, 09:30) Volker Barth
permanent link

answered 15 Mar '12, 09:21

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

As you state that the requirement to have just an exclusive connection is during unload/reload, I do assume that you are about to rebuild or migrate a big database, and you cannot afford to have the database unavailable for that timespan. (I do assume that as it is much easier to simply take down the database, do the rebuild/migration, and make the new database available again.)

If my assumptions are true, a different approach which doesn't need to explicitly disconnecting users, is as following:

  • Take a full image backup of the database (with renaming the log) while the database is running as usual.
  • Make the rebuild/migration with the backup (or a copy of that) - during that timespan, users can still work, and their operations will get logged in the new transaction log.
  • Stop the old database, take the current transaction log and use DBTRAN to translate the log, so you get a list of all those transaction that have been done in the meantime, and apply that to the new database. - That's the (hopefully very short) time when there's no database available for your users.
  • Make the new database available for your users.
permanent link

answered 16 Mar '12, 17:17

Volker%20Barth's gravatar image

Volker Barth
40.2k361550822
accept rate: 34%

I've already thought about such approach of migration after this topic.
This time the goal was just to find out the easiest or fastest way (or ways) to disable other connections. That can be used not only when unloading/reloading but also when validating databases as I prefer using Sybase Central or SQL statements instead of command line tools.

(19 Mar '12, 02:12) Arthoor
Replies hidden

FWIW, the maintenance plans one can create with Sybase Central can be used to do scheduled validation (by creating according events), and that does include the disabling of new connections (but does not drop existing ones)... just as a hint, if you prefer GUI-based solutions:)

(19 Mar '12, 04:54) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×159
×4

question asked: 15 Mar '12, 08:59

question was seen: 5,483 times

last updated: 22 May '13, 07:37