Concurrency Issue On A Single Database User For An Online Read/write Application

Aug 3, 2006

Hi to all DBAs,

I would like to ask if there will be a write and concurrency issue if i would create an online application with just one user connecting to the database, just like most open source php/mysql that can be downloaded

i.e
Setup 1. I will grant a single user that will connect to a database and will be set to a config.inc.php file and then create a table users (userid, username, password) and this table will be used for the application authentication and access control.

Setup 2. or is it much better to grant users that will connect to the database and have the table users for access control of the application?

to further illustrate my query:

Setup 1 would be:
###################Table: Users
user a@ipadd --> db1 --> userid mary --> myOnlineApp
user a@ipadd --> db1 --> userid john --> myOnlineApp
user a@ipadd --> db1 --> userid paul --> myOnlineApp


and Setup 2 would be:
#####################Table: Users
user mary@ipadd --> db1 --> userid mary --> myOnlineApp
user john@ipadd --> db1 --> userid john --> myOnlineApp
user paul@ipadd --> db1 --> userid paul --> myOnlineApp

will Setup 1 be enough if I will use SQL Server as database?

or Setup 2 is better user/database architecture?

This application will be online enrollment for a school with 16,000 students

very much appreciated for the feedbacks and suggestions

^_^x

View 3 Replies


ADVERTISEMENT

Read Only Database Restore Bringing Database In Single User Mode?

May 9, 2012

I'm taking a database(read-only) backup from one server and restoring it on other server. As soon as restore is done it is bringing database into single-user read-only mode.

why it is bringing the database into single user mode ?

View 1 Replies View Related

Recovery :: Read-only Routing With Single Application Connection?

Nov 30, 2015

My application supports a single database connection and in the app console I can produce reports. If I include the app database in an AlwaysOn availability group with a read-intent replica will SQL automatically route the “selects” to that second instance thus offloading my application’s reporting activities or I need a separate db connection (maybe from a reporting app or cli) with a connection specifying read-only intent?

View 4 Replies View Related

Transact SQL :: Making Server Database Read / Write From Read Only

Jan 12, 2012

i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.

View 11 Replies View Related

Reset Database Files From Read-only To Read-write

Jan 18, 2008

I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:

Server: Msg 3415, Level 16, State 2, Line 1
Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.

What command(s) are needed to make these files read_write?

thanks

View 7 Replies View Related

What Permission I Need To Grant To A User If He Need To Read Or Write A Link Server Tables

May 31, 2007

hello,



What role or system privilege do I need to grant to a user if he need to read the data from a table which is in a link server object? where I can find the document about these commands.



Thanks

View 1 Replies View Related

Transact SQL :: Send Changed Record Of Online Database To Application

Apr 23, 2015

I am programming an online game (such as chess) which two players can play together online.

