Bransby-IT
asked on
SQL2008 New Database
SQL2008 - Fresh Install
I have a current SQL2008 server on a Windows 2003 Sever.
I have now installoed a new SQL2008 Server on a Windows Server 2008.
My plan is to move several of the old server SQL Database from the old to the new.
I have opened up my "MS SQL Server Managment Studio" and it has asked me to login in.
There is currently no SQL Server Name and in the drop down there is just local and nothing else.
Do I just need to create a new one and if so how?
I have a current SQL2008 server on a Windows 2003 Sever.
I have now installoed a new SQL2008 Server on a Windows Server 2008.
My plan is to move several of the old server SQL Database from the old to the new.
I have opened up my "MS SQL Server Managment Studio" and it has asked me to login in.
There is currently no SQL Server Name and in the drop down there is just local and nothing else.
Do I just need to create a new one and if so how?
if you are running sql management studio on the same machine of the db, just click on server name, then browse for more, and local server.
If you have choosen mixed authentication can authenticate with windows authentication
If you have choosen mixed authentication can authenticate with windows authentication
You have to connect to the sql server engine, so you have to enter the machine where the sql server engine has been installed and the name of instance of the sql server.
is sql server 2008 express or the sql server 2008 ?
is sql server 2008 express or the sql server 2008 ?
ASKER
OK but it has not setup a new Server Engine. I thought the install would of.
So on the drop down there is just local, and this is on the C Drive I want a new Engine named "SQL_XXX01" stored on my D:\SQL2008_DATA" which is what I setup on the install.
So on the drop down there is just local, and this is on the C Drive I want a new Engine named "SQL_XXX01" stored on my D:\SQL2008_DATA" which is what I setup on the install.
ASKER
The Server is SQL 2008 Standard SP3
ASKER
The DB will be on the same Server as the Studio Manager.
I just want to create a New Server Engine instance.
I just want to create a New Server Engine instance.
Just to understand ... you need 2 instances of sql server or just need to move db from C: to D: to save space on C: ?
2 instances of sql server uses twice ram ... twice system resources ... etc
are you really sure that you want this ?
2 instances of sql server uses twice ram ... twice system resources ... etc
are you really sure that you want this ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sognoct
No I had 1 SQL Server which has 1 "SQL Server Engine" this has around 20 Database on it.
Now I have a second SQL Server
I want to keep the first SQL Server for some time, keep it running along side the new one.
After I have created the SQL Engine on the new server my plan is to move a couple of the database from the old to the new server. Not all of them.
So I will end up with 2 x SQL Servers running 1 SQL Engine on each server.
No I had 1 SQL Server which has 1 "SQL Server Engine" this has around 20 Database on it.
Now I have a second SQL Server
I want to keep the first SQL Server for some time, keep it running along side the new one.
After I have created the SQL Engine on the new server my plan is to move a couple of the database from the old to the new server. Not all of them.
So I will end up with 2 x SQL Servers running 1 SQL Engine on each server.
ASKER
Anujnb - I can move one database at a time yes?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will be detaching one at a time.
Got that bit just not to sure of the initial SQL Engine?
Got that bit just not to sure of the initial SQL Engine?
if it is on the same machine (so you followed the standard sql server installation)
click server name -> browse for more -> database engine -> "server\instancename"
click server name -> browse for more -> database engine -> "server\instancename"
ASKER
This is the install notes I played on it.
http://www.sqlserverclub.com/essentialguides/how-to-install-sql-server-2008-step-by-step-guide.aspx
http://www.sqlserverclub.com/essentialguides/how-to-install-sql-server-2008-step-by-step-guide.aspx
open
control panel->administrative tools->services
check if service
SQL server is installed and running ...
control panel->administrative tools->services
check if service
SQL server is installed and running ...
ASKER
Yep running.
ASKER
Is there a way I can create a new server engine and place it in the data folder I setup on the install?
put in servername:localhost\SQL_X XX01
ASKER
OK this has created the Database Engine but the location is on the C Drive. How can I now move this to the D drive?
Thanks for your assistance.
Thanks for your assistance.
for having the same data folder and engine folder douring install you have to choose
"data root directory" equal to "user database directory" ... but now you have to uninstall and re-install ... .. ....
if it is essential ... do it, but if you think it is not to crucial for you, it will work great also with different folders. Engine does not eat space, and if you format C: and reinstall everything you have to install engine the same, also if it is on the D: drive
"data root directory" equal to "user database directory" ... but now you have to uninstall and re-install ... .. ....
if it is essential ... do it, but if you think it is not to crucial for you, it will work great also with different folders. Engine does not eat space, and if you format C: and reinstall everything you have to install engine the same, also if it is on the D: drive
ASKER
OK was it the "Data root directory" I should of changed too?
When I move the database will they go into my D drive?
What would you do?
When I move the database will they go into my D drive?
What would you do?
set instance root directory to "D:\SQL2008_DATA\"
ASKER
OK as this is a long term server should I uninstall and reinstall?
also data root directory to d:\sq2008_data
ASKER
Or does this not really matter? I would like to set it up correctly.
first uninstall then reinstall everything ... then if the instance name is the same just have to put in servername:localhost\SQL_X XX01
for accessing the db
in the other machine detach db and copy them one by one on the
d:\sq2008_data
then attach them
for accessing the db
in the other machine detach db and copy them one by one on the
d:\sq2008_data
then attach them
no, it does not matter ... as much as I know ...
ASKER
Thanks for the advise, so in a SQL new installs would it always be better to set the both so the database enginee and the database are located in the same directory?
according to me no ... (that's why I always asked why you need them on the same dir)
it does not matter. This are the best configurations according to microsoft for sql 2008 :
C:\ Engine, Windows, Backups, Batch Processing, Full Text Catalogs
D:\ System Databases (TempDB), Transaction Logs
E:\ Large User Defined Database
I've read somewhere that there were better performances using dbf file and ldf file on different drives so I use ldf in c:\database and mdf in d:\database ... but never noticed so much differences.
it does not matter. This are the best configurations according to microsoft for sql 2008 :
C:\ Engine, Windows, Backups, Batch Processing, Full Text Catalogs
D:\ System Databases (TempDB), Transaction Logs
E:\ Large User Defined Database
I've read somewhere that there were better performances using dbf file and ldf file on different drives so I use ldf in c:\database and mdf in d:\database ... but never noticed so much differences.
ASKER
ldf? Logs?
ASKER
System Database and Large Database?
Are we talking like BES as System and inhouse ones as user?
Thanks for your advise its been great.
Are we talking like BES as System and inhouse ones as user?
Thanks for your advise its been great.
by System Databases it means :
master
model
msdb
tempd
they are the databases of the engine
Large database are the ldf / mdf files
master
model
msdb
tempd
they are the databases of the engine
Large database are the ldf / mdf files
ASKER