Creation of reporting database

We have a production database, but due to the crippling load of some
reporting tools, we are looking to have a separate reporting database.
Due to it's nature, the reporting database doesn't need to be as up
to date as the master, perhaps it could be refreshed every evening?
What I wish to know is the best way to go about this. Replicating a
whole database seems excessive as there are 7000+ tables and we don't
need it so closely matching the production database. Log shipping
also seems out as we need the reporting database to be highly
available and if we keep applying transaction logs ever 15 minutes or
so, we need to kick all the users out.
Is there a way of scripting or automatically getting SQL Server 2k to
copy over the last full backup it did and apply this to another
server? Also if we used this method, what would happen to the users
stored in the database? Would I have to keep re-adding them after the
upgrade?
Is there a better way to tackle this problem? All help gratefully
received. We are running SQL Server 2000 Enterprise Edition on a
Windows 2000 Server machine.
Thanks.Jonathan
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546#9 -- Move
Databases between computers running by sql server
"Jonathan" <nielsonj1976@.yahoo.co.uk> wrote in message
news:9db42c2b.0310200347.50044387@.posting.google.com...
> We have a production database, but due to the crippling load of some
> reporting tools, we are looking to have a separate reporting database.
> Due to it's nature, the reporting database doesn't need to be as up
> to date as the master, perhaps it could be refreshed every evening?
> What I wish to know is the best way to go about this. Replicating a
> whole database seems excessive as there are 7000+ tables and we don't
> need it so closely matching the production database. Log shipping
> also seems out as we need the reporting database to be highly
> available and if we keep applying transaction logs ever 15 minutes or
> so, we need to kick all the users out.
> Is there a way of scripting or automatically getting SQL Server 2k to
> copy over the last full backup it did and apply this to another
> server? Also if we used this method, what would happen to the users
> stored in the database? Would I have to keep re-adding them after the
> upgrade?
> Is there a better way to tackle this problem? All help gratefully
> received. We are running SQL Server 2000 Enterprise Edition on a
> Windows 2000 Server machine.
> Thanks.|||Personally I would
1. Identify the tables that you need to report on.
2. Take them over to the reporting server
3. Create triggers on your Source tables and have them capture the data
that is entered, updated or deleted in those tables to a seperate table.
4. Build a DTS package that is fired by the SQL Server Agent every night
that will pick up those changes and migrate them to your reporting server.
This means that you are only taking across the things you need to. Using
the backup and restore method is easy but you will need to possibly unorphan
logins and also even if nothing changes during the day you are still going
to take across the whole database whereas in the other method you wouldn't.
Just my £0.02
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jonathan" <nielsonj1976@.yahoo.co.uk> wrote in message
news:9db42c2b.0310200347.50044387@.posting.google.com...
> We have a production database, but due to the crippling load of some
> reporting tools, we are looking to have a separate reporting database.
> Due to it's nature, the reporting database doesn't need to be as up
> to date as the master, perhaps it could be refreshed every evening?
> What I wish to know is the best way to go about this. Replicating a
> whole database seems excessive as there are 7000+ tables and we don't
> need it so closely matching the production database. Log shipping
> also seems out as we need the reporting database to be highly
> available and if we keep applying transaction logs ever 15 minutes or
> so, we need to kick all the users out.
> Is there a way of scripting or automatically getting SQL Server 2k to
> copy over the last full backup it did and apply this to another
> server? Also if we used this method, what would happen to the users
> stored in the database? Would I have to keep re-adding them after the
> upgrade?
> Is there a better way to tackle this problem? All help gratefully
> received. We are running SQL Server 2000 Enterprise Edition on a
> Windows 2000 Server machine.
> Thanks.|||Jonathan (nielsonj1976@.yahoo.co.uk) writes:
> We have a production database, but due to the crippling load of some
> reporting tools, we are looking to have a separate reporting database.
> Due to it's nature, the reporting database doesn't need to be as up
> to date as the master, perhaps it could be refreshed every evening?
> What I wish to know is the best way to go about this. Replicating a
> whole database seems excessive as there are 7000+ tables and we don't
> need it so closely matching the production database. Log shipping
> also seems out as we need the reporting database to be highly
> available and if we keep applying transaction logs ever 15 minutes or
> so, we need to kick all the users out.
If you only need a daily refresh, there is no reason to apply logs
every 15 minutes. Once a day would do.
Of course with that low frequency, moving the entire database may
be a more palatable task. As Allan pointed out, you need to fix
users and login each time then, whereas with log shipping you would
only need to cater for new users.
> Is there a way of scripting or automatically getting SQL Server 2k to
> copy over the last full backup it did and apply this to another
> server?
Yes, you use the RESTORE and BACKUP commands. Read about them in Books
Online. And then you need to use some COPY command in the command-line
world to copy the backup file as it is not adviceable to backup or
restore from a network device. All this can be handled in a job from
SQL Agent.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Creation of reporting database

