We currently have a Server:
Dual Intel Xeon 3.0 GHz
4 GB ram
(C) 2x18 GB SCSI disks RAID 1 for OS
(D) 4x72 GB SCSI disks RAID 5 for Data
The server is running:
Windows 2003 + IIS (Single web application)
Tomcat (Single web application) (Is about to be outfased)
MS SQL 2005 (In simple recovery mode)
The C-driver is currently only used for OS and applications
The D-driver is holding all data + SQL Data files and SQL Transaction files...
E.g.
- SQL Data files = 7 GB
- SQL Transaction files = 20 GB
- Image liberary = 100 GB
- Other = 15 GB
The IIS uses around 768 MB ram for cashing and execution of the web application
The Tomcat uses around 350 MB ram
The SQL is the to use a max of 2.2 GB ram (and 1024 MB pr. query)
(Leaving around 512 MB ram left)
There is only one database installed on the SQL, but it is very large (+15 mill records)
We are currently doing Free-Text searches in one table (~3.5 mill records), and it is to slow.
So the question is what is the best way to gain performance...
- Increase to 8 GB ram?
- Purchase a new server as webserver, so the curr. server can be dedicated as SQL?
- Attach a NAS to split the SQL files into several groups, and seperate transaction logs from data?
(6 disks are max in curr. server)
We have a simple two-node x86 failover cluster attached to a SAN on which SQL Server 2005 runs. We recently bought two new x64 boxes to upgrade the cluster. My original plan was to just add the new nodes to the existing cluster and then remove the old nodes from the cluster, but I just found out that you can't mix architecture types in a cluster.
So far, it seems our choices are:
1) Install x86 Windows 2003 Server on the new nodes, losing performance. 2) Make a new cluster and migrate the data, possibly imposing downtime.
We have a server instance on SQL Server 2005 SP2 build 3042. We have a 32 bit x86 server. We attempted to upgrade to SP2 build 3054 KB934458. And we got the following error as stated in the Summary.txt file.
Code Snippet ********************************************************************************** Product Installation Status Product : SQL Server Database Services 2005 (MSSQLSERVER) Product Version (Previous): 3042 Product Version (Final) : Status : Failure Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log Error Number : 29538 Error Description : MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again. ----------------------------------------------------------------------------------
The client tools and workstation components were successfully installed. The server is still reporting build 3042.
Here is the portions of the HotFix.log file.
Code Snippet 05/12/2008 09:19:09.041 Copy Engine: Creating MSP install log file at: C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB934458_sqlrun_sql.msp.log 05/12/2008 09:19:09.072 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller" 05/12/2008 09:19:09.103 Registry: Cannot read registry key value "Debug" 05/12/2008 09:21:29.382 MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: e:SQLDatamssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again. 05/12/2008 09:22:33.678 MSP returned 1603: A fatal error occurred during installation. 05/12/2008 09:22:33.724 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
I am currently creating an application that requires the use of Pocket PC/PDA. The same application that runs on the desktop is to be run on the mobile device metioned above. I am confused as to which of the following combination servers i should use.
Option1) Desktop - SQL Server Express Edition (Multi-user) Mobile Device - SQL Sever Compact Edition
OR
Option 2) Desktop - SQL Sever Compact Edition Mobile Device - SQL Sever Compact Edition
To my understanding, if I use SQL Server Compact Edition on both my desktop and mobile device, when i need to synchronise, the entire database from the mobile device is copied over to the desktop and vice- versa. However, I prefer only modified data to be synchornized. Can I achieve it with the second option? Is it also possible with the first option? Please advice. Thank You
hi, I have total 45 GB of data in the following servers ( 40 GB in sql server 6.5, 5GB in ftp server). both servers are in one machine(server). I want to make sure that I am doing the right thing for backup. The machine is not connected to a Lan. What is the best way to insure that my data are backed up safely? what I am thinking of doing is to back up the database to using sql tools in the menu bar, that will backup the data to the same machine in this directory: c:mssqlackupDBname.DAT
I will do this for all database in the sql server. Am I save to do this... I am wondering what IF the machine in which both severs are in crashed. My backup effort will be lost too. right. so what is the best way to protect my data. Do I need to buy a tape backup so I can do what I am doing Plus back up the c:mssqlackupDBname.DAT to the tape backup... I would really appreciate any suggestion in choosing any media (software/ hardware)to back up my data.
I'm looking to purchase new server hardware that will host my corporate intranet and a sql server instance. In most cases I can't see this server being hit by more than 20 or 30 users but I want it to be quick. I'm planning on running Server 2003 with sql server 2k and the intranet on IIS.
What I'm not sure about is the single vs dual processor and the RAM. How valuable is the dual proc? And the RAM, I plan on 1.5 GB but I wonder what the benefits are beyond that.
Perhaps with this many users it doesn't make that much difference but I can see adding more load.
I've got a situation where I need to regularly (maybe each month), detach a DB, copy its files from their highspeed SAN location to a slower NAS, then re-attach it and make it available on-line. We're doing this for our DB's as they age to > 3 years.
Just wondering if any of you have scripts you can point me to so I don't have to re-invent the wheel.
Also, after you re-attach, how do you verify the NAS DB is 100% ok before deleting the original from its SAN location?
I'm hoping somebody can help me here as i'm struggling to find any information elsewhere on the net. We have recently purchased a new server, the rough specs are:
2 X Quad-Core Xeon E7320 2.13GHz 4Mb Cache 32Gb PC2-5300 DDR II RAM
We are planning to install the 64 Bit version of SQL Server 2005. We want to use the server for a number of purposes.
Building and weekly processing of 2 complex data marts (approx size is 1Tb each)
Processing and querying of 2 Analysis Services databases that will be built from these data marts. These will be queried by no more than about 15 users (no more than about 5 simultaneously).
Relational querying of the data marts themselves (same users as above) My problem is that I am not sure of the best way to configure SQL Server. Should I use 2 separate instances? How should the processors/memory be shared between SQL Server/Analysis Services? My main priority is the performance of the OLAP querying. However, I also want the weekly processing and any ad-hoc SQL querys run against the marts to be efficient.
I know it's bad to use the built-in SA account to access SQL Server from my ASP.NET application.
I did some reading but would like some suggestions as well. One article I read talked about Application roles in SQL Server. Is this the best way to handle SQL Server access for read/write/update/delete privileges from an ASP.NET application?
If this is the way to go, how do I set it up? Can anyone suggest a good web site to read up on this?
I'm looking for configuration advice for a SQL Server. I have thefollowing hardware to work with:1 Dell P4 2.66 GHz PC with 512 MB RAM and a 40GB IDE drive2 Seagtate Barracuda 4.55GB SCSI drives (ST34572W) in an external bay1 Adaptec 2940UW SCSI ControllerWindows 2000 Advanced ServerMSSQL 2000 EnterpriseThe PC will strictly be a SQL Server only - no IIS or FTP or otherservices. It will host about 8-10 databases that service a departmentof 300 people. My plan is to install W2K and MSSQL on the PC's IDEhard drive and configure the identical SCSI Barracudas as a mirroredvolume to host the databases (MDF/LDFs).Will there be any performance problems installing MSSQL Server on theIDE drive while the MDF/LDFs are on the SCSI drives? From aperformance standpoint, would I be better off installing MSSQL Serveron the SCSI drives?
I've been using Sql Server for my development for perhaps two years. My systems have experienced crashes, I switched from XP to Vista, it crashed again because of malware, etc. In short I have installed and reinstalled the Sql Server Express quite a few times. In the meantime my databases have been growing. So far I've had my development only on one desktop but now I am expecting a new DELL Dimention top of the line machine and I want to switch some of the development over there.
This is the scoop. I need to make numerous improvements in my databases to increase performance, sometimes you add a column or delete one from a table, etc. At the same time the data is being collected. I want to have my Sql DBs set up on one machine and be able to work on another and grab the data, etc. In other words I want to start doing some networking which so far I've done only on a limited basis just for file transfer and printing.
I have no clear image as to how it should be arranged. If I leave my Sql Server DBs on one machine and start using them on another one, do I have to have a new client set up?
Perhaps I need to have another instance set up also or is it the same?
In one of the prior installation which is now in my WIndows.old folder I have 3 Sql Server instance folders: MSSQL1, MSSQL2, MSSQL3. for some reason in this latest installation I got only one folder: MSSQL1. Shall I copy two other folders (MSSQL2 and MSSQL3) from Windows.old to this new Program FilesMicrosoft Sql Server 2005 folder or not?
Two of my machines that will access the databases will be at my office connected via a 10/100/1000 switch but the third one will be home. It will be connected via Vista Computer Name service (Telnet I presume) and I have a high speed cable to the server.
This is the setup as clear as I can see it now. Depending on the answers I get I may come back with some more questions.
Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
Does anyone have a good starter's tutorial on authorization advice & configuration for SQL Server 2005?I need to know what is adviced what web users can specifically do and dont in my db, and how to configure that...Thanks!
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
We have a 3 month old quad processor/dual core server running SQLServer 2005 and already it is getting close to hitting the CPU wall.An 8 way CPU box is prohibitively expensive and out of the question. Iam looking desperately for a way to TRULY scale out SQL server...in thesame way that IIS can be scaled out via App Center.The "in the box" solution for SQL Server 2005 scaling out is the DMV.Unfortunately this solution makes the system less available rather thanmore (one server outage takes them all out for that table) and requiresserious rearchitecting of the software to use. Contrast this to IISand AppCenter where each added server makes the system more available,and requires no rearchitecting to work.Before someone says "what you want can't be done in adatabase"...Oracle has an application server middleware product thatlets you do both of the above. Just plug a new server with Oracle onit, and you've doubled your capacity. But SQL Server 2005 doesn't yethave a similar capability.So I read with great interest the following article that talks aboutwhy this is the case with SQL Server. There are two issues that makeit very difficult to do:http://www.sql-server-performance.c...ability_availab...You can create a crude pool using replication, but the performancetimes look horrendous.However, the article also talks about the latest developments in thisfield...specifically MIDDLEWARE that can create a scale out solutionthat is more available and that requires simply adding new servers toscale up.I found two companies which seem to offer this new capability:http://www.metaverse.cc/newsevents.asp?cid=17999andhttp://www.pcticorp.com/product.aspxBoth companies appear to have patents or a patent pending on theprocess. I tried to contact metaverse but got no reply, despite theirrecent press release. I just emailed Pcticorp today to see if I couldlearn more about their product.My question for this group is:Does anyone have experience with either of the two products (or anyothers that provide this capability)?Many thanks in advance for your help.Ian Ippolitohttp://www.rentacoder.com
I am working on two versions of an application, one of which will be awindows forms application (which will need to be redistributable) andthe other will be a web application.I have MS Visual Studio 2005 (along with the developer's edition of MSSQL Server), but not MS Access. I also have MySQL, PostgreSQL, Sun'sapplication server, Tomcat and Apache web server. I am working onWindows XP Pro, and have installed the .NET 3 SDK and all relevantrelated products I could find (e.g. 2 extensions packages for VisualStudio).I have one MS Access database, to which my users should have read onlyaccess. I have, and have used, a tool for importing MS Accessdatabases into MySQL. I expect that SQL Server has a similar utilityhidden somewhere (where I haven't yet looked, though I HAVE beenlooking - obviously in the wrong places). I have located a similarutility for importing MS Access databases into PostgreSQL. I have notyet decided which servers to use for the web version, but that isanother story, for which I may raise another thread in due course (butI welcome suggestions which may reduce the effort required givenrequired effort for the windows forms app).My problem is for the windows form aplication (intended for use by asingle family). I expect to use ADO.NET. The question is, should Iimport the Access database into MS SQL, and redistribute it, along withMS SQL Server Express (or is that necessary), or distribute it just asan Access database and use the jet engine to access it. A relatedquestion is, "Does ADO.NET support creating new databases for a givenengine?" Imagine a recipe database. It is easy enough to create a SQLscript that creates all the required tables, indices, foreign keys,&c., but can I submit that SQL script to an ADO.NET object, along witha file name, and have it create, e.g., an Access database with thesupplied name. Or do I have to create a database file with nothing init other than the schema?I have more questions, but they'll have to wait.ThanksTed
It's arriving from ebuyer tomorrow for 32 quid. I already have aninstance of MSDE running on the laptop and ideally i would like SQLserver to be installed and use the current instance and not installany other additional services. [hope i got my terminology correcthere.] Basically all i would like is to use the Enterprise Managerwith my existing MSDE database. I was wondering if there is anything ishould be doing before I install. Should i shut down the service orleave it running for instance.Thanks for any tips,AndyB
I've been using SQL Server 2005 Std. for my development work on a laptop w/ 2 GHz Celeron and 768 Mb RAM. Now that Express Edition has Reporting Services and Full-Text Search, would I be wise to just use it instead? I actually have both on my machine since Express Edition installed with VS 2005 Standard. Also, if I uninstall SQL Server 2005 Std. what will I lose? Thanks in advance.
I am not new to the SQL Server world, but am somewhat new to the SQL Server 2005 world. I am just getting into the reporting tool. By just messing around for about an hour or so, I was able to get what I wanted somewhat. Basically, I'd like to get up to speed on the reporting side of things fairly quickly. So I am looking at some books, and was wondering if anyone had any suggestions on any.
Here is a few that caught my eye:
-MS SQL Server 2005 Reporting Services by Brian Larson -MS SQL Server 2005 Reporting Services for Dummies -Professional SQL Server 2005 Reporting Services by Paul Turley -The Rational Guide to SQL Server Reporting Services
I am thinking the 1st two would probably be my best bet, but wanted to get you folks suggestions.
Let me know as soon as you can as I am planning on going to the book store tonight or this weekend.
Hello. I am not very strong with SQL server. But I know enough to get my way around. The reason I am asking forhelp is that I need to change a SQL server script that resides on one of the production database servers of thecompany I work for. I just need to add two new lines to the stored procedure.(See the lines that are bolded.) Thesevalues are [MO_FAX],[MO_EMAIL]. Can someone tell me if there is a best way of going about doing this? Can I just simply open up SQL server and quickly make the change? The big issue here is that this script is used in production. So I am just a little worriedabout screwing something up. Anyway, I would appreciate some good advice on this?
/* Returns all data given the region and country */ALTER PROCEDURE [dbo].[GetAllInfomration] AS SELECT [COUNTRY], [Company_NAME], [CompanyAddress], [CompanyPhone], [MO_FAX] [MO_EMAIL] [CompanyFax], FROM [InfoLookup].[dbo].[Company_Contact] RETURN
I am currently working on a PHP based website that needs to be able to drawfrom Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. Itook a lot of time and care creating a flexible and solid wrapper and amdeep into coding. The only problem is a noticed VARCHAR fields being drawnfrom SQL Server 2000 are being truncated to 255 characters.I searched around php.net and found the following :Note to Win32 Users: Due to a limitation in the underlying API used by PHP(MS DbLib C API), the length of VARCHAR fields is limited to 255. If youneed to store more data, use a TEXT field instead.(http://www.php.net/manual/en/functi...ield-length.php)The only problem with this advice is Text fields seem to be limited to 16characters in length, and I am having similar results in terms of truncationwith other character based fields that can store more than 255 characters.I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions andthe functions referenced here http://www.php.net/manual/en/ref.mssql.php.What are my options here? Has anybody worked around this or am I missingsomething obvious?James
Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
The following version and editions have been verified.
1. .NET 2.0 installed
2. Windows XP SP2
3. MSDE 8.00.2039(SP4)
4. all MSDE databases are owned by sa
5. Instance and SQLAgent running under user that is member of Administrators
What are the possible reasons this error is occurring?
First, let me apologise, I tried to search for the answer to this, but the search wouldn't work for me, and other searches on the web have come up empty.I'm just about to move my asp.net/SQL 2k website to dedicated hosting, and as our budget isn't so big, we can't afford two servers, thus both the web-server and SQL server need to be on the same machine.My question is, what would people recommend for the software setup? The server is speced as follows:- Pentium 4 3ghz- 2gb ram- 15gb primary partition- 100gb secondary partition- 250gb sata secondary drive- Windows Server 2003 SEThe storage arrangement is dictated by the host, as they supply and setup the server. I have no control over this.I was thinking about putting SQL Server 2k Ent on the secondary partition, and the website on the secondary drive. I'm not sure about what to set SQL Server's memory configuration to, as I've always just worked with both the webserver and sql server on the same machine, with dynamic memory allocaton for sql server, in my development environments, with no problem, but also with no load.What would be the best compromise considering the requirements here? I appreciate that having a machine dedicated for SQL Server is best, but I can't do that here, yet. Any help humbly appreciated!
We are performing a SQL 2005 SP1 upgrade from SQL 2000 on our Windows 2003 SP1 Standard Edition.
When we run the upgrade, we got a error "Upgrade Advisor Return -1" as a pop-up window.
When we run the Upgrade Advisor separately, we get this error :
===================================
Common Language Runtime detected an invalid program.
===================================
Common Language Runtime detected an invalid program. (System.Xml)
------------------------------ Program Location:
at System.Xml.Schema.SchemaInfo..ctor() at System.Xml.Schema.XmlSchemaSet..ctor(XmlNameTable nameTable) at System.Xml.XmlReaderSettings.get_Schemas() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReportController.LoadAndValidateDataFile() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReport.ValidateDataFile() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAIssueReport.Refresh() at Microsoft.SqlServer.UpgradeAdvisor.ReportPanel.OpenReport(String reportFile)
We are in a lost for solutions. We do hope to hear from anyone asap...
We are running a test upgrade form sql 2000 standard edition 32 bit to sql 2005 developer edition 32bit. Followed through all the steps and specified the account(SA priveleges and currently used by the 2000 version) and is the local admin on the box for the services and the setup seemed to move fine, except for when it got to the point of installing database services - This is the error message that I got:
MSSQLServer could not be started. Verify you have sufficient priveleges to start system services. The was no choice but to hit cancel and then the set up progressed with a final message of 'SEtup failed'.
Here is a portion of the error log: Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required. Database Mirroring Transport is disabled in the endpoint configuration. Starting up database 'master'. Converting database 'master' from version 589 to the current version 611. Database 'master' running the upgrade step from version 589 to version 590. Error: 946, Severity: 14, State: 1. Cannot open database 'master' version 589. Upgrade the database to the latest version. System Task Aggregate global counters produced an error that was not handled. Major: 9, Minor: 46, Severity:14, State:1 Error: 928, Severity: 20, State: 1. During upgrade, database raised exception 15151, severity 16, state 1, address 01C4C50B. Use the exception number to determine the cause. Error: 15151, Severity: 16, State: 1. Cannot find the endpoint 'TSQL Local Machine', because it does not exist or you do not have permission.
Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.
I have a client whose DBA upgraded their SQL 2000 database to SQL 2005. They still have it running in 8.0 compatibility mode, for various reasons, and would like me to help them develop a migration strategy to get them to full 9.0 compatibility.
My question is: can I run the Upgrade Advisor on a SQL 2005 engine database running in 8.0 compatibility mode and get anything useful? I already know there will be some issues with existing DTS packages but it would be nice to catch any other issues in advance.
I know that the Upgrade Advisor was run before the upgrade took place but to my knowledge the report was not saved and none of the recommendations were followed.
I tried creating an alias to the server to get it to connect to analyze the server but it will not recognize the SQL 2000 server as a valid server to analyze. I can use the alias to connect in EM or SSMS. Any ideas? The server is not clustered and is at SP4. I've connected to several others in my environment but this one is causing me grief!
I upgraded my SQL 2005 Workgroup 2005 to SP2 and subsequently could not get the SQL Server service running. Turns out this was a problem because I had created separate folders for my data and log files. The distmdl.mdf and mssqlsystemresource.mdf files were in my data folder and distmdl.ldf and mssqlsystemresource.ldf were in my log folder. During SP2 upgrade a new distmdl.ldf and mssqlsystemresource.ldf were created in my data folder. I deleted the distmdl.ldf and mssqlsystemresource.ldf files in my log folder and then moved the newly created ones in my data folder into the log folder and all is well now.
If there was a view that joined 2 tables and I accessed the view the 2 ID fields in the view would still have the AutoIncrement attribute still set to true so that I knew those were Identity fields.
In SQL server 2005
I dont' know why but if you reference a View that has Identiy AutoInc fields in ADO it doesn't keep those properties.
Also for whatever reason we Set the ID field to 0 to let ourselves know its a new Record. SQL 2000 let it happen and assumed it to be null where as By Setting the ID to 0 in SQL 2005 causes it to blow up on me.
Is there some sort of setting in SQL that can make SQL 2005 work like SQL 2000 in these two instances...
