Thousands Databases ?

Feb 12, 2008



Hi,
i just started working on a new project, a RIA with a backend on SQL 2005.

The question that I'm asking - about the databases architecture - is it possible:
a) (this is the preferred solution) to have up to 5 000 (five thousands) databases inside a SQL 2005 instance (each client with his own small db)
b) would it be better to have one large database with tables eventually having near a billion of rows ?
c) one single database with thousands of small tables.

Thank You All.



Edit :
The clients databases will be identical in structure.
The motivaion for having each client with his own database is to ease management (backups, restores, roll-backs) and tools in this direction will be created (scripts, automated procedures, scheduled management plans etc). Each client is proprietary, and to have things separated seems more legal.
The hardware is not a problem, the db server will be a blade center runing a clustered MS Windows Server.





Code Snippet

(07:51:49) him: i haven't followed sql recently but let me see (seems difficult question)
(07:52:36) him: what is the motivation to have different datbases for each client?
(07:52:46) me: (the hardware is not a problem, probably ibm blade center, about the management - tools will be created to ease)
(07:52:52) him: it is it the size/efficiency or you want to separate them
(07:53:02) me: for the back-ups and restores and roll-backs
(07:53:13) me: the db-s r identical
(07:53:30) him: identical in design or data inside them
(07:53:33) me: yes, design
(08:02:12) him: so the limitation is Databases per instance of SQL Server 32,767
(08:02:21) him: so it seems that in that respect you are going to be fine
(08:02:45) me: yes, i've seen the specs
(08:02:50) him: yep
(08:03:03) him: in terms of design
(08:03:13) him: managing 5000 databases would be a nightmare
(08:03:22) him: unless there is some automatic way to do it
(08:04:18) me: that's for shure, and that's what is intented
(08:04:29) me: automadet procedures, scripts etc
(08:05:27) him: so again what is the motivation to partition each client in different database
(08:06:05) me: there is a need to keep their data separate
(08:06:19) me: each client is proprietary
(08:06:44) me: and to have things separated is more legal
(08:07:25) him: well you try with several databases and see how it scales
(08:08:37) him: one thing that would also prevent confusing databases connections and may be helpful if you decide to change the to a single database model is to design the database as if it would be used with many clients but use it just for one
(08:09:09) him: then, if you decide to merge the databases in the future it would be relatively easy
(08:09:16) him: unless this would create too much overhead
(08:09:41) me: yes, this is allready the case
(08:09:58) me: so, i go for a test u say
(08:10:12) me: and see how it's working
(08:10:16) him: ok. then it seems that the issue is maintaince then anything else
(08:10:29) him: yeah. i don't think the sql server would be a problem
(08:10:44) him: there would be some overhead of the different databases but should be small
(08:11:12) me: :)
(08:11:24) me: k, thank you verry much
(08:12:53) him: http://www.thescripts.com/forum/thread503521.html
(08:13:09) him: i think either way has plus and minuses
(08:16:56) him: also read the forum link i sent you
(08:17:00) me: ty, i'm on it
(08:17:16) him: there are some interesting considerations i did not think of
(08:17:58) me: i just saw about caching maintainance plans, stored procs

View 9 Replies


ADVERTISEMENT

Logging Thousands Of Errors

May 4, 2007

I'm receiving these two errors again and again:



Windows cannot load extensible counter DLL MSSQL$MSFW, the first DWORD in data section is the Windows error code.



Windows cannot load extensible counter DLL MSSQL$SBSMONITORING, the first DWORD in data section is the Windows error code.



What might be generating them and how can I cure it?



The system is SBS 2003 R2 Premium.



Thanks.

View 6 Replies View Related

Deleting Thousands Of Rows In A Table

Apr 10, 2003

How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:
Delete from transactions
WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())

increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.
With the command truncate table I cannot use query unfortunately but this would be faster.

Anyone has an idea?

mipo

View 3 Replies View Related

Slow Query Thousands Of Records.

Jul 20, 2005

I have a query that returns raw tick data from a table. Unfortunately aftereven a few days there are hundreds of thousands of rows so the followingquery is not efficient.SELECT * FROM RAWTICKDATA WHERE Status = 'I' AND ContractCode = ? ANDRawTickID = (SELECT Max(RawTickID) FROM RAWTICKDATA WHERE Status = 'I' ANDContractCode = ? AND PRICE =(SELECT Min(Price) FROM RAWTICKDATA WHERE Status= 'I' AND ContractCode = ?))The most obvious solution then is to get all tick data with status ='I'(Imported) for a contract, process it and then move it to another table forarchiving. I am faced with a problem however: After selecting all data for acontract with status='I' the application was updating these records to a newstatus of 'P' (processed). Unfortunately another application is continuningto feed in live data and so it is possible that we will inadvertantly updateunprocessed data to 'P'.Question: Is it possible to select all records with status 'I' (from abovequery) and update their status to 'P' in one sequence?I am not a programmer, but if this is possible I should be able to implementthe query.Many thanks.Steve

