Financial Year Closing...any sugestions

Hi,

I am a MSSQL DBA in a production environment in India.
I have a question, I am working on Navision 4.3 with SQL Server 2005 sp2.

Now the problem I have a pretty huge database say around 350 GB in Size
Our financial year closing is 2009-2010 which is exactly at March 31st.

I want to close the financial year for the same and start afresh
with a new database in the same server. Actually I want to close postings
in the old database and compress the same and keep it for reporting purpose.
Yes I want to access the old database for reference or reporting.
I would move the old database in a RAID 5 volume and then create a new database.

What are the best ways to do so? What considerations needs to be taken care of?
Is this easy I mean in Navision?

Your valuable inputs would be appreciated.

Best Regards,
Prashant

Prashant,

It’s not a problem at all - normal size for Navision, I would even say it is relatively small (seriously). Actually, there is no need to as you described - there are different methods you could use.

For info about SQL DB optimization & maintenance the first place to look for advices might be this forum’s moderator and our SQL guru Jorg’s blog here http://dynamicsuser.net/blogs/stryk/default.aspx.

Additionally, read this same SQL forum, there are many threads about maintenance.

added: BTW standard license doesn’t allow more than one DB - you need additional granule per every additional DB…

Its just a thought …But can I do the same …like I mentioned above. Is that possible ? If yes then how does one about it.

The main objective is to compress the old data carry out the openings to the new database and start afresh.

Maintenance is not a problem …have visited/read Stryk’s blog/posts/comments upteen number of times.

It is damn useful for people like us coz when u r supporting a prod database with Navision as an ERP many DBA’s expertise go for a toss. even I have learnt the hard way.

Regards,

Prashant

Prashant,

Such compression is possible, but - it is not a task for you as DBA and can not be done by SQL tools - never manipulate Navision database bypassing Navision. This doesn’t mean simply deleting some older records from some tables, Navision has its own functionality built-in, how to compress historical data. In short - Navision replaces many records by a summarised one, or, there are some tables, where Navision really deletes old entries.

Look in Administration / Data deletion… There is what Navision calls Date Compression, too. But you as DBA should understand without saying, that these functions are irreversible - either make backup before, or make a copy DB - as you suggested.

However, not all data can be compressed in such a way, and not all discontinued data can be removed - as you already mentioned, ERP system’s database has it’s specific.