ODBC Isolation level: offline

Showing posts with label offline. Show all posts

Monday, March 26, 2012

Offline/remote backups

What is the best way to perform daily offline/remote/off-premise backups of our websistes Sql Server 2005 database?

I've looked into using SSIS but for one reason or another am having serious problems with this. Ideally we'd like to be able connect over the internet and/or lan and pull the entire database down in one go. Our db is ~600mb so isn't major.

We used to use Sql Server 2000's DTS and this worked well, but I'm having a real hard time using SSIS to do the same so was wondering what the other options are

Many thanks

Ben

I'm curious as to why you're going the DTS/SSIS route rather than simply using a standard SQL backup and copying the backup file to a remote location. Understanding that would help us come up with a scenrio that would meet your needs.

If you need a remote system that is a hot or cold standby, you might consider something like log-shipping.

|||

Hi Kevin

The reason we used DTS originally was that it allowed us to backup over the internet. Obviously, upgrading to Sql Server 2005 made SSIS the logical choice.

My customer has two servers running inside the same building and he wants the live database periodically backed-up to the other server so in the event of hard-drive failure, they can still retrieve their data. Also, possibly just once a day, they'd like to download the entire database to another office via the internet.

I suggested to my customer about using the normal backup and possibly FTP'ing the backup file to a remote location. However, log-shipping certianly seems like a good idea. I'd never heard of it until just you mentioned it, so thankyou very much.

Do both instances of Sql Server have to be the same type? Ie, can an Enterprise or Standard version log-ship to a Sql Express instance? Can more than one other instance be used for log-shipping?

Thanks for your help

Ben

|||Log shipping is not supported in SQL Server 2005 Express. If you want, you can emulate log shipping by doing a job that backs up the transaction log on the primary server, FTP the backup to the standby server, and restore the backup on the standby server in read-only mode. This can be done using scripts. A good understanding of how log shipping works will give you an idea on how to create the custom log shipping using a combination of jobs and scripts. The problem with this is that you do not have the luxury of a monitor server nso you need to make sure that you implement your alerts properly. You can check http://www.sql-server-performance.com/sql_server_log_shipping.asp for a reference article on how to do a custom log shipping with scripts, jobs and all.

Offline use

Hi
We have an access app (both front & back ends are in access) that runs over
win server networks. There are now more and more users who would like to
take away a copy of app with them, work offline, and when they come back
expect the client copy to sync (both ways) with the server copy. Seems like
a reasonable requirement. The question is; what do I need to do to make this
happen? Do I need to switch the backend to sql server? Front end to vb or
something like that? Anything else? How do other people do this?
Thanks
Regards
use merge replication to MSDE databases, create pull subscriptions
managed by Windows Synchronization Manager.
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
|||Is this reliable "enough"?
Regards
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1114135809.601906.238980@.o13g2000cwo.googlegr oups.com...
> use merge replication to MSDE databases, create pull subscriptions
> managed by Windows Synchronization Manager.
> --
> 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
>
|||Hi
If architected correclty, it works perfectly.
But, considering it is offline, there is always a small chance that you
loose data. The device can be lost or fail before you have the opportunity
upload the data. If you need 100% guarantee that the data gets to your DB,
you have to do it online.
Regards
Mike
"John" wrote:

> Is this reliable "enough"?
> Regards
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:1114135809.601906.238980@.o13g2000cwo.googlegr oups.com...
>
>
|||> Is this reliable "enough"?
Compared to using Access Jet in a shared environment? Almost certainly!
David Portas
SQL Server MVP
|||Yes, Kratft Foods/Nabisco has a solution using this only to hand helds
running SQL CE with IIRC 1400 truck drivers. Their fleet distribution
network is built on this. Its reliable enough for them.
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
"John" <John@.nospam.infovis.co.uk> wrote in message
news:u%23eUINxRFHA.3972@.TK2MSFTNGP14.phx.gbl...
> Is this reliable "enough"?
> Regards
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:1114135809.601906.238980@.o13g2000cwo.googlegr oups.com...
>
|||I'd move to a SQL server (or MSDE) and .Net solution.
ADO.Net (the data access part of .Net) is already disconnected. Data
is moved back and forth between the client and server (even if they're
running on the same machine), in the form of disconnected datasets.
Datasets can be persisted to XML files.
This is one of the cool features of the .Net platform and is really
worth looking into for your needs.
-nick
On Fri, 22 Apr 2005 01:15:39 +0100, "John" <John@.nospam.infovis.co.uk>
wrote:

