question

skolip2 avatar image
skolip2 asked

Database Architecture

Hello, Need help with new database achitecture design like log file, primary and basic steps for a beginner. Can any one tell me how can we design the architecture in real time scenario ?
ssisarchitecture
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
Database architecture is a subject that fills many books and on which careers can be built. If you care to be a bit more specific this community might be able to help, otherwise my very general suggestions are: 1. Database architecture can be harder to change in the future than other parts of a software project, so it is worth taking the time to **do it right**. For a large project that does not have a database expert attached to it, this just might mean hiring a temporary consultant. 2. Unless you have a very good reason not to, **normalize** to at least 3NF. This will prevent certain types of data inconsistencies and help keep the database smaller. (Very good reasons generally include creating a data warehouse using a star schema.) 3. Take your best guess at **indexes** during design, but remember to revist that topic when you have actual usage data. 4. Every table should have a **primary key**. Amoungst other reasons, this is required for normalization. (The one exception might be staging tables where data is sorted before being put into the normalized tables. But I would keep staging tables out of your production database and even on a different server if possible.)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
The benefits are quite a few. Few reasons are - Not big log file size (The benefits for itself are quite a few) - Only real tables in the main database. Sometimes it is a restriction - No main workload of Sorting/Indexing, hashing, finding incremental updates etc on the main server - Simple Recovery model can be used on a different database At the moment I remember only these :)
1 Like 1 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
I don't really understand why staging tables should be kept away from production database and/or production server. On other filegroups for sure, and the SSIS-packages used for loading run on other servers.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Usman has some good reasons. Another reason is that (at least in the workflows I have dealt with) the staging tables frequently have ad hoc code run against them to look over the data, tweak, and reformat it in flexible ways. This means that we tend to have several people with database owner level permissions there.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Timothy has anwered about the table- and index-design. Here's something general to say about planning your hardware and where to place files etc. - If possible, place the logfile on its own hard drive. Make it relatively large and make it grow with large increments, to avoid too many virtual log files. - Place the datafile on RAID5-disks. Size it relatively large and make it grow in large increments. - If possible, enable instant file initialization so your data file growths are made quick. - Never ever allow SQL Server to auto-shrink your files. Here's why: [ http://www.karaszi.com/SQLServer/info_dont_shrink.asp][1] - If you have enterprise edition and you have more reads than writes against your database, you might benefit from enabling compression (less space required for for index pages and data pages means more pages in memory). - Maintain your indexes and your statistics (look at Ola Hallengrens maintenance scripts for example: [ http://ola.hallengren.com][2]) - Last but not least: It depends. If you have a database loaded once per year and a really busy search application against the data, your setup should be different than a database with transactions every second of the day. If you have a 24/7-requirement for database availability, your redundency strategy will differ a whole lot from a database which is used monday-friday and that can be happily switched offline during weekends. [1]: http://www.karaszi.com/SQLServer/info_dont_shrink.asp [2]: http://ola.hallengren.com
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.