View 3 Replies View Related

Full Text Searching....THOUSANDS Of Records!

Sep 12, 2005

Hope I am in the correct section.

I am installing a FTS system on an existing system (that used LIKE % queries!!  hahaha)

Anyway, it is working pretty well (AND FAST!) but when I type in a
common word like "damage" I get like 32,000 records.  Now, the
server handles those records in about one second but the ASP page that
returns the results takes about one MINUTE to download.  When I
save the source, it is almost 12 MEGS!!

So, basically, I am streaming 12 megs across the pipe and I want to reduce that.

I would like the system to detect over maybe 500 records and cancel the search.

I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?

Thanks!

cbmeeks

View 3 Replies View Related

Format Number Thousands Separator With Point

Aug 14, 2012

Format number thousands separator with point

I have this number in my database output result of query: 1013473

I need this output: 1.013.473

View 7 Replies View Related

Best Way To Compile Thousands Of TSQL Stored Procedures?

Jul 23, 2005

I have a custom application that on occasion requires thousands of TSQLfiles (on the file system) to be compiled to the database.What is the quickest way to accomplish this?We currently have a small vbs script that gets a list of all the files,the loops around a call to "osql". each call to osql opens/closes aconnection to the destination database (currently across the network).

View 5 Replies View Related

Format Number With Thousands Separator? 10000 --&&> 10,000

Oct 24, 2007



I would like to select a BIGINT type and get a formatted result with commas. Anyone have ideas?

declare @i bigint
set @i = 123456789

select @i

--Would like to get
123,456,789

View 39 Replies View Related

Generate Thousands Of SSRS PDFs Programmatically With SP Or SSIS

Jul 18, 2007

I have a report that I'd like to involve in delivering tons of PDFs for each of our customers for a billing cycle. There is a table that the report reads from that gives it all its data necessary for the report (which is a customer bill) and the table also has a column that has the file name for the PDF for that particular customers bill for the report. Basically each table row represents one output report (bill) and each table row has its name nicely formated for me. Writing the report is not a problem. Figuring out how to run through thousands of rows generating a PDF for each with the file name from the table is my challenge.

I would like to create either a SP or a SSIS package (that is scheduled) that can run a report for each line of data in my table and spit out a PDF file to a UNC path. It might have to generate thousands of PDFs to a UNC path that has plenty of space. There will be another SSIS package that moves the PDFs later to their proper directory.

So, is there someone who has done this before? Any suggestions? Is there a quick path to doing this, would it take a ton of time? Any tutorials out there?

Thanks,
Keith

p.s. My preference is to do this in SP's or SSIS and if I need a .NET language to do that in VB.NET, but I'd rather avoid that if I can.

View 3 Replies View Related

Inconsistency In Rendering Chart W/ Thousands Of Records To Plot

Oct 29, 2007

Hi guys,

I have this problem with reporting services wherein my report contains an Area Chart. My plot points is about 100,000 records. The problem is sometimes the Chart is displaying right and sometimes its not. The users need to refresh the report again for the chart to display right. This doesn't happen if my records to plot contains around 10,000 records.

Does RS have a limation in plotting records in an Area Chart? Any solutions?

View 2 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

Service Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)

Jan 24, 2006

I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.

When i tried the sample on a newly created database it worked fine.

Is there any solution to make the restored database to work for service broker.

Thanks

Prashanth

View 3 Replies View Related

Copying Databases Between Databases

Feb 8, 2000

hi from France !!!

i would like how to duplicate a database to another server with all datas, constraints, keys, indexes...
should i use sp_attach_db, dts, backup/restore, sql scripts... ???

thanks to all, nico

View 1 Replies View Related

Connect From Databases In 1st Instance To Databases In 2nd Instance

Mar 13, 2007

This is the scenario. Is it possible to create views or something (like Oracle DB Links) in a database in Instance 1 which can show data from another database in 2nd Instance ?


I want to do this to create reports.

View 2 Replies View Related

Two Databases

Sep 24, 2006

I have created a site which now has two databases. Firstly is the ASPNETDB.MDF which I beleives get set to manage the security, logon/Roles that I have on my site. The second dataabase "MyDatabase" is one that I have created for all my data withing the site. I would like to combine this to one Database for simplicity and cost. Now I can't see the  ASPNETDB.MDF when I use SQL Manager 2005 and I can't see how to change the set up of ASP.Net Configuration if I was to just use mydatabase. Any suggestions

View 1 Replies View Related

Two Databases?

Sep 25, 2006