>Hi
>We have an access app (both front & back ends are in access) that runs over
>win server networks. There are now more and more users who would like to
>take away a copy of app with them, work offline, and when they come back
>expect the client copy to sync (both ways) with the server copy. Seems like
>a reasonable requirement. The question is; what do I need to do to make this
>happen? Do I need to switch the backend to sql server? Front end to vb or
>something like that? Anything else? How do other people do this?
>Thanks
>Regards
>

Offline use

Hi
We have an access app (both front & back ends are in access) that runs over
win server networks. There are now more and more users who would like to
take away a copy of app with them, work offline, and when they come back
expect the client copy to sync (both ways) with the server copy. Seems like
a reasonable requirement. The question is; what do I need to do to make this
happen? Do I need to switch the backend to sql server? Front end to vb or
something like that? Anything else? How do other people do this?
Thanks
Regardsuse merge replication to MSDE databases, create pull subscriptions
managed by Windows Synchronization Manager.
--
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|||Is this reliable "enough"?
Regards
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1114135809.601906.238980@.o13g2000cwo.googlegroups.com...
> use merge replication to MSDE databases, create pull subscriptions
> managed by Windows Synchronization Manager.
> --
> 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
>|||Hi
If architected correclty, it works perfectly.
But, considering it is offline, there is always a small chance that you
loose data. The device can be lost or fail before you have the opportunity
upload the data. If you need 100% guarantee that the data gets to your DB,
you have to do it online.
Regards
Mike
"John" wrote:
> Is this reliable "enough"?
> Regards
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:1114135809.601906.238980@.o13g2000cwo.googlegroups.com...
> > use merge replication to MSDE databases, create pull subscriptions
> > managed by Windows Synchronization Manager.
> >
> > --
> > 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
> >
>
>|||> Is this reliable "enough"?
Compared to using Access Jet in a shared environment? Almost certainly!
--
David Portas
SQL Server MVP
--|||Yes, Kratft Foods/Nabisco has a solution using this only to hand helds
running SQL CE with IIRC 1400 truck drivers. Their fleet distribution
network is built on this. Its reliable enough for them.
--
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
"John" <John@.nospam.infovis.co.uk> wrote in message
news:u%23eUINxRFHA.3972@.TK2MSFTNGP14.phx.gbl...
> Is this reliable "enough"?
> Regards
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:1114135809.601906.238980@.o13g2000cwo.googlegroups.com...
>> use merge replication to MSDE databases, create pull subscriptions
>> managed by Windows Synchronization Manager.
>> --
>> 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
>|||I'd move to a SQL server (or MSDE) and .Net solution.
ADO.Net (the data access part of .Net) is already disconnected. Data
is moved back and forth between the client and server (even if they're
running on the same machine), in the form of disconnected datasets.
Datasets can be persisted to XML files.
This is one of the cool features of the .Net platform and is really
worth looking into for your needs.
-nick
On Fri, 22 Apr 2005 01:15:39 +0100, "John" <John@.nospam.infovis.co.uk>
wrote:
>Hi
>We have an access app (both front & back ends are in access) that runs over
>win server networks. There are now more and more users who would like to
>take away a copy of app with them, work offline, and when they come back
>expect the client copy to sync (both ways) with the server copy. Seems like
>a reasonable requirement. The question is; what do I need to do to make this
>happen? Do I need to switch the backend to sql server? Front end to vb or
>something like that? Anything else? How do other people do this?
>Thanks
>Regards
>

Offline use