Each one of the players should have installed the game application (developed by C#) on their computers.

When a player do some action, a record of a database (SQL Server 2012) which has been placed on the internet will be changed.

My need: when a record of this online database changes, whole the record send to both players' game application immediately.

View 3 Replies View Related

Application Locks Up When Trying To Write To Db After A Replication Where I Create The Database.

Jan 3, 2008

Hello all,

When I create a new database and replicate to it using BeginMonitoredBackgroundSync :







Code Block

public void BeginMonitoredBackgroundSync(string User)
{
CreateReplicationInstance(User);
repl.BeginSynchronize(
OnSimplifiedSynchronizeComplete,
SqlCeReplication_OnStartTableUpload,
SqlCeReplication_OnStartTableDownload,
SqlCeReplication_OnSynchronization,
repl);
}




private void CreateReplicationInstance(string User)
{
repl = new SqlCeReplication();
string host = repl.HostName;
repl.HostName = User;
string dbFilePath = "";


dbFilePath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) +
"\" + repl.HostName + ".sdf";
string myConnectionString = string.Format("Data Source = {0};PWD = {1}", dbFilePath, sqlSettings.Items["SqlPassword"]);

repl.InternetUrl = dynamicsReplicationSettings.ReplicationSettingsItems["InternetUrl"];
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.Publisher = dynamicsReplicationSettings.ReplicationSettingsItems["Publisher"];
repl.PublisherDatabase = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherDatabase"];
repl.PublisherLogin = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherLogin"];
repl.PublisherPassword = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherPassword"];
repl.Publication = dynamicsReplicationSettings.ReplicationSettingsItems["Publication"];

repl.Subscriber = "RemoteSubscription" + repl.HostName;
repl.SubscriberConnectionString = myConnectionString;

repl.ConnectionRetryTimeout = 120;
repl.LoginTimeout = 120;
repl.CompressionLevel = 6;

if (File.Exists(dbFilePath))
{
FileInfo info = new FileInfo(dbFilePath);
if (info.Length <= 20480)
{
File.Delete(dbFilePath);
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
}
else
{
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}

primeConnection();

}
After the replication finishes, I dispose the replication object like so:





Code Block

void OnAsyncSynchronizeComplete(IAsyncResult asyncResult)
{

try
{
repl.EndSynchronize(asyncResult);
if (repl != null)
{
repl.Dispose();
repl = null;

}
if (ReplicationComplete != null) ReplicationComplete(this, true);
}
catch(Exception ex)
{
if (ReplicationComplete != null) ReplicationComplete(ex, false);
}

}

Then later, if I try to update, insert or delete to the database, the application will hang. I can read from it, but I cannot write. If I close the application down and open it back up without replicating, I will not get any lockups. It also will not hang up after any replications prior to the create replication. I think I am doing something wrong in the initial replication that is holding on to some connection to the DB causing it to lock up. Has anyone seen anything like this before?

View 1 Replies View Related

SQL Server 2012 :: Determine Read / Write Frequency On Database Table

Oct 6, 2015

How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.

View 2 Replies View Related

Server Error In '/' Application. Database Cannot Be Upgraded Because It Is Read Only??

Dec 5, 2005

 Server Error in '/' Application.


Database 'C:INETPUBDEMO.TRUTHSTONE.COMAPP_DATAASPNETDB.MDF' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.Cannot open user default database. Login failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
Hi,
Strange one.  I have copied my working web-site to the production server and get this message.
Tried copying new set of database files and reset the server to ensure no applications could be accessing the database files.  Still get the same error.
Works fine on my Visual Studio VWD Express version.
 
Mitch
PS I am not trying to upgrade the database, just use it!
 

View 1 Replies View Related

SQL 2012 :: Identify Whether Files Are In Read / Write Or Read Only

Mar 24, 2015

How to identify whether the files are in read write or read only?

View 1 Replies View Related

Recovery :: Switch (Standby / Read-Only) DB To Be Read And Write

Aug 26, 2015

I'm trying to do Sharepoint DR with Log Shipping and every thing configured except one thing which is switch the WSS_Content (Standby /Read-Only) DB to be ready and Write. 

I tried from

GUI or ALTER DATABASE [WSS_Content] SET
READ_WRITE WITH NO_WAIT

but I received the below error: 

Database WSS_Content is in Warm Standby 

View 9 Replies View Related

Set Database Back To Multi User From Single User

Feb 27, 2008

I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.

While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.

I cannot now reset that database to Multi User, even by executing the query

exec sp_dboption 'database_name', 'single user', ''FALSE'

again by using the Management Express Tool.

Can someone please help, in plain english???? Thanks

View 5 Replies View Related

SQL 2012 :: Change Read-only And Read-write

Aug 15, 2014

Can a user of db owner role of a database change the databse option to read only and read-write?If not what permission I need to grant to the user?

View 1 Replies View Related

Change DB From Read-Only To Online

Dec 17, 2007

Hi,

I restored my database and and have now decided that we do not need the Transaction Logs. I have forgotten the SQL Syntax to change the database to online.

Please could sommeone assist?

David Caddick

View 1 Replies View Related

My Application Throws Error When I Have Single ( ' ) Quote In My String (SQL Database)

Aug 29, 2007

my asp.net application communicate with SQL database, but when I have single quote ( ' ) in my string then it throws error. and it does not insert that string in database.  How can I solve this problem . or give me some suggestions  on this issue.
 thank you
maxmax

View 3 Replies View Related

Adding A User To A Read-Only Database

Aug 1, 2007

Hey guys,

I currently have a sql server 2000 sp4 instance with a read-only db on it. We get tlogs shipped from an outside vendor and keep a copy of the production database in house for reporting purposes. The database is in read-only mode because we apply new tlogs daily. Recently we did a full recover and applied 2 tlogs.

The issue:
There is a user YYY in the database and at the server level, but they aren't linked. So I do what I usually do and run the following command:

exec sp_change_users_login 'Update_One', 'YYY','YYY'

Except for I get the following error:

Server: Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 109
Could not run BEGIN TRANSACTION in database 'ZZZ' because the database is read-only.

Now this error makes sense and I understand why, so my question is how do I fix the users not being linked?

View 1 Replies View Related

Online/Offline Application That Must Synchronize With SQL 2005

Mar 10, 2007

Hi !

I must developp a WPF Application with online and offline capabilities! First I think to use XML file on the local application and transfer these XML files to a webservice that will synchronize them with the SQL 2005 Server

BUT

I read about "Replication"... and I think it will be much simpler to implement!!!

Do you think it is a good idea to have a "local" SQL Express database and replicate it (when connection available is) with the principal database that will run a standard SQL 2005 version!

Do you have another suggestion to make such an application??

Thanks for help!!!

PlaTyPuS

PS: when the sql express solution a good idea is, does it give a simple solution to programm an automatic synchronization every hour?

View 3 Replies View Related

Using A Windows Application To Connect To An Online Access DB.

Aug 25, 2007

Hi all,

I'm working on a project where I have to create a password protected form for teachers to submit reports about students.
Originally I was going to use asp.net to create the form, but unfortunately the server I'm working with will not be granted asp functionality in the allotted timeframe.

To solve this issue I have another idea. So I will ask a few questions to help with research before I begin coding.

Is it possible to create a windows application that can connect to an access database hosted online? The windows app will need to be be able to update, insert, and select information from the database.

There is only going to be about 30-40 people using this windows application once or twice a week.

Thus I envision taking a password protected access database and dropping it onto a server.
The windows application will have the online location of the database embeded into the code. (connection string)
From there on out coding for the windows application should be the exact same as if the database was hosted locally on the computer, correct?

Also,
1. What is a good way to check and make sure the computer is connected to the internet before my application tries to access the database?

2. Are there any drastic security issues with this process that I should think about. The database will only be password proected, and the windows application will take a username/password from the user and check it against a table before they are able to submit info to the database.

I just need to be pointed in the right direction for this project.
Thanks.

View 1 Replies View Related

Batch File - Read From One Table And Write To Another Database And Table

Nov 17, 2011

Any easy way for a batch file or automated process to read from one db and table and what ever entry is missing out of another database + table it writes those missing entries to.

This is a simple table in one db that is filled with usernames, I want to see if there are missing usernames in another db and table and write those entries.

db1.usr_table.usr_name = jdoenew
If jdoenew is missing in the 2nd db I will need to write entries like:
db1.usr_table.usr_name = jdoenew
db1.usr_table.password = tmppassword
db1.usr_table.active = 1

View 1 Replies View Related

Database Marked As Single User Pls Help

Apr 19, 2004

Hi All,
Sql Server 7

I have database called ecatalog

i have a scheduled job which shrinks the database every day once at 12 am

today the job got failed

In the view Job history its showing the below contents
-------------------------------------------------------
Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error
924) DBCC execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE 01000] (Message 2528). The step failed.


And in Application Log of event viewer its showing the below contents
------------------------------------------------------------------------------
The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The
local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database
- ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed.
The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..


I ran the job manually it worked fine

but now when i go and see the database ecatalog in my Enterprise Manager
it is showing ecatalog(Single User)
What is the meaning of this, will this make any problem to my database

Please help me in this.

Waiting for Reply

Adil

View 4 Replies View Related

Multi-user Concurrency

May 30, 2006

Does anyone have links to various strategies for handling multi-user concurrency issues when updating data in SQL Server (2000 or 2005), either in the database (stored procedures) or via code.
I've seen a couple such as:
Check individual user updated columns to see if the database columns have changed and if not apply the updated columns (either individual updates or constructing a dynamic SQL statement).
Check all the fields for any change and if no change, update all the fields.
Check a version field for change and if no change, update all the fields.
What I haven't seen is complete solutions such as for the first one, ensuring the record is locked and can't be changed while checking for concurrency and ultimately updating the data.  For the second one, how to raise an error if there was a problem, or for the third one how to ensure the record is locked between the version check and the update.
Even pieces of solutions are welcome (locking records, testing for change, etc).  Just want to compare and contrast various methods as I create an infrastructure for 2005.
 
Thanks,
LarryC

View 2 Replies View Related

Database Stuck In Single User Mode

Apr 21, 2008

I have a database that got set into single user mode. Unsure the reason or who might opened it. I am trying to reset it, but I am having problems. I am trying the following from http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx to kill the connections. So I can then switch it back to multiple users.
DECLARE @DatabaseName nvarchar(50)DECLARE @SPId intSET @DatabaseName = N'Works'DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPIdOPEN my_cursor FETCH NEXT FROM my_cursor INTO @SPIdWHILE @@FETCH_STATUS = 0BEGINKILL @SPIdFETCH NEXT FROM my_cursor INTO @SPIdENDCLOSE my_cursor DEALLOCATE my_cursor
Which gives me the following error message when ran.
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@SPId'.

View 1 Replies View Related

Database Setting Itself To Single User Mode

Mar 19, 2001

Our company has several SQL Server 7 databases. One of them has now on two occassions, set itself to 'Single User' mode. I am completely baffled as to what can be causing it.

There's no jobs set up to do it.
There's no stored procedures programmed to do it.
The SQL Server Log shows that a DBCC CHECKDB ran at 18:00:37.73 and that users attempted to and couldn't log in from 18:01:01.95 onwards.
There is a maintenance plan set to run every two hours that does a backup, integrity checks, so it fired the CHECKDB.

Any ideas please?

Thanks.

View 2 Replies View Related

Recovering Database In Single User Mode

Jan 5, 2001

while recovering it prompts a message 'the system administrator must have exclusive use of database to run the restore operation'.
now,how to do it?
how to make database to use exclusively when it is connected to an web based aplication.
thanks.

View 1 Replies View Related

Putting The Master Database In Single User

Nov 18, 2002

How do you put the master database in single user mode if you want to restore it. the options in the permissions are greyed out? Thanks

View 2 Replies View Related

Database In Single User Mode After Backup

Feb 7, 2001

Hi!

I have a problem with databases that are left in single user mode after transaction log backup.
I have a database maintenance plan job that backs up the transaction log and checks data and index linkage every hour.
Sometimes the job fails and when I look in the report file it says that it has tred to put the database in single user mode and failed because the database is in use and then in the next step it says that it cannot perform the operation because there already is a user in the database.

Why is the database put in single user mode?
What can I do to avoid finding my databases in single user mode?


Below is a part of the report file:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 15089: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot change the 'single user' option of a database while another user is in the database.
[1] Database BV Produktion: Check Data and Index Linkage...

** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database BV Projektering: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 924: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'BV Projektering' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]Only the owner of object 'dms_user' can run DBCC CHECKTABLE on it.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'BV Projektering' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'BV Projektering' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]Only the owner of object 'dms_user' can run DBCC CHECKTABLE on it.
[Microsoft][ODBC SQL Server Driver][SQL Server]Database 'BV Projektering' is already open and can only have one user at a time.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
** Execution Time: 0 hrs, 0 mins, 9 secs **

