Autonumber field: machine

Showing posts with label machine. Show all posts

Tuesday, March 20, 2012

Backing online database table data to local machine

I have an online SQL Server database provided by an ISP. I do not have
permission to create a backup device and I understand this is normal
practice.

I am not using Enterprise Manager to administer the online database.

I know I can back up the structure of the database using SQL
scripts.

My question is:

How do I back up on my own machine the data contained in the online
database tables I have created? If I were using Enterprise Manager I
could do it by downloading tables using the DTS facility but how can I
do it without Enterprise Manager?

Is there some work around which I have missed eg creating a csv file
of the data?

Best wishes for 2005 to all those helpful people in this newsgroup!
John MorganJohn Morgan (jfm@.XXwoodlander.co.uk) writes:
> I have an online SQL Server database provided by an ISP. I do not have
> permission to create a backup device and I understand this is normal
> practice.
> I am not using Enterprise Manager to administer the online database.
> I know I can back up the structure of the database using SQL
> scripts.
> My question is:
> How do I back up on my own machine the data contained in the online
> database tables I have created? If I were using Enterprise Manager I
> could do it by downloading tables using the DTS facility but how can I
> do it without Enterprise Manager?

You can take a proper backup from to your own machine, as the SQL
Server machine is not likely to have access to your disks, and anyway
I don't think network backups are not supported. But you seem to understand
that anyway, since you mention DTS.

DTS can be run with dtsrun from the command-line, but I assume that
you need Enterprise Manager to create the packages. Then again, I don't
know DTS, so don't take it from me.

But there is BCP. Run this query:

SELECT 'BCP ' + db_name() + '..' + name + ' out ' + name + '.bcp ' +
'-S Server -n -U usr -P pw'
FROM sysobjects
WHERE type = 'U'
AND objectproperty(id, 'IsMSShipped') = 0

The cut and paste that result into a BAT file that you run.

Here I have specified -n which means native format. You can use -c instead
to get character format, with tab delimiting columns and newline delimiting
lines. You can also specify other delimiters.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

back upwith out any machine specific data

How can i take back up of a data base
with out having any machine/system specific
data in the .bak file?

What exactly do you mean by machine specific data?

When you take a backup your database is backed up, nothing more, nothing less.

If you are talking about the file location this can be changed in the RESTORE statement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Data or information? What issues are you trying to prevent with this? If you do a restore headeronly, you will see the server name that backed up the file. Is that the kind of thing you are concerned about?

Otherwise, as WesleyB said, you can backup on one server and restore to another.

-Sue

|||i cant restore a db ,in another system

i am getting error like

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Microsoft SQL Server 2005\mydatabase.mdf" failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)

i am trying to restore from a .bak file

i have sql servrer in D:
but on another system it in c:
|||

That's where the MOVE statement is for.

From BOL:

RESTORE DATABASE TestDB
FROM AdventureWorksBackups
WITH MOVE 'AdventureWorks_Data' TO 'C:\MySQLServer\testdb.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\MySQLServer\testdb.ldf';

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

back upwith out any machine specific data

How can i take back up of a data basewith out having any machine/system specific data in the .bak file?

What are you trying to accomplish? You can obviously move a database backup file from one machine to another.

|||

then why i cant restore a db ,in another system

i am getting error like

System.Data.SqlClient.SqlError:Directory lookup for the file "D:\Microsoft SQL Server2005\mydatabase.mdf" failed with the operating system error 2(Thesystem cannot find the file specified.). (Microsoft.SqlServer.Smo)

i am trying to restore from a .bak file

where d:is the place where sql server is installed in my system

and and in another system it is in c:

|||

Yes, the path to the data and log files is stored with the backup. This is for convenience when you are restoring a backup to the same machine from which it was taken. Unfortunately it is inconvenient when moving to another machine. It the restore database dialog, you have to find the options for where to store the data and transaction log files. Modify these settings to fit the local machine. If you need more specific details, please indicate which version of Sql Server you are using.

Hope this helps,

Matt

|||

i am using sql server 2005

|||

in the restore window, after adding the backup file, go to the "options" page and change the two paths for .mdf and and .ldf files

Monday, March 19, 2012

Back up on remote computer.