Hi
We have an access app (both front & back ends are in access) that runs over
win server networks. There are now more and more users who would like to
take away a copy of app with them, work offline, and when they come back
expect the client copy to sync (both ways) with the server copy. Seems like
a reasonable requirement. The question is; what do I need to do to make this
happen? Do I need to switch the backend to sql server? Front end to vb or
something like that? Anything else? How do other people do this?
Thanks
Regardsuse merge replication to MSDE databases, create pull subscriptions
managed by Windows Synchronization Manager.
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|||Is this reliable "enough"?
Regards
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1114135809.601906.238980@.o13g2000cwo.googlegroups.com...
> use merge replication to MSDE databases, create pull subscriptions
> managed by Windows Synchronization Manager.
> --
> 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
>|||Hi
If architected correclty, it works perfectly.
But, considering it is offline, there is always a small chance that you
loose data. The device can be lost or fail before you have the opportunity
upload the data. If you need 100% guarantee that the data gets to your DB,
you have to do it online.
Regards
Mike
"John" wrote:

> Is this reliable "enough"?
> Regards
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:1114135809.601906.238980@.o13g2000cwo.googlegroups.com...
>
>|||> Is this reliable "enough"?
Compared to using Access Jet in a shared environment? Almost certainly!
David Portas
SQL Server MVP
--|||Yes, Kratft Foods/Nabisco has a solution using this only to hand helds
running SQL CE with IIRC 1400 truck drivers. Their fleet distribution
network is built on this. Its reliable enough for them.
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
"John" <John@.nospam.infovis.co.uk> wrote in message
news:u%23eUINxRFHA.3972@.TK2MSFTNGP14.phx.gbl...
> Is this reliable "enough"?
> Regards
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:1114135809.601906.238980@.o13g2000cwo.googlegroups.com...
>|||I'd move to a SQL server (or MSDE) and .Net solution.
ADO.Net (the data access part of .Net) is already disconnected. Data
is moved back and forth between the client and server (even if they're
running on the same machine), in the form of disconnected datasets.
Datasets can be persisted to XML files.
This is one of the cool features of the .Net platform and is really
worth looking into for your needs.
-nick
On Fri, 22 Apr 2005 01:15:39 +0100, "John" <John@.nospam.infovis.co.uk>
wrote:

>Hi
>We have an access app (both front & back ends are in access) that runs over
>win server networks. There are now more and more users who would like to
>take away a copy of app with them, work offline, and when they come back
>expect the client copy to sync (both ways) with the server copy. Seems like
>a reasonable requirement. The question is; what do I need to do to make thi
s
>happen? Do I need to switch the backend to sql server? Front end to vb or
>something like that? Anything else? How do other people do this?
>Thanks
>Regards
>sql

Offline Synchronization

