On this article, we will show you several tips to prepare and to plan data aware application with SQL Server 2014. The tips should be considered if you want to make your application evolve with the database technology as well as technology trend like a cloud.
Tips 1 – Avoid Create a SQL Login Directly as the user
It is not a good idea to create an user that has direct access to sql server. It will be great if your application uses Windows Authenticaon (if the application on the same machine) or SQL Authentication with only several user role. Never put end user as a sql login database. It will make you harder to maintenance your database.
Tips 2 – Avoid Views on the database
Views not only will make your database consumes more memory, it will also make your database wont be compatible with the cloud environment like Azure. So drop your Views, use your Stored Procedure instead.
Tips 3- Never Shrink Your Database if less than 10 GB
If you think that shrinking database will help your database performance. Think it again!, shrink a database will make your database highly fragmented. Please consider shrink the database if larger than 10 GB and you follow the shrink method with the rebuilding index.
Tips 4. Creating a maintenance Plan
Fixing database is the last option, please create maintenance plan to make sure your database work on optimal situation. Take a look the Maintenance Plan tips here
Tips 5. Stored Procedure is recommended way
If you have intensive data transaction, please think to use SP rather than direct access via ADO.NET, LINQ2SQL, or ORM.
Tips 6. Backup your database to the cloud
You can backup your database to the cloud to make sure your backup is well stored. we will discuss this backup model on the next post.