We have a production database, but due to the crippling load of some
reporting tools, we are looking to have a separate reporting database.
Due to it's nature, the reporting database doesn't need to be as up
to date as the master, perhaps it could be refreshed every evening?

What I wish to know is the best way to go about this. Replicating a
whole database seems excessive as there are 7000+ tables and we don't
need it so closely matching the production database. Log shipping
also seems out as we need the reporting database to be highly
available and if we keep applying transaction logs ever 15 minutes or
so, we need to kick all the users out.

Is there a way of scripting or automatically getting SQL Server 2k to
copy over the last full backup it did and apply this to another
server? Also if we used this method, what would happen to the users
stored in the database? Would I have to keep re-adding them after the
upgrade?

Is there a better way to tackle this problem? All help gratefully
received. We are running SQL Server 2000 Enterprise Edition on a
Windows 2000 Server machine.

Thanks.Jonathan

http://support.microsoft.com/defaul...en-us;Q314546#9 -- Move
Databases between computers running by sql server

"Jonathan" <nielsonj1976@.yahoo.co.uk> wrote in message
news:9db42c2b.0310200347.50044387@.posting.google.c om...
> We have a production database, but due to the crippling load of some
> reporting tools, we are looking to have a separate reporting database.
> Due to it's nature, the reporting database doesn't need to be as up
> to date as the master, perhaps it could be refreshed every evening?
> What I wish to know is the best way to go about this. Replicating a
> whole database seems excessive as there are 7000+ tables and we don't
> need it so closely matching the production database. Log shipping
> also seems out as we need the reporting database to be highly
> available and if we keep applying transaction logs ever 15 minutes or
> so, we need to kick all the users out.
> Is there a way of scripting or automatically getting SQL Server 2k to
> copy over the last full backup it did and apply this to another
> server? Also if we used this method, what would happen to the users
> stored in the database? Would I have to keep re-adding them after the
> upgrade?
> Is there a better way to tackle this problem? All help gratefully
> received. We are running SQL Server 2000 Enterprise Edition on a
> Windows 2000 Server machine.
> Thanks.|||Personally I would

1. Identify the tables that you need to report on.
2. Take them over to the reporting server
3. Create triggers on your Source tables and have them capture the data
that is entered, updated or deleted in those tables to a seperate table.
4. Build a DTS package that is fired by the SQL Server Agent every night
that will pick up those changes and migrate them to your reporting server.

This means that you are only taking across the things you need to. Using
the backup and restore method is easy but you will need to possibly unorphan
logins and also even if nothing changes during the day you are still going
to take across the whole database whereas in the other method you wouldn't.

Just my 0.02

--

---------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Jonathan" <nielsonj1976@.yahoo.co.uk> wrote in message
news:9db42c2b.0310200347.50044387@.posting.google.c om...
> We have a production database, but due to the crippling load of some
> reporting tools, we are looking to have a separate reporting database.
> Due to it's nature, the reporting database doesn't need to be as up
> to date as the master, perhaps it could be refreshed every evening?
> What I wish to know is the best way to go about this. Replicating a
> whole database seems excessive as there are 7000+ tables and we don't
> need it so closely matching the production database. Log shipping
> also seems out as we need the reporting database to be highly
> available and if we keep applying transaction logs ever 15 minutes or
> so, we need to kick all the users out.
> Is there a way of scripting or automatically getting SQL Server 2k to
> copy over the last full backup it did and apply this to another
> server? Also if we used this method, what would happen to the users
> stored in the database? Would I have to keep re-adding them after the
> upgrade?
> Is there a better way to tackle this problem? All help gratefully
> received. We are running SQL Server 2000 Enterprise Edition on a
> Windows 2000 Server machine.
> Thanks.|||Jonathan (nielsonj1976@.yahoo.co.uk) writes:
> We have a production database, but due to the crippling load of some
> reporting tools, we are looking to have a separate reporting database.
> Due to it's nature, the reporting database doesn't need to be as up
> to date as the master, perhaps it could be refreshed every evening?
> What I wish to know is the best way to go about this. Replicating a
> whole database seems excessive as there are 7000+ tables and we don't
> need it so closely matching the production database. Log shipping
> also seems out as we need the reporting database to be highly
> available and if we keep applying transaction logs ever 15 minutes or
> so, we need to kick all the users out.