Hi All,
Sorry for a long post but I'm not very good in english and so cannot
describe my problem in any other way. Please bear with me.
I'm trying to implement a solution that requires data synchronization
across multiple locations. My application will be running on various
branches of a company. These branches are not connected to each other.
Each branch will have their own local SQL Server with the complete
data. Each branch will have full access to data (insert /
update/edit/delete). Periodically the data across all branches will
need to be synchronized (offline via export and import). Any ideas on
how I can achieve this?
Right now what I have in mind is this:
My Application is generating the primary keys for all the tables (like
customers, orders etc.) Here I have ensured that the primary across all
branches will remain unique by prefixing the branchcode (2 characters)
to the primary keys in all tables. All tables also have a CreatedOn
and UpdatedOn fields (datetime). Now for synchronizing data from
Branch A to Branch B, I will export the data from branch A into a MS
Access database. And then I will import it into branch B. While
importing, the application will check each record in the SQL Server. If
the record is not present then it will insert it into SQL Server. If
the record is present and the SQL Server data is older, it will update
with the Access record. If the data in SQL Server is newer then
the record will be skipped.
I hope I have been able to convey what I'm trying to do.
In theory, this seems to be an OK solution but I think a lot of work
will go into this (specially for conflict resolution).
Is there any other (better) way to implement what I'm trying to do? I
have heard about replication, but I'm not sure if it will be useful in
my case. Are there any built-in features in SQL that I can use to for
this kind of offline synchronization?
Any help or pointers would be very appreciated.
Thanks in Advance,
Anoushka
replication requires a link between the two nodes.
You will have to cobble together something like what you are talking about,
if you don't have a network connection.
"Anoushka" <anoushka.jones@.gmail.com> wrote in message
news:1095829976.948995.199280@.h37g2000oda.googlegr oups.com...
> Hi All,
> Sorry for a long post but I'm not very good in english and so cannot
> describe my problem in any other way. Please bear with me.
> I'm trying to implement a solution that requires data synchronization
> across multiple locations. My application will be running on various
> branches of a company. These branches are not connected to each other.
> Each branch will have their own local SQL Server with the complete
> data. Each branch will have full access to data (insert /
> update/edit/delete). Periodically the data across all branches will
> need to be synchronized (offline via export and import). Any ideas on
> how I can achieve this?
> Right now what I have in mind is this:
> My Application is generating the primary keys for all the tables (like
> customers, orders etc.) Here I have ensured that the primary across all
> branches will remain unique by prefixing the branchcode (2 characters)
> to the primary keys in all tables. All tables also have a CreatedOn
> and UpdatedOn fields (datetime). Now for synchronizing data from
> Branch A to Branch B, I will export the data from branch A into a MS
> Access database. And then I will import it into branch B. While
> importing, the application will check each record in the SQL Server. If
> the record is not present then it will insert it into SQL Server. If
> the record is present and the SQL Server data is older, it will update
> with the Access record. If the data in SQL Server is newer then
> the record will be skipped.
> I hope I have been able to convey what I'm trying to do.
> In theory, this seems to be an OK solution but I think a lot of work
> will go into this (specially for conflict resolution).
> Is there any other (better) way to implement what I'm trying to do? I
> have heard about replication, but I'm not sure if it will be useful in
> my case. Are there any built-in features in SQL that I can use to for
> this kind of offline synchronization?
> Any help or pointers would be very appreciated.
> Thanks in Advance,
> Anoushka
>
|||Hello Hilary,
Thanks for the reply. I am ready to "cobble together" the solution
that I described

The reason for me posting here is that I just
wanted to make sure that it is absolutely the most elegant way (if we
can call it that!) to do it.
I have never implemented replication of any kind so I wasn't sure.
Thanks once again,
Anoushka

Offline Resources

I have taken SQL Server resources OFFLINE. Will the SQL Server resources be
brought ONLINE automatically during failover or will it be in the OFFLINE
state until brought ONLINE manually?
When you manually bring a group offline, it will NOT come back online during
a failover or a restart. You have to bring it online manually.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Samuel" <Samuel@.discussions.microsoft.com> wrote in message
news:0154DAAB-E305-4101-96DE-FD0280D092A1@.microsoft.com...
>I have taken SQL Server resources OFFLINE. Will the SQL Server resources be
> brought ONLINE automatically during failover or will it be in the OFFLINE
> state until brought ONLINE manually?

Offline Reporting DB...

A customer of ours was considering replication as a means of maintaining
data in a second DB on a second server for reporting use only. We think
replication is overkill since they won't mind if the data is a day or two
old.
I like the idea of simply restoring their most recent full backup on the
reporting server, but I'm not sure if they do a full backup every night.
What are other approaches that folks use to accomplish this sort of thing?
Log shipping doesn't seem right; replication seems like overkill. DTS
possibly? Any thoughts, links, or words will be greatly appreciated!
Thanks in advance for your time,
James Hunter RossWithout details about the database size, your connection bandwidth, etc.,
it's hard to say what is the best way for your needs.
If the customer can tolerate data two days old, you can use snapshot
replication and set the snapshot interval as 48 hours. Yuo can also make use
of log shipping secondary server with restored databases being in read-only
mode. Then users can run reports off the databases from time to time. The
inconvenience with log shipping is to regularly kick users out of the
database so that restore of transaction logs can go on. The third option
will be increase full backup frequency so that there is a full backup every
two days, otherwise you will have to restore from a full backup more than 2
days ago and apply all subsequent transaction logs.
Other things you can consider: will copy database wizard apply? It requires
the database being shut down.
Richard
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining
>data in a second DB on a second server for reporting use only. We think
>replication is overkill since they won't mind if the data is a day or two
>old.
> I like the idea of simply restoring their most recent full backup on the
> reporting server, but I'm not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
> Log shipping doesn't seem right; replication seems like overkill. DTS
> possibly? Any thoughts, links, or words will be greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>|||Keep it simple, if possible. Backup restore is as simple as it gets, and you get your backups tested
each time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining data in a second DB on a
>second server for reporting use only. We think replication is overkill since they won't mind if
>the data is a day or two old.
> I like the idea of simply restoring their most recent full backup on the reporting server, but I'm
> not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing? Log shipping doesn't
> seem right; replication seems like overkill. DTS possibly? Any thoughts, links, or words will be
> greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>|||We use log shipping for this as our main DB is too large to restore nightly.
Works well.
Mike Kruchten
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining
>data in a second DB on a second server for reporting use only. We think
>replication is overkill since they won't mind if the data is a day or two
>old.
> I like the idea of simply restoring their most recent full backup on the
> reporting server, but I'm not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
> Log shipping doesn't seem right; replication seems like overkill. DTS
> possibly? Any thoughts, links, or words will be greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>

