Designing Database Solutions For SQL Server
Posted By : Rudhishthir Prakash | 23-May-2018
While designing Database Solutions for SQL Server, the following should be taken care of:
1. Managing a SQL Server Environment
(a). SQL Server Agent Performance Alerts-
(i) An alert is a predefined response to an event.
(ii) Alerts can be triggered by Logged SQL server events, WMI events or SQL server performance conditions.
(iii) Alerts can notify an operator or start a job.
(b). Managing resources with Resource Governor-
It is used to classify workload that is coming in the SQL Server. It can be classified based on Database names, SQL Server Internal and based on applications. The classified tasks are then assigned to a Workload Group that is defined while setting up Resource Governor. Then we assing percentage of resources to specific workload group using Resource Pool.
2. Designing Database Security
(a). Introduction to SQL Server Security-
(i) Securables: Objects to which access must be secured.
(ii) Principals: Security identities that access securables and perform actions.
(iii) Permissions: The actions Principals can perform on securables.
(b). SQL Server Permissions-
(i) GRANT assigns a permission - Inherited permissions are cumulative unless denied.
(ii) DENY explicitly denies a permission - use to override inherited permissions.
(iii) REVOKE removes GRANT or DENY.
3. Managing Server-Level Security
(i) Trusted Server Application Security Model - User accesses an application, then the application uses its own credentials to authenticate and access SQL server.
(ii) Impersonation/Delegation Security Model - User accesses an application, then application impersonates the user and SQL server is accessed based on user identity.
4. SQL Server Authentication Options
Authentication is a process of verifying that an identity is valid. There can be two types of authentication:
(i) Windows authentication - only users authenticated by Windows can connect.
(ii) Mixed authentication - users authenticated by Windows or SQL Server can connect.
5. Managing Server-Level Roles
(i) All server-level principals are members of the public server role.
(ii) Assign logins to fixed server-level roles to delegate administrative tasks.
(iii) Create user-defined server roles if fixed roles don't meet your needs.
6. Managing Database Users
(i) Logins cannot access a database to which they have not been granted access.
(ii) Grant access to a login by creating a database user for it.
7. Database Ownership
(i) Like other objects, databases have owners.
(ii) A database owner will default to the user who created it.
(iii) This user will be mapped to the dbo user.
(iv) Don't have databases owned by user accounts.
Request for Proposal
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Rudhishthir Prakash
Rudhishthir is a technical enthusiast having experience in C#.NET, NodeJS & various front-end technologies. He has great experience in building quality applications with innovative ideas. He also has proven expertise in handling clients.