If you only need a daily refresh, there is no reason to apply logs
every 15 minutes. Once a day would do.

Of course with that low frequency, moving the entire database may
be a more palatable task. As Allan pointed out, you need to fix
users and login each time then, whereas with log shipping you would
only need to cater for new users.

> Is there a way of scripting or automatically getting SQL Server 2k to
> copy over the last full backup it did and apply this to another
> server?

Yes, you use the RESTORE and BACKUP commands. Read about them in Books
Online. And then you need to use some COPY command in the command-line
world to copy the backup file as it is not adviceable to backup or
restore from a network device. All this can be handled in a job from
SQL Agent.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating SQL Server 2005 Mobile Edition On Desktop

Hello Everybody

I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application.

Anybody having this solution for this prob.

Regards

Chikuu

Have you had any chance to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

My problem is solved using this link. I had copied those dll's into bin folder it woks fine on any windows desktop running VS2005.

Thanks

Chikuu

|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

We are trying to install sdf creation code on windows server 2000, but it is giving message that ' sql mobile usage is restricted on this platform to use sql mobile you need dot net 2005,sql server 2005 or Tablet PC sku' i got this message eralier when i am installing it on windows server 2003 that time i had installed sql mobile sdk and then this message is not shown. Is there another solution for windows sever 2000, specifically in destributing these application.

Thanks

Chekku

|||

Chekku,

You really do need to install VS2005 or SS2005 on your Windows 2000 server to use SQL Mobile. It's part of the licensing.

-Darren

|||Hopefully the problems will be sorted with SQL Everywhere. You CAN get around the problem and still stay within Microsoft licencing terms without having to have Visual Studio, etc:

1) Download SQL Express from Microsoft (free download)

2) Extract to a folder using the /x option

3) In the 'Setup' folder there is a file called 'SqlSupport.msi'. Install this on your target PC (only a 9MB file so no big deal).

4) Also on your target PC, deploy the following files along with your app:

sqlceca30.dll

The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.

sqlcecompact30.dll

Provides the compact database functionality. Required if your application will use compaction.

sqlceer30[language].dll

Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.

sqlceme30.dll

Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.

sqlceoledb30.dll

Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.

sqlceqp30.dll

The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.

sqlcese30.dll

The SQL Server Mobile Engine. Required for all SQL Server Mobile applications.

System.Data.SqlServerCe.dll

Seems bizarre how a free download keeps the licence happy !

Creating SQL Server 2005 Mobile Edition On Desktop

Hello Everybody

I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application.

Anybody having this solution for this prob.

Regards

Chikuu

Have you had any chance to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

My problem is solved using this link. I had copied those dll's into bin folder it woks fine on any windows desktop running VS2005.

Thanks

Chikuu

|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

We are trying to install sdf creation code on windows server 2000, but it is giving message that ' sql mobile usage is restricted on this platform to use sql mobile you need dot net 2005,sql server 2005 or Tablet PC sku' i got this message eralier when i am installing it on windows server 2003 that time i had installed sql mobile sdk and then this message is not shown. Is there another solution for windows sever 2000, specifically in destributing these application.

Thanks

Chekku

|||

Chekku,

You really do need to install VS2005 or SS2005 on your Windows 2000 server to use SQL Mobile. It's part of the licensing.

-Darren

|||Hopefully the problems will be sorted with SQL Everywhere. You CAN get around the problem and still stay within Microsoft licencing terms without having to have Visual Studio, etc:

1) Download SQL Express from Microsoft (free download)

