I need to design a system which represents multiple "projects" in SQL
Server. Each project has the same data model, but is independent of all
others. My inclination is to use one database to store all projects.
Looking at the numbers involved, however, I wonder if I would get
better performance by storing each project in its own database.
Suppose I have 50 projects, each with two users and 10,000 rows; it
seems to me I'd rather have 50 x 2 users working in a table with 10,000
rows than 1 x 100 users working in a table with 500,000 rows.
On the other hand, the single database approach seems more elegant from
a design perspective. I wouldn't be creating multiple copies of an
identical data model, and I wouldn't be creating new databases as a
business procedure, every time a new project is required.
Here are my questions:
1. For the scenario described above, am I correct to assume I will get
better performance by using multiple databases, or does SQL Server have
some clever way of achieving the same performance in a single database?
2. Is the multiple database approach common? If anyone has tried it,
please tell me about how it works in practice.
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.* FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************', 'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
We have our replication environment with a separate SQL 2014 publisher, distributor and subscriber. We have a table named DPSP on 14 databases which is to be published via transactional replication to 1 subscription database.
The source tables are identical in name, schema and clustered index. We have created 14 tables in the subscription database with a suffix to the name (see below):
Each table in the subscription database has its own uniquely named primary key and clustered index:
Below are the settings for one of the published article:
The same process is repeated for each DPSP table created in 14 separate publications. Snapshots are generated and subscriptions created successfully. After some time, the log reader starts to show the following errors in replication monitor:
2015-08-12 21:48:24.156 7 transaction(s) with 11 command(s) were delivered. 2015-08-12 21:49:04.233 5 transaction(s) with 7 command(s) were delivered. 2015-08-12 21:50:04.355 No replicated transactions are available. 2015-08-12 21:50:09.378 13 transaction(s) with 17 command(s) were delivered. 2015-08-12 21:51:12.168 No replicated transactions are available. 2015-08-12 21:52:12.306 No replicated transactions are available. 2015-08-12 21:52:32.337
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
I am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
Hello, I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services? Thanks
Is it possible to retrieve Data from SQL Database #1 and insert it into SQL Database #2 using a Stored Procedure? Thanks. If so Can you show example. Thanks
I have a question about having one or multiple databases for a large project. What are the main differences between designing my database as one or breaking in to multiple databases. I should mention that there are many relationships between tables in different DBs in the multiple DBs state?
Hey there! I'm not sure how to explain this but here goes... I'm a bit stuck, a new project that's come about "requires" me to query multiple databases as if they were tables. 1 Oracle database which stores information on our staff (this database is a part of some MIS software and can't be changed in any way) 1 SQL Server 2005 database which stores information on staff sickness Basically say theres 1 table in each database Oracle DB People
Person_Code Forename Surname SQL DB Sickness
Person_Code Daydate Lets say the query I want to perform is to select all the records from sickness where person code is 22334 and also get their name from the other db, so the output may look like 22334 Dann Rees 01/01/2007 22334 Dann Rees 03/01/2007 22334 Dann Rees 10/02/2007 Now I realise I can write a quick function to pull the information but this is just a basic example. Effectivley what I "need" is to be able to query sickness while sub querying people to get the names, or some kind of pass through query? Please remember this is just a very simple example and the "actual" queries will be far more complicated, for instance finding all the employees of a certain department who is male and was sick in January. All the data for that example is stored in people (oracle) except for the dates which is stores in sick (SQL 2005). Now these are easy enough if they were tables in 1 database....but their not, their tables in 2 databases, and theres nothing I can do to change that :( All help appreciated as this is becoming very urgent. Many thanks Dann (I couldnt post this in the General data access forum for some reason)
Hi all:I am trying to create a website which will search and return results that are from multiple tables in different databases. I have the code to search a singular database right now, and I tried to make a connection to a second one, which worked, but I don't know how to send the proper sql commands to the second database or how to link them in the code. Does anyone have any pointers on creating a site which will search different tables in different databases with a known connection between databases? Any help would be really really really really appreciated.-Shrey
so, I am trying to write an sql that requires information across two different database. It's under the same sql server. However, the location of the tables are from different database.
Any links I can read about that can show me how to write the queries?
We have a number of databases(on sqlserver 7.0) which has the same structure but with data for different users. We would like to change the structure of the databases with minimum effort and with minimum down time. Is there any method to automate the change so that the change is reflected in all the databases when a single database structure is changed? Any suggestions in this regard are welcome and urgent
Okay this is a test...actually I am still learning SQL and need some help. Does anyone have any information on being able to move indexes from one database to another. My scenario is I have 3 databases, Development, QA and Production. I want to move/copy indexes I created in Development to the QA database. I have many indexes so I do not want to have to recreated them if I can avoid it. Any suggestions?
Currently developping a c# database based application, which will be used in different establishments worldwide. I'm currently a bit confused by the ms sql features offered. Because the huge data amount which will be fetched during application use from the database, the best option is probably to put a ms sql server in each establishment to realise quick data fething. Problem is that the servers, which are all running on the same database design have to be synchronised real-time. I read about datareplication with the merge option which I thought was a nice solution. The subscribers can only have read-only rights on these subscriber databases, because the merging doesn't work appropriate i heard, so this is no good solution. Then I read about distributing transaction coordination. Seems a good solution as well, but what happens if a server system crashes for one day and then comes back online? It won't be up to date anymore... Right now I think about a combination of these 2 features. DTC in combination with merge replication, but there must be a better solution. Fact is that a lot of users will be editing data worldwide non stop, and everyone has to be up to date. Because of huge data fetching I don't think it's a good solution to let everyone work worldwide with one single database. Is there a possibility to realise this with ms sql server? Because I'm getting more and more confused... Thx in advance.
I need some guidance! We are running SQL Server 2000. We have 4 or 5 databases with the same schema, and I need to make sure that their structures all stay synched. We will have more soon. I also need certain tables to have the same data across all the databases.
I need a way to manage changes on the multiple databases! Right now I generate a script in Enterprise Manager, then run it in Query Analyzer on each database, manually. There's too much room for error here.
Any suggestions are appreciated! I am currently evaluating Embarcadero's products, but they are complicated and confusing - I'm not sure yet which of their tools is meant for this purpose.
I have a problem like this. I have an application which shud be internet hosted. All transactions should be updated to the central server of the net as well as an intranet DB server. (I'll have a DB server - SQL Server 2000 in each intranet). Is this possible.. i'm planning to use ASP to develop the net based application. Thank you in advance.. Geetha R
Ok, so right now we have a bunch of queries similar to the following:
Code:
use db001 select co#=(001), fieldvalue from tablename where fieldid = '1'
use db002 select co#=(002), fieldvalue from tablename where fieldid = '1'
what i need to find is if there are duplicates of fieldvalue and have it display all in one.
Basically right now we run all these there are 25 databases and we export everything to a file and then open it in excel. we then format it it so its just co# and fieldvalue and then look for duplicates.
i would like to avoid the whole excel part and just run a query that will display any duplicates of fieldvalue across all 25 databases in "tablename"
Could anyone possibly help me out with an issue I am having... I need to copy all my SQL2005 databases from Server1 to Server2. How can I, using SSIS, copy all the databases and not just 1 specific one (database transfer task)??? My actual task is much deeper than this, but this is the main problem I seem to be encountering and I cannot find this anywhere on the web...
Over and above the what I have mentioned, is there a way to also specify a name of the target DB the copy will go through to? e.g. Server 1 has 3 instances, each one with a different copy of the same DB. I would like to copy all 3 copies to Server 2 running 1 SQL instance, but to target DB's like DB_dev, DB_prod and DB_test.
The latter section is not so crucial at the moment, but the first part is really an issues I am struggling with at the moment...
I'm very new to SQL, so you'll have to excuse the nubishness of this question. I've got my trusty O'Reilly book here!
We're using Microsoft SQL Server 2005 on our web server. I'm currently working on a project to get a few MS Access Databases available on the web page.
The web part is actually working fine, with the DBs exported into SQL for performance reasons. (From what I've read pulling data from the .mdb file could cause me problems in the future, so I'm tossing it all onto the SQL server from the start.)
Before I get into the project full-tilt, though, I'm curious about how best to organize all the data?
I'm using the Upsizing Wizard in Access for the conversions, and every new DB gets added under Databases. If this gets approved I may be looking at well over a hundred DBs which will eventually be converted and made web accessible.
I can keep everything neat and tidy on the web server by just tossing things in a few directories, but the menu on the SQL Server Management Studio is going to become a mess really quickly if I let it just keep adding to +Databases.
Is there a way to sort everything into a series of folder/groups, like how System Databases has it?
we have SQL instance which contains databases (A, B, C...). When ever i configured LS with Standby option, and i want complete restore of database A (Primary) to stand by A (Secondary), then logs will always going to restore...
but SQL server is taking a copy of the Backup like "GJ_Temp_data, GJ_Temp_log" as the default file names, for restore the DB Backups.
if i want to set up for the database B, it is taking "GJ_Temp_data, GJ_Temp_log" again for the database B, and giving error as "the files are already in use".
If i script the Log shipping setup while enabling for Database A, and make sure that i will enabling it for remaining databases B, C, D..
Please advice, and give us best experienceS for enabling LS FOR Multiple databases on the single SQL Instance.
This is probably a simple solution but I'm a bit confused about which flows to use.
I have two databases on different servers that contain the same information but for the appropriate server. I want to retrieve data from each of the servers using a "select" statement.
Would I use a ForLoop and change the connection in a Script Task and then execute the SQL statement? Or can a ForEach Loop be used, but I'm not sure which type of enumerator.
I'm a newbie to SQL Everywhere. I have gon e through the examples and created a C# application to create and a manipulate database on desktop. SFSG. For my application, though, I see the need to work around the 4GB limit. So I will need to segment my data into multiple databases.
Does SQL Server Everywhere supports joins across mutiple SQL/e databases on the same machine ? I have not found any examples that guide to address this issue. can anyone point me to something/help me write queries that join D1.T1 and D2.T2.
I usually use Access for my database work, but a recent request needs data from a table with about 8 million records - a mite outside of Access' league.
So, I am using SQL Server Report Services to create this report.
Essentially, I need to be able to use a table from database A and a table from databse B in a single dataset for the report I'm making.
I'm self-learning this package and have not been able to locate if this is even possible, let alone how to do it. I can't see it not being possible, so I figure the procedure is simply eluding me.
I've seen a thread on a similar - if not the same - problem, and the answers given all seemed to be based upon the user being able to write to the database server or in some other manner manipulate the server. I have no such capabilities. All I can do is look at the data and create a report based upon what I see. No write access at all.
This wasn't a problem in Access, because, though the main data was on the server, I linked to any needed tables and everything else was local to my box.
We're designing our data model, and have found that we have two groups of tables (about 10 tables in each group). The tables within each group are dependent, but the two groups are independent of eachother.
Now, our two choices are:
1. Put all tables into one database.
2. Put the two groups into two separate databases.
For simplicity, option 1 is the winner. However, my question is, will there be noticeable peformance benefits by using two databases? (In which case, option 2 will be the winner).
Ok, this is kind of an odd problem. Back in June we were having problems with our call manager software, and they decided to have it just start usinga new database. Now I'm trying to generate some reports which need to cover both the old call stats and the new, so that means the stored procedure builds a temp table and populates it from both databases.This works perfectly fine in Management Studio, and when being called from Excel.However when I try to call it from an ASP.NET web app using SqlCommand.ExecuteReader(), I only get results from the new database!What on earth could cause that?
We are designing a new data model, and have many clients. Currently they each have their own database in SQL.
We are contemplating the pro's and con's of migrating to a Single Master Database.
I would like to hear from others on what your thoughts are on the pros and cons. Also, if you have experience doing something similar, please speak up.
Hi I need to retrive data from multiple tables which are in two databases. How do i connect to both of them at a time and query the tables with some criteria. Thank you
I need to merge tables from several databases. Before the merge the table contents must be checked to rule out any duplicates. Is there a most efficient way to do it? Thanks to you all.
Please provide advice on how to upgrade from v6.5 to v.7.0 where v.6.5 databases are sharing same devices. What do i need to do to put db's on their own device/file. Should I upgrade first or fix on 6.5 side? Does SQL 7.0 rebuild the DB on its own file during upgrade? Thank you....
