I have planned the layout for this first part and found that in only employee salary table, 40,000 records per month will be stored. That comes out to be around 480,000 records annually. This is for one table alone, excluding data in other tables.
Since each section of the department will be integrated into this later, this application will become the backbone the department. The employee service, leave, salary, allowance, deduction and other records shall be maintained.
For this type of mission-critical application, I want to get some queries cleared:
(1) What backup strategy should be followed? I want to have schedule and maual backups both. Is there any way to have a mirror image on another server?
(2) What considerations to keep in mind in the inital stage of database design?
(3) How to keep the design scalable and configurable to meet future needs?yes you can have a mirror image of the database in another server by configuring log shipping or database mirroring both are high availability and disaster recovery solutions............you can refer the below articles for the same,
www.sql-articles.com
www.sql-articles.com/articles/dbmrr.htm and
www.sql-articles.com/articles/lship/lship.htm regarding the rest i am not sure........you can configure mirroring or log shipping depending on the criticality of the database........
|||Initially, I would like to start with the Express Edition. Does Express Edition provides these features? I also want to know whether SQL Server 2005 now supports Multi-Version Concurrency Control (MVCC) now?|||
For database mirroring you can have your monitor server as express edition but the partners need to enterprise or std edition.......for log shipping you need to have either enterprise,std or workgroup edition..........
|||Those are fairly broad questions but in regards to the other issues, a backup strategy is determined by the business needs. You need to look at any SLAs that will be in place, issues of data loss, time to recover, frequency of inserts and updates, frequency of log backups, location of backups, size of the database and backups, if backups go to tape or network storage, etc. A general starting point for many databases is a daily full backup and then log backups depending on many of the already mentioned factors. Having both scheduled and manual backups - I'm not sure what you mean by this. Most backup routines are scheduled, automated. You can always do an ad hoc or manual backup in addition. And you generally do additional backups prior to activities such as applying service packs, implemented changes.
The initial considerations would be to just follow normal database development practices in terms of normalization, keys, selecting appropriate data types for columns, placement of log and data files, usage of tempdb, security considerations, etc. Chosing your indexes and accounting for index space would be important as well as considering potential data archiving strategies that may be needed. You'd want to consider other maintenance tasks as well such as regular integrity checks, reorgs or rebuilds of your indexes to address fragmentation. You would want to consider the server as a whole, not just the one database and any impacts the various database have on each other, system resources.
-Sue
|||Sue has given you lots of good info to consider.
I'd also add that you probably want to go with at least Workgroup Edition for a business-critical database such as this.
There are limitations built into Express which you will run into sooner or later (such as the 4GB limit on DB size).
Don't design your overall solution for a small corner and then grow - design for your eventual big picture and fill in the pieces as you get to them.
No comments:
Post a Comment