MSDE and 2GB database

MSDE and 2GB database

Post by Miroo_new » Sat, 06 Sep 2003 10:34:29



Hi,
Is there any way to override 2GB size limit
for database in MSDE?
Of course except of buying bigger version of SQLServer
because the database is used on many computers and it becomes
greater and greater... I couldn't afford to buy so many
bigger versions of SQLServers for my clients...And there is rather
small possibility to lower number of data in database.

There is a lot of relations between data so it would be
a great problem with splitting it between several
databases.

Is there any solution?

Regards,
Miroo

 
 
 

MSDE and 2GB database

Post by Benny Tordru » Sat, 06 Sep 2003 11:19:01


Miroo,

The 2GB limitation is by design from Microsoft regarding MSDE.

Only option is to upgrade to SQL Server Standard Version (or any other
version)

Best regards,

Benny Tordrup



Quote:> Hi,
> Is there any way to override 2GB size limit
> for database in MSDE?
> Of course except of buying bigger version of SQLServer
> because the database is used on many computers and it becomes
> greater and greater... I couldn't afford to buy so many
> bigger versions of SQLServers for my clients...And there is rather
> small possibility to lower number of data in database.

> There is a lot of relations between data so it would be
> a great problem with splitting it between several
> databases.

> Is there any solution?

> Regards,
> Miroo


 
 
 

MSDE and 2GB database

Post by Shinichi Yoned » Sat, 06 Sep 2003 13:14:01


Hi,

Quote:> because the database is used on many computers and it becomes

 For local SQL Server data storage on a client computer?

SQL Server 2000 Personal Edition
(One Master SQL Server - a lot of PC with local SQL Server)

-------

Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003


Quote:> Hi,
> Is there any way to override 2GB size limit
> for database in MSDE?
> Of course except of buying bigger version of SQLServer
> because the database is used on many computers and it becomes
> greater and greater... I couldn't afford to buy so many
> bigger versions of SQLServers for my clients...And there is rather
> small possibility to lower number of data in database.

> There is a lot of relations between data so it would be
> a great problem with splitting it between several
> databases.

> Is there any solution?

> Regards,
> Miroo

 
 
 

MSDE and 2GB database

Post by Miroo_new » Sat, 06 Sep 2003 14:05:44




Quote:> > because the database is used on many computers and it becomes
>  For local SQL Server data storage on a client computer?

People have own databases all over the country. There is not one central
SQLServer.
Is there a possibility to have FK relations between tables in two databases
on one server? Maybe I should move some tables into another database?

Solution with SQL Server upgrade is too expensive for me.

Regards,
Miroo

 
 
 

MSDE and 2GB database

Post by Jacco Schalkwij » Sat, 06 Sep 2003 14:27:02


Hi Miroo,

You can't have foreign key relations between tables in different database
but you can implement referential integrity between tables in two different
databases via triggers.

You might also want to review your database design and check if you can
change columns to use smaller datatypes like smallint instead of int, and if
you can implement a way to archive data to a table in a different database.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.




> > > because the database is used on many computers and it becomes
> >  For local SQL Server data storage on a client computer?

> People have own databases all over the country. There is not one central
> SQLServer.
> Is there a possibility to have FK relations between tables in two
databases
> on one server? Maybe I should move some tables into another database?

> Solution with SQL Server upgrade is too expensive for me.

> Regards,
> Miroo

 
 
 

MSDE and 2GB database

Post by WKid » Sat, 06 Sep 2003 16:28:11


    Look into partitioning your data into seperate databases/tables. How you
would partition the data (by year, state, account, etc.) depends on the case
usages of how the data is referenced and how often.
    Also explore the possibility of migrating rarely used data to offline
backups. You mentioned that the databases were installed at various remote
locations. If you have remote software, like PC Anywhere, installed at each
site, then you can use the command line version of SQL Server Bulk Copy to
export records to tab delimited format. This could also be easily run by the
client from a batch file or VB script.

For example:

BCP mycompany..sales out c:\backup\sales20030905.dat -c -S SERVER -U UID -P
PW

To bulk load the records back into the table, do the following:

BCP mycompany..sales in c:\backup\sales20030905.dat -c -S SERVER -U UID -P
PW


Quote:> Hi,
> Is there any way to override 2GB size limit
> for database in MSDE?
> Of course except of buying bigger version of SQLServer
> because the database is used on many computers and it becomes
> greater and greater... I couldn't afford to buy so many
> bigger versions of SQLServers for my clients...And there is rather
> small possibility to lower number of data in database.

> There is a lot of relations between data so it would be
> a great problem with splitting it between several
> databases.

> Is there any solution?

> Regards,
> Miroo

 
 
 

MSDE and 2GB database

Post by Miroo_new » Tue, 09 Sep 2003 10:51:30




Quote:> Hi Miroo,

> You can't have foreign key relations between tables in different database
> but you can implement referential integrity between tables in two
different
> databases via triggers.

I think it would be too slow with data I have...

Quote:> You might also want to review your database design and check if you can
> change columns to use smaller datatypes like smallint instead of int, and
if
> you can implement a way to archive data to a table in a different

database.

I can change datetime to smalldatetime in most cases
but I'm not sure how it is supported with software
I use (Delphi7 Pro).

I'm doing experiments about it now.

Thank you all,
Miroo

 
 
 

MSDE and 2GB database

Post by Aaron Bertrand - MV » Tue, 09 Sep 2003 15:50:49


Quote:> I think it would be too slow with data I have...

Well, speed isn't free... you can't have everything your application needs
for free, if a free database system doesn't do it all.