schedule job to set database to single user mode

Task Summary:
I have a remote user to sending records every 5 minutes by DTS package.
I think the best way to handle this is to create a job that will up the
database
into single user mode, set the remote user to the single user.
allow the job to run 10 to 15 minutes then take it out of single user mode.
Task Break down:
1:
Put the database into single user mode.
assign myself as the user.
(How do I define which user has access?)
2:
Backup the database
unassign myself as the single user
assign remote user as the single user
3:
Scheduled the job to execute for 15 minutes
4:
take database out of single user mode
5:
Schedule job to execute evey 6 hours.
Hi
"mj" wrote:

> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
You can not assign another user to be the single user in single user mode!
If you are not connected to the database when it is set in single user mode
then potentially anyone could get in and be that single user.
I am not sure why the remote user is send things every 5 minutes and you
will only want him to send something for a 15 minute period every 6 hours.
What may be a better scenario is that every 6 hours you fetch the information
from the remote location and then processes it in one batch, then the process
is controlled by the server. A different approach you be to have a second
database that the user updates (local or remote) and then use replication to
merge the differences. With SQL 2005 service broker may possibly be useful.
John
|||What is the justification for this attempt to "lock the database into
single-user mode for a specific user"?
TheSQLGuru
President
Indicium Resources, Inc.
"mj" <mj@.discussions.microsoft.com> wrote in message
news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
> Task Summary:
> I have a remote user to sending records every 5 minutes by DTS package.
> I think the best way to handle this is to create a job that will up the
> database
> into single user mode, set the remote user to the single user.
> allow the job to run 10 to 15 minutes then take it out of single user
> mode.
>
> Task Break down:
> 1:
> Put the database into single user mode.
> assign myself as the user.
> (How do I define which user has access?)
> 2:
> Backup the database
> unassign myself as the single user
> assign remote user as the single user
> 3:
> Scheduled the job to execute for 15 minutes
> 4:
> take database out of single user mode
> 5:
> Schedule job to execute evey 6 hours.
>
>
|||Thanks John and SQLGuRu for the interest;
More Background Details:
The remote user has developed an extensive DTS package.
When the transformations have been completed, then the transformed
data is sent to a central server.
For this reason I can not just go get the data.
To ensure the migration is completed without interuption, I need to put the
server into single user mode.
To preserve the data prior to the migration, I need to perform a backup.
My current solution looks like this:
Procedure MySolution AS
Alter database Set Single_User
Perform back up of database
perform backup of logs
alter database Set Multi_User
End Procedure
Create Job
Call Procedure Mysolution
Schedule Job 6hrs
"TheSQLGuru" wrote:

> What is the justification for this attempt to "lock the database into
> single-user mode for a specific user"?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:137E0713-D3ED-4915-95CD-E4CD4D766A57@.microsoft.com...
>
>
|||Hi
"mj" wrote:
[vbcol=seagreen]
> Thanks John and SQLGuRu for the interest;
>
> More Background Details:
> The remote user has developed an extensive DTS package.
> When the transformations have been completed, then the transformed
> data is sent to a central server.
> For this reason I can not just go get the data.
> To ensure the migration is completed without interuption, I need to put the
> server into single user mode.
> To preserve the data prior to the migration, I need to perform a backup.
> My current solution looks like this:
> ----
> Procedure MySolution AS
> Alter database Set Single_User
> Perform back up of database
> perform backup of logs
> alter database Set Multi_User
> End Procedure
> Create Job
> Call Procedure Mysolution
> Schedule Job 6hrs
> ----
>
> "TheSQLGuru" wrote:
I can only assume that this is not a system with users, as a period of
unavailability every 6 hours is not the sort of service level that would be
classed as acceptable!!
Who do you need to load this every 6 hours?
Have you considered replication?
If you have a proper sequence of log backups you may not need to do the full
backup if you accept that you will need to restore the last full backup and
roll forward to return to the state prior to your process running.
Alternatively a differential backup would remove the need to restore some of
the log backups.
If you set the database into single user mode, then you can not guarantee
getting access to the database (for that single user) in subsequent job steps.
What do you do with the files that the process has transferred?
John