Backing up SSRS objects

I'm using sqlserver 2005 developer edition.
Unlike databases, there appears to be no way, in Management Studio, to back
up ReportSever.
How is it done?
--
Regards,
Gary BlakelyHi Gary,
Thank you for your posting!
You could backup the ReportServer database as you backup other databases.
In the databases, right-click ReportServer and then click Back up. Then you
could backup the Reporty Server Database.
Here is an article for your reference:
Back Up Database (General Page)
http://msdn2.microsoft.com/en-us/library/ms183383(d=ide).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Backing up Databases using SQLServer

I have SQL 2000 on a W2K advanced server. I want to use SQL server's
backup routine to backup the databases to a directory on a separate
computer. I have set up the SQL Client Agent on that other server hoping
that would somehow facilitate this, but there doesn't seem to be any way
to force SQL server to backup the file to a device that is not local to
the server itself.
I understand that one possible option would be to install a full version
of SQL 2000 on the other server and has the primary backup to that
secondary server. But that would mean 2 licenses of SQL Server. I
don't think we have 2 licenses, though I will check on that.
Surely there is some way to backup databases to an non-local device.
CurtisHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
The same technique applies to manual backup jobs as well.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:eiqgAsisEHA.1468@.TK2MSFTNGP12.phx.gbl...
> I have SQL 2000 on a W2K advanced server. I want to use SQL server's
> backup routine to backup the databases to a directory on a separate
> computer. I have set up the SQL Client Agent on that other server hoping
> that would somehow facilitate this, but there doesn't seem to be any way
> to force SQL server to backup the file to a device that is not local to
> the server itself.
> I understand that one possible option would be to install a full version
> of SQL 2000 on the other server and has the primary backup to that
> secondary server. But that would mean 2 licenses of SQL Server. I
> don't think we have 2 licenses, though I will check on that.
> Surely there is some way to backup databases to an non-local device.
> Curtis|||So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:

> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>|||Curtis,
Per the KB article ...
2) The SQL Server service account must have FULL CONTROL rights to the file
system folder and to the share
Tunji
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message news:envLaWjsEHA.160@.
TK2MSFTNGP11.phx.gbl...
So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:

> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
>
> The same technique applies to manual backup jobs as well.
>|||Everything works better if you give the domain-level account (DataAdmin in
your example) local administrator rights on the SQL server host computer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> So, if I understand correctly the MSSQLSERVER service must be switched
> from running under the LocalSystem account to running under a
> domain-level account, such as, let's say, DataAdmin with a password. And
> which account is not local to the server, but global to the domain. Right?
> If that's true, what rights do I have to give the DataAdmin account?
> Curtis
> Geoff N. Hiten wrote:
>

Backing up Databases using SQLServer

I have SQL 2000 on a W2K advanced server. I want to use SQL server's
backup routine to backup the databases to a directory on a separate
computer. I have set up the SQL Client Agent on that other server hoping
that would somehow facilitate this, but there doesn't seem to be any way
to force SQL server to backup the file to a device that is not local to
the server itself.
I understand that one possible option would be to install a full version
of SQL 2000 on the other server and has the primary backup to that
secondary server. But that would mean 2 licenses of SQL Server. I
don't think we have 2 licenses, though I will check on that.
Surely there is some way to backup databases to an non-local device.
Curtis
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
The same technique applies to manual backup jobs as well.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:eiqgAsisEHA.1468@.TK2MSFTNGP12.phx.gbl...
> I have SQL 2000 on a W2K advanced server. I want to use SQL server's
> backup routine to backup the databases to a directory on a separate
> computer. I have set up the SQL Client Agent on that other server hoping
> that would somehow facilitate this, but there doesn't seem to be any way
> to force SQL server to backup the file to a device that is not local to
> the server itself.
> I understand that one possible option would be to install a full version
> of SQL 2000 on the other server and has the primary backup to that
> secondary server. But that would mean 2 licenses of SQL Server. I
> don't think we have 2 licenses, though I will check on that.
> Surely there is some way to backup databases to an non-local device.
> Curtis
|||So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:

> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>
|||Curtis,
Per the KB article ...
2) The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share
Tunji
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:

> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>
|||Everything works better if you give the domain-level account (DataAdmin in
your example) local administrator rights on the SQL server host computer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> So, if I understand correctly the MSSQLSERVER service must be switched
> from running under the LocalSystem account to running under a
> domain-level account, such as, let's say, DataAdmin with a password. And
> which account is not local to the server, but global to the domain. Right?
> If that's true, what rights do I have to give the DataAdmin account?
> Curtis
> Geoff N. Hiten wrote:

Backing up Databases using SQLServer

I have SQL 2000 on a W2K advanced server. I want to use SQL server's
backup routine to backup the databases to a directory on a separate
computer. I have set up the SQL Client Agent on that other server hoping
that would somehow facilitate this, but there doesn't seem to be any way
to force SQL server to backup the file to a device that is not local to
the server itself.
I understand that one possible option would be to install a full version
of SQL 2000 on the other server and has the primary backup to that
secondary server. But that would mean 2 licenses of SQL Server. I
don't think we have 2 licenses, though I will check on that.
Surely there is some way to backup databases to an non-local device.
CurtisHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
The same technique applies to manual backup jobs as well.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:eiqgAsisEHA.1468@.TK2MSFTNGP12.phx.gbl...
> I have SQL 2000 on a W2K advanced server. I want to use SQL server's
> backup routine to backup the databases to a directory on a separate
> computer. I have set up the SQL Client Agent on that other server hoping
> that would somehow facilitate this, but there doesn't seem to be any way
> to force SQL server to backup the file to a device that is not local to
> the server itself.
> I understand that one possible option would be to install a full version
> of SQL 2000 on the other server and has the primary backup to that
> secondary server. But that would mean 2 licenses of SQL Server. I
> don't think we have 2 licenses, though I will check on that.
> Surely there is some way to backup databases to an non-local device.
> Curtis|||So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01D8_01C4B22C.EC0EE200
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Curtis,
Per the KB article ... 2) The SQL Server service account must have FULL CONTROL rights to the =file system folder and to the share
Tunji
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message =news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
So, if I understand correctly the MSSQLSERVER service must be switched =
from running under the LocalSystem account to running under a domain-level account, such as, let's say, DataAdmin with a password. =And which account is not local to the server, but global to the domain. =Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=3D555128
> > The same technique applies to manual backup jobs as well.
>
--=_NextPart_000_01D8_01C4B22C.EC0EE200
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Curtis,
Per the KB article ...
2) The SQL Server service account must have FULL CONTROL =rights to the file system folder and to the share
Tunji
"Curtis Vaughan" wrote in =message news:envLaWjsEHA.160@.T=K2MSFTNGP11.phx.gbl...So, if I understand correctly the MSSQLSERVER service must be switched =from running under the LocalSystem account to running under a =domain-level account, such as, let's say, DataAdmin with a password. And which =account is not local to the server, but global to the domain. Right?If =that's true, what rights do I have to give the DataAdmin account?CurtisGeoff N. Hiten wrote:> HowTo: =Backup to UNC name using Database Maintenance Wizard>http://support.microsoft.com/?id=3D555128">http://support.microso=ft.com/?id=3D555128> > The same technique applies to manual backup jobs as well.>

---

--=_NextPart_000_01D8_01C4B22C.EC0EE200--|||Everything works better if you give the domain-level account (DataAdmin in
your example) local administrator rights on the SQL server host computer.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
> So, if I understand correctly the MSSQLSERVER service must be switched
> from running under the LocalSystem account to running under a
> domain-level account, such as, let's say, DataAdmin with a password. And
> which account is not local to the server, but global to the domain. Right?
> If that's true, what rights do I have to give the DataAdmin account?
> Curtis
> Geoff N. Hiten wrote:
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> > http://support.microsoft.com/?id=555128
> >
> > The same technique applies to manual backup jobs as well.
> >|||Yeh! It's working!!!
Thanks everyone!!!
Geoff N. Hiten wrote:
> Everything works better if you give the domain-level account (DataAdmin in
> your example) local administrator rights on the SQL server host computer.
>sql

Backing up a database?

I've got two developers that are working on a asp.net app that use SQL
Server 2000. They've both got the developer edition on each of their PCs.
I'd like to know what is the best way to "backup" the database so that 1) we
can put this backup on the server so our tape backup picks it up each night
and 2) so a developer can "synch" his version of the database with the other
developer (i.e. developer A does a backup and stores it on a network share
then developer B uses that backup to recreate developer a's database on his
PC).
I've used the backup command, and it seems that it does backup the data and
the schema to a location on the local disk. Then I copied that to the other
developer's PC and did a restore into a new database. That worked well, but
when I tried to restore the same backup file into a second new database on
the other developer's PC, SQL Server complained that the first database
(that I restored to) was using this file. So, something just seemed odd
about that. I also see there is an export wizard, but it seemed like that
was geared towards exporting the raw data to some other format.
Any help would be much appreciated. Thanks!
Yikes. What happens when you have 5 developers? 20? 50? Maintaining that
many development databases will be a nightmare!
Here are my suggestions:
Buy a development database server that everyone can use.
Obtain a source control product such as Visual Source Safe.
When a developer creates a stored procedure or view have them create the
text for the object using Query Analyzer. Save the script to a file (with
the same name as the object) and have him check the file into VSS. When you
need to modify an object grab the script from source control, make the
change, create/recreate the object within the database, save the script
file, and check it back in to source control (with comments of course).
That will give you the ability to know what changed, who changed it, and
why.
Start backing up your database and your source control repository. Make
sure that they get to tape.
Keith
"epigram" <nospam@.spammy.com> wrote in message
news:1111753952.477f69973b1b43c598d9c65d29aec71a@.b ubbanews...
> I've got two developers that are working on a asp.net app that use SQL
> Server 2000. They've both got the developer edition on each of their PCs.
> I'd like to know what is the best way to "backup" the database so that 1)
we
> can put this backup on the server so our tape backup picks it up each
night
> and 2) so a developer can "synch" his version of the database with the
other
> developer (i.e. developer A does a backup and stores it on a network share
> then developer B uses that backup to recreate developer a's database on
his
> PC).
> I've used the backup command, and it seems that it does backup the data
and
> the schema to a location on the local disk. Then I copied that to the
other
> developer's PC and did a restore into a new database. That worked well,
but
> when I tried to restore the same backup file into a second new database on
> the other developer's PC, SQL Server complained that the first database
> (that I restored to) was using this file. So, something just seemed odd
> about that. I also see there is an export wizard, but it seemed like that
> was geared towards exporting the raw data to some other format.
> Any help would be much appreciated. Thanks!
>

Backing up a database?

I've got two developers that are working on a asp.net app that use SQL
Server 2000. They've both got the developer edition on each of their PCs.
I'd like to know what is the best way to "backup" the database so that 1) we
can put this backup on the server so our tape backup picks it up each night
and 2) so a developer can "synch" his version of the database with the other
developer (i.e. developer A does a backup and stores it on a network share
then developer B uses that backup to recreate developer a's database on his
PC).
I've used the backup command, and it seems that it does backup the data and
the schema to a location on the local disk. Then I copied that to the other
developer's PC and did a restore into a new database. That worked well, but
when I tried to restore the same backup file into a second new database on
the other developer's PC, SQL Server complained that the first database
(that I restored to) was using this file. So, something just seemed odd
about that. I also see there is an export wizard, but it seemed like that
was geared towards exporting the raw data to some other format.
Any help would be much appreciated. Thanks!Yikes. What happens when you have 5 developers? 20? 50? Maintaining that
many development databases will be a nightmare!
Here are my suggestions:
Buy a development database server that everyone can use.
Obtain a source control product such as Visual Source Safe.
When a developer creates a stored procedure or view have them create the
text for the object using Query Analyzer. Save the script to a file (with
the same name as the object) and have him check the file into VSS. When you
need to modify an object grab the script from source control, make the
change, create/recreate the object within the database, save the script
file, and check it back in to source control (with comments of course).
That will give you the ability to know what changed, who changed it, and
why.
Start backing up your database and your source control repository. Make
sure that they get to tape.
Keith
"epigram" <nospam@.spammy.com> wrote in message
news:1111753952. 477f69973b1b43c598d9c65d29aec71a@.bubbane
ws...
> I've got two developers that are working on a asp.net app that use SQL
> Server 2000. They've both got the developer edition on each of their PCs.
> I'd like to know what is the best way to "backup" the database so that 1)
we
> can put this backup on the server so our tape backup picks it up each
night
> and 2) so a developer can "synch" his version of the database with the
other
> developer (i.e. developer A does a backup and stores it on a network share
> then developer B uses that backup to recreate developer a's database on
his
> PC).
> I've used the backup command, and it seems that it does backup the data
and
> the schema to a location on the local disk. Then I copied that to the
other
> developer's PC and did a restore into a new database. That worked well,
but
> when I tried to restore the same backup file into a second new database on
> the other developer's PC, SQL Server complained that the first database
> (that I restored to) was using this file. So, something just seemed odd
> about that. I also see there is an export wizard, but it seemed like that
> was geared towards exporting the raw data to some other format.
> Any help would be much appreciated. Thanks!
>

AWE Hotfix - SQLServer.exe \ OSQL.exe Versions

Hi,
I see that after the AWE hot fix in our production server [Not the standard
microsoft recommended MSI way, only the SQLServer.exe and OSQL.exe are
replaced] the following are the versions of each component.
osql.exe , 2000.80.1025.0, 16-May-2005 6:51:12PM
sqlservr.exe , 2000.80.2040.0, 13-May-2005 9:11:46PM
I do not know why the OSQL version chosen is 2000.80.1025.0 instead of
2000.80.2039.0?
Does any has any support documentation which tells that we need to have the
SQLServer\OSQL versions 2000.80.2040.0\2000.80.1025.0 respectively? I feel
that the current combination is wrong and it should be
2000.80.2040.0\2000.80.2039.0. [I have installed the AWE in the standard way
in server and noticed that SQLServer\OSQL versions are
2000.80.2040.0\2000.80.2039.0].
TIA,
Manoj
I have to ask. Why would you NOT use the hotfix installer and hand-patch
the binaries?
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Manoj Kumar" <ManojKumar@.discussions.microsoft.com> wrote in message
news:13955C6A-8D22-4A6A-BCAD-EBDA006FF26D@.microsoft.com...
> Hi,
> I see that after the AWE hot fix in our production server [Not the
> standard
> microsoft recommended MSI way, only the SQLServer.exe and OSQL.exe are
> replaced] the following are the versions of each component.
> osql.exe , 2000.80.1025.0, 16-May-2005 6:51:12PM
> sqlservr.exe , 2000.80.2040.0, 13-May-2005 9:11:46PM
> I do not know why the OSQL version chosen is 2000.80.1025.0 instead of
> 2000.80.2039.0?
> Does any has any support documentation which tells that we need to have
> the
> SQLServer\OSQL versions 2000.80.2040.0\2000.80.1025.0 respectively? I
> feel
> that the current combination is wrong and it should be
> 2000.80.2040.0\2000.80.2039.0. [I have installed the AWE in the standard
> way
> in server and noticed that SQLServer\OSQL versions are
> 2000.80.2040.0\2000.80.2039.0].
>
> TIA,
> Manoj
>

AWE enabled

Hi, please let me know if I enable AWE and PAE and turn on /3 GB switch, why
can't I see sqlserver.exe or total/target server memory more that 2.72 GB?
Event though there is a page/sec goes upto 6000. Server must need memory more
than 3 GB. My email address is : atiqur_rahman02@.yahoo.com or you can paste
here. Thanks for your help!
How much memory does your server machine have? What is the version and
edition of the operating system and SQL Server you are using?
Anith

AWE

Our current production environment consists of an 2 node (active-active) SQL
Server 2000 SP3a database cluster. It was recently rebuilt and we did not
reenable Address Windowing Extensions (AWE). Is it recommended that I turn
it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
asking this because it appears to be performing well with AWE off. Also
should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
but is performing well without it.
Thanks,
Jay
Hi,
If you have 8 GB ram please enable AWE. My value for SQL Server will be from
6 to 6.5 GB.
I will leave the rest to OS and other applications.
Thanks
Hari
SQL Server MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay
|||"jay d" wrote:
> Our current production environment consists of an 2 node (active-active) SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
> but is performing well without it.
Jay,
If the servers are running well without AWE, I would say keep it turned
off.
In my experience, the AWE implementation has some bugs. Better to keep
it simple unless you need the extra memory.
-- J
|||Just because you have lots of ram does not mean sql server will be able to
use it all effectively. It depends a lot on your data and how you access it.
If your cache hit ratio and page life expectancy arealways high and your
response times are good you may not need to turn it on. But if you do you
certainly should not set it to 7gb since you have an active - active
configuration. Since AWE memory is not dynamic you can have a situation
where one instance gets 7GB and leaves only 1GB for the other instance and
the OS and performance will suffer on all fronts.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay
|||Thank you all for the help. One more quick question. If I am 3 instances
should I set the max server memory to 7 GIG on each of them or do I divide
the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
and Instance 3 - 2 GIG.
Thanks,
Jay
"Andrew J. Kelly" wrote:

> Just because you have lots of ram does not mean sql server will be able to
> use it all effectively. It depends a lot on your data and how you access it.
> If your cache hit ratio and page life expectancy arealways high and your
> response times are good you may not need to turn it on. But if you do you
> certainly should not set it to 7gb since you have an active - active
> configuration. Since AWE memory is not dynamic you can have a situation
> where one instance gets 7GB and leaves only 1GB for the other instance and
> the OS and performance will suffer on all fronts.
> --
> Andrew J. Kelly SQL MVP
>
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
|||As I stated when you set AWE it is no longer dynamic. The first instance to
come up will grab all the memory it can up to the MAX Memory setting. If
you set it to 7GB for each instance and you only have 8GB total there isn't
enough to go around. When you use AWE you can not share the memory. So if
you really do have three instances that is probably the real reason why AWE
is turned off to begin with.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:9144E391-55F4-4B7F-9858-321A6973AE50@.microsoft.com...[vbcol=seagreen]
> Thank you all for the help. One more quick question. If I am 3 instances
> should I set the max server memory to 7 GIG on each of them or do I divide
> the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
> and Instance 3 - 2 GIG.
> Thanks,
> Jay
> "Andrew J. Kelly" wrote:
|||Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:

> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
|||Just be sure not to apply SP4 for SQL Server. There is a bug with respect
to AWE. They are working on a fix.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:8A900118-4F9B-4378-87F5-15EA79CC42F4@.microsoft.com...
Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:

> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be
> from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>

AWE

Our current production environment consists of an 2 node (active-active) SQL
Server 2000 SP3a database cluster. It was recently rebuilt and we did not
reenable Address Windowing Extensions (AWE). Is it recommended that I turn
it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
asking this because it appears to be performing well with AWE off. Also
should I allocate 7 GB of RAM to for 'max server memory'? It is not set now
,
but is performing well without it.
Thanks,
JayHi,
If you have 8 GB ram please enable AWE. My value for SQL Server will be from
6 to 6.5 GB.
I will leave the rest to OS and other applications.
Thanks
Hari
SQL Server MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||"jay d" wrote:
> Our current production environment consists of an 2 node (active-active) S
QL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I tur
n
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I a
m
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set n
ow,
> but is performing well without it.
Jay,
If the servers are running well without AWE, I would say keep it turned
off.
In my experience, the AWE implementation has some bugs. Better to keep
it simple unless you need the extra memory.
-- J|||Just because you have lots of ram does not mean sql server will be able to
use it all effectively. It depends a lot on your data and how you access it.
If your cache hit ratio and page life expectancy arealways high and your
response times are good you may not need to turn it on. But if you do you
certainly should not set it to 7gb since you have an active - active
configuration. Since AWE memory is not dynamic you can have a situation
where one instance gets 7GB and leaves only 1GB for the other instance and
the OS and performance will suffer on all fronts.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||Thank you all for the help. One more quick question. If I am 3 instances
should I set the max server memory to 7 GIG on each of them or do I divide
the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
and Instance 3 - 2 GIG.
Thanks,
Jay
"Andrew J. Kelly" wrote:

> Just because you have lots of ram does not mean sql server will be able to
> use it all effectively. It depends a lot on your data and how you access i
t.
> If your cache hit ratio and page life expectancy arealways high and your
> response times are good you may not need to turn it on. But if you do you
> certainly should not set it to 7gb since you have an active - active
> configuration. Since AWE memory is not dynamic you can have a situation
> where one instance gets 7GB and leaves only 1GB for the other instance and
> the OS and performance will suffer on all fronts.
> --
> Andrew J. Kelly SQL MVP
>
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>|||As I stated when you set AWE it is no longer dynamic. The first instance to
come up will grab all the memory it can up to the MAX Memory setting. If
you set it to 7GB for each instance and you only have 8GB total there isn't
enough to go around. When you use AWE you can not share the memory. So if
you really do have three instances that is probably the real reason why AWE
is turned off to begin with.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:9144E391-55F4-4B7F-9858-321A6973AE50@.microsoft.com...[vbcol=seagreen]
> Thank you all for the help. One more quick question. If I am 3 instances
> should I set the max server memory to 7 GIG on each of them or do I divide
> the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
> and Instance 3 - 2 GIG.
> Thanks,
> Jay
> "Andrew J. Kelly" wrote:
>|||Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:

> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be fr
om
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>|||Just be sure not to apply SP4 for SQL Server. There is a bug with respect
to AWE. They are working on a fix.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:8A900118-4F9B-4378-87F5-15EA79CC42F4@.microsoft.com...
Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:

> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be
> from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>

Autostart Sql Server Agent

In sql server 2005 how do you change the settings for autostarting the sqlserver agent when the OS starts. For eg in sql server 2000 when you click the server properties you can cha nge it there but what about sql server 2005?right click My Computer and then Manage. Once there, expand Services and Applications, then click on Services. From there, you can find the SQL Server Agent() service, go to properties, and make the startup type Automatic.|||

Hello Tim,

Thanks for this info, but i wanted to know how could you do it from management console. like how you can do it from Enterprise manager in SQL server 2000. This will help my cause but just for better knowledge i wanted to know.

|||

SQL Server Configuration Manager is the tool to be used in sql server 2005 for any administration of Services, protocols etc... You can find it Programs --MSSQLServer 2005 --Configuration Tool.

Right click on any service and do whatever setting you want there.

ref : http://msdn2.microsoft.com/en-us/library/ms174212.aspx

Madhu

|||Thanks a lot Madhu. I appreciate that.|||

Make sure you mark it as "Answered"

yes, SQL Configuration Tool would be the place to configure Service Start type of services

AutoNumber with specified format

Hi all,
In SQLServer 2005, are there any method to create the autonumber with
specific format?
for example,
I would like to have a key which the pattern is ABC00001 and the next
autonumber is ABC00002 and the next autonumber is ABC00003...
However, I won't like to handle it in the front-end application. Any idea?
Regards
alexGood wrote:
> Hi all,
> In SQLServer 2005, are there any method to create the autonumber with
> specific format?
> for example,
> I would like to have a key which the pattern is ABC00001 and the next
> autonumber is ABC00002 and the next autonumber is ABC00003...
> However, I won't like to handle it in the front-end application. Any
> idea?
> Regards
> alex
You'll have to roll that solution yourself. Your proposed key will
consume 8 bytes and give you only 100,000 values - unless you change the
prefix. In any case, I would store the next value as a numeric in a
"next key table" and use an instead of insert trigger on the table to
automatically create the key values. For example:
Set nocount on
go
create table dbo.NextKey (TableName nvarchar(255) not null primary key,
NextKeyVal int not null default 1)
insert into dbo.NextKey (TableName) Values ('TestTable')
Select * from dbo.NextKey
go
Create Table dbo.TestTable (SomeID char(8) not null primary key, SomeCol
int not null)
go
Drop Trigger TestTableIOI
go
Create Trigger TestTableIOI on dbo.TestTable Instead Of Insert
as
Begin
Declare @.NextKeyVal int
If (Select count(*) from inserted) != 1
RAISERROR ('Only one row inserted at a time please', 15, 1)
Else Begin
Update dbo.NextKey Set @.NextKeyVal = NextKeyVal = NextKeyVal + 1
Where TableName = N'TestTable'
If @.NextKeyVal Is Null
RAISERROR ('Could not determine next key value for table: %s', 15,
1, N'TestTable')
Else Begin
Insert dbo.TestTable (SomeID, SomeCol)
Select 'ABC' + right('00000' + cast(@.NextKeyVal as varchar(5)),
5), SomeCol From inserted
End
End
End
Go
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Select * from dbo.TestTable
Go
Drop Table dbo.TestTable
Drop Table dbo.NextKey
David Gugick - SQL Server MVP
Quest Software|||Alternatively, go with the identity column and use a computed column to gene
rate
the value derived from the identity column. In your example it looks like
the identity you need is just a concatenation of the identity value with
a fixed prefix. For example,
create function dbo.IdAsMyId (@.id int)
returns char(8)
as begin
declare @.prefix char(3);
set @.prefix = 'ABC';
declare @.leadingZeros int;
set @.leadingZeros = 8 - len(@.id) - len(@.prefix);
declare @.myId char(8)
set @.myId = 'ABC' + replicate('0', @.leadingZeros) + cast(@.id as varchar(5));
return @.myId;
end
go
create table CustomIdentity (
id int identity(1,1),
myId as dbo.IdAsMyId(id),
data varchar(255))
go
insert CustomIdentity (data) values ('One')
insert CustomIdentity (data) values ('Two')
insert CustomIdentity (data) values ('Three')
go
select *
from CustomIdentity
go
You probaby want to index the computed column if you're going to search on
it.
Cheers,
Stefan
http://www.fotia.co.uk

> Good wrote:
>
> You'll have to roll that solution yourself. Your proposed key will
> consume 8 bytes and give you only 100,000 values - unless you change
> the prefix. In any case, I would store the next value as a numeric in
> a "next key table" and use an instead of insert trigger on the table
> to automatically create the key values. For example:
> Set nocount on
> go
> create table dbo.NextKey (TableName nvarchar(255) not null primary
> key,
> NextKeyVal int not null default 1)
> insert into dbo.NextKey (TableName) Values ('TestTable')
> Select * from dbo.NextKey
> go
> Create Table dbo.TestTable (SomeID char(8) not null primary key,
> SomeCol
> int not null)
> go
> Drop Trigger TestTableIOI
> go
> Create Trigger TestTableIOI on dbo.TestTable Instead Of Insert
> as
> Begin
> Declare @.NextKeyVal int
> If (Select count(*) from inserted) != 1
> RAISERROR ('Only one row inserted at a time please', 15, 1)
> Else Begin
> Update dbo.NextKey Set @.NextKeyVal = NextKeyVal = NextKeyVal + 1
> Where TableName = N'TestTable'
> If @.NextKeyVal Is Null
> RAISERROR ('Could not determine next key value for table: %s',
> 15,
> 1, N'TestTable')
> Else Begin
> Insert dbo.TestTable (SomeID, SomeCol)
> Select 'ABC' + right('00000' + cast(@.NextKeyVal as varchar(5)),
> 5), SomeCol From inserted
> End
> End
> End
> Go
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Select * from dbo.TestTable
> Go
> Drop Table dbo.TestTable
> Drop Table dbo.NextKey|||Stefan Delmarco wrote:
> Alternatively, go with the identity column and use a computed column
> to generate the value derived from the identity column. In your
> example it looks like the identity you need is just a concatenation of
> the identity value
> with a fixed prefix. For example,
>
I think if you go that route you are better off using a computed column
without the scalar function.
create table TestTable (id int identity not null primary key, ak as
'ABC' + right('00000' + cast(id as varchar(5)), 5), col1 int not null)
create unique index TestTable_ak on TestTable (ak)
go
insert into TestTable (col1) values (1)
insert into TestTable (col1) values (2)
insert into TestTable (col1) values (3)
Select * from TestTable
go
drop table TestTable
David Gugick - SQL Server MVP
Quest Software

AutoNumber in SQLServer 2000

Hi
Please help me URGENT
How do i make an tableField with a
Auto-Incr, the same like in an access DB where it's
called AutoNumber
Thanx in advance
__________________________________________________________________ Flemming
Paulsen ICQ#: 270065050 Current ICQ status: + More ways to contact me
__________________________________________________________________Please dpo not post the same message independently to multiple groups. If
you must post o . 1 group then you can include them all in the same post.
See Andrew's post in pne of the other groups.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"VideoSmeden" <software@.picsign.dk> wrote in message
news:%23qO7%23oThDHA.2164@.TK2MSFTNGP09.phx.gbl...
> Hi
> Please help me URGENT
> How do i make an tableField with a
> Auto-Incr, the same like in an access DB where it's
> called AutoNumber
> Thanx in advance
> __________________________________________________________________
Flemming
> Paulsen ICQ#: 270065050 Current ICQ status: + More ways to contact me
> __________________________________________________________________
>

autonomous transactions

Is there any equivalent to Oracle's autonomous transaction in sqlserver.

Depends on what does "autonomous transaction" mean.

|||No. There is no way to do this in SQL Server right now. You can write extended SPs or CLR SPs (SQL 2005) that performs a different connection to the database. But this approach has it's own limitations and scalability problems. One workaround that will work in SQL2000/2005 depending on your requirements is to use a table variable within a transaction to dump the information. Table variables do not participate in user transactions so you can later retrieve the rows and dump them into an error log table for example after the transaction has been rolled back.|||Thanks Umachander for the information.
Autonomous Transaction can be used for controlling transactions in stored procedures. Autonomous Transactions allow you to create a new subtransaction that may commit or rollback changes independent of the parent transaction. Assume two procedures - Parent and child procedure. The parent procedure calls child procedure. You can set the statements with in the child procedure as Autonomous and this will be independent of the transaction of the Parent procedure. Hope the above explains the Autonomous Transaction.
|||Helped me. Thanks to both of you I learned something today!|||

Hi Umachander,

I have read your comments and I had the same problem when using SSMA to convert a PL/SQL stored procedure to T-SQL.

Interesting note is the error message - "ERROR ORA2MS-2046 line: 3 col: 4: Conversion of PRAGMA AUTONOMOUS_TRANSACTION is not implemented in this version"

I sincerely believe that if SQL Server is to be RDBMS of choice, code from any other RDBMS must be convertable

If you have any suggestions please let me know as it would be most appreciated

|||The only workarounds are those I mentioned in my previous reply. And we are considering this feature for a future version of SQL Server.