2) Extract to a folder using the /x option

3) In the 'Setup' folder there is a file called 'SqlSupport.msi'. Install this on your target PC (only a 9MB file so no big deal).

4) Also on your target PC, deploy the following files along with your app:

sqlceca30.dll

The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.

sqlcecompact30.dll

Provides the compact database functionality. Required if your application will use compaction.

sqlceer30[language].dll

Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.

sqlceme30.dll

Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.

sqlceoledb30.dll

Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.

sqlceqp30.dll

The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.

sqlcese30.dll

The SQL Server Mobile Engine. Required for all SQL Server Mobile applications.

System.Data.SqlServerCe.dll

Seems bizarre how a free download keeps the licence happy !

Creating SQL Server 2005 Mobile Edition On Desktop

Hello Everybody

I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application.

Anybody having this solution for this prob.

Regards

Chikuu

Have you had any chance to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

My problem is solved using this link. I had copied those dll's into bin folder it woks fine on any windows desktop running VS2005.

Thanks

Chikuu

|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

We are trying to install sdf creation code on windows server 2000, but it is giving message that ' sql mobile usage is restricted on this platform to use sql mobile you need dot net 2005,sql server 2005 or Tablet PC sku' i got this message eralier when i am installing it on windows server 2003 that time i had installed sql mobile sdk and then this message is not shown. Is there another solution for windows sever 2000, specifically in destributing these application.

Thanks

Chekku

|||

Chekku,

You really do need to install VS2005 or SS2005 on your Windows 2000 server to use SQL Mobile. It's part of the licensing.

-Darren

|||Hopefully the problems will be sorted with SQL Everywhere. You CAN get around the problem and still stay within Microsoft licencing terms without having to have Visual Studio, etc:

1) Download SQL Express from Microsoft (free download)

2) Extract to a folder using the /x option

3) In the 'Setup' folder there is a file called 'SqlSupport.msi'. Install this on your target PC (only a 9MB file so no big deal).

4) Also on your target PC, deploy the following files along with your app:

sqlceca30.dll

The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.

sqlcecompact30.dll

Provides the compact database functionality. Required if your application will use compaction.

sqlceer30[language].dll

Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.

sqlceme30.dll

Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.

sqlceoledb30.dll

Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.

sqlceqp30.dll

The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.

sqlcese30.dll

The SQL Server Mobile Engine. Required for all SQL Server Mobile applications.

System.Data.SqlServerCe.dll

Seems bizarre how a free download keeps the licence happy !

Creating SQL Server 2005 Mobile Edition On Desktop

Hello Everybody

I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application.

Anybody having this solution for this prob.

Regards

Chikuu

Have you had any chance to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

My problem is solved using this link. I had copied those dll's into bin folder it woks fine on any windows desktop running VS2005.

Thanks

Chikuu

|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

We are trying to install sdf creation code on windows server 2000, but it is giving message that ' sql mobile usage is restricted on this platform to use sql mobile you need dot net 2005,sql server 2005 or Tablet PC sku' i got this message eralier when i am installing it on windows server 2003 that time i had installed sql mobile sdk and then this message is not shown. Is there another solution for windows sever 2000, specifically in destributing these application.

Thanks

Chekku

|||

Chekku,

You really do need to install VS2005 or SS2005 on your Windows 2000 server to use SQL Mobile. It's part of the licensing.

-Darren

|||Hopefully the problems will be sorted with SQL Everywhere. You CAN get around the problem and still stay within Microsoft licencing terms without having to have Visual Studio, etc:

1) Download SQL Express from Microsoft (free download)

2) Extract to a folder using the /x option

3) In the 'Setup' folder there is a file called 'SqlSupport.msi'. Install this on your target PC (only a 9MB file so no big deal).

4) Also on your target PC, deploy the following files along with your app:

sqlceca30.dll

The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.

sqlcecompact30.dll

Provides the compact database functionality. Required if your application will use compaction.

sqlceer30[language].dll

Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.

sqlceme30.dll

Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.

sqlceoledb30.dll

Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.

sqlceqp30.dll

The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.

sqlcese30.dll

The SQL Server Mobile Engine. Required for all SQL Server Mobile applications.

System.Data.SqlServerCe.dll

Seems bizarre how a free download keeps the licence happy !