Are Seperate Databases Betters?
Jan 4, 2004
Hi. I have been talking with some developers who have built a hosted application supporting multiple customers. Their database approach is to create a new, dedicated database (same schema each time) for every customer that signs-up.
This approach is contrary to typical hosted DB designs that I have delt with -- that is, a single database holding multiple customer information rather than a unique database for each customer.
Does the improved security of a dedicated database out-weigh the additional maintenance requirements?
If anyone has some objective thoughts on this topic, I'd love to hear them.
Thanks,
Bill
View 9 Replies
ADVERTISEMENT
May 22, 2008
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
View 5 Replies
View Related
Oct 15, 2007
We are looking at expanding our web application (C# and Sql Server). The module we are going to be adding will be far more processor intense (plotting and modeling) than any of the current code.
I need to know what the best design for this add on would be.
Would it be better to
a) Keep the new module as part of the core db (entire application on 1 database) and use loading balncing between 2 (or more) servers to handle the huge increase in demand?
b) Create the new module as a seperate database that runs on a seperate server?
If B is the best option (I am thinking it is not) is there a way to write easy and efficient queries and views of the 2 databases as 1.
Thanks
Justin
Justin Bezanson
www.justinbezanson.com
www.geekdaily.net - web development and technology blog and news
www.offsidegames.com - free online flash games
www.thrufare.com - free proxy website
View 4 Replies
View Related
Nov 28, 2007
I've got a customer who thinks their data should be sepeated and reside on two seperate databases in case one is compromised. Now I've never heard of anyone doing this and would like comments from the users here as to why this should/(shoult not) be done.
I'm assuming that they want to keep their general info e.g. name and address on one database and keep other (medical info) on the other db.
None of our db's are outside of the firewalls so to me this makes no sense but I would like feedback.
thanks.
View 3 Replies
View Related
May 9, 2006
I have a main database...for this large Web site...and Im wondering
what would be the PROS and CONS of using another database (located on
the same, or on another SQL Server). Im just thinking this would be
good incase we ever needed to take some load off one of the servers.
Also, we will be integrating Community Server into this Web site. Of
course you know CS adds its own database objects which crowd up our
main database objects.
We were thinking of giving CS its own database also; bad practice, or....it doesn't matter much?
Thank you
View 3 Replies
View Related
Mar 20, 2008
Hello,
I have two databases in sql server. I'll call them DB1 and DB2. I have a table in DB2 that needs to form a relationship with a table in DB1. When I attempt to add a relationship I only see tables in DB2. Can this be done?
Thanks,
Mark
View 10 Replies
View Related
Aug 29, 2007
Hello everyone,
Here's my situation...
I'm running a web service which involves 51 seperate servers and databases.
There are fifty licensee servers (One for each US state) and one corporate server.
Each night I need to upload sales and membership data from the licensee's databases to the corporate database to compile reports.
The application platform I'm using is ASP.NET 2.0 and the the database is SQL2005 express.
I want this process to be run automatically, so I believe it's a scheduled windows service I need to setup up in .NET to make the data transfers.
If anyone has already set something up like this, or knows the steps to take? I would love to have your input.
Thanks in advance,
Robert
View 5 Replies
View Related
Jan 16, 2004
Hi All,
I have got MSSQL 2000 set up on a machine in my rack at my local telehouse, and a web server set up at home on an ADSL line.
Both servers can see (ping) eachother fine , so you can rule out any kind of connectivity issues straight away, but when i try to get my forum to connect to the mssql database using the correct credentials it just fails saying that the credentials are incorrect ot the server does not exist.
I also installed an SQL database tool on my web server (Shusheng SQL Tool) and attempted to connect to my SQL server using that tool, and got the following message: '[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.'
The server is currently using mixed mode authentication (SQL/Windows) and has both TCP/IP and Named pipes enabled.
Is there some kind of 'Enable remote connections' option in SQL? I need to be able to allow connections to my SQL server from any system, anywhere...
Any ideas?
View 1 Replies
View Related
Oct 25, 2004
Hi,
Why we allocate .mdf and .ldf on seperate drives?
Please tell me a proper logical reason behind it.
View 2 Replies
View Related
Dec 17, 2007
MERRY CHRISTMAS EVERYONE :)
I need to update a table on our Test Server which is GCSQLTEST, with another table thats on our live server GCSQL. How would I go about doing that in a stored procedure??
CREATE PROCEDURE [InsertRevised_MainTable]
AS
INSERT INTO dbo.RevisedMainTable
([IR Number], [Date], [I/RDocument], [Violation Type])
SELECT [Incident Report No], [Date], [I/RDocument], TypeOfIncident
FROM dbo.RevisedMainTable
WHERE NOT EXISTS (SELECT * FROM dbo.RevisedMainTable
WHERE [IR Number] = [IR Number])
View 3 Replies
View Related
Jan 3, 2008
Dear All,
i've a string to pass as a parametre to a procedure.
like
create preocedure myproc(@EMPID VARCHAR(50),'abc,def,ghi,jkl')
...
end
i need the output like this
1 abc
2 def
3 ghi
4 jkl
.....
how can i do that?
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 7 Replies
View Related
Apr 14, 2008
Hello ALL,
I want to seperate a FULNAME into First name, last name and middle name using SSIS. Provided "," Seperator
u can take example
John first name,mathew middle name,
smith last name
Please give me the command how to do it.
Thank YOu
View 3 Replies
View Related
Apr 15, 2008
I have twon names in one row and in the same column.
Column Name
John Baker, Sarah Lynn
I want to seperate them using Derived Column like. First part before COMMA,
SECOND PART after Comma. into two diffrent directions. and later Union.
What is the command to extract the first name and second name.
Please le t me know
View 1 Replies
View Related
Jan 14, 2008
I have a field that contains values such as 8ft , 7ft, 18ft
I have a report in reporting services that shows this:
Before Restock After Restock
Date 1/12/2008 8ft 9ft
1/13/2008 10ft 7ft
1/14/2008 5ft 4ft
I want to create a subquery that grabs the before restock and figures out if it sheds the "ft" part of the value, and then put in a where before restock > (greater than) After Restock.
Is there a an easy way to do this?
View 9 Replies
View Related
May 10, 2008
I have 4 tables with the respective amount of records
1) 6755
2) 2021
3) 2021
4) 355
They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?
View 2 Replies
View Related
Sep 22, 2000
Hi all -
Quick question... I want to move all my non-clustered indexes to their own seperate drive array. How would I accomplish this?
Do I just add the filename at the end of the statement like you do in Oracle? (EXAMPLE: CREATE INDEX IDX_Cls_Code on dbo.Class(Code) on secondary.ndf)
If this isn't correct, can someone please post the correct syntax?
Thanks!
Rich
View 1 Replies
View Related
Jul 8, 1999
I have been asked to move the indexes on our membership database tables to seperate partitions on the server. This is a new concept to me and thought I could use some advice on how to go about doing it.
Thanks in advance.
Brad Keck
View 2 Replies
View Related
Nov 11, 2007
Hi,
I'm struggling with this. I'd like to perform a joined query
from two or more tables and take the same field from several
rows of one table into seperate fields of a single row in a new table.
Like this:
table 1
uidunameuloc
1mehere
1methere
2youhere
2 youthere
table 2
uidulocuparam#uparamval
1here1a
1here2b
1here3c
1there1d
1there2e
1there3f
2here1g
2here2h
2here3i
2there1j
2there2k
2there3l
result in table 3
uidunameuloc uparval1 uparval2 uparval3
1mehere a b c
1methere d e f
2youhere g h i
2 youthere j k l
uparam# field in table 2 always the same sequence
Any ideas????
Thanks...
View 2 Replies
View Related
Nov 15, 2004
Hey all, Here is what I would like to do.. if any can give some advice or direction with this i would appreciate it.
We have a "master database" (MS SQL) at our main office. Then a smaller office with only a dial-up connection. Can I have my db application write all the data input to a local DB. Then write something to connect with the master DB, through a Terminal server, etc., and update it with the data from the other smaller db?
View 1 Replies
View Related
Jul 20, 2005
Does anyone have any idea how to syncronise two SQL identical tables acrossa network, between servers?We want to run our website from a SQL tables on the same server, but set upso that if the main SQL tables across the network change, or the tables onthe web server change, the two seperate servers willsyncronise/update/trigger updates to the tables on each other?We are running SQL server 2000.AHAJohn
View 2 Replies
View Related
Apr 7, 2008
Recently I was stumped on a problem where I was granting permissions to a user, from within a script that was creating a stored procedure. Then I would check the permissions on the procedure, but the permissions were empty. It turned out that I was missing a "go" statement to seperate the create procedure statement from the grant statement. So that got me to thinking about what other kids of statements must be seperated into seperate batches ? I would thik that anything that is being created must be seperated from any statements that grant or alter permissions, because the items would need to exist first.
comments ?
View 2 Replies
View Related
Nov 9, 2007
We have a table with an indesx that is varchar(7). It contains up to 5 numbers and 2 characters to denote batches. IE. 100a
105zz
1c
2
10001w
In an Access gui I need to be able to sort this field numericaly so each number shows up in its correct position. IE.
1c
2
100a
105zz
10001w
In order to accomplish this, I created a seperate field on the table named Num as varchar(5). I would like to create a trigger that updates this field any time a new batch number is entered. This way I can have my queries order by Num when returning the recordsets to the GUI.
Below is the code that I thought would work, but it is giving me an error near the keyword BEGIN after the IF(ISNUMERIC) statement. I can't seem to find an error with this code, but I thought maybe a thousand fresh sets of eyes could. Any help would be greatly appreciated. Also if you know of a better way to accomplish this task, please feel free to post here.
TIA,
Aaron
CREATE TRIGGER [InsertNum] ON [dbo].[IP_Batch_Table_Temp]
FOR INSERT, UPDATE
AS
DECLARE @Num varchar(7)
DECLARE @Num1 CHAR(7)
DECLARE @x INT
SET @Num = (SELECT IP_Batch_Number FROM inserted)
SET @x = 1
WHILE (@x < len(@Num))
BEGIN
IF (ISNUMERIC( SUBSTRING(@Num, @x, 1) ) )
BEGIN
SET @Num1 = @Num1 + SUBSTRING(@Num, @x, 1)
END
SET @x = @x + 1
END
UPDATE IP_BATCH_TABLE_TEMP SET NUMBER = ltrim(rtrim(@Num1)) WHERE IP_BATCH_NUMBER = @Num
View 5 Replies
View Related
Jun 29, 2007
I have a project were I will have it so that users can sign in and change information on an SQL server. The catch is that this site will be from a different domain name and from a different hosting company then where the SQL database is located. Sorry if this is a dumb question but how can I utilize asp.net to change and view an SQL database that is located else-where.
For example: a user logs into www.something.com and he/she can view and edit SQL tables from www.somethingelse.com's database.
Thanks in advance.
View 2 Replies
View Related
Jan 16, 2008
Hi All.
I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query.
This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have the table below:-
Column Name Data Type
OrderID intOrderNo intInvoiceNo varchar(50)PurchaseDate smalldatetimeCost moneyInsure money
I wish to have a dropdownlist on an aspx page display the OrderNo and the Cost e.g. 12345 £3.50. I have tried the SQL Statement below but that just calculates the addition of both.
SELECT OrderNo + ' ' + Cost AS Expr1
FROM [Order]
Do i have to convert them both to string/varchar first? If so, how?
Thanks for your replies.
View 3 Replies
View Related
Mar 5, 2005
Hi,
I know that SQL Server (MSDE), databases run much faster when the data and log files are on seperate physical devices. I can do this explictly when I write a CREATE DATABASE SQL script, but would like to change the MODEL database so that all new databases use the sperate data/log file assignments by default. I haven't found a way to alter the sysfiles table in model - I can read the data, but it balks at trying to update it. (recordset is not updateable, or adhoc querries not allowed against a system database).
I didn't see any options on the MSDE install that implements seperate data/log paths.
I don't have the SQL Server tools (like Enterprise Manager) available, but I do have VS.NET 03 (Standard). And Web Data Administrator.
Any suggestions? Thanks in advance.
View 5 Replies
View Related
Apr 12, 2006
Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.
Here's the basic lowdown:
I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.
I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.
So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.
Right now I explicitly check the value of each column. ie:
WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc
I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.
Any thoughts?
View 5 Replies
View Related
Dec 8, 2007
I have two tables in a SQL db.
Each has 3 separate fields used to store a date info:
lastservicemonth tinyint1
lastserviceday tinyint1
lastserviceyear smallint2
(and these fields can be nulls)
I want to compare the date info in Table A vs. Table B and find the latest date between the two.
I know I somehow need to combine the 3 separate fields in each table to form one date field. Then I can compare the dates.
But ths far I have been unsuccessful.
Any help would be greatly appreciated!
View 9 Replies
View Related
May 25, 2007
Hi, this is my first post to this forum so thank you all in advance..
I am trying to design a database to store information about the Specification required by each customer. And the main problem I am having is how to store 2 instants of ContactID (from CustomerContacts table) in a seperate table called CustomerSpec.
For example each Customer has many contacts,(the Customer data is stored in a table called Customers which has a one to many relationship with the CustomerContacts table) each customer has one and only one Customer spec, and each customer spec needs to have 2 customer contacts, ie. one for Artwork and one production. (it should also be possible to have the same contact for both Artwork and production).
The problem is how to associate these contactId's with the customer spec...(if there was only one Contact per spec I could simply link the CustomerContacts table with the CustomerSpec table and drop ContactID into CustomerSpec as a foreign key.. But I am stuck to how to save more than one ID..
Can you help please..
View 6 Replies
View Related
Oct 17, 2007
I am creating a view for the table:
bellus=# select * from host_application_definition;
id | type_value | connection_value | group_value | application_value | host
----+------------+------------------+-------------+-------------------+----
From the table meta_host_types;
id | value | types | name
----+-------+-----------+--------
1 | agm | host-type | Rencid
I would like to seperate value into type_value and connection_value, because it holds both values.
Any tip?
bellus=# d host_application_definition;
Table "public.host_application_definition"
Column | Type | Modifiers
-------------------+---------+--------------------------------------------------------------------------
id | integer | not null default nextval('host_application_definition_id_seq'::regclass)
type_value | integer |
connection_value | integer |
group_value | integer |
application_value | integer |
host | integer |
Indexes:
"host_application_definition_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"host_application_definition_application_value_fkey" FOREIGN KEY (application_value) REFERENCES appsmarteye(id)
"host_application_definition_connection_value_fkey" FOREIGN KEY (connection_value) REFERENCES meta_host_types(id)
"host_application_definition_group_value_fkey" FOREIGN KEY (group_value) REFERENCES meta_host_grouptypes(id)
"host_application_definition_host_fkey" FOREIGN KEY (host) REFERENCES master_hosts(id)
"host_application_definition_type_value_fkey" FOREIGN KEY (type_value) REFERENCES meta_host_types(id)
d meta_host_types;
Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
"meta_host_types_value_key1" UNIQUE, btree (value)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)
View 3 Replies
View Related
Apr 13, 2007
Hello,
I don't know if this could be done, but I will present this question...
I have an employee_table with empid, firstName, lastName, phExt columns.
I have another table called location_table that contains locationID, locationName, locPhext.
Here is the dataset from the 2 tables...
From Employee Table....
empid firstName lastName phExt
1 Ann Smith 1234
2 Barb Jones 4567
3 Jeff Teeves 8901
From Location Table
locationID locationName locPhext
1 Computer Room 3245
2 Board Room 1 8745
3 Conference Room 1 4564
Here is the data that I would like to pull in this format...
Ann Smith 1234
Barb Jones 4567
Board Room 1 8745
Computer Room 3245
Conference Room 1 4564
Jeff Teeves 8901
What SQL script could I use to produce the above results?
Thanks in advance
View 3 Replies
View Related
Oct 4, 2006
Hello:
I'd like to create a view on server x which references tables on an
entirely seperate server. Is this possible? Is seems
strange to have to copy the tables over just to create a view. In
the view wizard I can't seem to 'browse' to the tables on the other
server.
The code I'm working with would conceptually be something like this:
select server name.database instance.owner.table.field
from server name.database instance.owner.table
where <field name> like 'xxxx%'
or something along those lines.
Any help would be appreciated!
Thanks.
View 8 Replies
View Related
Jan 28, 2008
Hi!
I have a big database which need some separation
in multiple filegroups.
Therefore I plan to move tables to new filegroups.
As I can understand this must be done by
dropping the clustered index and
recreating it on new filegroup, then data will move.
1. best way to get a list of all clustered indexes (sys.indexes)?
2. how to get index scripts? (using SSMS, right click is manual task, but I need scripts for than 100 tables)
3. when having the scripts it should be easy to replace filegroup part
4. after movement: just shrinking old files?
Thanks for help ;-)
View 1 Replies
View Related
Dec 4, 2002
I have seen in this forum that many people has posted like my query time taken problem. But I couldn't get clear solution for this.
Here is the my issue:-
I have written couple of batch query to bring data from another sql server.
I have used proper index and index hints for these query.
I have used SET NOCOUNT ON in stored procedure.
I am having the time difference for one of the blow qeury in various running process that executed in QA as single query , stored procedure for whole batch query and schedule this stored procedure in job
sotored Proc = 31 second > job =28 second > seperate query = 12 second.
one of 15 batch query
declare @last smalldatetime
set @last ='11/08/2002'
UPDATE ENTERPRISE_STATE
SET RESP_BUYER_FST_NAME = i.first_name,
RESP_BUYER_LST_NAME = i.last_name
FROM oesd.dbo.individual i WITH (NOLOCK index(XPKindividual))
INNER JOIN Leap.dbo.fnIndBuyer(@last) fn
ON fn.individual_num=i.individual_num
WHERE ENTERPRISE_NUM = fn.enterprise_num
Anbody can able to give proper reason why I am getting this time difference.
Thabnks,
Ravi
View 3 Replies
View Related
