wiki.dynamicsbook: NAV native database vs SQL

[Blog bot]

Are you ready to move





в†ђOlder revision
Revision as of 19:39, 30 April 2008

Line 48:
Line 48:

If you change to SQL, then by far the biggest internal issue for an end user, is going to be SQL maintenance. Once Navision Server is up and running that really is it. You have your daily backups, you do a restore 2 or 3 times a week to confirm that you have a backup. You have a plan that allows you 24 hours down time whilst a new server is brought on line if the system fails, and a procedure to reenter the lost days data, so all is fine. Not so with SQL. Once you move to SQL, you need to have some one that will maintain the server. In this case, your best bet (at least for the first 6 months or so) is to contract with your Dynamics VAR (NSC) to come in on a regular basis (once a week in the beginning) to do the maintenance for you. But eventually you are probably going to want to get someone in house SQL certified, other wise it will get very expensive. Don't get into the idea that you will just have someone come in if you have a problem, SQL needs regular maintenance. If you change to SQL, then by far the biggest internal issue for an end user, is going to be SQL maintenance. Once Navision Server is up and running that really is it. You have your daily backups, you do a restore 2 or 3 times a week to confirm that you have a backup. You have a plan that allows you 24 hours down time whilst a new server is brought on line if the system fails, and a procedure to reenter the lost days data, so all is fine. Not so with SQL. Once you move to SQL, you need to have some one that will maintain the server. In this case, your best bet (at least for the first 6 months or so) is to contract with your Dynamics VAR (NSC) to come in on a regular basis (once a week in the beginning) to do the maintenance for you. But eventually you are probably going to want to get someone in house SQL certified, other wise it will get very expensive. Don't get into the idea that you will just have someone come in if you have a problem, SQL needs regular maintenance.
+
+SQL routine maintenace may include some basic database maintenance tasks:
+
+(1) DataBase Backup -
+ This may include taking full backup every week once, taking differential backup daily, and taking transactional backup every two hours.
+ All this backup routines can be once written and scheduled as "Maintenance Plan" in SQL 2005 by SQL admin in SSMS(SQL server management studio).
+ This kind of backup routine will make sure that we loose minimal data in case of DataBase failure.
+
+(2)Rebuilding an Index -
+ When we perform any data modification operations like INSERT,UPDATE or DELETE, fragmentation can take place on tables. When changes
+ are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered
+ in the database.
+ Unnecessarily more data reads are done on fragmented data, so a queries performance against a heavy fragmented data will be very bad.
+
+ SQL admin should keep on checking the scan density on tables by running DBCC SHOWCONTIG (on SQL 2000) and
+ sys.dm_db_index_physical_stats (on SQL 2005).
+ If the scan density on a table is less than 75%, then we need to rebuild of indexes of that table.
+
+(3) Key usage statistics -
+ Time to time SQL admin should keep on checking the key usage statistics on table.Those keys which are not or rarely usaged should be
+ dropped.As unnecessary keys create an overhead while writing records to the table.

==Maintaining a SQL server== ==Maintaining a SQL server==




Источник http://wiki.dynamicsbook.com/index.php?tit...database_vs_SQL