Multiple Small Databases Vs Single Big Databas

Jun 17, 2008

Hi, I'm planning to develop a project on .NET using Sql server 2005. Just stuck at a point where I need to decide that Should I use 2 or 3 huge databases with a lot of tables and data into it or should i create a lot of databases which would contain small chunks of data. Please let me know which option would give me a better performance. Thanks in advance

God is great. We are not God. But we still can be great.

View 2 Replies


ADVERTISEMENT

Restoring Multiple Databases From Single BAK?

Oct 24, 2013

I have a single .bak file containing full backups of 20+ databases.

Whats the best way of creating and restoring these database onto an instance on a separate server.

note that this is to migrate the whole instance of sql server onto a newly build server.

View 1 Replies View Related

Single Endpoint Multiple Databases

Apr 17, 2007

I'm building an app that will data stored in multiple databases. I have an endpoint with a single webmethod like so:



CREATE ENDPOINT [Rosters_Endpoint]

AUTHORIZATION [ADHEadministrator]

STATE=STARTED

AS HTTP (PATH=N'/SQL/Rosters', PORTS = (CLEAR), AUTHENTICATION = (DIGEST, NTLM, KERBEROS, INTEGRATED), SITE=N'10.0.1.116', CLEAR_PORT = 8080, COMPRESSION=DISABLED)

FOR SOAP (

WEBMETHOD 'GetACSStudents'( NAME=N'[fadb].[dbo].[GetACSStudents]'

, SCHEMA=DEFAULT

, FORMAT=ALL_RESULTS), BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]', SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N'fadb', NAMESPACE=N'http://tempUri.org/', SCHEMA=STANDARD, CHARACTER_SET=XML)



Each webmethod will be accessed thru a multiview on the page.



It would seem to me that I can describe the webmethods using a fully qualified stored proc name and that wouldn't cause a big problem. But, my question is what does that DATABASE=N'fadb' clause do?



Does anybody see a problem with using a single endpoint to access multiple databases?



Any advice would be greatly appreciated as this is my first web app using this data access method.



Thanks.

View 1 Replies View Related

Multiple Databases In Single Linked Server ?

Apr 25, 2008

HI Guys,

I just need to add multiple catalogs to the same linked server, in Sql Server 2000 or Sql Server 2005. Here i need only to add 3 databases in to a single linked server name.


Thanks In Advance,
Krishna

View 1 Replies View Related

Creating Multiple Databases From A Single Backup F

Nov 9, 2006

Hi,
I want to create two databases by restoring from a
single backup file in sql server. I am using 2005-sqlexpress .Is it possible?

Thanx in advance..

View 3 Replies View Related

Restoring Multiple Databases From A Single Backup

Jan 16, 2008

I have a backup that contains multiple databases. I am restoring it to a different server. How do I restore each database? Tried to restore under SQL Server Management Studio, but can only restore one of many from the backup (it appears it has only 1 logical file name in the backup) If you can refer me to a web page that would be great. I tried googling the topic but nothing seems to come up.

View 5 Replies View Related

Mirroring Multiple Databases On A Single Instance

Jul 7, 2007

Can someone explain what does this mean? This is from http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx



<quote>However, all these techniques for coordinating client redirection with a database mirroring have an important limitation. Database mirroring occurs only at the database level, not the server level. Be careful if your application relies on querying several databases on a server, or uses multi-part object names to query across several databases. When several databases reside on one server, and they are mirrored to a standby server, it is possible that one of several databases might fail over to the standby but the others remain on the original server. In that case, you might need one connection per database that you are querying, so that you do not attempt cross-database queries on a standby server where only one database is a principal and the remaining are mirrors.</quote>

Also I read somewhere that if one mirrored DB fails, all other mirrored DB should also be transferred to the mirror.

For example: Let's say Srv1 (principle) and Srv2 (mirror) and Srv3(witness) are SQL servers with only default instances. The SQL instance has 4 DBs (DB1, DB2, DB3, DB4) and all of them are mirrored to Srv2.

An ASP.NET application has four seperate connection strings like:
objConn1 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB1;Integrated Security=True;
objConn1.connect();

objConn2 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB2;Integrated Security=True;
objConn2.connect();

objConn3 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB3;Integrated Security=True;
objConn3.connect();

objConn4 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB4;Integrated Security=True;
objConn4.connect();

If DB2 failsover to Srv2 (mirror), why should all other DBs be failedover?

Thanks

View 10 Replies View Related

