Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Create New SQL Database
- Thread starter wamason
- Start date
ptheriault
IS-IT--Management
Open Enterprise Manager
Drill down into the server you are going to create the db on.
Right click on Databases and select new database.
in the general tab put the name of the db.
On the data files tab you are going to select the location of the data file. You can also set the initial size and autogrow options here. do the same for the log tab. If you can you should always place your data file on a different array than your log file.
Click OK. This will create an empty db.
- Paul
- Database performance looks fine, it must be the Network!
Drill down into the server you are going to create the db on.
Right click on Databases and select new database.
in the general tab put the name of the db.
On the data files tab you are going to select the location of the data file. You can also set the initial size and autogrow options here. do the same for the log tab. If you can you should always place your data file on a different array than your log file.
Click OK. This will create an empty db.
- Paul
- Database performance looks fine, it must be the Network!
Additionally, you can use the CREATE DATABASE command in Query Analyzer. Books Online has all the command options listed in it for this command.
When you are creating the new database, it will ask you for size requirements. If you want to start of with 15GB of space, then you need to tell the data file to start off that large. Then you also need to tell it how to increment, if indeed you're going to allow it to increment. Same with the log file.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
When you are creating the new database, it will ask you for size requirements. If you want to start of with 15GB of space, then you need to tell the data file to start off that large. Then you also need to tell it how to increment, if indeed you're going to allow it to increment. Same with the log file.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
- Thread starter
- #4
Big first question.........having Enterprise Manager installed does NOT mean you installed the database portion. Enterprise Manager can be installed as part of the Client Tools, in which case there isn't a database.
Where did you do the install? On your workstation or on a server? Which version (edition) did you install (Enterprise, Standard, Developer, other)?
Developer has all the features of Enterprise and can be installed on a workstation, but cannot be used for production.
If you installed Enterprise or Standard on your workstation, then you only have the client tools and must find a server to install the actual SQL Server server portion.
-SQLBill
Posting advice: FAQ481-4875
Where did you do the install? On your workstation or on a server? Which version (edition) did you install (Enterprise, Standard, Developer, other)?
Developer has all the features of Enterprise and can be installed on a workstation, but cannot be used for production.
If you installed Enterprise or Standard on your workstation, then you only have the client tools and must find a server to install the actual SQL Server server portion.
-SQLBill
Posting advice: FAQ481-4875
- Thread starter
- #6
I inherited the SQL server with several databases that have been in production for over a year.
I did notice that under SQL Server Group there are several instances of:
SQL SERVERNAME\Databasename and there are databases under each each instance. How do I create this instance? When I try to create a group under SQL server group I am unable to put that "\" in to create the group so I assume I am doing something wrong. Any ideas?
This is definitely basic stuff, but I am ver new to SQL.
Thanks all.
I did notice that under SQL Server Group there are several instances of:
SQL SERVERNAME\Databasename and there are databases under each each instance. How do I create this instance? When I try to create a group under SQL server group I am unable to put that "\" in to create the group so I assume I am doing something wrong. Any ideas?
This is definitely basic stuff, but I am ver new to SQL.
Thanks all.
The stuff under the SQL Server Group is a Server name. If you don't have a server to add to it, you can't create a new group.
Right click the SQL Server Group and you'll see a line saying "New SQL Server Registration". This is used to add servers to the Group that are not currently part of the group. "Delete SQL Server Registration" removes the server name from your group. Delete does NOT delete a server, it just removes the name from your SQL Server group.
If you want to create a group at the same level as SQL Server Group (the default group), right click Microsoft SQL Server and go to New SQL Server Group. Then you can delete registrations from the default group and add them to the new group. Groups are just organizational structures within EM, BTW. They don't actually mean anything. They're just kinda like folders in MY Documents.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
Right click the SQL Server Group and you'll see a line saying "New SQL Server Registration". This is used to add servers to the Group that are not currently part of the group. "Delete SQL Server Registration" removes the server name from your group. Delete does NOT delete a server, it just removes the name from your SQL Server group.
If you want to create a group at the same level as SQL Server Group (the default group), right click Microsoft SQL Server and go to New SQL Server Group. Then you can delete registrations from the default group and add them to the new group. Groups are just organizational structures within EM, BTW. They don't actually mean anything. They're just kinda like folders in MY Documents.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
- Thread starter
- #8
What I am seeing under SQL Server Group is:
SQL servername\databasename.
I have several instances of this. The SQL servername does not change only the databasename eg.
SQL server\databasename1 (Under this has all the DB's.)
SQL server\databasename2 (Under this has all the DB's.)
SQL server\databasename3 (Under this has all the DB's.)
Server registration does not allow me to create:
SQL server\databasename4
I hope that I am explaining myself properly.
SQL servername\databasename.
I have several instances of this. The SQL servername does not change only the databasename eg.
SQL server\databasename1 (Under this has all the DB's.)
SQL server\databasename2 (Under this has all the DB's.)
SQL server\databasename3 (Under this has all the DB's.)
Server registration does not allow me to create:
SQL server\databasename4
I hope that I am explaining myself properly.
That's because the SQL Server part of the SQL Server\databasename is an actual Server name. The name of a box running SQL Server. It is not a group name. And the Database part of that name is actually called an Instance name. It is not a database.
Below the instance names are databases, but the only way you can create a new SQL Server\Instance is to install another instance of SQL Server on a server as a named instance and then add it to the SQL Server Group as a new registration.
Does that make sense?
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
Below the instance names are databases, but the only way you can create a new SQL Server\Instance is to install another instance of SQL Server on a server as a named instance and then add it to the SQL Server Group as a new registration.
Does that make sense?
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
- Thread starter
- #10
Ahh, now I am seeing light at the end of the tunnel. I checked the services and i now see all the instances in the services.
Now the follow-up question:
How do I install another instance of SQL Server on a server as a named instance? Do I have to install SQL?
Now the follow-up question:
How do I install another instance of SQL Server on a server as a named instance? Do I have to install SQL?
You install SQL and when it gets to the part about naming the instance, the default will be grayed (greyed) out and you will have a chance to enter a name. You will NOT enter the server name. Just enter the 'database4' and the install automatically adds the server name.
-SQLBill
Posting advice: FAQ481-4875
-SQLBill
Posting advice: FAQ481-4875
As SQLBill said, don't enter the Server name where it asks for the Instance name. However, you can use any user-friendly thing you want. Database4 if you want, or you can use 'BobsPrivateInstance', etc. Just make sure it's something easy because you'll need that instance name to make calls between instances or servers.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
- Thread starter
- #13
avanderlaan
Programmer
- Jul 11, 2002
- 156
No offense intended, but do you have a "test" environment that you can practice on? Also, are your production databases backed up?
avanderlaan also brings up a good point. Find a sandbox server (something you can play on) to do your first couple of practice installs. This way you can get used to doing it without crashing your production server.
If that doesn't work, do you have a Developer copy of SQL lying around? You could install that on your PC to get the practice in.
Regardless, before you do it for real on the production server, make sure all the dbs, including Master, Model and MSDB, are backed up and the backups are moved to tape or a network drive so you have an emergency recovery plan.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
If that doesn't work, do you have a Developer copy of SQL lying around? You could install that on your PC to get the practice in.
Regardless, before you do it for real on the production server, make sure all the dbs, including Master, Model and MSDB, are backed up and the backups are moved to tape or a network drive so you have an emergency recovery plan.
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
- Thread starter
- #17
Thanks for everyone's input. I found a test server and installed SQL, also installed a 2nd instance and everything looks. I am assuming that after eah instance install I should also install the service pack. Am I correct?
If my client wants a 15GB database should I create the data file to be 15GB initially. Is that good practice? What about the transaction log? How much space should I allocate for the transaction log?
You gurus have been awesome and patient with your advice and tips. Thanks!!
If my client wants a 15GB database should I create the data file to be 15GB initially. Is that good practice? What about the transaction log? How much space should I allocate for the transaction log?
You gurus have been awesome and patient with your advice and tips. Thanks!!
Yes, service packs have to be installed for each separate instance (unfortunately. @=).
As far as the database size goes, the client may want a 15 GB database, but how much data do they actually have for the database? Is it more? Is it less?
There is a whole buncha math involved in figuring out the proper size of a data file. You take the datatypes of all the columns and add up their byte size (see Data Types in Books Online or google for the information) and any overhead (for variable length fields, assume the maximum size). A row can be 8060 bytes long maximum. Then, once you have the total for all your datatypes, multiple that times the number of anticipated rows. You have to do this for each table. Add all the tables together and you have a minimum size for starting your database.
Remember, if the client has the data in a different format, you have to account for the difference between SQL Datatypes and the data types in the previous format.
If the client has a previous SQL Server database they want upgraded, I'd say look at the current size and add 10% for the data file size.
There is no good rule of thumb for a transaction file size. I believe the minimum size is 512 MB. The best you can do is look at how much data comes in and out of the database (Deletes, Inserts & Updates) every day. If you have a flow of 1 GB a day, I'd consider making the Trans file 1 GB with a growth percentage of 10%. Essentially, you want your log file to be big enough that it doesn't have to grow every day to process the day's work, but small enough that it isn't taking up more space than it needs.
Confused yet? @=)
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
As far as the database size goes, the client may want a 15 GB database, but how much data do they actually have for the database? Is it more? Is it less?
There is a whole buncha math involved in figuring out the proper size of a data file. You take the datatypes of all the columns and add up their byte size (see Data Types in Books Online or google for the information) and any overhead (for variable length fields, assume the maximum size). A row can be 8060 bytes long maximum. Then, once you have the total for all your datatypes, multiple that times the number of anticipated rows. You have to do this for each table. Add all the tables together and you have a minimum size for starting your database.
Remember, if the client has the data in a different format, you have to account for the difference between SQL Datatypes and the data types in the previous format.
If the client has a previous SQL Server database they want upgraded, I'd say look at the current size and add 10% for the data file size.
There is no good rule of thumb for a transaction file size. I believe the minimum size is 512 MB. The best you can do is look at how much data comes in and out of the database (Deletes, Inserts & Updates) every day. If you have a flow of 1 GB a day, I'd consider making the Trans file 1 GB with a growth percentage of 10%. Essentially, you want your log file to be big enough that it doesn't have to grow every day to process the day's work, but small enough that it isn't taking up more space than it needs.
Confused yet? @=)
Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"
mutley1
MIS
- Jul 24, 2003
- 909
Hi wamason,
Talking of backups / transaction log sizes, do you know what recovery model you will be using? If you are in simple recovery, then one method we use is to truncate the log and shrink it regularly. If you are in full recovery then be sure you back up the transaction logs otherwise it will keep growing and growing!
As Catadmin says, see if you can get a rough idea of daily throughput on the database, and you can work out the best time interval for backing up the log.
Cheers,
M.
Talking of backups / transaction log sizes, do you know what recovery model you will be using? If you are in simple recovery, then one method we use is to truncate the log and shrink it regularly. If you are in full recovery then be sure you back up the transaction logs otherwise it will keep growing and growing!
As Catadmin says, see if you can get a rough idea of daily throughput on the database, and you can work out the best time interval for backing up the log.
Cheers,
M.
- Thread starter
- #20
Thanks for the tips. I have created the database on my production server. I notice that the instance is using port 2977. Does that mean that all DB's created on that instance will use port 2977? I suppose I can also change the port to any other unused port.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question