Benefits of SQL Server

Microsoft SQL Server is a robust and scalable solution all of your business application needs. Whether you're using it for data centralization for an Access front-end, a primary data source for your web-based software system, or for managing content on your business website; SQL Server is ideal for your application.

Learn more about the benefits of using SQL Server for your data storage needs:

• Server Roles
• Database Roles
• Scalability
• Centralized Data
• Data Recovery
• Security Benefits

Server Roles

Server roles provide levels of access to server operations and tasks.

Server roles are predefined and grant the permission for the whole server. These roles are not database specific and cannot be customized. The table below provides a listing and explanation for each type of server role.

sysadmin	Able to do anything in SQL Server
serveradmin	Able to modify SQL Server settings and shut down SQL Server
setupadmin	Able to install replication and control extended stored procedures
securityadmin	Able to control server logins and create database permissions
processadmin	Able to control SQL Server processes
dbcreator	Able to create and modify databases
diskadmin	Able to manage disk files
bulkadmin	Able to execute bulk insert statements

Database Roles

Database roles provide the assignment of a set of database-specific permissions to an individual or a group of users. Database roles can be assigned to Windows Authenticated logins or SQL Server Authenticated logins. Roles that are assigned to Windows Authenticated logins can be assigned to NT users and NT groups. Roles can also be built with a hierarchical model.

Database roles are database specific. SQL Server provides three types of roles:

1. Predefined database roles
2. User-defined database roles
3. Implicit roles

Predefined Database Roles

Predefined database roles are standard SQL Server database roles. Each database in SQL Server has these roles. Predefined database roles make it easy to delegate responsibility.

Predefined database roles are database specific and cannot be customized. The table below provides a listing and explanation for each type of database role.

db_owner	Has complete access to all objects within the database, can drop and re-create objects, and has the capability to assign object permissions to other users. It can modify database settings and perform database maintenance tasks. This role encompasses all functionality listed in the other predefined database roles.
db_accessadmin	Controls access to the database by adding or removing Windows Authentication users and SQL Server users.
db_datareader	Has complete access to SELECT data from any table in the database. This role does not grant INSERT, DELETE, or UPDATE permissions on any table in the database.
db_datawriter	Can perform INSERT, DELETE, or UDPATE statements on any table in the database. This role does not grant SELECT permission on any table in the database.
db_ddladmin	Has the capability to create, modify, and drop objects in the database.
db_securityadmin	Performs security management within the database. This role manages statement and object permissions and roles within the database.
db_backupoperator	Has the capability to back up the database.
db_denydatareader	Denies SELECT permission on all tables in the database. However, this role does allow users to modify existing table schemas. It does not allow them to create or drop existing tables.
db_denydatawriter	Denies data modification statements (INSERT, DELETE, or UPDATE) from being performed against any tables in the database.
public	Every database user is a member of the public role. A user automatically becomes part of the public role when she is permitted access to the database.

Scalability

Computer systems typically grow over time. Maybe you have a need for 30 concurrent users in your application now, but you have plans to grow and expand. SQL Server is built to be scalable and is able take your application from 30 users to thousands of users, quickly and easily.

Table size can be an issue with other database servers, but not SQL Server. The table size is only limited by the amount of space that you have on your physical server. This means that 90% of SQL Server users can store all of their data within the same instance.

This database server gives you so much flexibility that you can have multiple applications using the same SQL Server. Imagine your web applications and desktop applications using the same data, so that people in the office and employees remotely could simultaneously update data just as if they were sitting next to each other. When you choose SQL Server, you choose flexibility for your data-driven applications.

Centralized Data

Having a centralized place to store data is a great benefit to SQL Server users. Centralization is a primary SQL Server benefit and means that everyone is using the same data source. As a result there is no need to merge information together in order to receive an accurate version of a record.

With centralized data, every time that you retrieve a record, you will be confident that you have the latest information.

Data Recovery

One of the SQL Server benefits is the presence of built-in tools to manage data and database backups.  Imagine if you had a Microsoft Access database and it was deleted.  There is no good way to get your information back.  With SQL Server, you have the ability to schedule regular backups of the database.  These back-ups could be stored in a separate location for disaster recovery.

There are two kinds of backups available in Microsoft SQL Server:

• Full Backup
• Differential Backup

Full Backup

A full backup stores a complete copy of the database on any supported backup media.  You do not have to take the database offline to perform a full backup, however these backups consume a large amount of resources and users may notice that response times are a little slower.  Full backups can be scheduled for off peek times to minimize the impact that performance degradation would have on the end users.

Differential Backup

There are situations where you may not want to use as many resources as a full backup requires, but feel that it is important to have a backup at a certain time.  This is when you may consider a differential backup.  This backup uses less resources since it backs up information that has changed or been added since the last full backup.  **It is very important to note that a differential backup alone will not be enough to restore the database, since it only contains data that was modified since the last full backup.

Security Benefits

There is an old saying that states that knowledge is power.  The ability to securely store and retrieve data is one of the pillars of the IT world.  With SQL Server there are several security options to ensure that your data is stored safely.

SQL Server supports two security modes:

1. Windows Authentication
   • Uses the credentials of the person logged into the Windows machine.
2. SQL Server and Windows Authentication (Mixed Authentication)
   • Uses both Windows Authentication and SQL Server Authentication.

If you are using Mixed Authentication, please note that sa (which means system administrator) by default, does not contain passwords.  The sa username is very powerful and gives the user the right to manipulate all databases on SQL Server.  After the installation of SQL Server, please make sure that changing the sa password is at the top of your list of things to do.

If you are using Mixed Authentication it is good practice to create a username and password for applications that would restrict access to only the databases that the application uses.

There are two types of permissions in SQL Server:

• Server Roles
• Database Roles