SQL 2012 :: Mirroring Multiple Databases On Single Instance?

Jun 10, 2014

I have to mirror 3 DBs on my sql server instance . I have restored there full backups and log backups on mirror.I have also created endpoints on principal and mirror using default ports 5022 and 5023.

know if i can use the same endpoint for the databases on principal instance ?

View 3 Replies View Related

T-SQL (SS2K8) :: Multiple Databases - Return A Single Table With Three Columns

Jan 13, 2015

I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.

I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:

DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23

The results should be ordered by DateChangedOn.

View 4 Replies View Related

How To I Enter A Small Square Or A Small Circle In A NVARCHAR Field?

Dec 25, 2006

I want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.

Thanks

View 4 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

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. 
 

View 3 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008



Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

View 8 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

Jul 24, 2015

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:

EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary', 
@profile_name  =
'SQL SMTP',
   
[code]....

View 3 Replies View Related

Creating A Database From Multiple Databases Accross Multiple Servers

Sep 13, 2007

Hi,

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)

Thanks

View 8 Replies View Related

Merging Data From Multiple Databases With Multiple Tables (all With The Same Structure)

Nov 15, 2006

Hi!

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

Any pointer most welcome!

best regards and thanks

Thibaut

View 1 Replies View Related

Single ID Access 2 Databases

Mar 27, 2006

Is it possible to have an ID that can access 2 databases? If it's possible, I have to create the same ID and password under each database?

View 6 Replies View Related

How Many Databases Are There In A Single Server

Sep 28, 2007

Hi all,
How to find out how many databases are there in a
single server.
example: server name 172.16.9.33 i want to find out how many
databases are there in this server.
Any solution for this plz help me.



Thanks&Regards,

Msrs

View 3 Replies View Related

DTS: Single SELECT From 2 Databases Possible?

Jul 19, 2006

Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?

I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:

SELECT
a.something
FROM
DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c
WHERE
a.PCat <> c.Pcat

(where DB_ONE and DB_TWO are the 2 different db names)

I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?



Thanks,

Andre

View 1 Replies View Related

SQL Tools :: How To Restore Multiple Databases From Multiple BAK Files At Once

Aug 14, 2012

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.

View 13 Replies View Related

How To Make A Single Database From Two Databases ....

Nov 8, 2006

Hi ...

I have two separete databases, what Ineed is that I want to make a single database of the two. Although all the table definitions of both the database are same.

I will be glad to know "how to make a single database from two" and i will be happy to see the query to insert the values from one database to another one.

Abdul Ghaffar

View 1 Replies View Related

SQL 2012 :: What If We Locked Down All Databases To A Single Interface

Aug 30, 2015

Would this go a long way towards securing the data?

Database.Store(this)
Database.Delete(this)
Database.Load(this)

Where "this" is a single object mapped to a single stored procedure.

Two parameters are recommended: one to identify user, and one to specify an "action" or different SP.

View 2 Replies View Related

JOIN Multiple Tables From Multiple Databases

May 23, 2008

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

View 3 Replies View Related

Connecting To 2 Diffrent Sql Servers Databases From Single Web Application

Oct 31, 2007

i am having 2 sql servers one is web sql server  server and other server is my local sql server. i am making web application through which i want to connect both sql servers at a time and by selecting data from web server i want to insert it into my local sql server both having same database on which i want to work , i want to know how it can be done
my idea as follows in  web.config  i specify 2 connection string and by selecting data from  websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database.but database records are much large. can any body guide me .
i am going to place my web application on local server (C# asp.net 2.0) .
thank you,
 
i tried this code
c# file
 public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
Uupdatedata();

}
public void Uupdatedata()
{
string constr1=ConfigurationSettings.AppSettings["abccon1"];
string constr2 = ConfigurationSettings.AppSettings["abccon2"];
System.Data.SqlClient.SqlConnection dbcon1 = new System.Data.SqlClient.SqlConnection(constr1);
dbcon1.Open();
System.Data.SqlClient.SqlConnection dbcon2 = new System.Data.SqlClient.SqlConnection(constr2);
dbcon2.Open();

SqlDataReader dr;
SqlCommand cmd = new SqlCommand("select * from Unit_Master",dbcon1);
dr = cmd.ExecuteReader();
while (dr.Read())
{
string Sql = "insert into Table1(Unit_Id,Unit_Desc) values(" + dr.GetInt32(0).ToString() + "," + dr.GetString(1) + ")";

SqlCommand cmd1=new SqlCommand (Sql,dbcon2);
//cmd1.EndExecuteNonQuery ();

}

//dr.Close();
//dbcon2.Close();
}
 web.config<configuration>
