We always heard that we should adopt Best Practices in SQL Server and most of the time we tried our best to adopt best practices in our work but the problem is who will analyze the best practices which we did for our database ,SQL Server.
Hmm, that’s the point my dear friends but don’t worry on this part also because Microsoft introduce BPA (Best Practice Analyzer).
I tried this tool recently it is easy to use. It scans your database, SQL SERVER and provides you the best way to adopt Best Practices with your current structure.
You can download the installer of this tool from http://www.microsoft.com/downloads/en/details.aspx?FamilyId=da0531e4-e94c-4991-82fa-f0e3fbd05e63
After install this BPA utility you can easily scan your SQL SERVER, Databases with this.
The BPA will provide you report of scan. The scan report will give you idea about what is missing, what need to do?
Below are some listed options which BPA raised after scan report
1) Backup is latest or is it out dated?
2) Database having any anomalies.
3) Database integrity check required or not?
4) Check Disk IO delay problem
5) Backup should be on separate volume
6) Bulletin\Administrators in sysAdmin Role
7) Set Page verify option
8) SQL Login Password policy violation
9) Database is not in full recovery mode
Although some of the warning in the above list which we can ignore according to our business need.
And many more issues to make SQL Server database robust just check below report snap.
As per my opinion the tool is good but need some more functionality or utility to manage and apply
Best practices. So why you people waiting just download, install and use.
Make your database robust with best practices.
Enjoy Best Practices.
Designing database is one of the most interesting works but on the same time it should be proper because it is base of any business application. We should follow Microsoft best practices while designing the database. Here I am with a simple database design (Address book) concept. I will try to put my best to use Microsoft best practices while designing database. I know you are thinking Address book is so much easy to design, so my answer is yes you are right you can design database easily but using Microsoft best practices it bit more important and I am trying for those best practices.
So first best practice is 1) Define proper data type :-
Most of the time we design database but we ignore proper database but it should not because when you talking for very large scale database application (VLSDB) then wrong data type will give you space problem. According to best practices choose smallest data type first (if it fit in your requirement).We understand this by an example suppose you designed a table with name “seed Data” in which column with name lngId having data type float and float consume 8 bytes and if the seed data cannot exceed more than 10 rows then we should use Tinyint data type instead of float data type because tinyint consume only 1 byte. 2) Define proper Primary & Foreign key:-
According to best practices we should make database normalized and for normalized database we should create proper Primary & Foreign keys. By creating proper primary & foreign keys we get two advantage 1) data anomalies chances reduce we get fast response as well after defining primary key (because cluster index created on primary key) 3) Define proper constraints :-
Microsoft best practices also suggest defining proper constraints for column like not null, unique key constraint on columns so that data anomalies will reduce and we get proper data. 4) Define partition :-
If your database is going to be very large then define proper partition both horizontally & vertically. 5) Normalization: – Database should be proper normalized.
In our Address Book database example we have categorized the data table in 3 categories
1) Lookup table having prefix “lku”
The lookup table is mainly for seed value or we can say this table contains all the data which just act like pre pop data. We have following tables for our Address book
2) Link table having prefix “lnk”
By the name it is clear that its main purpose is making relation between main data record and other data. We have used following tables lnkPhoneToPerson,lnkAddressToPerson,lnkAddressToCompany,lnkURLToPerson,lnkPersonToCompany,lnkPersonRelations,lnkPhoneToCompany
3) Main table which start with prefix “tbl”
The main table indicates by “tbl” in our Address book database we have used following tables tblPerson, tblAddresses, tblPhotos and tblCompany.
In the entire lookup table we have preferred tiny int & Small int data type because we know the value in look up table could not go beyond the tiny int & small int max limit.
In similar way we have applied all the not null constraint to require field provide proper data type.
Please see fig below.
For more detail you can download script for Address book.