This is a very simple task, however, one must ask themselves a few questions before doing this.
how much space do we need?
How many concurrent users do we expect on the server?
Always look at more users that you think or will use the database for expation.
What is the DB going to be used for?
I.E Office, Intranet, Internet,
If its going to be used for the internet, intranet we need to think about security because we'll need to utilze predefind zones within the firewall.
Do we need to configure IPSEC or SSL?
Do we know when we can do maitanance esp if the db needs to be up all the time?
Backing up the database?
Creating a db with sql management studio
select databases then right click and click create
you will now see this screen after you cleck create
When creating a database one rule you should always follow is the database should not contain spaces. Putting spaces in the name requires surrounding it with brackets [My Database] in TSQL to avoid syntax errors and I prefer not to have to use the brackets. I follow that by almost always setting the owner of the database to SA. In most cases on systems I administer changes will always be made by someone in the sysadmin group, rarely we might put someone into the db_owner role to let them make whatever changes are needed. The only value derived from being the database owner rather than a member of db_owner is that the owner is also dbo, which means that if they don't qualify objects with a schema when created they will be owned by dbo by default. Members of db_owner can use two part syntax to put objects into the dbo schema (create table dbo.employess...). Note that you can always change the database owner later using sp_changedbowner.
Full text indexing isn't commonly used, but we can activate now if we know it will be needed, or it can activated later.
As you can see in the next image I'm going to call my database Test, and it has automatically generated the logical names for the two files that get created by default (one MDF and one LDF). I never change these names as the generated one works fine. Moving to the right I've taken a guess at my database usage and increased the database size to 100 MB and the log size to 10 MB. It's just a guess though, so why bother? Mainly to avoid fragmentation by allocating a good chunk of space at once.
Once you have selected the databases name in this case you will need to change the default values for autogrow to do this click on the 3 dots next to the autogrowth it will look like bt 1mb, unrestricted growth ... you will then see this screen
I rarely turn autogrow off, and contrary to what many recommend I typically let the database grow if and when it's needed, not deliberately resizing the database to maintain a given amount of free space. The advantage to doing it deliberately is that you avoid the performance hit of waiting for the file to grow if it happens during production hours, but that penalty has been greatly reduced in SQL 2005 with instant initialization, a feature change that causes SQL to initialize pages on first write instead of during the auto grow. The advantage of doing it my way is less time spent on a very mundane task. The more important decision is whether to grow in percent or megabytes, and how much. There's no right answer, but in general we want to grow in as large a chunk as makes sense to avoid physical fragmentation on the drive. For this example I'm going to set both to grow at 10%, and leave file growth unrestricted.
Click on the tab next to path
and change the db bath where you would like the data and log to go and the click cilck ok this will create the database you can also change the name of the file here if you like.
Here is also the code to create this db.
REATE DATABASE [SCD] ON PRIMARY ( NAME = N'SCD', FILENAME = N'G:\MSSQL2K5\MSSQL.1\MSSQL\DATA\SCD.mdf' , SIZE = 102400KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'SCD_log', FILENAME = N'G:\MSSQL2K5\MSSQL.1\MSSQL\DATA\SCD_log.ldf' , SIZE = 10240KB , FILEGROWTH = 10%)GOEXEC dbo.sp_dbcmptlevel @dbname=N'SCD', @new_cmptlevel=90GOALTER DATABASE [SCD] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [SCD] SET ANSI_NULLS OFF GOALTER DATABASE [SCD] SET ANSI_PADDING OFF GOALTER DATABASE [SCD] SET ANSI_WARNINGS OFF GOALTER DATABASE [SCD] SET ARITHABORT OFF GOALTER DATABASE [SCD] SET AUTO_CLOSE OFF GOALTER DATABASE [SCD] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [SCD] SET AUTO_SHRINK OFF GOALTER DATABASE [SCD] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [SCD] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [SCD] SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE [SCD] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [SCD] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [SCD] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [SCD] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [SCD] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [SCD] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [SCD] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [SCD] SET READ_WRITE GOALTER DATABASE [SCD] SET RECOVERY FULL GOALTER DATABASE [SCD] SET MULTI_USER GOALTER DATABASE [SCD] SET PAGE_VERIFY CHECKSUM GOUSE [SCD]GOIF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [SCD] MODIFY FILEGROUP [PRIMARY] DEFAULTGO
go
sp_changedbowner 'sa'
go
No comments:
Post a Comment