Hi, I have my site set up, but have had difficulty using the data gathered by the aspnet.mdf. I wanted to use the email and username columns from the membership table to send newsletters automatically from the database(but that's a whole other post)I also have my own databse, which will be populated by information gathered from the website, so is currently empty. I'd like to take this opportunity to say that i don't think there's enough information given on how to populate a blank database from a website application. Most tuorials etc are concentrated on retrieving and displaying data, which is all well and good, once you actually have data to play with!I don't need two databases, so can I add tables to the aspnet.mdf ? But then what about relationships etc? I won't damage the current relationships by adding new ones? On the other hand can i copy the aspnet.mdf into my own database, and then assign the relevant permissions to that db? Any help would be greatly appreciated-in language for newbies please! I am relatively new to .asp.net/Visual Basic/ and SQL. It's a steep learning curve!Also....I have used create user wizard, login, password recovery etc within my site, the data is inserted into the correct db tables-without using an sql datasource for some reason...? is that right?I have also created a wizard to collect some more data (because i couldn't work out how to insert the data from the extra steps in create user wizard, into my own database)  but when i click submit the data is not inserting into the specified table - (yes the connection string is fine, insert and update has been selected from the advance tab in connections wizard). Would this/could this be because i have assigned a 'finishbutton url'? should i use a Response.Redirect("blahblah.aspx") instead? 

View 4 Replies View Related

Databases

Nov 16, 2004

I am very new to asp.net, but am having trouble trying to link the asp.net page that i have created to an sql database that has been set up on a local system. Am actually trying to use the sql control to extract info from the database. Would appreciate any ideas.

View 1 Replies View Related

Two Databases

Jul 13, 2001

Dear friends,

There are two databases in SQL Server and I have to designed the view or store procedure joing tables from two databases. How ? It will be kind, if you give me answer with detail example.

Best regards!
manoj

View 1 Replies View Related

Use 1 Or 2 Databases?

Jul 16, 2000

Hello,

I’m using VB6 as the client and MS SQL Server 7 as the database. The company I’m consulting for has a proven track record for marketing and getting millions of customers. The parent company would like to create two new companies selling the same product; with 1 million customers per company. How many records can MS SQL Server 7 handle? I’ve been told it can handle 4 terabytes. What does 4 terabytes translate into records? I have already set up an application for company #1. Should I put company #2 in the same MS SQL Server database? Or should I put company #2 in a different database? My VB Application and SQL Server database were designed to handle multiple companies selling the same product. For performance sake; would it be better to separate the databases? Any suggestions? Is anyone currently using MS SQL Server with 2 or more million records?

Thanks,
Denise

View 2 Replies View Related

Max. # Of Databases In SQL 7

Aug 3, 2000

I am presenting using MS SQL 6.5 and have used up all my (VDEVNO) device numbers (1-255). Will I have this problem when I migrate to SQL 7? I understand SQL 7 does not use a device when creating databases, so what
is the maximum number of databases I can create in SQL 7?

Thanks in advance.

View 4 Replies View Related

SQL Databases From A CD

Apr 9, 2005

Gusy im currently making and app that runs from a cd rom yet it uses databases made in sql and i already made the part for ataching the database from the cd but i haven't been able to do 2 things:
1. Can i actually attach my database from a cd without any need for the computer
2. does anyone has the code for detaching a database in Sql in c#
thanks ahead

View 6 Replies View Related

2 Databases

Jul 30, 2004

How can you make two databases communicate with each other?? they both reside in sql server 2000. How would I go about doing that. Access 2000 is the interface

View 3 Replies View Related

From 6.5 Databases To 7.0

May 21, 2004

Hello,

Is it possible to restore a 6.5 database to a 7.0? I backup the entire 6.5 database to a BAK file then tried to restore it to SQL 7.0 but it didn't work. SQL 7.0 states that the disk device is not a valid Microsoft Tape Format backup set.

We have an application that was developped long long time ago by a old team and I don't want to upgrade the server. I tried to build a second 6.5 server (new hardware) but I couldn't restore the database as SQL 6.5 states that the server is in single user mode. I don't know how to switch to multi users mode. The database I want to restore is not in DBO or single user mode.

Any help will be appreciated.
Thanks.

Toan.

View 1 Replies View Related

100 Databases

Mar 16, 2006

Hello,
Can someone show me the TSQL I need to write in order to create 100 SQL databases in one go.
I want them to be called DB1, DB2, DB3, DB4...etc etc

I know how to create one database;
---------------------------------------------------
USE MASTER
GO
CREATE DATABASE DB1
ON PRIMARY
(
NAME = DB1_dat,
FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDataDB1.mdf',
SIZE=4,
MAXSIZE=10,
FILEGROWTH=1
)
-------------------------
But I don't want to do this 100 times,