Offline Reporting DB...

A customer of ours was considering replication as a means of maintaining
data in a second DB on a second server for reporting use only. We think
replication is overkill since they won't mind if the data is a day or two
old.
I like the idea of simply restoring their most recent full backup on the
reporting server, but I'm not sure if they do a full backup every night.
What are other approaches that folks use to accomplish this sort of thing?
Log shipping doesn't seem right; replication seems like overkill. DTS
possibly? Any thoughts, links, or words will be greatly appreciated!
Thanks in advance for your time,
James Hunter Ross
Without details about the database size, your connection bandwidth, etc.,
it's hard to say what is the best way for your needs.
If the customer can tolerate data two days old, you can use snapshot
replication and set the snapshot interval as 48 hours. Yuo can also make use
of log shipping secondary server with restored databases being in read-only
mode. Then users can run reports off the databases from time to time. The
inconvenience with log shipping is to regularly kick users out of the
database so that restore of transaction logs can go on. The third option
will be increase full backup frequency so that there is a full backup every
two days, otherwise you will have to restore from a full backup more than 2
days ago and apply all subsequent transaction logs.
Other things you can consider: will copy database wizard apply? It requires
the database being shut down.
Richard
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining
>data in a second DB on a second server for reporting use only. We think
>replication is overkill since they won't mind if the data is a day or two
>old.
> I like the idea of simply restoring their most recent full backup on the
> reporting server, but I'm not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
> Log shipping doesn't seem right; replication seems like overkill. DTS
> possibly? Any thoughts, links, or words will be greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>
|||Keep it simple, if possible. Backup restore is as simple as it gets, and you get your backups tested
each time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining data in a second DB on a
>second server for reporting use only. We think replication is overkill since they won't mind if
>the data is a day or two old.
> I like the idea of simply restoring their most recent full backup on the reporting server, but I'm
> not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing? Log shipping doesn't
> seem right; replication seems like overkill. DTS possibly? Any thoughts, links, or words will be
> greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>
|||We use log shipping for this as our main DB is too large to restore nightly.
Works well.
Mike Kruchten
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining
>data in a second DB on a second server for reporting use only. We think
>replication is overkill since they won't mind if the data is a day or two
>old.
> I like the idea of simply restoring their most recent full backup on the
> reporting server, but I'm not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
> Log shipping doesn't seem right; replication seems like overkill. DTS
> possibly? Any thoughts, links, or words will be greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>

Offline Reporting DB...

