Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The company I'm working for has a SQL Server with read-only access. They use Crystal Reports hooked up to PostgreSQL for reporting. Is there any way to make it so I can move all the data from the MSSQL DB to PostgreSQL without user interaction? That seems to be the caveat to what I'm trying to do. They need to be able to run this report after I leave without having to interact with it during the process.

Or am I looking at this the wrong way? Is there a way to save a "snapshot" of the SQL Server DB that can be manipulated in Crystal Reports? The ultimate goal is that since the DB is dynamic we need to be able to have a static DB at the end of the month that all the reports can be ran on without having to worry about it changing.

share|improve this question
    
How do you do it now with user interaction? –  Aaron Bertrand Jul 24 '13 at 15:11
    
They export it as a .csv and then go through and fix all the errors. Namely conversion errors, a couple '' NULL errors, and I guess something about dates that Postgre doesn't like. –  Vap0r Jul 24 '13 at 15:17
2  
Any chance they can be convinced to connect Cyrstal Reports to SQL Server? What is the benefit of dealing with this translation all the time just to run reporting from PostgreSQL? –  Aaron Bertrand Jul 24 '13 at 15:21
    
See my response to David below. –  Vap0r Jul 24 '13 at 18:51
add comment

3 Answers

up vote 2 down vote accepted

Can you not just hook Crystal Reports to an instance of MS SQL Server? That way the process should be as simple as "backup the live database, restore it into test/snapshot location when needed".

If the database is less than 10Gb in size you can use SQL Express so there would be no licensing implications, if it is 10Gb or more than you could restore it on the same SQL Server under a different name assuming you have access to be able to do that. Otherwise you might end up paying for an instance of SQL Server (avoiding this may be why you are using postgres in the first place).

If you have a specific process that you are currently following manually which you would like to automate, then you need to specify that process in detail in your question otherwise we can only guess and that may not be helpful.

share|improve this answer
    
You nailed it right on the head, it's bigger than 10GB in size, we only have the read-only as I said, and I know we don't have the resources for that. I was speaking to my supervisor and he said moving the entire DB would be too much. And I guess the reason he doesn't connect it to MS SQL Server is because he would lose all his reports when he connected to it. –  Vap0r Jul 24 '13 at 16:41
    
I still don't understand why you don't just clone the database to a new differently named database on the same server. That'd be the simplest option. –  Craig Ringer Jul 25 '13 at 1:09
    
@CraigRinger: I get the impression that they don't own the SQL server, but have just been given access to the DB for reporting. Could also be that the production environment is tightly controlled (as production environments should be) so adding extra DBs is very much against policy as is adding the general back-end reporting load. –  David Spillett Jul 25 '13 at 9:52
    
@CraigRinger, exactly what David said. We don't really own the sql server, and have limited access as to what we can install, and an entirely new server installation is out of the question. I marked David's answer as correct since it would be the way to do it. –  Vap0r Jul 26 '13 at 14:30
add comment

This sort of thing is possible, but painful, with ETL tools like Talend Studio, Pentaho Kettle, or CloverETL.

Alternately, you could dump each table to CSV then COPY it into PostgreSQL, after manually converting the DDL.

Either way, DDL changes will be painful to keep in sync. You'll be way better off just using another MS SQL Server DB for your reporting server if you can afford it. Compare it to the cost of developing and maintaining the data sync you need.

This would be true for any pair of DBs, it's not just PostgreSQL or just MS SQL Server. If you were asking about dumping PostgreSQL to a reporting MySQL instance (not that I can imagine why you'd want to do that, but anyway...) I'd be answering much the same way: don't.

Use a second SQL Server instance or a second database on the same SQL Server instance. Back the DB up and then restore the backup to the other DB to provide you with a static copy for reporting.

share|improve this answer
add comment

Is there a way to save a "snapshot" of the SQL Server DB that can be manipulated in Crystal Reports? The ultimate goal is that since the DB is dynamic we need to be able to have a static DB at the end of the month that all the reports can be ran on without having to worry about it changing.

My first thought is that you could use a Foreign data wrapper to query the MS SQL Server directly from Postgres. On the other hand when I read this last part of your question, I want to say that you need to start exploring data warehouse models for your reporting database.

Slowly changing dimensions are designed to capture exactly the information you are talking about (with "snapshots"). You will need to use some sort of extract, transform, and load (ETL) tool, to load these dimensions. It's not a quick fix, but I think it will give you a more reliable platform for your reporting.

Here are a few books that you can check out, for more information:

  • Matt Casters & Roland Bouman & Jos van Dongen. Pentaho Kettle Solutions
  • Christopher Adamson. Star Schema: The Complete Reference
  • Ralph Kimball & Margy Ross. The Data Warehouse Toolkit
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.