Solved

SQL server 2008

Posted on 2013-01-02
8
199 Views
Last Modified: 2013-01-04
Dear all/

I would appreaciate if someone could comprehensively enlighten me on this.
Can anyone answer the following terms in context of installation of SQL 2008 database server.


 What is the recommendation for SQL Compatibility Level requirement (80, 90 or 100)?
 while installation of the database for an application,if the application documentation does not mention it at all.
       

What is recovery model (simple & full). what should be the model if the application does not mention it at all.what is "point in time restore" needs?

What should be the Autogrowth recommendations for db. should the autogrowth turned on or off. if the application does not mention it at all.the application only mentions that you can add filegroups later using application interface when required.


· what should be the sql user for database access.  Application documentation mentions about sa user. what privileges does this user has and is it Ok to use this one.
                   
·
0
Comment
Question by:AmmarDJ
8 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 500 total points
ID: 38737611
Might not be the most thorough response, but here are my thoughts.

- What is the recommendation for SQL Compatibility Level requirement (80, 90 or 100)?
Compatibility level can be considered the "version" of the database.
Since there's not specification as to what "version" should be installed, I would go for the highest. Why? Because all your "old" code/functions will work with new "versions" (forward compatibility), while the opposite is not always true.

- What is recovery model (simple & full). what should be the model if the application does not mention it at all.what is "point in time restore" needs?
Simple recovery model should not be an option if you need Point In Time Restore.
Full gives you all the possibilities of restoring a SQL Server Database (as long as you make the appropriate backups), so if no specification is set, I'd go for that one.

- what should be the sql user for database access.  Application documentation mentions about sa user. what privileges does this user has and is it Ok to use this one.
The best practice is to NOT use the SA account for application development.
The reason behind this is that the SA account is an "Admin" account with ALL the priviliges on the server. For security reasons this account should not be used by anyone except the DBA (in most cases it is disabled, I think in SQL 2008 it's disabled by default).

The "optimal" way to go would be to create a new SQL user for this application and grant him only the "necessary" access to the database/server.

Hope it helps.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 38737633
What is the recommendation for SQL Compatibility Level requirement (80, 90 or 100)?
 while installation of the database for an application,if the application documentation does not mention it at all.
100 = SQL Server 2008 and SQL Server 2008 R2
       

What is recovery model (simple & full). what should be the model if the application does not mention it at all.what is "point in time restore" needs?
http://msdn.microsoft.com/en-us/library/ms189275.aspx

What should be the Autogrowth recommendations for db. should the autogrowth turned on or off. if the application does not mention it at all.the application only mentions that you can add filegroups later using application interface when required.
http://www.sqlservercentral.com/Forums/Topic697970-146-1.aspx


· what should be the sql user for database access.  Application documentation mentions about sa user. what privileges does this user has and is it Ok to use this one.
Think of SA as (god mode) they have full access to everything in the DB and when configuring connections to other applications its best to use SA account
0
 

Author Comment

by:AmmarDJ
ID: 38737990
As been told the sa account is not a safe user to set so what should be the permissions set on another created user. I need a user which can create the initial application database i.e. admin data
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38738073
What does this "new" user have to do?
The database is only going to have to be created once, if you want you can create it with the SA account (or any other account that can create a database in the server), but use a different account (the new one) as the application user.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:AmmarDJ
ID: 38738175
the "new" user (not the sa user) is mandatory as far as this application is concerned to create the initial database for the application.Please note that this database setup creates the admin schema to run this application.

Before doing this , the database engine has already been installated with an appropriate user,

SInce the DB administrator would never give the sa user to create this initial database for the applicaion (admin schema) how can another user created (which roles) to enable this.

Also I would appreciate if someone could tell me if I shoud use sql server authentical, windows authentication or mixed mode. How to decide between the three when installing and configuring the database server.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38738252
In order to be able to create databases the user must be a member of the dbcreator server role.

SQL Server Authentication is necessary when you're using a SQL user to connect to the database (as in your case)
Windows Authentication is used when you authenticate in the server with your windows user, used with Active Directory.

Mixed mode allows you to connect to SQL Server using either a windows account or a SQL Server user...

Since you need to authenticate with a SQL Server User, you need to enable Mixed Mode Authentication.
0
 

Author Comment

by:AmmarDJ
ID: 38738384
Thanks guys for your help.

Lastly can you please tell.


What is "built in system account" . This pops up while installing the database engine,

And what is sql collation.

Also can I create an instance for my application with a user with sysadmin role other than the 'sa' user .
If yes ,how?

regardss
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38739596
--looks like this post should be set as at least 10 questions: please do not add extra questions in the 1 question... open a new one
---
check:
"built in system account":
Built-In Accounts
technet.microsoft.com/en-us/library/cc722455.aspx


Default User Accounts and Groups
http://technet.microsoft.com/en-us/library/bb726980.aspx
---

sql collation:
SQL Server Collation Fundamentals
http://technet.microsoft.com/en-us/library/aa174903(v=SQL.80).aspx
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now