| Author |
Topic |
|
sidjames
Starting Member
11 Posts |
 Posted - 2004-06-10 : 03:00:49
|
| Hi,We have a problem with an sql server installation that has 2 different Db applications running on it. We believe the sql server configuration was changes when the second application installed. The changes have affected the 1st application (doesn't work properly). I have installed the 1st application on my test sql server (working fine), and would like to compare the 2 sql server configurations to see if there are any differneces.Question:Is it possible to export the server settings (configuration) from sql server as an ascii file. That way I can compare the 2 installations.Would greatly appreciate any help.RegsSid |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-10 : 13:16:06
|
| Which server settings are you referring to? sp_configure?Tara |
 |
|
|
sidjames
Starting Member
11 Posts |
Posted - 2004-06-11 : 01:09:14
|
| I have run sp_configure, but are there any other configuration parameters that could affect the 1 of the applications. Isn't there anyway to export a config file that can be used to create a duplicate server configuration, recovery or duplicate test system for example. I know about iss, but that file has to be created at install time.CheersSid |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-11 : 01:30:40
|
| There isn't any file like IIS has. Any server-wide changes though would have been caught in the event log for SQL Server. Have you looked through there on the day the second application was installed?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
sidjames
Starting Member
11 Posts |
Posted - 2004-06-15 : 00:54:27
|
| Hi,Unfortunatly, the 2nd App was installed in Jan so there is no log now. sp_configure output on the production server with the 2 apps and a test server with opnly the 1st app are the same. Are there any other config parameters that I can compare?regsSid |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-15 : 01:31:24
|
| What sort of problems are you getting with 1st application? That might help to narrow down what 2nd application could have done to interfere.What version of SQL server?Kristen |
|
|
|
sidjames
Starting Member
11 Posts |
Posted - 2004-06-15 : 07:05:03
|
| Sql Server 2000 SP3The 1st app ArcView (GIS), when it retrieves data from the Db gets multiple copies of the same record. This causes problems when it tries to display the data graphically.I've run the queries the GIS app uses to retrieve the data manually with no problem, same data set returned on test as on production.cheersSid |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-15 : 08:25:09
|
| Blimey!When you say you've run the same querries, I presume you got them from PROFILER? Anything "fancy" in the querries? Any chance that the application is running them as a different User to your test-rig?Any chance that there is some corruption in the production database?Other than that it sounds like an application error to me, but I can't fathom how. Maybe the SProcs etc. got created with different defaults on TEST and PRODUCTION? (Things like SET ANSI-STUFF which are taken from the state when the SProc is created, rather than run-time state). Might be worth scripting the whole database on both environments and seeing if they are the same - scripting will put SET ANSI-WHATNOT ON and SET QUOTED-IDENTIFER-STUFF ON/OFF in the script, so be sure to watch out for differences in those, seemingly benign!, critters when comparing.Beyond those few modest thoughts its outside my knowledge, I think you're going to need the clever-crew to answer this one, hopefully they'll be along in a minute.Kristen |
|
|
|
sidjames
Starting Member
11 Posts |
Posted - 2004-06-16 : 01:30:17
|
| I have scripted the test and production Db'd - No difference in the dboption settings.A little more info on how the 1st app works.It has a Frontend Access Db that stores (in text) the queries for the Backend Sql Server. The queries "select * from <a view>" returning the coordinates for a closed area (minefield) to be displayed on a map in the app. (ArcView)´Since I have no problem running the queries directly on the Sql Server (I get the same data set from both the Db'd) I can only assume it must be something between the app and the front end access Db or between the access Db and Sql Server. It uses ODBC to connect, is there something there that could screw up the records that are returned. dll missmatch or something. Unfortunatly as you well know MS (big bill) doesn't let us read the transaction log so I can't see what the Sql Server is executing and returning to the app, I could rule out the Sql Server if I could see that it was returning the correct record set. I don't have any tool to read the logs (expensive) and I don't have access to a good download site to get a "test" version, the UN block all the good sites.Any suggestions on how to track down this problem would be appreciated.CheersSid |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-16 : 02:11:00
|
| I'll throw out some ideas, but this sounds like a real puzzle. You might see if the second application modified something in master or model.select * from master..sysobjects where crdate >= '<date 2nd app was installed>'select * from model..sysobjects where crdate >= '<date 2nd app was installed>'When you run the queries interactively, are you also SETTING all the options in effect by the client as captured by PROFILER for the connection? Are you sure the application has not changed something on the client as opposed to the server? (I.e. Regional settings, date format etc.) Check those settings on the server too...Did you reboot the server after installing the 2nd app? Is it possible a DLL had been modified by something else but it did not get loaded until reboot? Maybe search for modified DLL's on the server since the last reboot.Look at sp_dboption for the model db, since that will be the template used for tempdb when the server is booted. It would impact all applications after a restart.Character set and sort order (see sp_helpsort) are settings that would possibly result in multiple rows where there were single rows before.Have you installed the 2nd app on your test server and seen the same errors in the first app?Are you sure the access db has not been corrupted? You might try rebuilding the indexes for your db. I have seen corrupt indexes cause problems only when a threshold of rows in the resultset was reached.Good luck! |
|
|
|
sidjames
Starting Member
11 Posts |
Posted - 2004-06-16 : 02:53:45
|
| Before I try your suggestions,I have now run profiler on the 2 servers and started the application. I see the sql it runs - both the same on the 2 servers.The number of reads on the production server which has the 2 apps installed and we have problems with the 1st app = 29972On the test server which has only the 1st app installed the number of reads = 8728So the situation is like this.If I run the sql manually from sql analyser I get the same number of records back.The app (arcview) gets a different result from the prod and test servers.What the *&¤#"!? can be happening.Now I'll try your suggestions.The same Access Db Front end is used against both Sql Servers.CheersSid |
|
|
|
sidjames
Starting Member
11 Posts |
Posted - 2004-06-16 : 03:48:12
|
| This is probably a silly question but I need to have it cleared up.Our production m/c is a dell power edge with 2 processors.Our Test m/c is a dell desk top with one processor.Can the number of processors cause this problem we are having. Is there any config settings for ther HW that could affect the Sql Server.I've now reindexed the Db, doesn't fix for the problem.CheersSid |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-16 : 04:35:07
|
quote:
Originally posted by sidjames
I have scripted the test and production Db'd - No difference in the dboption settings.
Just to make sure I haven't misunderstood.w/r to "scripting" I meant to do a "generate SQL script" on both DBs and do a text compare of the two, rather than just compare the output from dbopions.If you are not familiar with this:Enterprise ManagerRight-click databaseAll tasks : Generate SQL Script[general] - Click SHOW ALLthen click "Script all objects"[formatting] click CREATE/DROP and also "Include extended properties"[options] - click everything in Security and Table options section - with the exception of "Script database"Probably best to choose File Format = "Windows text"Make sure "Files to generate" = ONEPress OK and save to suitable file, then do the same on the other server, then compare the two text filesKristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-16 : 05:25:15
|
| I don't think your 2 CPUs will make a difference, but it does cause some "parallel processing" which caused one of our applciations to fall over a SQL7 bug in the past.You can disable "affinity" to specific processors in Enterprise Manager, but I don't think SQL Server would behave differently between Application and Query Analyser in this regard.Kristen |
|
|
|
sidjames
Starting Member
11 Posts |
Posted - 2004-06-16 : 08:43:35
|
| YeeeeeeHaaaaaa - found the problem, I didn't think the 2 processors would cause the problem but they do.In enterprise manager I changed the parallelism to "one" processor and it fixed the problem.Ok now I know what is causing the problem, problem now is that I have removed one processor from running queries. The response time is noticably longer,Any ideas how I can fix the problem and use both processors again. This is an access 2 Sql Server interface problem isn't it, are there a known bugs in this area? I've search around a bit but no hits as yet.Any leads would be appreciated.Sid (a very happy chappie just now) (earned a few extra brownie points today) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-16 : 10:05:22
|
| Could be the version of ODBC I suppose, but I imagine you've got latest version.Tell your boss you need a 4 CPU Xenon for a DEV machine to solve the problem :)As I said earlier I did have a paralellism problem on SQL7, and I found the details of that in TechNet or something on msdn.microsoft.comKristen |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-16 : 10:09:48
|
| Great!If you have access to the view you can add OPTION (MAXDOP 1) to the query to force only that query to not generate a parallel execution plan. |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-16 : 10:34:26
|
| BTW: As a possible explanation, one instance where different results are possible due to multiple CPU's is selecting data from a view containing UNION ALL.If columns are not explicictly CAST to a datatype, SQL will attempt to convert results from the different queries to the datatypes of the columns in the SELECT that finished in parallel first. That may not be the first SELECT in the view. You may have hit a rowcount threshold that caused the order of result sets to become different than it had been before.This seems unlikley from a vendor provided app but maybe they missed it. Look at their views if there are any. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-16 : 12:25:30
|
| I never would have thought of that.Kristen |
|
|
|
sidjames
Starting Member
11 Posts |
Posted - 2004-06-17 : 01:36:16
|
| I have investigated the problem with parallelism, it is a known problem in Sql Server - according to MS support SP3 fixes the problem see http://support.microsoft.com/default.aspx?scid=kb;en-us;330212 We already have SP3 installed, so I suppose I will have to try getting help from MS, unless anyone has any other ideas.CheersSid |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
|
|
Next Page
|
Export SQL Server configuration.