<appSettings>
<add key="abccon1" value="Data Source=comp01;Initial Catalog=abc;User Id=sa ; Password=sa"/>
<add key="abccon2" value="Data Source=comp01;Initial Catalog=abctest;User Id=sa ; Password=sa"/>
</appSettings>
<connectionStrings/>
 this 2 databases are from single my loacl server
it wont work it gives exception
please healp me.

View 4 Replies View Related

Recovery :: How Many Databases Can Be Configured In Log Shipping From A Single Instance

May 1, 2015

SQL Server 2012: Out of all the databases in the instance we have a requirement in which we need to maintain a high availability for the databases around (128). Our team believed log shipping will apt for this requirement but not sure on it's limitations, how many databases are allowed or supports this log shipping from a single instance.

View 7 Replies View Related

Merging Two Different SQL Server 2000 Databases With Same Schemas In A Single Database

Jan 7, 2008

Hi,

I have two databases lets say DB1 and DB2.
Schemas for both databases is same.
In both database schemas there are tables which has identity columns as primary key.
Now i want to merge these two databases in a single database say DB3.
It may also possible that some master records in both databases are common so they should not repeat in DB3

Is there any way so that i can do it quickly and as soon as possible.

Thanks in advance
Rohit

View 1 Replies View Related

Connect To SQL-Databas Over TCP/IP

Jan 29, 2007

Hi..

I am really new in C# Programming witf h Database. My problem is:

I have a SQL Database (in an other country) and My Application has to connect it for data transfer. How can I do that? Which alternatives do i have. Should I use a Web Service?

Thanks...



View 1 Replies View Related

Sync/Merge Local Sql Compact Databases To Single Global Database

Jun 23, 2007

Hi, I have compact sql databases which will be local on multiple users
devices. Due to space constraints, for one of the tables i have had to use
auto incrementing integer which works fine for the local database but i
would like to merge all of the users databases into a global database. The
table format can be seen below:




Code SnippetCREATE TABLE Players
(
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
FirstName NVARCHAR(32),
LastName NVARCHAR(32)
);

CREATE TABLE Sessions
(
ID INTEGER NOT NULL IDENTITY,
PlayerID UNIQUEIDENTIFIER ,
SessionDateTime DATETIME,
CONSTRAINT pkSessions PRIMARY KEY (ID),
CONSTRAINT fkPlayerID FOREIGN KEY (PlayerID) REFERENCES Players(ID)
);

CREATE TABLE SessionDetail
(
SessionID INTEGER,
Time real,
Power real,
CONSTRAINT pkSessionDetail PRIMARY KEY (SessionID,StrokeTime),
CONSTRAINT fkSessionID FOREIGN KEY (SessionID) REFERENCES Sessions(ID)
);



The Players table will merge fine as GUIDs are used. However, how will the
sync capabilities of compact SQL handle the sessions table? When it pushes
the local data to the remote database will it change the Sessions.ID column
to a unique field (as no doubt lots of people will have 1, 2, 3 in their
local databases and the global database must have a unique ID), and then
transfer this changed ID back to the local database? Furthermore, if it
changes the Sessions.ID column during the merge it will also need to update
the SessionDetail.SessionID foreign key to maintain referential integrity,
is this also handled?

So my question is, how much of this sync and data merge is automated and are
there any good examples or pointers for my scenario? I cannot reasonably use
a GUID for Session.ID as there will be lots of SessionDetail entries and the
size would be far too much.

Many thanks,

Chris

View 7 Replies View Related

ASPNETDB Relation With My Databas

Sep 8, 2007

HI!I have a ASPNETDB as my login databas. but now i want to connect this databas or the aspnet_user table to my table, how do i?I want to check the username in aspnet_user table and select the same username in my table?I want to write all sql code in a sql file. so i want to know how i can connect to the sql file from my c# code?I hope somebody understand me...

View 2 Replies View Related

Access Error To SQL Databas

Jan 9, 2008



Im trying to follow the Introduction to Visual Basic 2008 Express Edition video but when i come to the part whith
test the connection to Northwind.sdf i get the following error


Access to the databas file is not allowed.[File name = C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5SamplesNorthwind.sdf]

Is this at Vista 64 problem, the girl in the video is not using Vista and there it works fine.

View 11 Replies View Related







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