Advice?

hellooo

I have an Accounting system(vb.net 2003, SQL server 2000), every new year data is cleared, but i may use some data from previous years (such as liabilities)?

whats the best way to that ?

-Shall I create programmatically a new clone DB every year (new DB with same structure as previous year)
OR
-Shall I add a "year field" for tables in DB?

knowing that data will keep growing every year?

whats the best solution, knowing that i dont want the end user of my application to do anything manually, such as creating DB ......

Thank you

Hi,

I would add the year field and provide a tool to delete entries older than a given amount of years. Most liability rules are defined to keep data for a fixes amount of time (e.g. 1, 3, 10 years). That helps to limit the growth of the DB.

--
SvenC

|||Hi,

as you did not mention which edition of SQL Server you are using, you should take a look at partitioning. Vertical Partitioning will help you based on the date the data was entered (or should be archived) to autimatically let SQL Server move this data to another partition (SAN Storage area or another disk). You will not need to worry about picking the data then from some other database or changing your logic, because of the differentiation of the relational and the storage engine. E.g.: As the relational engine will get a command to retrieve all data from the years 2000 (which are already "outdated") it will query the storage engine which has information about where the data is stored (partition).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Man thanks alot, but can you praphrase ?
or give me a link to an article tht might help me

I need a brief A_Z solution ......

Thanks

|||ohh sorry i forgot to tell you SQL Server 2000 Enterprise Edition|||The answer is you NEVER delete anything in an accounting system, the accountants will not be happy if you delete things at year end.

The accounting system logic should always be date range specific. As soon as you hard code in Jan - Dec, someone will want to change to July-June.|||

I dont wanna delete anything, I want at each beginning year to create a new DB(same structure as previous year) and may use some Data from previous DB (such as liabilities of previous year).

That all wht I need, can you direct me on the way to so that?

Thank you

|||

That would make quering the data of several years a nightmare.

--
SvenC

|||so whats the solution?|||

hi,

have a new database per year.the advantage:

1. you can put the entire database (when everything has been balanced)

on a readonly mode and its readonly no matter who logged into the system

2. you're not going to delete anything

3. in this way your scaling out your enterprise database

in the front end:

give the user a dropdown to choose which database he is going to connect

behind the scene he/she is just choosing a connection string

store the connection string in a class

and let every sql connection object to use the connection string stored in the class.

regards,

joey

|||

thanks man,

but how to clone my DB2006 to DB2007?
and still access DB2006?

|||

hi,

use the copy database wizard.

open enterprise manager.

drill down to the database you want to copy.

click on tools>wizard>management>copy database wizard.

youll be fine from there

regards,

joey

|||thanks for these replies

I need to do this cloning programmatically|||Isnt there a way to do this programmatically?|||

Have a look at SQL-DMO.

You find the SQLServer object which has a DetachDB and AttachDB method which you can use to detach a db, copy the files and attach it on another server or the same server as a different DB. You might need the Database object prior to that to find the database files.

MSDN has information about the class library and I guess BOL also.

--
SvenC