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.
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.
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
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.
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?
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?
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
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...
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])
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.
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?
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.
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.
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?
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
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.
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
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.
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.
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.
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.
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.
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..
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.
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
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?
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.