We have 5 computer (peer-to-peer).
SQL Serve is intalled on one machine. I want to do the
backup (put the backup files) on remote machine but it
doesn't show the network drives. It just shows the local
drives.
How can I do the backup on remote machine?HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
> We have 5 computer (peer-to-peer).
> SQL Serve is intalled on one machine. I want to do the
> backup (put the backup files) on remote machine but it
> doesn't show the network drives. It just shows the local
> drives.
> How can I do the backup on remote machine?|||Backup to the fully-qualified UNC name rather than a mapped drive letter.
For example: \\machine_name\share\backups. The mapped drives visible to you
on your machine won't necessarily be available to SQL Server.
--
David Portas
SQL Server MVP
--|||Good one, Geoff,. Short and straight to the point.
(The MS disclaimer is probably more text than the actual KB. :-) )
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eZ9$YLBoEHA.2096@.TK2MSFTNGP15.phx.gbl...
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> <anonymous@.discussions.microsoft.com> wrote in message
> news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
>> We have 5 computer (peer-to-peer).
>> SQL Serve is intalled on one machine. I want to do the
>> backup (put the backup files) on remote machine but it
>> doesn't show the network drives. It just shows the local
>> drives.
>> How can I do the backup on remote machine?
>|||The MSDN Article you mentioned/wrote says:
"1) The SQL Server service must be running under a domain-
level account."
My questions are:
(1) How do I know whether my SQL Server service is running
under a domain-level account or not?
(2) All our computers are in a Workgroup. What steps
should I take to start the SQL Server service to run under
domain-level account.
Our SQL Server is Standard Edition and version is 2000
SP3a.
>--Original Message--
>HowTo: Backup to UNC name using Database Maintenance
Wizard
>http://support.microsoft.com/?kbid=555128
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
><anonymous@.discussions.microsoft.com> wrote in message
>news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
>> We have 5 computer (peer-to-peer).
>> SQL Serve is intalled on one machine. I want to do the
>> backup (put the backup files) on remote machine but it
>> doesn't show the network drives. It just shows the local
>> drives.
>> How can I do the backup on remote machine?
>
>.
>|||Comments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:346801c4a020$e318d6b0$a501280a@.phx.gbl...
> The MSDN Article you mentioned/wrote says:
> "1) The SQL Server service must be running under a domain-
> level account."
> My questions are:
> (1) How do I know whether my SQL Server service is running
> under a domain-level account or not?
>
Look under the Services applet in Control Panel for the SQL Server service
(MSSQLSERVER) or Enterprise manager Properties page Security tab. A
domain-level account is one that authenticates as Domain_Name\LoginName
> (2) All our computers are in a Workgroup. What steps
> should I take to start the SQL Server service to run under
> domain-level account.
I haven't tried this in a workgroup. It might work if you have a login and
password that is the same on both servers that you run the SQL service
under. The other option is to create a domain.
> Our SQL Server is Standard Edition and version is 2000
> SP3a.
>
> >--Original Message--
> >HowTo: Backup to UNC name using Database Maintenance
> Wizard
> >http://support.microsoft.com/?kbid=555128
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
> >> We have 5 computer (peer-to-peer).
> >>
> >> SQL Serve is intalled on one machine. I want to do the
> >> backup (put the backup files) on remote machine but it
> >> doesn't show the network drives. It just shows the local
> >> drives.
> >>
> >> How can I do the backup on remote machine?
> >
> >
> >.
> >|||Thanks. There seemed to be a need for something to explain how to do this.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uYgKQsBoEHA.3460@.tk2msftngp13.phx.gbl...
> Good one, Geoff,. Short and straight to the point.
> (The MS disclaimer is probably more text than the actual KB. :-) )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eZ9$YLBoEHA.2096@.TK2MSFTNGP15.phx.gbl...
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> > http://support.microsoft.com/?kbid=555128
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:01be01c4a00d$b3c7a0c0$a401280a@.phx.gbl...
> >> We have 5 computer (peer-to-peer).
> >>
> >> SQL Serve is intalled on one machine. I want to do the
> >> backup (put the backup files) on remote machine but it
> >> doesn't show the network drives. It just shows the local
> >> drives.
> >>
> >> How can I do the backup on remote machine?
> >
> >
>|||We have 5 machines. First machine Windows 2000 Server (SQL
Server 2000 Standard is installed here). Rest of the
machines have Windows 2000 Pro.
Keep the above scenario in mind, could you explain me what
you wrote earlier:
>It might work if you have a login and password that is
> the same on both servers that you run the SQL service
>under. The other option is to create a domain.|||OK. Create a login on the SQL host computer. Put it in the local
administrators group on that machine. Change the SQL Service to run under
that login. Create a login on the backup target machine with the exact same
username and password. Grant that login full control over the file share
and the NTFS folder you want to back up to. No guarantees, but this may
work in a workgroup.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:426701c4a023$dc891f50$a301280a@.phx.gbl...
> We have 5 machines. First machine Windows 2000 Server (SQL
> Server 2000 Standard is installed here). Rest of the
> machines have Windows 2000 Pro.
> Keep the above scenario in mind, could you explain me what
> you wrote earlier:
> >It might work if you have a login and password that is
> > the same on both servers that you run the SQL service
> >under. The other option is to create a domain.
>|||On Windows 2000 Server Machine the SQLServer Services are
running under Administrator. Also on the other computer
(windows 2000 professional) login is administrator (and
obviously has all rights on the shared folder).
This doesn't work when I try to backup on that shared
folder. The error says that location of the (backup) file
cannot be verified..do u want to continue...and when I
say YES then after a couple of seconds the backup process
stops."
SO does this CONCLUDE that this is not possible in
Workgroup scenario?
Thank you.
>--Original Message--
>OK. Create a login on the SQL host computer. Put it in
the local
>administrators group on that machine. Change the SQL
Service to run under
>that login. Create a login on the backup target machine
with the exact same
>username and password. Grant that login full control
over the file share
>and the NTFS folder you want to back up to. No
guarantees, but this may
>work in a workgroup.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
><anonymous@.discussions.microsoft.com> wrote in message
>news:426701c4a023$dc891f50$a301280a@.phx.gbl...
>> We have 5 machines. First machine Windows 2000 Server
(SQL
>> Server 2000 Standard is installed here). Rest of the
>> machines have Windows 2000 Pro.
>> Keep the above scenario in mind, could you explain me
what
>> you wrote earlier:
>> >It might work if you have a login and password that is
>> > the same on both servers that you run the SQL service
>> >under. The other option is to create a domain.
>
>.
>|||Do the Administrator account have the same password on the two machines?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
> On Windows 2000 Server Machine the SQLServer Services are
> running under Administrator. Also on the other computer
> (windows 2000 professional) login is administrator (and
> obviously has all rights on the shared folder).
> This doesn't work when I try to backup on that shared
> folder. The error says that location of the (backup) file
> cannot be verified..do u want to continue...and when I
> say YES then after a couple of seconds the backup process
> stops."
> SO does this CONCLUDE that this is not possible in
> Workgroup scenario?
> Thank you.
> >--Original Message--
> >OK. Create a login on the SQL host computer. Put it in
> the local
> >administrators group on that machine. Change the SQL
> Service to run under
> >that login. Create a login on the backup target machine
> with the exact same
> >username and password. Grant that login full control
> over the file share
> >and the NTFS folder you want to back up to. No
> guarantees, but this may
> >work in a workgroup.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
> >> We have 5 machines. First machine Windows 2000 Server
> (SQL
> >> Server 2000 Standard is installed here). Rest of the
> >> machines have Windows 2000 Pro.
> >>
> >> Keep the above scenario in mind, could you explain me
> what
> >> you wrote earlier:
> >>
> >> >It might work if you have a login and password that is
> >> > the same on both servers that you run the SQL service
> >> >under. The other option is to create a domain.
> >>
> >
> >
> >.
> >|||Yes.
>--Original Message--
>Do the Administrator account have the same password on
the two machines?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
>> On Windows 2000 Server Machine the SQLServer Services
are
>> running under Administrator. Also on the other computer
>> (windows 2000 professional) login is administrator (and
>> obviously has all rights on the shared folder).
>> This doesn't work when I try to backup on that shared
>> folder. The error says that location of the (backup)
file
>> cannot be verified..do u want to continue...and when I
>> say YES then after a couple of seconds the backup
process
>> stops."
>> SO does this CONCLUDE that this is not possible in
>> Workgroup scenario?
>> Thank you.
>> >--Original Message--
>> >OK. Create a login on the SQL host computer. Put it
in
>> the local
>> >administrators group on that machine. Change the SQL
>> Service to run under
>> >that login. Create a login on the backup target
machine
>> with the exact same
>> >username and password. Grant that login full control
>> over the file share
>> >and the NTFS folder you want to back up to. No
>> guarantees, but this may
>> >work in a workgroup.
>> >
>> >--
>> >Geoff N. Hiten
>> >Microsoft SQL Server MVP
>> >Senior Database Administrator
>> >Careerbuilder.com
>> >
>> >I support the Professional Association for SQL Server
>> >www.sqlpass.org
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
>> >> We have 5 machines. First machine Windows 2000
Server
>> (SQL
>> >> Server 2000 Standard is installed here). Rest of the
>> >> machines have Windows 2000 Pro.
>> >>
>> >> Keep the above scenario in mind, could you explain
me
>> what
>> >> you wrote earlier:
>> >>
>> >> >It might work if you have a login and password
that is
>> >> > the same on both servers that you run the SQL
service
>> >> >under. The other option is to create a domain.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Are you using an UNC path or a mapped drive?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:12fb01c4a074$c5073190$a601280a@.phx.gbl...
> Yes.
> >--Original Message--
> >Do the Administrator account have the same password on
> the two machines?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
> >> On Windows 2000 Server Machine the SQLServer Services
> are
> >> running under Administrator. Also on the other computer
> >> (windows 2000 professional) login is administrator (and
> >> obviously has all rights on the shared folder).
> >>
> >> This doesn't work when I try to backup on that shared
> >> folder. The error says that location of the (backup)
> file
> >> cannot be verified..do u want to continue...and when I
> >> say YES then after a couple of seconds the backup
> process
> >> stops."
> >>
> >> SO does this CONCLUDE that this is not possible in
> >> Workgroup scenario?
> >>
> >> Thank you.
> >>
> >> >--Original Message--
> >> >OK. Create a login on the SQL host computer. Put it
> in
> >> the local
> >> >administrators group on that machine. Change the SQL
> >> Service to run under
> >> >that login. Create a login on the backup target
> machine
> >> with the exact same
> >> >username and password. Grant that login full control
> >> over the file share
> >> >and the NTFS folder you want to back up to. No
> >> guarantees, but this may
> >> >work in a workgroup.
> >> >
> >> >--
> >> >Geoff N. Hiten
> >> >Microsoft SQL Server MVP
> >> >Senior Database Administrator
> >> >Careerbuilder.com
> >> >
> >> >I support the Professional Association for SQL Server
> >> >www.sqlpass.org
> >> >
> >> ><anonymous@.discussions.microsoft.com> wrote in message
> >> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
> >> >> We have 5 machines. First machine Windows 2000
> Server
> >> (SQL
> >> >> Server 2000 Standard is installed here). Rest of the
> >> >> machines have Windows 2000 Pro.
> >> >>
> >> >> Keep the above scenario in mind, could you explain
> me
> >> what
> >> >> you wrote earlier:
> >> >>
> >> >> >It might work if you have a login and password
> that is
> >> >> > the same on both servers that you run the SQL
> service
> >> >> >under. The other option is to create a domain.
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||I am using UNC path.
>--Original Message--
>Are you using an UNC path or a mapped drive?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:12fb01c4a074$c5073190$a601280a@.phx.gbl...
>> Yes.
>> >--Original Message--
>> >Do the Administrator account have the same password on
>> the two machines?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
>> >> On Windows 2000 Server Machine the SQLServer
Services
>> are
>> >> running under Administrator. Also on the other
computer
>> >> (windows 2000 professional) login is administrator
(and
>> >> obviously has all rights on the shared folder).
>> >>
>> >> This doesn't work when I try to backup on that
shared
>> >> folder. The error says that location of the (backup)
>> file
>> >> cannot be verified..do u want to continue...and
when I
>> >> say YES then after a couple of seconds the backup
>> process
>> >> stops."
>> >>
>> >> SO does this CONCLUDE that this is not possible in
>> >> Workgroup scenario?
>> >>
>> >> Thank you.
>> >>
>> >> >--Original Message--
>> >> >OK. Create a login on the SQL host computer. Put
it
>> in
>> >> the local
>> >> >administrators group on that machine. Change the
SQL
>> >> Service to run under
>> >> >that login. Create a login on the backup target
>> machine
>> >> with the exact same
>> >> >username and password. Grant that login full
control
>> >> over the file share
>> >> >and the NTFS folder you want to back up to. No
>> >> guarantees, but this may
>> >> >work in a workgroup.
>> >> >
>> >> >--
>> >> >Geoff N. Hiten
>> >> >Microsoft SQL Server MVP
>> >> >Senior Database Administrator
>> >> >Careerbuilder.com
>> >> >
>> >> >I support the Professional Association for SQL
Server
>> >> >www.sqlpass.org
>> >> >
>> >> ><anonymous@.discussions.microsoft.com> wrote in
message
>> >> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
>> >> >> We have 5 machines. First machine Windows 2000
>> Server
>> >> (SQL
>> >> >> Server 2000 Standard is installed here). Rest of
the
>> >> >> machines have Windows 2000 Pro.
>> >> >>
>> >> >> Keep the above scenario in mind, could you
explain
>> me
>> >> what
>> >> >> you wrote earlier:
>> >> >>
>> >> >> >It might work if you have a login and password
>> that is
>> >> >> > the same on both servers that you run the SQL
>> service
>> >> >> >under. The other option is to create a domain.
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||That is strange. I just did exactly that. I have a workgroup, and SQL Server is running under a
service account that also exists on the other machine, having the same password on both machines. I
executed below command, and it worked perfectly:
BACKUP DATABASE pubs TO DISK = '\\TibMedia\C\pubs.bak'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:3b8001c4a0ad$30389f20$a501280a@.phx.gbl...
>I am using UNC path.
>>--Original Message--
>>Are you using an UNC path or a mapped drive?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:12fb01c4a074$c5073190$a601280a@.phx.gbl...
>> Yes.
>> >--Original Message--
>> >Do the Administrator account have the same password on
>> the two machines?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> ><anonymous@.discussions.microsoft.com> wrote in message
>> >news:36e801c4a05b$6dd83310$a501280a@.phx.gbl...
>> >> On Windows 2000 Server Machine the SQLServer
> Services
>> are
>> >> running under Administrator. Also on the other
> computer
>> >> (windows 2000 professional) login is administrator
> (and
>> >> obviously has all rights on the shared folder).
>> >>
>> >> This doesn't work when I try to backup on that
> shared
>> >> folder. The error says that location of the (backup)
>> file
>> >> cannot be verified..do u want to continue...and
> when I
>> >> say YES then after a couple of seconds the backup
>> process
>> >> stops."
>> >>
>> >> SO does this CONCLUDE that this is not possible in
>> >> Workgroup scenario?
>> >>
>> >> Thank you.
>> >>
>> >> >--Original Message--
>> >> >OK. Create a login on the SQL host computer. Put
> it
>> in
>> >> the local
>> >> >administrators group on that machine. Change the
> SQL
>> >> Service to run under
>> >> >that login. Create a login on the backup target
>> machine
>> >> with the exact same
>> >> >username and password. Grant that login full
> control
>> >> over the file share
>> >> >and the NTFS folder you want to back up to. No
>> >> guarantees, but this may
>> >> >work in a workgroup.
>> >> >
>> >> >--
>> >> >Geoff N. Hiten
>> >> >Microsoft SQL Server MVP
>> >> >Senior Database Administrator
>> >> >Careerbuilder.com
>> >> >
>> >> >I support the Professional Association for SQL
> Server
>> >> >www.sqlpass.org
>> >> >
>> >> ><anonymous@.discussions.microsoft.com> wrote in
> message
>> >> >news:426701c4a023$dc891f50$a301280a@.phx.gbl...
>> >> >> We have 5 machines. First machine Windows 2000
>> Server
>> >> (SQL
>> >> >> Server 2000 Standard is installed here). Rest of
> the
>> >> >> machines have Windows 2000 Pro.
>> >> >>
>> >> >> Keep the above scenario in mind, could you
> explain
>> me
>> >> what
>> >> >> you wrote earlier:
>> >> >>
>> >> >> >It might work if you have a login and password
>> that is
>> >> >> > the same on both servers that you run the SQL
>> service
>> >> >> >under. The other option is to create a domain.
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>>
>>.

