Showing posts with label mirroring. Show all posts
Showing posts with label mirroring. Show all posts

Tuesday, March 27, 2012

Database Snapshots, Mirroring, and Log Shipping

Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>

Database Snapshots, Mirroring, and Log Shipping

Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegroups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>

Database Snapshots, Mirroring, and Log Shipping

Can someone explain to me how/why you can create a database snapshot
against a mirrored database, but not a log shipped database?
Some extra logic is required to keep the snapshot active during recovery.
This is built into the mirroring logic. Log shipping just uses normal log
restore logic.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<jalbenberg@.yahoo.com> wrote in message
news:1169249796.950379.163120@.q2g2000cwa.googlegro ups.com...
> Can someone explain to me how/why you can create a database snapshot
> against a mirrored database, but not a log shipped database?
>

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.
An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.

Database Snapshots & Reporting

We are looking at mirroring some of our databases to a remote location
and snapshotting those databases in order to report off them.
One of Microsofts recommendations is to add a time suffix to the
snapshotname in order to identify the age of the snapshot.
Any reporting system is going to use a DSN to connect to the snapshot,
we intend to snapshot frequently in order to keep the data as fresh as
possible. Does this not mean that the DSN is going to need to change to
point to the latest snapshot database?
The only alternative I can think of is to sp_renamedb the existing
snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
the original name.
This is a superb feature and a great selling point for 2005.
Has anyone implemented this ? and if so how did you overcome this
problem.
Kind Regards & Thanks.An update for anyone else who has this problem, we have found a
possible solution.
If your application uses DSNs
You can create a number of of file DSNs relevant to the snapshot name
e.g.
appdb_1200.dsn
appdb_1800.dsn
appdb_0000.dsn
appdb_0600.dsn
Have your application use a DSN named appdb.dsn, and after creating the
snapshot database, do an xp_cmdshell to copy the relevant file over the
top of the appdb.dsn on the application server.
This allows your application to use a consistent DSN Name, but you are
cycling the DSNs with regards to the snapshot.
You can do the same thing if you use system DSNs as these are stored in
the registry.
Check out the following page
[url]http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov04/hey1110.mspx[/
url]
If you use a connection string hardcoded into the app, I guess you
could use the system views to determine the latest snapshot, or
populate a 'latest snapshot' table and build your connection string
dynamically based on that.
Hope this helps someone out there.
sqldood@.googlemail.com wrote:
> We are looking at mirroring some of our databases to a remote location
> and snapshotting those databases in order to report off them.
> One of Microsofts recommendations is to add a time suffix to the
> snapshotname in order to identify the age of the snapshot.
> Any reporting system is going to use a DSN to connect to the snapshot,
> we intend to snapshot frequently in order to keep the data as fresh as
> possible. Does this not mean that the DSN is going to need to change to
> point to the latest snapshot database?
> The only alternative I can think of is to sp_renamedb the existing
> snapshot 'dbsnap' to 'dbsnap_old' and then create a new snapshot with
> the original name.
> This is a superb feature and a great selling point for 2005.
> Has anyone implemented this ? and if so how did you overcome this
> problem.
> Kind Regards & Thanks.sql

Wednesday, March 21, 2012

Database server layout

Hi All,
Being small, we have only one database server that serves our need (SQL
2000 Enterprise Edition). We never had any replication or mirroring.
Now we will be installing SQL 2005.
I was asked to look into the best way of creating a good database
server so that we will not have any downtime.
At the present if we need to stop/restart or something goes wrong with
the server, then we loose many working hours sometimes even a few days.
Could you please point me the right direction (articles, web link) so
that I can see an example of good database server layout?
I do appreciate your help.
Thanks a million in advance.
Best regards,
MamunHi Mamun
I would suggest the best and most cost effective solution with the lease
amount of administrative effort would have to be mirroring. THe SQL Native
Client monitors the server status and fails over automatically for you and
your applications can continue working without have to re-establish
connection again of having to wait for failovers to complete. We tested
mirroring extensively and achieved failover in 5 sec, MS claim u can be up
and running on the mirror instance in 3 sec.
have a look at it, i'm sure it would fulfill all your needs
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
--
http://www.sqlserver.co.za
"mamun_ah@.hotmail.com" wrote:
> Hi All,
> Being small, we have only one database server that serves our need (SQL
> 2000 Enterprise Edition). We never had any replication or mirroring.
> Now we will be installing SQL 2005.
> I was asked to look into the best way of creating a good database
> server so that we will not have any downtime.
> At the present if we need to stop/restart or something goes wrong with
> the server, then we loose many working hours sometimes even a few days.
>
> Could you please point me the right direction (articles, web link) so
> that I can see an example of good database server layout?
> I do appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun
>

Database server layout

Hi All,
Being small, we have only one database server that serves our need (SQL
2000 Enterprise Edition). We never had any replication or mirroring.
Now we will be installing SQL 2005.
I was asked to look into the best way of creating a good database
server so that we will not have any downtime.
At the present if we need to stop/restart or something goes wrong with
the server, then we loose many working hours sometimes even a few days.
Could you please point me the right direction (articles, web link) so
that I can see an example of good database server layout?
I do appreciate your help.
Thanks a million in advance.
Best regards,
MamunHi Mamun
I would suggest the best and most cost effective solution with the lease
amount of administrative effort would have to be mirroring. THe SQL Native
Client monitors the server status and fails over automatically for you and
your applications can continue working without have to re-establish
connection again of having to wait for failovers to complete. We tested
mirroring extensively and achieved failover in 5 sec, MS claim u can be up
and running on the mirror instance in 3 sec.
have a look at it, i'm sure it would fulfill all your needs
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
http://www.sqlserver.co.za
"mamun_ah@.hotmail.com" wrote:

> Hi All,
> Being small, we have only one database server that serves our need (SQL
> 2000 Enterprise Edition). We never had any replication or mirroring.
> Now we will be installing SQL 2005.
> I was asked to look into the best way of creating a good database
> server so that we will not have any downtime.
> At the present if we need to stop/restart or something goes wrong with
> the server, then we loose many working hours sometimes even a few days.
>
> Could you please point me the right direction (articles, web link) so
> that I can see an example of good database server layout?
> I do appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun
>

Database server layout

Hi All,
Being small, we have only one database server that serves our need (SQL
2000 Enterprise Edition). We never had any replication or mirroring.
Now we will be installing SQL 2005.
I was asked to look into the best way of creating a good database
server so that we will not have any downtime.
At the present if we need to stop/restart or something goes wrong with
the server, then we loose many working hours sometimes even a few days.
Could you please point me the right direction (articles, web link) so
that I can see an example of good database server layout?
I do appreciate your help.
Thanks a million in advance.
Best regards,
Mamun
Hi Mamun
I would suggest the best and most cost effective solution with the lease
amount of administrative effort would have to be mirroring. THe SQL Native
Client monitors the server status and fails over automatically for you and
your applications can continue working without have to re-establish
connection again of having to wait for failovers to complete. We tested
mirroring extensively and achieved failover in 5 sec, MS claim u can be up
and running on the mirror instance in 3 sec.
have a look at it, i'm sure it would fulfill all your needs
http://www.microsoft.com/technet/pro.../dbmirror.mspx
http://www.sqlserver.co.za
"mamun_ah@.hotmail.com" wrote:

> Hi All,
> Being small, we have only one database server that serves our need (SQL
> 2000 Enterprise Edition). We never had any replication or mirroring.
> Now we will be installing SQL 2005.
> I was asked to look into the best way of creating a good database
> server so that we will not have any downtime.
> At the present if we need to stop/restart or something goes wrong with
> the server, then we loose many working hours sometimes even a few days.
>
> Could you please point me the right direction (articles, web link) so
> that I can see an example of good database server layout?
> I do appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun
>
sql

Thursday, March 8, 2012

Database Restore with NORECOVERY

Hi All,

im trying to set up database mirroring and in the initial phase of database restoring im getting some problem.

the scenario is .

1- i create a tempDB on server1(which will be principal server) and backup full and transaction logs.

2- create tempDB in server 2( going to be used as mirror) .

3- restore the full backup of "tempDB" from server1 with NORECOVERY option.

this step results in a message box showing that the restore was successfull...

but the database explorer( management studio) shows the tempDB in (restoring ...) state. and no more operations can be performed with this Database. ( because teh databse isin restoring mode).. please help what this issue is?

Thanks In Advance for any suggestions

hi amir,

1.you have a db in principal server and it shud be in full recovery mode to configure db mirroring

2.take a full a full backup and restore it with norecovery option in the mirror prior to configuring mirroring

3.take a tran log backup in principal and restore it with norecovery option in the mirror.

then start configuring db mirroring using the wizard............

norecovery option restores the db but it will be in restoring state i.e you can apply tran logs to it but you cant use it and no objects will be visible

normally there are 3 types while performing restoration,

1.Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
2.Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
3.Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

http://msdn2.microsoft.com/en-us/library/ms177429.aspx - refer this link for restoring options.........

refer ,

http://deepakinsql.blogspot.com/2007/07/how-to-configure-database-mirroring.html - for configuring dbmirroring

http://deepakinsql.blogspot.com/2007/07/prerequisites-for-database-mirroring.html - prerequisites for mirroring

revert if you have nay concerns

thanks

|||

Hi Amir,

What Deepak xplained is right.

Follow the steps and if want to see the objects in mirror database just force a failover. The new mirror will be in restoring... state

Its quite confusing, it should have been "dont worry continue..." rather than "restoring..."

Goodluck

Vj

Database Restore with NORECOVERY

Hi All,

im trying to set up database mirroring and in the initial phase of database restoring im getting some problem.

the scenario is .

1- i create a tempDB on server1(which will be principal server) and backup full and transaction logs.

2- create tempDB in server 2( going to be used as mirror) .

3- restore the full backup of "tempDB" from server1 with NORECOVERY option.

this step results in a message box showing that the restore was successfull...

but the database explorer( management studio) shows the tempDB in (restoring ...) state. and no more operations can be performed with this Database. ( because teh databse isin restoring mode).. please help what this issue is?

Thanks In Advance for any suggestions

hi amir,

1.you have a db in principal server and it shud be in full recovery mode to configure db mirroring

2.take a full a full backup and restore it with norecovery option in the mirror prior to configuring mirroring

3.take a tran log backup in principal and restore it with norecovery option in the mirror.

then start configuring db mirroring using the wizard............

norecovery option restores the db but it will be in restoring state i.e you can apply tran logs to it but you cant use it and no objects will be visible

normally there are 3 types while performing restoration,

1.Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
2.Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
3.Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

http://msdn2.microsoft.com/en-us/library/ms177429.aspx - refer this link for restoring options.........

refer ,

http://deepakinsql.blogspot.com/2007/07/how-to-configure-database-mirroring.html - for configuring dbmirroring

http://deepakinsql.blogspot.com/2007/07/prerequisites-for-database-mirroring.html - prerequisites for mirroring

revert if you have nay concerns

thanks

|||

Hi Amir,

What Deepak xplained is right.

Follow the steps and if want to see the objects in mirror database just force a failover. The new mirror will be in restoring... state

Its quite confusing, it should have been "dont worry continue..." rather than "restoring..."

Goodluck

Vj