A customer of ours was considering replication as a means of maintaining
data in a second DB on a second server for reporting use only. We think
replication is overkill since they won't mind if the data is a day or two
old.
I like the idea of simply restoring their most recent full backup on the
reporting server, but I'm not sure if they do a full backup every night.
What are other approaches that folks use to accomplish this sort of thing?
Log shipping doesn't seem right; replication seems like overkill. DTS
possibly? Any thoughts, links, or words will be greatly appreciated!
Thanks in advance for your time,
James Hunter RossWithout details about the database size, your connection bandwidth, etc.,
it's hard to say what is the best way for your needs.
If the customer can tolerate data two days old, you can use snapshot
replication and set the snapshot interval as 48 hours. Yuo can also make use
of log shipping secondary server with restored databases being in read-only
mode. Then users can run reports off the databases from time to time. The
inconvenience with log shipping is to regularly kick users out of the
database so that restore of transaction logs can go on. The third option
will be increase full backup frequency so that there is a full backup every
two days, otherwise you will have to restore from a full backup more than 2
days ago and apply all subsequent transaction logs.
Other things you can consider: will copy database wizard apply? It requires
the database being shut down.
Richard
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining
>data in a second DB on a second server for reporting use only. We think
>replication is overkill since they won't mind if the data is a day or two
>old.
> I like the idea of simply restoring their most recent full backup on the
> reporting server, but I'm not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
> Log shipping doesn't seem right; replication seems like overkill. DTS
> possibly? Any thoughts, links, or words will be greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>|||Keep it simple, if possible. Backup restore is as simple as it gets, and you
get your backups tested
each time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining da
ta in a second DB on a
>second server for reporting use only. We think replication is overkill sin
ce they won't mind if
>the data is a day or two old.
> I like the idea of simply restoring their most recent full backup on the r
eporting server, but I'm
> not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
Log shipping doesn't
> seem right; replication seems like overkill. DTS possibly? Any thoughts,
links, or words will be
> greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>|||We use log shipping for this as our main DB is too large to restore nightly.
Works well.
Mike Kruchten
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OYhK%23KdcFHA.3828@.tk2msftngp13.phx.gbl...
>A customer of ours was considering replication as a means of maintaining
>data in a second DB on a second server for reporting use only. We think
>replication is overkill since they won't mind if the data is a day or two
>old.
> I like the idea of simply restoring their most recent full backup on the
> reporting server, but I'm not sure if they do a full backup every night.
> What are other approaches that folks use to accomplish this sort of thing?
> Log shipping doesn't seem right; replication seems like overkill. DTS
> possibly? Any thoughts, links, or words will be greatly appreciated!
> Thanks in advance for your time,
> James Hunter Ross
>sql

Offline Report Generation

Hi,

I have a requirement of generating very long reports (1-2hrs approx). Obviously the user would not like to wait for the report to come up to view.

Can I generate reports offline using SSRS 2005? If yes, then how can i submit the report for generation and re-direct the user to any other page, where he/she can see the status of the report (or continue to work on other pages).
If yes, then can I run an exe in the background to check the status of the report at regular intervals and notify the user once the report is completed? I checked the status of the report in the ExecutionLog table should be 'rsSuccessful', indicating the successful generation of the report.

If yes, then where (path) the reports generated offline are stored?

Thanks in advance.One option would be to schedule the reports and then deliver them to an email address or fileshare.
Another option would be to use the snapshot feature.
From BOL:

Report history is a collection of report snapshots. You can maintain report history by adding and deleting snapshots, or by modifying properties that affect report history storage. You can create report history manually or on a schedule.

To create report history, your role assignment must include the "Manage report history" task. To view report history, your role assignment must include the "View reports" task. Report history is available to all users who have access to the report. You cannot selectively enable or disable report history for a subset of users.

Snapshots in report history are identified by the date and time they were created. The date and time is based on when the query executed.

offline OLAP Cube +MDX

I will be really very much greatful to one who can help me out as I have been struggling to solve this from 2 months.

I have a .mdb database and I want to craete a pivot from it.The size of the database is around 400 MB so it was suggested to create a cube from this database and then create pivot.The database is populated with TOTAL values for all its class variables .

When I create cube from Excel wizard it again summarises and craetes ALL level and the pivot is completely wrong.Infact I get values in the multiples of 2**classvariables.Is there a way where can I create cube by VBA and eliminate ALL LEVEL for every dimension. I have tried so many codes but nothing is working.Any sample code will be a great help.

Here I am getting Multiple Selections for page field as well .Is there any way to disable them?