Thursday, March 8, 2012

AWE Question

What would be the optimal max server memory setting for the following
machine?
Dedicated SQL Server:
Intel Xeon MP 3.3 GHz (4 physical Processors)
136 GB (C Drive)
8.0 GB RAM
Microsoft Windows 2003 Enterprise Edition Service Pack 1 Build 3790
SQL Server Enterprise 2000, with SP3
The machine has following database sizes. All databases are being accessed
all the time by around 300+ concurrent users.
DB#1 - 536.44 MB
DB#2 - 1.09 GB
DB#3 - 3.27 GB
DB#4 - 4.92 GB
DB#5 - 5.29 GB
DB#6 - 15.09 GB
DB#7 - 50.13 GB
I am still learning the ins and outs of configuring higher end db servers.
Are there any other non-default server settings that I should change?"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:uqJkBN9uGHA.4756@.TK2MSFTNGP04.phx.gbl...
> What would be the optimal max server memory setting for the following
> machine?
> Dedicated SQL Server:
> Intel Xeon MP 3.3 GHz (4 physical Processors)
> 136 GB (C Drive)
> 8.0 GB RAM
> Microsoft Windows 2003 Enterprise Edition Service Pack 1 Build 3790
> SQL Server Enterprise 2000, with SP3
> The machine has following database sizes. All databases are being accessed
> all the time by around 300+ concurrent users.
> DB#1 - 536.44 MB
> DB#2 - 1.09 GB
> DB#3 - 3.27 GB
> DB#4 - 4.92 GB
> DB#5 - 5.29 GB
> DB#6 - 15.09 GB
> DB#7 - 50.13 GB
> I am still learning the ins and outs of configuring higher end db servers.
>
Does each user access all databases or does each database have its own set
of users?
If the different databsaes represent seperate applications, you should
consider dividing the server into multiple SQL instances to divide up the
memory on the box and optionally the CPU's into 2 or 3 different resource
pools. 32bit SQL Server works more efficiently when it doesn't have to use
AWE to access memory, and multiple instances allow you to easilly partition
memory and CPU resources between the workloads.
David|||>
> Does each user access all databases or does each database have its own set
> of users?
All databases are the same application (different clients on each db similar
to an ASP service).
Each user usually accesses only a single database. Admin users would need
access to all.
> If the different databsaes represent seperate applications, you should
> consider dividing the server into multiple SQL instances to divide up the
> memory on the box and optionally the CPU's into 2 or 3 different resource
> pools. 32bit SQL Server works more efficiently when it doesn't have to
> use AWE to access memory, and multiple instances allow you to easilly
> partition memory and CPU resources between the workloads.
>
Interesting option. Your making me think about that.
You mention that 32bit SQL Server works more efficiently when it doesn't use
AWE. Does it affect performance that much?
My concern is the 50 GB database itself would benefit from more then 2 GB of
RAM.
You also mention that you can configure CPUs into different resource pools.
How does this work? Do you have a knowledge base link?|||"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
> >
>> Does each user access all databases or does each database have its own
>> set of users?
> All databases are the same application (different clients on each db
> similar to an ASP service).
> Each user usually accesses only a single database. Admin users would need
> access to all.
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up the
>> memory on the box and optionally the CPU's into 2 or 3 different resource
>> pools. 32bit SQL Server works more efficiently when it doesn't have to
>> use AWE to access memory, and multiple instances allow you to easilly
>> partition memory and CPU resources between the workloads.
> Interesting option. Your making me think about that.
> You mention that 32bit SQL Server works more efficiently when it doesn't
> use AWE. Does it affect performance that much?
> My concern is the 50 GB database itself would benefit from more then 2 GB
> of RAM.
Yes it would, but you have 7 databses and only 8G of ram. How much ram can
you afford to allocate for that one workload. And using the /3GB switch you
can give a SQL instance access to 3GB of memory without using AWE.
Also you will have one procedure cache per instance, and on 32bit SQL is
strictly limited in size. Multiple instances would have multiple procedure
caches.
"On 32-bit platforms configured with 4 GB or more of physical memory, you
can either use Address Windowing Extensions (AWE) or use multiple instances
of SQL Server as a way to fully utilize the large amount of physical memory.
AWE may work well for some scenarios; however, you should be aware that AWE
memory can only be used for data cache. The memory for procedure cache,
connections, locks, and other internal resources of SQL Server must come
from the 2 GB (or 3GB, depending on the settings used) portion of the
virtual memory. On systems needing to support a large number of databases
and user connections, multiple instances of SQL Server may be a better
approach to fully alleviate the 2 GB or 3GB memory constraint imposed by the
32-bit platform for these data structures."
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
> You also mention that you can configure CPUs into different resource
> pools. How does this work? Do you have a knowledge base link?
>
Simple partition of CPU's to SQL Instances should be done with the "affinity
mask" server option.
SQL Server 2005 Books Online
affinity mask Option
http://msdn2.microsoft.com/en-us/library/ms187104.aspx
Dynamic CPU allocation policies can be implemented with WSRM:
Windows System Resource Manager
http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O$v%232B%23uGHA.3428@.TK2MSFTNGP02.phx.gbl...
> "Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
> news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> >
>> Does each user access all databases or does each database have its own
>> set of users?
>> All databases are the same application (different clients on each db
>> similar to an ASP service).
>> Each user usually accesses only a single database. Admin users would need
>> access to all.
>>
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up
>> the memory on the box and optionally the CPU's into 2 or 3 different
>> resource pools. 32bit SQL Server works more efficiently when it doesn't
>> have to use AWE to access memory, and multiple instances allow you to
>> easilly partition memory and CPU resources between the workloads.
>>
>> Interesting option. Your making me think about that.
>> You mention that 32bit SQL Server works more efficiently when it doesn't
>> use AWE. Does it affect performance that much?
>> My concern is the 50 GB database itself would benefit from more then 2 GB
>> of RAM.
> Yes it would, but you have 7 databses and only 8G of ram. How much ram
> can you afford to allocate for that one workload. And using the /3GB
> switch you can give a SQL instance access to 3GB of memory without using
> AWE.
> Also you will have one procedure cache per instance, and on 32bit SQL is
> strictly limited in size. Multiple instances would have multiple
> procedure caches.
> "On 32-bit platforms configured with 4 GB or more of physical memory, you
> can either use Address Windowing Extensions (AWE) or use multiple
> instances of SQL Server as a way to fully utilize the large amount of
> physical memory. AWE may work well for some scenarios; however, you should
> be aware that AWE memory can only be used for data cache. The memory for
> procedure cache, connections, locks, and other internal resources of SQL
> Server must come from the 2 GB (or 3GB, depending on the settings used)
> portion of the virtual memory. On systems needing to support a large
> number of databases and user connections, multiple instances of SQL Server
> may be a better approach to fully alleviate the 2 GB or 3GB memory
> constraint imposed by the 32-bit platform for these data structures."
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
>> You also mention that you can configure CPUs into different resource
>> pools. How does this work? Do you have a knowledge base link?
> Simple partition of CPU's to SQL Instances should be done with the
> "affinity mask" server option.
> SQL Server 2005 Books Online
> affinity mask Option
> http://msdn2.microsoft.com/en-us/library/ms187104.aspx
>
> Dynamic CPU allocation policies can be implemented with WSRM:
> Windows System Resource Manager
> http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
> David
>
Thanks David. This info is very helpful.|||Isaac
> Dedicated SQL Server:
> Intel Xeon MP 3.3 GHz (4 physical Processors)
> 136 GB (C Drive)
> 8.0 GB RAM
I'd consider (based on your description) MAX Memory 7GB
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23$AxxK%23uGHA.4752@.TK2MSFTNGP02.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:O$v%232B%23uGHA.3428@.TK2MSFTNGP02.phx.gbl...
>> "Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
>> news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> >
>> Does each user access all databases or does each database have its own
>> set of users?
>> All databases are the same application (different clients on each db
>> similar to an ASP service).
>> Each user usually accesses only a single database. Admin users would
>> need access to all.
>>
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up
>> the memory on the box and optionally the CPU's into 2 or 3 different
>> resource pools. 32bit SQL Server works more efficiently when it
>> doesn't have to use AWE to access memory, and multiple instances allow
>> you to easilly partition memory and CPU resources between the
>> workloads.
>>
>> Interesting option. Your making me think about that.
>> You mention that 32bit SQL Server works more efficiently when it doesn't
>> use AWE. Does it affect performance that much?
>> My concern is the 50 GB database itself would benefit from more then 2
>> GB of RAM.
>> Yes it would, but you have 7 databses and only 8G of ram. How much ram
>> can you afford to allocate for that one workload. And using the /3GB
>> switch you can give a SQL instance access to 3GB of memory without using
>> AWE.
>> Also you will have one procedure cache per instance, and on 32bit SQL is
>> strictly limited in size. Multiple instances would have multiple
>> procedure caches.
>> "On 32-bit platforms configured with 4 GB or more of physical memory, you
>> can either use Address Windowing Extensions (AWE) or use multiple
>> instances of SQL Server as a way to fully utilize the large amount of
>> physical memory. AWE may work well for some scenarios; however, you
>> should be aware that AWE memory can only be used for data cache. The
>> memory for procedure cache, connections, locks, and other internal
>> resources of SQL Server must come from the 2 GB (or 3GB, depending on the
>> settings used) portion of the virtual memory. On systems needing to
>> support a large number of databases and user connections, multiple
>> instances of SQL Server may be a better approach to fully alleviate the 2
>> GB or 3GB memory constraint imposed by the 32-bit platform for these data
>> structures."
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
>> You also mention that you can configure CPUs into different resource
>> pools. How does this work? Do you have a knowledge base link?
>>
>> Simple partition of CPU's to SQL Instances should be done with the
>> "affinity mask" server option.
>> SQL Server 2005 Books Online
>> affinity mask Option
>> http://msdn2.microsoft.com/en-us/library/ms187104.aspx
>>
>> Dynamic CPU allocation policies can be implemented with WSRM:
>> Windows System Resource Manager
>> http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
>> David
> Thanks David. This info is very helpful.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYF1sPEvGHA.724@.TK2MSFTNGP05.phx.gbl...
> Isaac
>> Dedicated SQL Server:
>> Intel Xeon MP 3.3 GHz (4 physical Processors)
>> 136 GB (C Drive)
>> 8.0 GB RAM
>
> I'd consider (based on your description) MAX Memory 7GB
>
Thanks Uri.

