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 vs Snaphot replication

In the enterprise version of SQL there is the facility to do snapshot
replication which I have not tried.
How does this differ from snapshot replication?
What this does is capture a point in time image of a database which you can
connect to as a different database.
Database snapshots are local to the database and uses up considerable disk
space. Snapshot replication can replicate locally and/or remote. It can
replicate to multiple subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1170891510.578187.22220@.v33g2000cwv.googlegro ups.com...
> In the enterprise version of SQL there is the facility to do snapshot
> replication which I have not tried.
> How does this differ from snapshot replication?
>
|||Thanks Hilary
My client is considering using mirroring for fault tolerance under SQL
Server 2005 but they also require a version of the database that is
reasonably up to date for reporting purposes. They dont have the
Enterprise version of SQL, so I was wondering if mirroring and then
doing snapshot replication (from the mirror) would be a viable
solution. I suspect not as the database would be unavailable for too
long. In addition, I am not even sure if this is an available option.
NB: They are unable to use transactional or merge replication due to
the tables not having primary keys.
I know that snapshot replication can be extremely slow but I am not
sure how much faster database snapshots are? In addition, when you do
them, can you simply overwrite the previous one as you dont want the
clients to have to access a different database?
Bottom line, is it a viable solution to make multiple database
snapshots during the course of a production day (from the mirror)?
I dont want to propose going to Enterprise version if it isnt going to
work.
On Feb 8, 12:29 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> What this does is capture a point in time image of a database which you can
> connect to as a different database.
> Database snapshots are local to the database and uses up considerable disk
> space. Snapshot replication can replicate locally and/or remote. It can
> replicate to multiple subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "PromisedOyster" <PromisedOys...@.hotmail.com> wrote in message
> news:1170891510.578187.22220@.v33g2000cwv.googlegro ups.com...
>
>
> - Show quoted text -
|||One problem, you can't do snapshot replication off a mirror.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1170908921.373111.263690@.v33g2000cwv.googlegr oups.com...
> Thanks Hilary
> My client is considering using mirroring for fault tolerance under SQL
> Server 2005 but they also require a version of the database that is
> reasonably up to date for reporting purposes. They dont have the
> Enterprise version of SQL, so I was wondering if mirroring and then
> doing snapshot replication (from the mirror) would be a viable
> solution. I suspect not as the database would be unavailable for too
> long. In addition, I am not even sure if this is an available option.
> NB: They are unable to use transactional or merge replication due to
> the tables not having primary keys.
> I know that snapshot replication can be extremely slow but I am not
> sure how much faster database snapshots are? In addition, when you do
> them, can you simply overwrite the previous one as you dont want the
> clients to have to access a different database?
> Bottom line, is it a viable solution to make multiple database
> snapshots during the course of a production day (from the mirror)?
> I dont want to propose going to Enterprise version if it isnt going to
> work.
> On Feb 8, 12:29 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
|||Thanks, well I guess I can rule out snapshot replication.
However, what about database snapshots. How feasible is it to take
them once every half hour (say) and overwrite the existing one and
then use that as the reporting database server?
On Feb 8, 8:18 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> One problem, you can't do snapshot replication off a mirror.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "PromisedOyster" <PromisedOys...@.hotmail.com> wrote in message
> news:1170908921.373111.263690@.v33g2000cwv.googlegr oups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -

Database Snapshots question

Dear all,
How can I make a snapshot in sql25k? When I do click on the option 'Database
snapshots' only appears 'refresh'.
Thanks for any input,from bol
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO
"Enric" wrote:

> Dear all,
> How can I make a snapshot in sql25k? When I do click on the option 'Databa
se
> snapshots' only appears 'refresh'.
> Thanks for any input,
>|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Omnibuzz" wrote:
> from bol
> CREATE DATABASE AdventureWorks_dbss1800 ON
> ( NAME = AdventureWorks_Data, FILENAME =
> 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
> AS SNAPSHOT OF AdventureWorks;
> GO
> --
>
>
> "Enric" wrote:
>sql

Database Snapshots Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

Database Snapshots Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

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

Database Snapshots

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.Can you please explain the term about 'low', is it the data size or data?|||

Satya SKJ wrote:

Can you please explain the term about 'low', is it the data size or data?

It was "Slow" and not "low". Sorry.
I mean the time to execute a query.|||

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

|||

Satya SKJ wrote:

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

Ok. I read that note.
But what does it mean "Performance is reduced"?
Query on the normal table executed in about 40 seconds; on the snapshot it's over 8 minutes...

Database Snapshot Disk IO considerations and Best Practices

I understand the concept of database snapshots, however, I'm unsure how to tackle this from disk utilization point of view.

First off I'll explain how I want to use database snapshots, then the configurations at the disk level I was considering.

Currently in 2005 we can also find Snapshot Isolation whcih helps with concurrency but it will use the tempdb heavily. Since I have enough things hammering the tempdb I was considering using database snapshots to help with the reporting functionality.

Since a database snapshot creates sparse files there will not be a lot of space being consumed (I'll have a question on this one later). Since creating a database snapshot is pretty light weight I was considering creating a job that will snapshot the database every minute (don't freak out yet let me explain). Then I will have another job to drop snapshots that do not have an active transaction in them. I've created a function that will return the latest available snapshot and using some dynamic SQL I can use that in my reporting code.

I've created all the code for this to happen, but now comes the fun part... where do I put the database snapshots?

Currently my databases have seperated array for Log files, seperate array for the clustered indexes, and a seperate array for non-clustered indexes (as well as partitioned tables, BLOB, etc...). So all together my IO is split up across about 32 spindles. I have 3 spindles in a RAID 5 available. Do I created the database snapshot in the same location as the files (because creating a db snapshot is just like creating a real database). Or to I lump them up on the small array, data and logs and all?

Also, if my database is let say 75GB and I want to snapshot it to a 36GB partition I know that the sparse files will be small and it show actual space on disk to be small but logically it will show at 75GB will SQL or the OS have an issue with that?

Thanks

Rich

If there is a possibility to create another drive then use that to store the backup/snapshot files. I'm not sure if you are using EMC then it will be easy to store them inspite of any issue. Why you want to compress the snapshot in this case.|||I'm not looking to compress anything. I'm not sure if I'm being clear but I'm talking about SQL 2005 new ability to create database snapshots, which automatically creates sparse files. We are using an EMC CX3-20 SAN if that helps.

Database Snapshot

Need some help, i have some database snapshots files provided from an external source. Need to be able to understand how i get them back into a database format if possible.

Files for example are table1.bcp, table2.bcp with als a file called scheme.sql which sets up these tables in sql but does not populate them. Nothing else was provided except a .vcd file which i dont know whtat its for.

any clues?

Dan

This link describes how to restore a database from a database snapshot

http://www.sqlservercentral.com/columnists/akhosla/2733.asp

unfortunately I don't have right now access to an SQL server to see how to 'make' SQL Server 'see' a snapshot created by another server

|||

I dont have the original database though, only these files and this link assumes you have the original database?/

Dan

|||

yes this link assumes you made the snapshot on the same sql server :(

|||

Are you CERTAIN that these files were created using the SQL Server Database Snapshot feature?

It looks to me as if you have a SQL script to create tables, and BCP data files to feed into the BCP (Bulk copy) utility to load the data.

I would read BOL on BCP and attempt to load the files that way.

|||

Yes you are correct, this does indeed seem to be a Bulk Copy in Native format, guess i was going down the wrong path.

Thanks i now have the data in the correct format, also the extract works in SQL 2000 which is good.

Much appreciated.

Dan