View 1 Replies View Related

Backup Of SQL Database In Single User Mode ?

Oct 30, 2007

Hello ,

Is it possible to do the backup of SQL server instance in a Single user mode ?
What is the recommended practice ?

Thanks in advance,
atv

View 5 Replies View Related

DB Engine :: Database In Single User Mode

Oct 22, 2015

I put the database in emergency mode, then I executed Checkdb, it shows no errors. The database status is still EMERGENCY and when I try to bring it online, it says he database is in single-user mode, and a user is currently connected to it. There is one processes which is accessing the database, i try to kill it, but it is not going. I am not able to set database to multi user.

View 7 Replies View Related

What Are The Situations To Keep Database In Single User Mode

May 6, 2015

I know what single user mode does but my doubt is at what situations we will go for this? What operations to perform ? we will keep the database in single user mode.

View 7 Replies View Related

SQL Server/Access 97 User Concurrency

Feb 6, 2004

I'm currently looking at a multi-user app that has an Access 97 frontend and an SQL2K backend. Proposed changes to the system mean that the user concurrency count is likely to increase dramatically, and I'm wanting to know if there are any limits to the concurrency for this architecture.

Any experiences/help appreciated.

View 2 Replies View Related

Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed

Dec 7, 2006

This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee

View 3 Replies View Related

What Causes A SQL Server 7 Database To Swithc To Single-user Mode

Apr 3, 2002

We have a database that is somehow switching to single-user mode every few days. We suspect that the client application (Access 97) written by a non-developer may be doing something funny.

Has anybody encountered such a problem?
How is this possible?

Thanks in advance for any help or pointers.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved