Post reply

Identical Databases not Performing Identically?

  • September 2, 2005 at 3:58 am

    #187255

    Hi guys,

    Sorry about the strange title - wasn't quite sure how to field this one!  Here's a wee bit of background about my problem.

    We have three servers in this sorry story, a test server (that I'll called "Test"), a staging server (let's call him "staging") and the live cluster server (who can go by the name of "live").  Live acts as a back end for a web app that allows customers to request the download of pdf files.  The customer fills some information into a web form, it is entered into the database and the then have the PDF opened.

    Here's where things get wierd. 

    Originally, the database for this was on test.  When the web page was instructed to connect to this server it worked fine and dandy.  The database was then restored to staging and, again, it worked fine.  A backup of the database from test (the same one) was then restored to the live server and the app wouldn't work.  It would write the data to the database, but it would simply wrap the user back to the web form rather than opening a PDF.  Assuming that this was simply a case of data corruption we restored a copy of the database (the same one from test) to staging.  We then pointed the web app at this database and we got the same issue.  We have tried this both with backups of the test database and the working one from staging.

    We have run Red Gate's SQL compare against the databases and we can't find any differences.

    Does anyone have any advice they can offer?

    All databases are running MS SQL Server 2000 with SP3a.

    TIA!

    Iain

  • September 2, 2005 at 4:33 am

    #587131

    Why do you think this is a database problem?

    Does the SQL Server generate a PDF?

    Is the customer data being written into the live db? Such as tighter security on live?

    Same front-end?

  • September 2, 2005 at 4:40 am

    #587132

    The only reason we believe that it is the database causing the issue is that the db that the front end is pointed at is the only thing that changes.  All we are doing is changing which database the connection string refers to.

  • September 2, 2005 at 5:07 am

    #587135

    Live server permissions then?

  • September 2, 2005 at 5:15 am

    #587139

    Well, we've tried with two databases (identical according to SQL compare) on staging and one is fine but the other won't work (the restored one).  Would server permissions come in here?

  • September 2, 2005 at 5:19 am

    #587140

    Probably, if you using the sp_OA,,, procs for example or xp_cmdshell

  • September 2, 2005 at 5:25 am

    #587142

    But if the two databases are on the same server, surely server persmissions don't apply?

  • September 2, 2005 at 5:36 am

    #587144

    The error will NOT be in the database.

    Databases don't generate PDFs!

    Something else does that needs a trigger or check to start the PDF process.

    If you have 2 databases on the same server, they cannot be identical because the name is different.

    What if you have some hardcoded dbname or servername somewhere, that is causing connection to different servers and dbs? Did someone forget to update something when promoted to live?

    This is not a database problem, nor a permissions assuming you have permissions for roles and your front end app ha sa login mapped to a user in this role.

    It is server permissions or app...

  • September 2, 2005 at 5:39 am

    #587145

    Thanks - I'll take a look at this and get back to you.

  • September 5, 2005 at 6:34 am

    #587569

    As Shawn said...

    What are you using to generate the PDFs?

    Are some of your server defaults set differently?  (SET options)

    Are any other parts of the DB working?  Eg searching, etc - is just the PDF part breaking?  If all parts of the DB are broken, then maybe you have something else wrong with your server, like name resolution to it not working, your client libraries just wanting TCP but the server wants to use named pipes, etc.  If just the PDF part is broken, check for whether you have your PDF library installed, if it is installed in the same location, any environment variables set on the machine, ensure the registry entries required for the software are present, etc, etc....

    Run a profiler trace when you attempt to generate the PDF - have it capture all sorts of errors, etc - see if anything is amiss.  Compare the profiler output to that of the test / staging servers...

    Good luck!

  • September 5, 2005 at 4:23 pm

    #587633

    Could possibly be database permissions.  These aren't as portable as some people assume, when you restore a database from one server to another.  You may have similarly named users / roles, but with different uid at the different servers.

    Do you have different users / roles controlling the different actions (the working vs.  the failing) at the database?

     

  • September 5, 2005 at 5:18 pm

    #587637

    Yes good point.

    Try

    sp_change_users_login 'auto_fix', USERNAME

    where USERNAME is the name of the login/user you are using.  Does the login you use (SQL or NT) have access to the production server?

  • September 6, 2005 at 4:54 am

    #587719

    I'd actually recommend against using 'auto_fix' - ever..

    Use the 'REPORT' option instead, and after eventual results, use 'UPDATE_ONE' for each login you need to sync.

    'Auto_Fix' can have some side effects, making unintended changes that can be easily missed if not carefully looked for after it's been used. Better then (imo of course ) to not use 'auto' at all. Just list first and then change explicitly.

    ..just my .02

    /Kenneth

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply