Role change in log shipping

As a last step to implementing a log shipping role change...i..e making the
secondary the primary and the primary the secondary, do users change the
servernames so that apps that are referring the SQL server by host name
reflect the actual name .
We would like to implement some kind of disaster recovery using SQL 2000 log
shipping and wanted to know when we do role changes , what are the best ways
to allow the clients to still communicate to the now secondary server that
has been promoted to be the primary.I prefer the application being prepared for this. The app can for instance read the server name in a
configuration file somewhere and you quite simply change the config file to change roles.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:%232MdTFolDHA.372@.TK2MSFTNGP11.phx.gbl...
> As a last step to implementing a log shipping role change...i..e making the
> secondary the primary and the primary the secondary, do users change the
> servernames so that apps that are referring the SQL server by host name
> reflect the actual name .
> We would like to implement some kind of disaster recovery using SQL 2000 log
> shipping and wanted to know when we do role changes , what are the best ways
> to allow the clients to still communicate to the now secondary server that
> has been promoted to be the primary.
>
>|||Heelo
> As a last step to implementing a log shipping role change...i..e making
the
> secondary the primary and the primary the secondary, do users change the
> servernames so that apps that are referring the SQL server by host name
> reflect the actual name .
Sure. I'd recommend you to make an alias on DNS server
wich will always lead to your production server. In this case
you'll need to change only this alias. (But if you use large
network you'll need to propagate modifications across all
DNS servers your clients use).
Serge Shakhov

Robust audit tools

With HIPAA security becoming law on April 20th, I've got some customers that
want to know about products that will log events on the server...
Does anyone have any knowledge or experience with tools like this.
For example, tracking...
1) Every call to a SPROC and what parameters were used
2) Every SQL statement executed against the database - from any client tool
3) Change events - original data, new data, who did it - stuff like that...
Thanks in advance.Lumigent Entegra
http://www.lumigent.com/
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:C99ACADF-DD67-48A8-B3A7-FED13F1C99CE@.microsoft.com...
> With HIPAA security becoming law on April 20th, I've got some customers
> that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client
> tool
> 3) Change events - original data, new data, who did it - stuff like
> that...
> Thanks in advance.|||sql profiler will do a nice job with #1 and #2
Greg Jackson
PDX, Oregon|||You have to set up a pretty squeaky-clean trace to justify running profiler
24/7 in a production environment.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> sql profiler will do a nice job with #1 and #2|||yes...very good point
gaj
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23U$iLYEQFHA.3988@.tk2msftngp13.phx.gbl...
> You have to set up a pretty squeaky-clean trace to justify running
> profiler 24/7 in a production environment.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>|||what about changes to the source code and having a complete history/audit
trail of those...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Z" wrote:

> With HIPAA security becoming law on April 20th, I've got some customers th
at
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client to
ol
> 3) Change events - original data, new data, who did it - stuff like that.
.
> Thanks in advance.sql

Robust audit tools

With HIPAA security becoming law on April 20th, I've got some customers that
want to know about products that will log events on the server...
Does anyone have any knowledge or experience with tools like this.
For example, tracking...
1) Every call to a SPROC and what parameters were used
2) Every SQL statement executed against the database - from any client tool
3) Change events - original data, new data, who did it - stuff like that...
Thanks in advance.
Lumigent Entegra
http://www.lumigent.com/
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:C99ACADF-DD67-48A8-B3A7-FED13F1C99CE@.microsoft.com...
> With HIPAA security becoming law on April 20th, I've got some customers
> that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client
> tool
> 3) Change events - original data, new data, who did it - stuff like
> that...
> Thanks in advance.
|||sql profiler will do a nice job with #1 and #2
Greg Jackson
PDX, Oregon
|||You have to set up a pretty squeaky-clean trace to justify running profiler
24/7 in a production environment.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> sql profiler will do a nice job with #1 and #2
|||yes...very good point
gaj
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23U$iLYEQFHA.3988@.tk2msftngp13.phx.gbl...
> You have to set up a pretty squeaky-clean trace to justify running
> profiler 24/7 in a production environment.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
|||what about changes to the source code and having a complete history/audit
trail of those...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Z" wrote:

> With HIPAA security becoming law on April 20th, I've got some customers that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client tool
> 3) Change events - original data, new data, who did it - stuff like that...
> Thanks in advance.

Right edition for Log shipping

We have
Svr1 - Production - SQL2K Ent Edition.(sp3a)
Svr 2 -Satellite - SQL2K Std Edition.(sp3a)
We currently have replication between Svr1 and Svr2 on a database called
'Sales' (some tables).
Now we want to have an additional 'Instance' in Svr2 to act as a Disaster
Recovery for the database Sales from Svr1 using Log shipping.
Questions:
Do we need to upgrade to Enterprise edition in Svr 2 to make Log shipping to
work? (Can I manage it with just the Resource kit)
Are there any issues to watch out for?
Thanks,
Suresh KumarI believe you Enterprise Edition for Log Shipping to work.
HTH
DeeJay
>--Original Message--
>We have
>Svr1 - Production - SQL2K Ent Edition.(sp3a)
>Svr 2 -Satellite - SQL2K Std Edition.(sp3a)
>We currently have replication between Svr1 and Svr2 on a
database called
>'Sales' (some tables).
>Now we want to have an additional 'Instance' in Svr2 to
act as a Disaster
>Recovery for the database Sales from Svr1 using Log
shipping.
>Questions:
>Do we need to upgrade to Enterprise edition in Svr 2 to
make Log shipping to
>work? (Can I manage it with just the Resource kit)
>Are there any issues to watch out for?
>Thanks,
>Suresh Kumar
>
>.
>

Revover MS SQL 2000 from Log file (last 4 hours)

Sorry in advance for the stupid question but I am not a technical expert at all!!!

I made a backup from a SQL-Server 2000 DB 3 days ago. Today data have been deleted from the system and they should be recovered. I have a full recovery model.

I tried to restore the DB but before I made a new backup (Just in case) I hope that was no error??

Is there a way to restore the DB from the log file but just restore the last 4 hours?

Thanks
AIf you maintain Tlog backups and rEview information on books online about restoring Tlogs upto a point of time.

Reverse log shipping

We are in the planning stage of a redundant SQL environment and had a
question about log shipping.
We would like to double our use of the standby server and use it as a
pre-production server from time to time. Meaning, we would like to import
and test data on our standby server and then be able to log ship the changes
over to the primary server. Is this possible/recommended? If so, how would
it be done ?
Thanks
Nick
No.
Your better option is to move the DDL changes by script, or by using a 3rd
party tool such as Red Gate's SQL Compare.
You can move data using Script, DTS/SSIS, Red Gate's SQL Data Compare, etc.
You cannot change the data/schema in a Log Shipping Destination server
(whether its the Prod box or the standby) without Recovering it first, which
stops the Log Shipping part.
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"N." <larosan@.yahoo.com> wrote in message news:wR6wh.1$_i4.0@.newsfe09.lga...
> We are in the planning stage of a redundant SQL environment and had a
> question about log shipping.
> We would like to double our use of the standby server and use it as a
> pre-production server from time to time. Meaning, we would like to import
> and test data on our standby server and then be able to log ship the
> changes over to the primary server. Is this possible/recommended? If so,
> how would it be done ?
> Thanks
> Nick
>

Returning log File space sued

I am writing a database space tracking setup. I can get
data file space figures eventually (with my variant of
sp_spaceused), but finding the log file space used - such
as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
Manager, how do I get that preferably with some simple SQL
query ?
TIA
Jim
CREATE TABLE Test
(
[Database Name] VARCHAR(50),
[Log Size] REAL,
[Log Space Used] REAL,
Status TINYINT
)
INSERT INTO Test EXEC ('DBCC SQLPERF(LOGSPACE)')
GO
SELECT * FROM Test WHERE .........
GO
Drop table Test
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:1f5c01c44874$e1165580$3a01280a@.phx.gbl...
> I am writing a database space tracking setup. I can get
> data file space figures eventually (with my variant of
> sp_spaceused), but finding the log file space used - such
> as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
> Manager, how do I get that preferably with some simple SQL
> query ?
> TIA
|||Hi,
Try the below query, This query will work even if we have multiple
transaction log files.
select sum(maxsize * 8.0/1024.0) AS MAX_SIZE ,sum(size * 8.0/1024.0) AS
CURRENT_SIZE
from sysfiles a where groupid = 0
Thanks
Hari
MCDBA
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:1f5c01c44874$e1165580$3a01280a@.phx.gbl...
> I am writing a database space tracking setup. I can get
> data file space figures eventually (with my variant of
> sp_spaceused), but finding the log file space used - such
> as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
> Manager, how do I get that preferably with some simple SQL
> query ?
> TIA
|||The query "insert into <table> exec ('dbcc sqlperf (logspace ' )"
worked a treat !
This one
select sum(maxsize * 8.0/1024.0) AS MAX_SIZE ,sum(size * 8.0/1024.0) AS
CURRENT_SIZE
from sysfiles a where groupid = 0
didn't because maxsize is set to unlimited.
Thanks for your answers !
Jim Trowbridge
DBA
jtrowbridge@.adelaidebank.com.au
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Returning log File space sued

I am writing a database space tracking setup. I can get
data file space figures eventually (with my variant of
sp_spaceused), but finding the log file space used - such
as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
Manager, how do I get that preferably with some simple SQL
query ?
TIAJim
CREATE TABLE Test
(
[Database Name] VARCHAR(50),
[Log Size] REAL,
[Log Space Used] REAL,
Status TINYINT
)
INSERT INTO Test EXEC ('DBCC SQLPERF(LOGSPACE)')
GO
SELECT * FROM Test WHERE .........
GO
Drop table Test
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:1f5c01c44874$e1165580$3a01280a@.phx.gbl...
> I am writing a database space tracking setup. I can get
> data file space figures eventually (with my variant of
> sp_spaceused), but finding the log file space used - such
> as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
> Manager, how do I get that preferably with some simple SQL
> query ?
> TIA|||Hi,
Try the below query, This query will work even if we have multiple
transaction log files.
select sum(maxsize * 8.0/1024.0) AS MAX_SIZE ,sum(size * 8.0/1024.0) AS
CURRENT_SIZE
from sysfiles a where groupid = 0
Thanks
Hari
MCDBA
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:1f5c01c44874$e1165580$3a01280a@.phx.gbl...
> I am writing a database space tracking setup. I can get
> data file space figures eventually (with my variant of
> sp_spaceused), but finding the log file space used - such
> as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
> Manager, how do I get that preferably with some simple SQL
> query ?
> TIA|||The query "insert into <table> exec ('dbcc sqlperf (logspace ' )"
worked a treat !
This one
select sum(maxsize * 8.0/1024.0) AS MAX_SIZE ,sum(size * 8.0/1024.0) AS
CURRENT_SIZE
from sysfiles a where groupid = 0
didn't because maxsize is set to unlimited.
Thanks for your answers !
Jim Trowbridge
DBA
jtrowbridge@.adelaidebank.com.au
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Returning log File space sued

I am writing a database space tracking setup. I can get
data file space figures eventually (with my variant of
sp_spaceused), but finding the log file space used - such
as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
Manager, how do I get that preferably with some simple SQL
query ?
TIAJim
CREATE TABLE Test
(
[Database Name] VARCHAR(50),
[Log Size] REAL,
[Log Space Used] REAL,
Status TINYINT
)
INSERT INTO Test EXEC ('DBCC SQLPERF(LOGSPACE)')
GO
SELECT * FROM Test WHERE .........
GO
Drop table Test
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:1f5c01c44874$e1165580$3a01280a@.phx.gbl...
> I am writing a database space tracking setup. I can get
> data file space figures eventually (with my variant of
> sp_spaceused), but finding the log file space used - such
> as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
> Manager, how do I get that preferably with some simple SQL
> query ?
> TIA|||Hi,
Try the below query, This query will work even if we have multiple
transaction log files.
select sum(maxsize * 8.0/1024.0) AS MAX_SIZE ,sum(size * 8.0/1024.0) AS
CURRENT_SIZE
from sysfiles a where groupid = 0
Thanks
Hari
MCDBA
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:1f5c01c44874$e1165580$3a01280a@.phx.gbl...
> I am writing a database space tracking setup. I can get
> data file space figures eventually (with my variant of
> sp_spaceused), but finding the log file space used - such
> as DBCC SQLPERF(LOGSPACE) shows, and also Enterprise
> Manager, how do I get that preferably with some simple SQL
> query ?
> TIA