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.
Showing posts with label reporting. Show all posts
Showing posts with label reporting. Show all posts
Tuesday, March 27, 2012
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.
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
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 snapshot
I drop and recreate a database snapshot for reporting purposes at the end of a DW loading process.
I need to create some indexes to improve query performance.
Where I should create the index, on the originale db or on the snapshot db ?
Cosimo
If I remember from an article, you can not do this on a snapshot database. So I guess you should do this on the source DB. Please check it though.|||SOLVED
I create the index on the source db.
Wednesday, March 7, 2012
Database 'ReportServer' does not exist
Shouldn't reporting services setup create ReportServer database
automatically?
The setup fails at the very end with this message:
SQL Server failed to execute command for server configuration. The error
was: Database 'ReportServer' does not exist. Check sysdatabases. ...
Thanks,
-StanYes, Reporting Services install should create the ReportServer and
ReportServerTempDB databases, as well as the AdventureWorks2000 sample
database if you specified that feature to be installed. Be sure that you
have permissions to create the database. Also check the Reporting Services
install log, it may have more information about the error you are receiving.
Jonathan Kyle, MCSD
Microsoft WebData Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| Subject: Database 'ReportServer' does not exist
| Date: Thu, 16 Sep 2004 16:24:41 -0400
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 12.148.36.131
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.reportingsvcs:29431
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Shouldn't reporting services setup create ReportServer database
| automatically?
|
| The setup fails at the very end with this message:
|
| SQL Server failed to execute command for server configuration. The error
| was: Database 'ReportServer' does not exist. Check sysdatabases. ...
|
| Thanks,
|
| -Stan
|
|
||||I have seen this error if there is not default directory set for SQL server
to create databases in. You can check this via Enterprise manager go to the
properties on the server node.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Kyle [MSFT]" <jkyle@.online.microsoft.com> wrote in message
news:5Pan3rynEHA.4040@.cpmsftngxa06.phx.gbl...
> Yes, Reporting Services install should create the ReportServer and
> ReportServerTempDB databases, as well as the AdventureWorks2000 sample
> database if you specified that feature to be installed. Be sure that you
> have permissions to create the database. Also check the Reporting
Services
> install log, it may have more information about the error you are
receiving.
> Jonathan Kyle, MCSD
> Microsoft WebData Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> | From: "Stan" <nospam@.yahoo.com>
> | Subject: Database 'ReportServer' does not exist
> | Date: Thu, 16 Sep 2004 16:24:41 -0400
> | Lines: 13
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 12.148.36.131
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.reportingsvcs:29431
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Shouldn't reporting services setup create ReportServer database
> | automatically?
> |
> | The setup fails at the very end with this message:
> |
> | SQL Server failed to execute command for server configuration. The
error
> | was: Database 'ReportServer' does not exist. Check sysdatabases. ...
> |
> | Thanks,
> |
> | -Stan
> |
> |
> |
>|||Yes, I ran out of space on SQL Server C: driver and setup failed to create a
reporting database
Thanks!
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%233TUfQ3nEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have seen this error if there is not default directory set for SQL
server
> to create databases in. You can check this via Enterprise manager go to
the
> properties on the server node.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jonathan Kyle [MSFT]" <jkyle@.online.microsoft.com> wrote in message
> news:5Pan3rynEHA.4040@.cpmsftngxa06.phx.gbl...
> > Yes, Reporting Services install should create the ReportServer and
> > ReportServerTempDB databases, as well as the AdventureWorks2000 sample
> > database if you specified that feature to be installed. Be sure that
you
> > have permissions to create the database. Also check the Reporting
> Services
> > install log, it may have more information about the error you are
> receiving.
> >
> > Jonathan Kyle, MCSD
> > Microsoft WebData Support Engineer
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > --
> > | From: "Stan" <nospam@.yahoo.com>
> > | Subject: Database 'ReportServer' does not exist
> > | Date: Thu, 16 Sep 2004 16:24:41 -0400
> > | Lines: 13
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> > | Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
> > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > | NNTP-Posting-Host: 12.148.36.131
> > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> > | Xref: cpmsftngxa06.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:29431
> > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > |
> > | Shouldn't reporting services setup create ReportServer database
> > | automatically?
> > |
> > | The setup fails at the very end with this message:
> > |
> > | SQL Server failed to execute command for server configuration. The
> error
> > | was: Database 'ReportServer' does not exist. Check sysdatabases. ...
> > |
> > | Thanks,
> > |
> > | -Stan
> > |
> > |
> > |
> >
>
automatically?
The setup fails at the very end with this message:
SQL Server failed to execute command for server configuration. The error
was: Database 'ReportServer' does not exist. Check sysdatabases. ...
Thanks,
-StanYes, Reporting Services install should create the ReportServer and
ReportServerTempDB databases, as well as the AdventureWorks2000 sample
database if you specified that feature to be installed. Be sure that you
have permissions to create the database. Also check the Reporting Services
install log, it may have more information about the error you are receiving.
Jonathan Kyle, MCSD
Microsoft WebData Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| Subject: Database 'ReportServer' does not exist
| Date: Thu, 16 Sep 2004 16:24:41 -0400
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 12.148.36.131
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.reportingsvcs:29431
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Shouldn't reporting services setup create ReportServer database
| automatically?
|
| The setup fails at the very end with this message:
|
| SQL Server failed to execute command for server configuration. The error
| was: Database 'ReportServer' does not exist. Check sysdatabases. ...
|
| Thanks,
|
| -Stan
|
|
||||I have seen this error if there is not default directory set for SQL server
to create databases in. You can check this via Enterprise manager go to the
properties on the server node.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Kyle [MSFT]" <jkyle@.online.microsoft.com> wrote in message
news:5Pan3rynEHA.4040@.cpmsftngxa06.phx.gbl...
> Yes, Reporting Services install should create the ReportServer and
> ReportServerTempDB databases, as well as the AdventureWorks2000 sample
> database if you specified that feature to be installed. Be sure that you
> have permissions to create the database. Also check the Reporting
Services
> install log, it may have more information about the error you are
receiving.
> Jonathan Kyle, MCSD
> Microsoft WebData Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> | From: "Stan" <nospam@.yahoo.com>
> | Subject: Database 'ReportServer' does not exist
> | Date: Thu, 16 Sep 2004 16:24:41 -0400
> | Lines: 13
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 12.148.36.131
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.reportingsvcs:29431
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Shouldn't reporting services setup create ReportServer database
> | automatically?
> |
> | The setup fails at the very end with this message:
> |
> | SQL Server failed to execute command for server configuration. The
error
> | was: Database 'ReportServer' does not exist. Check sysdatabases. ...
> |
> | Thanks,
> |
> | -Stan
> |
> |
> |
>|||Yes, I ran out of space on SQL Server C: driver and setup failed to create a
reporting database
Thanks!
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%233TUfQ3nEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have seen this error if there is not default directory set for SQL
server
> to create databases in. You can check this via Enterprise manager go to
the
> properties on the server node.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jonathan Kyle [MSFT]" <jkyle@.online.microsoft.com> wrote in message
> news:5Pan3rynEHA.4040@.cpmsftngxa06.phx.gbl...
> > Yes, Reporting Services install should create the ReportServer and
> > ReportServerTempDB databases, as well as the AdventureWorks2000 sample
> > database if you specified that feature to be installed. Be sure that
you
> > have permissions to create the database. Also check the Reporting
> Services
> > install log, it may have more information about the error you are
> receiving.
> >
> > Jonathan Kyle, MCSD
> > Microsoft WebData Support Engineer
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > --
> > | From: "Stan" <nospam@.yahoo.com>
> > | Subject: Database 'ReportServer' does not exist
> > | Date: Thu, 16 Sep 2004 16:24:41 -0400
> > | Lines: 13
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> > | Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
> > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > | NNTP-Posting-Host: 12.148.36.131
> > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> > | Xref: cpmsftngxa06.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:29431
> > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > |
> > | Shouldn't reporting services setup create ReportServer database
> > | automatically?
> > |
> > | The setup fails at the very end with this message:
> > |
> > | SQL Server failed to execute command for server configuration. The
> error
> > | was: Database 'ReportServer' does not exist. Check sysdatabases. ...
> > |
> > | Thanks,
> > |
> > | -Stan
> > |
> > |
> > |
> >
>
Database reporting time differently than system
Hiya,
I have a database on a SQL Server 2000 (sp3a) installation. For some reason it's reporting time that is 7 hours ahead of the system time.
The application is on one server the DB is on a shared production server. The app server and the DB server are reporting the same system time and are using a network time server. All the other db's on the shared production db server are reporting time correctly.
My questions:
Is there a T-SQL query to use to see what the time/timezone is for that database?
Is there a T-SQL query I can use to set the db time (not the system time)?
Anyone have any other suggestions as to what could be wrong?
Thanks in advance for any help!
'cheleIs this from a select getdate() query in QA or the output of some app or stored proc? If app or stored proc, check for date manipulation. SQL Server picks up its time from the serer clock, there is no separate SQL Server clock.
Unless it is using the getutcdate(), which is also derived from the system date / time / time zone setting.
<QUOTE>
GETUTCDATE
Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.
Syntax
GETUTCDATE()
</QUOTE>|||Thanks for the help.
It's a canned app. Epolicy by McAfee, to be precise. I guess I will go to them for tech support now that I know it's probably not something I did.
I have a database on a SQL Server 2000 (sp3a) installation. For some reason it's reporting time that is 7 hours ahead of the system time.
The application is on one server the DB is on a shared production server. The app server and the DB server are reporting the same system time and are using a network time server. All the other db's on the shared production db server are reporting time correctly.
My questions:
Is there a T-SQL query to use to see what the time/timezone is for that database?
Is there a T-SQL query I can use to set the db time (not the system time)?
Anyone have any other suggestions as to what could be wrong?
Thanks in advance for any help!
'cheleIs this from a select getdate() query in QA or the output of some app or stored proc? If app or stored proc, check for date manipulation. SQL Server picks up its time from the serer clock, there is no separate SQL Server clock.
Unless it is using the getutcdate(), which is also derived from the system date / time / time zone setting.
<QUOTE>
GETUTCDATE
Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.
Syntax
GETUTCDATE()
</QUOTE>|||Thanks for the help.
It's a canned app. Epolicy by McAfee, to be precise. I guess I will go to them for tech support now that I know it's probably not something I did.
Saturday, February 25, 2012
Database Replication
we have setup a sql2005 server for reporting. we want to replicate the
transaction databases which used for live applications to that server such
that all reports will be generated in that server.
This reporting server is supposed read-only and with let's say 30 mins delay
from production data.
With this requirement, should we use the transactional replication or there
any method?
Thanks,
Ryan
Ryan,
transactional replication is often used for this type of reporting
requirement. You could also enhance the system by using the snapshot
committed isolation levels to maintain access while the distribution agent
is running. The main 'competitor' technology on SQL Server 2005 is database
mirroring with database snapshots. There's no detailed list of pros and
cons, but as I'm a replication guy I'll point out that mirroring doesn't
support FTI and you can't take back ups of snapshots
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul-
I have client who maintains a local SQL 2005 database which has
numerous bulk updates applied throughout the day. They wish to
replicate (most of) this data to their web host which is in another
city. They are trying to decide whether to use Transactional
Replication or once-per-day Merge Replication. (Concurrency is not a
big issue here).
What method would you recommend? What are the most important
considerations?
Thanks,
Paul
Paul Ibison wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Merge is generally slower. If there are numerous updates to the same row,
then it can approach transactional times, but I have rarely seen cases of
someone claiming it to be faster. It's geared up for offline updates at teh
subscriber and conflict resolution, neither of which you'll need. I'd
definitely go with transactional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks, Paul!
Does Transactional require an "always on" connection to the subscriber?
Paul Ibison wrote:
> Merge is generally slower. If there are numerous updates to the same row,
> then it can approach transactional times, but I have rarely seen cases of
> someone claiming it to be faster. It's geared up for offline updates at teh
> subscriber and conflict resolution, neither of which you'll need. I'd
> definitely go with transactional.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No - as long as there is a connection when the distribution agent is
scheduled to run you're ok (different for immediate updating subs but not
relevant in your case).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul, thanks a lot!
Do you know any limitation on using transactional replication? eg the min
delay time, can it perform query during the replication.
Aslo how's the overhead on resources of compare to mirroring? Is it require
many resources (eg. CPU and RAM) during the processing?
Regards,
Ryan
"Paul Ibison" wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Ryan,
I've been using transactional replication at my current employer for years
and latency will always be determined by geographic region and equipment.
In my case I'm seeing less than 5 second latency, usually lower than 2, and
yes, of course you can run queries on the data that is being replicated.
Resources are minimal once replication is setup, during the initial
snapshot, the only issues you might run into are the locking of the tables
as they are processed for replication.
Adam P. Cassidy
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:D4AA35E2-CBA7-4052-93AB-37DF213D729D@.microsoft.com...[vbcol=seagreen]
> Paul, thanks a lot!
> Do you know any limitation on using transactional replication? eg the min
> delay time, can it perform query during the replication.
> Aslo how's the overhead on resources of compare to mirroring? Is it
> require
> many resources (eg. CPU and RAM) during the processing?
> Regards,
> Ryan
>
> "Paul Ibison" wrote:
|||Ryan,
I agree with Adam, but just to clarify, if you mean queries applied to the
publisher then there' s no issue, but if the query is to the subscriber, you
might experience the normal blocking issues. The new snapshot isolation
level can be of use here. I have no stats regarding the performance
comparison between database mirroring and replication as yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Yes agreed. Sorry for not clarifying. We replicate for Cognos reporting
and since non of the reports are require a committed state, we have all the
queries executed against the replicated data as read uncommitted and there
are no problems - definitely a point I should have made.
Adam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OhCAQbWFHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Ryan,
> I agree with Adam, but just to clarify, if you mean queries applied to the
> publisher then there' s no issue, but if the query is to the subscriber,
> you might experience the normal blocking issues. The new snapshot
> isolation level can be of use here. I have no stats regarding the
> performance comparison between database mirroring and replication as yet.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
transaction databases which used for live applications to that server such
that all reports will be generated in that server.
This reporting server is supposed read-only and with let's say 30 mins delay
from production data.
With this requirement, should we use the transactional replication or there
any method?
Thanks,
Ryan
Ryan,
transactional replication is often used for this type of reporting
requirement. You could also enhance the system by using the snapshot
committed isolation levels to maintain access while the distribution agent
is running. The main 'competitor' technology on SQL Server 2005 is database
mirroring with database snapshots. There's no detailed list of pros and
cons, but as I'm a replication guy I'll point out that mirroring doesn't
support FTI and you can't take back ups of snapshots
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul-
I have client who maintains a local SQL 2005 database which has
numerous bulk updates applied throughout the day. They wish to
replicate (most of) this data to their web host which is in another
city. They are trying to decide whether to use Transactional
Replication or once-per-day Merge Replication. (Concurrency is not a
big issue here).
What method would you recommend? What are the most important
considerations?
Thanks,
Paul
Paul Ibison wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Merge is generally slower. If there are numerous updates to the same row,
then it can approach transactional times, but I have rarely seen cases of
someone claiming it to be faster. It's geared up for offline updates at teh
subscriber and conflict resolution, neither of which you'll need. I'd
definitely go with transactional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks, Paul!
Does Transactional require an "always on" connection to the subscriber?
Paul Ibison wrote:
> Merge is generally slower. If there are numerous updates to the same row,
> then it can approach transactional times, but I have rarely seen cases of
> someone claiming it to be faster. It's geared up for offline updates at teh
> subscriber and conflict resolution, neither of which you'll need. I'd
> definitely go with transactional.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No - as long as there is a connection when the distribution agent is
scheduled to run you're ok (different for immediate updating subs but not
relevant in your case).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul, thanks a lot!
Do you know any limitation on using transactional replication? eg the min
delay time, can it perform query during the replication.
Aslo how's the overhead on resources of compare to mirroring? Is it require
many resources (eg. CPU and RAM) during the processing?
Regards,
Ryan
"Paul Ibison" wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Ryan,
I've been using transactional replication at my current employer for years
and latency will always be determined by geographic region and equipment.
In my case I'm seeing less than 5 second latency, usually lower than 2, and
yes, of course you can run queries on the data that is being replicated.
Resources are minimal once replication is setup, during the initial
snapshot, the only issues you might run into are the locking of the tables
as they are processed for replication.
Adam P. Cassidy
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:D4AA35E2-CBA7-4052-93AB-37DF213D729D@.microsoft.com...[vbcol=seagreen]
> Paul, thanks a lot!
> Do you know any limitation on using transactional replication? eg the min
> delay time, can it perform query during the replication.
> Aslo how's the overhead on resources of compare to mirroring? Is it
> require
> many resources (eg. CPU and RAM) during the processing?
> Regards,
> Ryan
>
> "Paul Ibison" wrote:
|||Ryan,
I agree with Adam, but just to clarify, if you mean queries applied to the
publisher then there' s no issue, but if the query is to the subscriber, you
might experience the normal blocking issues. The new snapshot isolation
level can be of use here. I have no stats regarding the performance
comparison between database mirroring and replication as yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Yes agreed. Sorry for not clarifying. We replicate for Cognos reporting
and since non of the reports are require a committed state, we have all the
queries executed against the replicated data as read uncommitted and there
are no problems - definitely a point I should have made.
Adam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OhCAQbWFHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Ryan,
> I agree with Adam, but just to clarify, if you mean queries applied to the
> publisher then there' s no issue, but if the query is to the subscriber,
> you might experience the normal blocking issues. The new snapshot
> isolation level can be of use here. I have no stats regarding the
> performance comparison between database mirroring and replication as yet.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
Friday, February 17, 2012
Database Performance Halts
Hi,
I have been running a reporting App on an SQL 2000 server, which reads from one large table (roughly 80 million records which grows at around 2 million records a week).
However, it would seem that when multi-users try to access the App and the one large table is Read from using 1 database user name up to approximately 25 times, the system would slow to a halt. Essentially, each request on the App would be a new connection to the SQL database using the same database user name. Recently, we have been running into performance issues since we have increased the number of users for the App.
What would be causing this slow down? and what could solve this problem?
>>What would be causing this slow down? and what could solve this problem?
Do you have indexes on the table? are the queries sargable? are your statistics up to date?
>>and the one large table is Read
What does this mean how is the table read? Are you just doing a select * from table?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||>>Do you have indexes on the table? are the queries sargable? are your statistics up to date?
I have indexes on the table and seems to work perfectly fine when there is only 1 query running. All the queries I am running are based on atleast 1 index on that table. Also which statistics are you referring to?
>>What does this mean how is the table read? Are you just doing a select * from table?
From what I can tell, it should only be a series of select statements or nested select statements that the report App runs and stores on the report App side. The reporting App is making from 10-30 of these requests on that 1 indexed table at a single moment in time.
Subscribe to:
Posts (Atom)