Thanks in advance
ICW

View 20 Replies View Related

Databases

Feb 10, 2007

i really want to know,there are how types of databases?how many types of servers?how many types of inernet based languages?

View 2 Replies View Related

Looking For SQL Databases For

Jul 20, 2005

Hello,I am currently looking for databases that contain available prescriptionmedications and diagnosis codes (i.e. ICD9 medical diagnosis codes) thathave been created using SQL.Anybody know of companies that have these commerically available?Thanks,Eric

View 1 Replies View Related

Using Two Databases Vs. One?

Oct 20, 2007

I'm writing an app in vs2005 using sql express. This may be the wrong forum for this question, but here it is. I'm going to have several tables: Seven for Customer information, six for Employee information, 15 for Misc Definitions (status types, skill types, discipline types, gender types, etc.) and 10 for Scheduling information. This is a total of 38 tables.

Is there any advantage to having four databases, one for each type, or should I put all 38 tables in one database and add relationships to everything?

View 5 Replies View Related

Example Databases

May 28, 2007

Hello Everyone:



I am testing SQL Sever Express and I would like to know how to load the adventure works databaes into SQL server? I have downloaded the files in accordance to the instructions providered however, when I open SQL Server the Adventure Works database does not appear. How do I add the adventure works database to my instance of SQL Server? It appears that the files downloaded correclty and teh setup executed correclty, and the database files can be found in my control panel.



Thanks

John

View 1 Replies View Related

Two Or More Databases

Jun 20, 2007

How do I join tables from two separate databases in SQL Server 2005 reporting services?

View 1 Replies View Related

Use CLR UDT Across Databases

Jan 22, 2008

I have assembly with several UDT. Let's call one of them - T. I have loaded assembly to 2 different databases. Let€™s call them €“ d1 and d2. I also have function F(), that returns type T.
I want to run such statement:

use d2

declare
@v t = d1.dbo.F();

If type T in d2 lies in schema dbo, then such statement will work without any problems. But if I want to register T not in dbo, but in some other schema (let€™s call it S), I will have an error:

Msg 206, Level 16, State 2, Line 1
Operand type clash: sysdb.ssma_oracle.Record is incompatible with t1.ssma_oracle.Record

Only conditions for CLR UDT, that I found, were:

When you use user-defined types across databases, you should remember the following:
· The types must have the same name, including the same CLR name, and must be implemented through the same assembly in both databases. Assemblies in two databases are identical if they have the same name, strong name, culture, version, code access permission set, and binary contents.
· You must have SELECT and EXECUTE permissions on each user-defined type column referenced in the query. These permissions are checked with regard to the database in which each column is defined.
· If a type that is implemented in one assembly calls a method in another assembly, the cross-assembly call is checked in the same way as if the user-defined type query were issued without crossing databases.

But I still have the same error. Why?

View 3 Replies View Related

Help Needed About Databases!

Aug 23, 2006

 Hi
I'm a newbie to SQL server..... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user? What about the case where I reaches let's say 1000 users in the one user per database case, it would be extremly difficult to have an overview of the databases or what?
The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom.
Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P.
Regards
Joachim

View 1 Replies View Related

Accessing Two Databases

Sep 16, 2006

Hi, I am developing a small asp.net  application where I read from a readonly omnis database via odbc then dependant on user input write records to a second database.My first idea was to use an odbc connection to Omnis database and write to ms sql database.  There is quite a few queries that need to be made to the Omnis database and I am binding a datagrid to the MS Sql server database.  I was hoping to use stored procedures to update the mssql tables.  So there will be much opening and closing of connections. Should I go down that road or should i think of using an Access database, with linked table to the Omnis database, then only one connection needs to be made.  And I can store my information in the Access database tables. Thanks.

View 3 Replies View Related

Scheduling With SQL Databases.

Feb 22, 2007

I know enough about Databases to get myself in trouple.  So maybe someone can point me in the right direction, or help me out.  I work for the American Red Cross in Provo UT.  We want to be able to put class registration on line so that someone can visit our website chose the class they want to take, register and pay for it all on-line.  Our website http://www.redcrossut.org is hosted with godaddy and I am able to post a SQL Database.  I know that our website as it stands is very poor.  The people who have maintained it in the past have no idea what they were doing, my job is not the website, I am over Health & Safety Training, but have done some work with websites for fun in the past.  I know a little about Visual Studio and plan to rebuild the site using C#.  I can build a basic Database and interface to it with Visual Studio, Godaddy can help me with the payment part, but I have no idea how to set up SQL to allow scheduling.  Any help would be appreciated.  Even if it’s just a link to a tutorial.
 
Ed Hall
Director Health & Safety Services
Mountain Valley Chapter
American Red Cross

View 2 Replies View Related







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