Saturday, February 25, 2012

AWE - SQL Server 2000 - Memory usage

Hi all!
We have a SQL Server 2000 Enterprise machine with 3.75 GB of ram in it.
Microsoft gave the advise to enable the "AWE" sql server option and the
"/3GB" windows option.
(http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech)
But when I go to the task manager I see SQL server is only using about 144
mb ?
And the total system memory usage is 2.6 GB... and i cannot see the process
that is using that amount of memory.....
-How can I see what it using so much memory?
-Is this a normal situation when you enablee /3GB and AWE or do you see
sqlserver.exe consuming more memory in your task manager?
I want to know if there is something wrong with our configuration.
Thanks in advance for your answer!
Best regards
Floris van HaasterTask Manager isn't AWE aware. Use Perf Mon instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris van Haaster" <floris@.NO-SPAM-datasmit.nl> wrote in message
news:435de4b3$0$11072$e4fe514c@.news.xs4all.nl...
> Hi all!
> We have a SQL Server 2000 Enterprise machine with 3.75 GB of ram in it.
> Microsoft gave the advise to enable the "AWE" sql server option and the "/3GB" windows option.
> (http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech)
> But when I go to the task manager I see SQL server is only using about 144 mb ?
> And the total system memory usage is 2.6 GB... and i cannot see the process that is using that
> amount of memory.....
> -How can I see what it using so much memory?
> -Is this a normal situation when you enablee /3GB and AWE or do you see sqlserver.exe consuming
> more memory in your task manager?
> I want to know if there is something wrong with our configuration.
> Thanks in advance for your answer!
> Best regards
> Floris van Haaster
>|||Ok thanks,
But what meter shows the correct value?
When i take a look @. "SQLServer:Memory Manager > Maximum Workspace memory
(KB)" it's value = 1175344 KB and that's only 1 GB or so...
Or do i have to look at another counter?
Best regards
Floris
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:%230isnEU2FHA.3228@.TK2MSFTNGP15.phx.gbl...
> Task Manager isn't AWE aware. Use Perf Mon instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris van Haaster" <floris@.NO-SPAM-datasmit.nl> wrote in message
> news:435de4b3$0$11072$e4fe514c@.news.xs4all.nl...
>> Hi all!
>> We have a SQL Server 2000 Enterprise machine with 3.75 GB of ram in it.
>> Microsoft gave the advise to enable the "AWE" sql server option and the
>> "/3GB" windows option.
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech)
>> But when I go to the task manager I see SQL server is only using about
>> 144 mb ?
>> And the total system memory usage is 2.6 GB... and i cannot see the
>> process that is using that amount of memory.....
>> -How can I see what it using so much memory?
>> -Is this a normal situation when you enablee /3GB and AWE or do you see
>> sqlserver.exe consuming more memory in your task manager?
>> I want to know if there is something wrong with our configuration.
>> Thanks in advance for your answer!
>> Best regards
>> Floris van Haaster
>>
>|||Use "Buffer Manager: Total Pages" or DBCC MEMORYSTATUS command
(http://support.microsoft.com/?id=271624), check Buffer Counts, Committed,
which should be close to what you are consuming through AWE. Assuming AWE is
enabled.
Later versions (service packs) of SQL Server 2000 also have Visible counter
in DBCC MEMORYSTATUS output which will show how much of AWE-allocated memory
is actually mapped into virtual address space of SQL Server.
HTH,
Boris.
Boris Baryshnikov
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Floris van Haaster" <floris@.NO-SPAM-datasmit.nl> wrote in message
news:435e27bf$0$11063$e4fe514c@.news.xs4all.nl...
> Ok thanks,
> But what meter shows the correct value?
> When i take a look @. "SQLServer:Memory Manager > Maximum Workspace memory
> (KB)" it's value = 1175344 KB and that's only 1 GB or so...
> Or do i have to look at another counter?
> Best regards
> Floris
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
> in bericht news:%230isnEU2FHA.3228@.TK2MSFTNGP15.phx.gbl...
>> Task Manager isn't AWE aware. Use Perf Mon instead.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Floris van Haaster" <floris@.NO-SPAM-datasmit.nl> wrote in message
>> news:435de4b3$0$11072$e4fe514c@.news.xs4all.nl...
>> Hi all!
>> We have a SQL Server 2000 Enterprise machine with 3.75 GB of ram in it.
>> Microsoft gave the advise to enable the "AWE" sql server option and the
>> "/3GB" windows option.
>> (http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech)
>> But when I go to the task manager I see SQL server is only using about
>> 144 mb ?
>> And the total system memory usage is 2.6 GB... and i cannot see the
>> process that is using that amount of memory.....
>> -How can I see what it using so much memory?
>> -Is this a normal situation when you enablee /3GB and AWE or do you see
>> sqlserver.exe consuming more memory in your task manager?
>> I want to know if there is something wrong with our configuration.
>> Thanks in advance for your answer!
>> Best regards
>> Floris van Haaster
>>
>

Monday, February 13, 2012

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.Be
It really depends on the perfomance of your hardware. There is no set rule that I know of for time. It also depends on what needs to be recovered and what was in the log at the time of the crash. The more of the work in the log the longer the time. It could take anywhere from 1 minute to 45 minutes depending on how much work it has to do
Sorry it is so vagu
Jef
MCDBA, MCSE+I|||The recovery interval setting can impact the speed at which the =databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =if it could happen or not.
In my experience the databases are available shortly after the machine =comes online. I have experienced a few hardware related machine crashes =that have caused a server to black screen (become totally unresponsive). = We had to press the power button to turn the machine off and back on =again. Upon restarting the database was available almost right away. =This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =that the machine became unplugged from the UPS. Upon power-up SQL =Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =backup and restore databases. Books Online has lots of good information =in this area.
-- Keith
"ben brugman" <ben@.niethier.nl> wrote in message =news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> > If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> > What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> > How long will it take SQL-server to 'recover', from
> a crash ?
> > (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> > On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> > thanks for your attention,
> ben brugman.
> >|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.
Ben
It really depends on the perfomance of your hardware. There is no set rule that I know of for time. It also depends on what needs to be recovered and what was in the log at the time of the crash. The more of the work in the log the longer the time. It
could take anywhere from 1 minute to 45 minutes depending on how much work it has to do.
Sorry it is so vague
Jeff
MCDBA, MCSE+I
|||The recovery interval setting can impact the speed at which the =
databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =
Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =
if it could happen or not. =20
In my experience the databases are available shortly after the machine =
comes online. I have experienced a few hardware related machine crashes =
that have caused a server to black screen (become totally unresponsive). =
We had to press the power button to turn the machine off and back on =
again. Upon restarting the database was available almost right away. =
This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =
that the machine became unplugged from the UPS. Upon power-up SQL =
Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =
backup and restore databases. Books Online has lots of good information =
in this area.
--=20
Keith
"ben brugman" <ben@.niethier.nl> wrote in message =
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
>=20
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
>=20
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
>=20
> How long will it take SQL-server to 'recover', from
> a crash ?
>=20
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
>=20
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
>=20
> thanks for your attention,
> ben brugman.
>=20
>
|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>

Availability after an instance crash.

Up to now I have no experience with an instance failure of
a large database.
If you have a database say 150 Gb, and the machine has
to 'reboot', what would be a 'normal' time before the
database is available again ?
And in a worse case scenario ?
What if the OS does start a diskcheck before booting ?
(On home pc's I have seen this happening, but never
on a serious server, can this happen on a server ?)
How long will it take SQL-server to 'recover', from
a crash ?
(I am thinking of failures like a power failure or
an OS crash, not a hardware failure).
On a small testsystem where I did try this kind of
scenario's, after the OS boot, it took SQL-server only
a small time to recover (seconds).
thanks for your attention,
ben brugman.Ben
It really depends on the perfomance of your hardware. There is no set rule
that I know of for time. It also depends on what needs to be recovered and
what was in the log at the time of the crash. The more of the work in the l
og the longer the time. It
could take anywhere from 1 minute to 45 minutes depending on how much work i
t has to do.
Sorry it is so vague
Jeff
MCDBA, MCSE+I|||The recovery interval setting can impact the speed at which the =
databases will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL =
Server program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know =
if it could happen or not. =20
In my experience the databases are available shortly after the machine =
comes online. I have experienced a few hardware related machine crashes =
that have caused a server to black screen (become totally unresponsive). =
We had to press the power button to turn the machine off and back on =
again. Upon restarting the database was available almost right away. =
This was an OLTP database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range =
that the machine became unplugged from the UPS. Upon power-up SQL =
Server was running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to =
backup and restore databases. Books Online has lots of good information =
in this area.
--=20
Keith
"ben brugman" <ben@.niethier.nl> wrote in message =
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
>=20
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
>=20
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
>=20
> How long will it take SQL-server to 'recover', from
> a crash ?
>=20
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
>=20
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
>=20
> thanks for your attention,
> ben brugman.
>=20
>|||Thanks Jeff and Keith,
My greatest concern was the time the OS will take on a reboot.
(And do diskchecks or other time consuming matters).
As you both indicate that you are not concerned, I'll drop this
concern.
Thanks for your answers,
Ben Brugman
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OMKoDB9IEHA.3968@.TK2MSFTNGP12.phx.gbl...
The recovery interval setting can impact the speed at which the databases
will be made available.
exec sp_configure 'recovery interval (min)'
Lots more information available within Books Online (within the SQL Server
program group). Read up on the recovery interval option.
I have not observed a checkdisk within a server based OS. I don't know if
it could happen or not.
In my experience the databases are available shortly after the machine comes
online. I have experienced a few hardware related machine crashes that have
caused a server to black screen (become totally unresponsive). We had to
press the power button to turn the machine off and back on again. Upon
restarting the database was available almost right away. This was an OLTP
database roughly 30 GB.
I have also worked with OLAP databases that were in the 150-200GB range that
the machine became unplugged from the UPS. Upon power-up SQL Server was
running and the databases were available in a few seconds.
The worst case scenario...you could loose your data.
A proper backup strategy is important! Make sure that you know how to
backup and restore databases. Books Online has lots of good information in
this area.
Keith
"ben brugman" <ben@.niethier.nl> wrote in message
news:OTyxOZ8IEHA.3720@.tk2msftngp13.phx.gbl...
> Up to now I have no experience with an instance failure of
> a large database.
> If you have a database say 150 Gb, and the machine has
> to 'reboot', what would be a 'normal' time before the
> database is available again ?
> And in a worse case scenario ?
> What if the OS does start a diskcheck before booting ?
> (On home pc's I have seen this happening, but never
> on a serious server, can this happen on a server ?)
> How long will it take SQL-server to 'recover', from
> a crash ?
> (I am thinking of failures like a power failure or
> an OS crash, not a hardware failure).
> On a small testsystem where I did try this kind of
> scenario's, after the OS boot, it took SQL-server only
> a small time to recover (seconds).
> thanks for your attention,
> ben brugman.
>

Sunday, February 12, 2012

AutoStart

Hi,
Installed MSDE desktop (sp3) on XP, however the server does not autostart on
re-booting the machine. In the service manager the server/instance name is
missing after every re-boot. I enter it manually, check the autostart box
and it runs fine, but again after re-boot the server/instance name is lost
again.
I know our work machines are limited in allowing us to edit the registry,
but on a same build machine with full sql svr installed the problem does not
occur.
Any idea?
Cheers, Josh
Hi,
In Control Panel -- Admin tools -- Services -- Select the SQL Server
service.
Double click on the services and make it to automatic and click ok.
Thanks
Hari
SQL Server MVP
"Josh" <joshua.phillips@.notthisbit.blueyonder.co.uk> wrote in message
news:8CF778B9-DED3-48D2-912E-9EEFCC91BA80@.microsoft.com...
> Hi,
> Installed MSDE desktop (sp3) on XP, however the server does not autostart
> on
> re-booting the machine. In the service manager the server/instance name
> is
> missing after every re-boot. I enter it manually, check the autostart box
> and it runs fine, but again after re-boot the server/instance name is lost
> again.
> I know our work machines are limited in allowing us to edit the registry,
> but on a same build machine with full sql svr installed the problem does
> not
> occur.
> Any idea?
> Cheers, Josh
|||Hi,
It is alreday set to auto - the icon in the task bar tray does start, but
the white circle is empty. When I then click on the sql server service
manager, the server / instance name is blank - i.e. it is not remembering the
server / instance name from before the reboot.
Josh
"Hari Prasad" wrote:

> Hi,
> In Control Panel -- Admin tools -- Services -- Select the SQL Server
> service.
> Double click on the services and make it to automatic and click ok.
> Thanks
> Hari
> SQL Server MVP
>
> "Josh" <joshua.phillips@.notthisbit.blueyonder.co.uk> wrote in message
> news:8CF778B9-DED3-48D2-912E-9EEFCC91BA80@.microsoft.com...
>
>
|||hi Josh,
Josh wrote:
> Hi,
> Installed MSDE desktop (sp3) on XP, however the server does not
> autostart on re-booting the machine. In the service manager the
> server/instance name is missing after every re-boot. I enter it
> manually, check the autostart box and it runs fine, but again after
> re-boot the server/instance name is lost again.
> I know our work machines are limited in allowing us to edit the
> registry, but on a same build machine with full sql svr installed the
> problem does not occur.
please have a look at
http://support.microsoft.com/default...b;EN-US;814132
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Tuesday, March 20, 2012

Backing online database table data to local machine

back upwith out any machine specific data

back upwith out any machine specific data

Monday, March 19, 2012

Back up on remote computer.

Back up on remote computer.

Thursday, March 8, 2012

AWE Question

Saturday, February 25, 2012

AWE - SQL Server 2000 - Memory usage

AWE - SQL Server 2000 - Memory usage

AWE - SQL Server 2000 - Memory usage

Monday, February 13, 2012

Availability after an instance crash.

Availability after an instance crash.

Availability after an instance crash.

Sunday, February 12, 2012

AutoStart

Autonumber field

Blog Archive

About Me