cancel
Showing results for 
Search instead for 
Did you mean: 

How to temporarily disable connections to SA12 for non-DBA users?

6,079

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.

Accepted Solutions (1)

Accepted Solutions (1)

MarkCulp
Participant

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.

0 Kudos

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

Answers (3)

Answers (3)

VolkerBarth
Contributor

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".)

0 Kudos

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

VolkerBarth
Contributor
0 Kudos

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

MCMartin
Participant
VolkerBarth
Contributor
0 Kudos

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.
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

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:)