The second option could be to populate the Listviews with the dimension values and then create a report in Excel using MDX but its not working as well.

I am relly looking looking forward to you great guys.

Regards

Hi,

create the cube with the Excel-Wizar. Save the to C:\

Then there is a SclicerDDL.txt with the CREATE GLOBAL Cube-Statement.

Remove the All Level

Example:

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [(All)], <-Delete this line
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
)

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
),

Run this edited SlicerDDL.txt in the Analysis Server MDX-Samples and the local cube will not contain the All-Level.

hth

Bernhard Saemmer

|||I only have Excel in my machine.Is it possible to do it by VBA?

offline OLAP Cube +MDX

I will be really very much greatful to one who can help me out as I have been struggling to solve this from 2 months.

Here I am getting Multiple Selections for page field as well .Is there any way to disable them?

The second option could be to populate the Listviews with the dimension values and then create a report in Excel using MDX but its not working as well.

I am relly looking looking forward to you great guys.

Regards

Hi,

create the cube with the Excel-Wizar. Save the to C:\

Then there is a SclicerDDL.txt with the CREATE GLOBAL Cube-Statement.

Remove the All Level

Example:

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [(All)], <-Delete this line
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
)

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
),

Run this edited SlicerDDL.txt in the Analysis Server MDX-Samples and the local cube will not contain the All-Level.

hth

Bernhard Saemmer

|||I only have Excel in my machine.Is it possible to do it by VBA?

Offline mode ?

Hi all,
I am designing and testing a report using an ODBC connection. The
report is short (one or two pages) but the SQL request has many tables
and takes several minutes. Being in development phase I don't need the
datas to be refreshed from SQL database every time I click on
"preview" to see the report, but this is what happens (I find no other
reason for so long response time...). So I spend a lot of time waiting
for response.
Is there a kind of "offline" mode to see layout modifications without
refreshing datas every time ?
Thanks !
Jean-MarcTry using your SQL query to export the data from the multiple tables into a
single table. For your development and testing, base your report data on the
single table.
Using Query Analyzer, do:
SELECT * INTO MyTable FROM (rest of complex query)
Now base your test report on the data in MyTable. (You can also use DTS to
create the single table, and can even export the data into a local Access
table).
Charles Kangai, MCT, MCDBA
"Jean-Marc Audrin" wrote:
> Hi all,
> I am designing and testing a report using an ODBC connection. The
> report is short (one or two pages) but the SQL request has many tables
> and takes several minutes. Being in development phase I don't need the
> datas to be refreshed from SQL database every time I click on
> "preview" to see the report, but this is what happens (I find no other
> reason for so long response time...). So I spend a lot of time waiting
> for response.
> Is there a kind of "offline" mode to see layout modifications without
> refreshing datas every time ?
> Thanks !
> Jean-Marc
>sql

Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:
> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece of
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without the
> need for OLEDB drivers.
>
>|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:
>> I don't know where to begin with this. We designed an online product
>> catalogue who now want to put it on a CD. Is there any third party piece
>> of
>> software that could convert an sql server or access db into standalone
>> program that the client could put on a CD without he need for Access
>> Forms,
>> OLEDB etc.
>> If I could put the db in XML could I point the Access runtime, which the
>> developer version of Office can export, at that as a datasource without
>> the
>> need for OLEDB drivers.
>>

Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.
Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:

> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece of
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without the
> need for OLEDB drivers.
>
>
|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...[vbcol=seagreen]
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:

Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:

> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece o
f
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms
,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without th
e
> need for OLEDB drivers.
>
>|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...[vbcol=seagreen]
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:
>

Monday, March 26, 2012

Offline/remote backups

Offline use

Offline use

Offline use

Offline use

Offline use

Offline use

Offline use

Offline Synchronization

Offline Resources

Offline Reporting DB...

Offline Reporting DB...

Offline Reporting DB...

Offline Report Generation

offline OLAP Cube +MDX

offline OLAP Cube +MDX

Offline mode ?

Offline database

Offline database

Offline database

ODBC Isolation level

Blog Archive

About Me