What Are The High Level Considerations For 24x7 Installations?
Feb 13, 2008
in a prior "legacy" life we couldn't imagine 24x7 implementations because it was important to 1) reorganize databases periodically to remove fragmentation that adversely affected performance and 2) back up databases just in case.
In a 24x7 SQL Server 2005 implementation, high level only, how are these and other maintenance related things accomplished with confidence?
I dont think SQL cleanses its own page splits unsolicited. Are DBAs totally reliant on logs in full recovery installations where db must be up 24x7? What if the devices those logs sit on fail? What if the logs become too large? Is it likely that if you want 24x7 you're looking at Enterrise Edition only?
I'm totally aware of and confident in the sliding window partitioning thing but it seems to me there must be more out there in terms of periodic, more frequent maintenance activity.
Hi, Can anybody please explain me, what is low level and high level locking in SQL Server 2005 database. Also what is the name of process which converts low level locking into high level locking and vise versa. -Sanjeev
ASP.NET v 2.0 application uses this SQL 2005 server consists of 10 webservers. Max Pool Size on each was changed today to 300 (was default so I suppose - 100) and it didnt change logins/sec graph at all. Application uses SqlConnection object from ADO.NET v 2.0 obviously.
Can someone explain - what could be reasonable explanation - is load that high? Do we need 3000 as Max Pool Size? (I cant imagine what could be maximum recommended size of pool for W2003 32-bit server)
Hello, we have a customer-facing ASP.NET web reporting application that allows the user to a). select a report b). make report settings (filter, sort, include/exclude columns), and c). run the report. An Oracle stored proc is used to return the data into a DataSet. Today the output is displayed in a bound, paginated DataGrid.
Wed like to plug-in Reporting Services for improved formatting, grouping, matrix reporting, drilldown, graphics, etc.. I have read through the online docs, examples, and tutorials but have not found the exact (high-level) information Im looking for.
We need to keep our report settings page, Oracle SP architecture, etc... Is there a way to simply hand off a dynamically created DataSet to a report for display like binding to the DataGrid? The data (query) would be specific to a report but columns can be selected/unselected for display. In terms of deployment, is it necessary to install SQL Server in each environment (DEV-Integration, QA, PROD) to support Reporting Services? Since we are essentially an Oracle shop, this could be a tough sell. Is it possible to develop the report on a dev workstation (perhaps with SQL Server), and then publish to a web server where SQL Server is not installed?
These are obviously high-level questions - any help will be appreciated (examples, how-to's, etc..) thanks in advance. -Bill
The problem that I am having is that with Visual Web Developer I am creating a webpage and having it directly put online, so for example when I start a new ASP.NET page, I select the location to be HTTP, with the location http://MYWEBSERVER/Website and for the language and Visual BasicI notice a couple of things, first that there is no longer a a link under the Main toolbar "Website" selection called the ASP.NET configuration. So how can I configure what I want to have users be able to do? It seems that this choice is only available if I am building the ASP.NET page on my "localhost". So that is the first problem. So I am able to get the pages to work, atleast the things such as the textboxes to show up etc, (Even things as advanced as the "Login" box). How ever when I try to get someone to try to login I am taken to a page that has an server error. The error is: "The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. " The Stack Trace Errors are at the bottom. I think this is happening because the automatically generated databases are not getting built online as they are on my computer. On my computer I have MSSQL express. So either the databases are not getting built for some reason, (and I think that is the case as I don't see any in the folder). So I think that somehow I have to create a database on my server, and then somehow configure the ASP.NET file, perhaps in the Web.Config file to look for that new database. Is this the correct methodology? Is there some simpler way that I can just somehow upload things as they are and have them work correctly on my server? The error says that either the Server did not find the database or that the trust level was insufficient. I don't think that is it as I just looked again and I don't see any .MDF files. So how would I go about getting this to work right? Is there a way to do this with MySQL also? So that I don't have to use MSSQL? My server only allows 1 DataBase for that. Thanks and I hope my question makes sense. It is basically how can I get it to be able to create and check users etc. online? Brian [ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.] System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2555237 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
When I deploy the cube which is sitting on my PC (local) the following 4 errors come up:
Error 1 The datasource , 'AdventureWorksDW', contains an ImpersonationMode that that is not supported for processing operations. 0 0 Error 2 Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'AdventureWorksDW'. 0 0 Error 3 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Customer', Name of 'Customer' was being processed. 0 0 Error 4 Errors in the OLAP storage engine: An error occurred while the 'Customer Alternate Key' attribute of the 'Customer' dimension from the 'Analysis Services Tutorial' database was being processed. 0 0
hi, I have SQL 2000 ProductionBox.It is in 24x7 environment. We need to maintain data only for 30 days. Even if I schedule deletetion of data on daily basis - SQL will take the lock as data we receive is too huge. Secondly,Since the indexes are heavily used - defrag don't work for them.the only option I think I am left with is to rebuild the indexes.Though in SQL2K - index creation is on fly - but here we are talking of table sizes of 8-10GB. I suggested my Boss to bring down the box for few hours for maintenance.but he insists that since this is 24x7 - Box can never be brought down. I am finding it hard to convice him and do my job. Any idea on how to rebuild the indexes & how to delete this many records(avg.50k per day-data of xml type)without creating a block is highly appreciated...or how to convince my boss to give me a window for maintenance...:) TIA
Hi, I maintain a 24x7 SQL server on a claster with a big table (one of the 900 others) and few indexes on it. It takes up to 4hrs to rebuild those indexes on this table (alone) every week on Sunday morning, and cause of that users who use the server complain that the application is "down" and they can't access the data and blah, blah, blah. I tried all the possible scenarios of index maintenance and no way to fit it within our 1hr window. So I just wondering how you guys as DBAs of the 24x7 systems facing the same issue (if you have it indeed). Would be nice to know your expirience on that.
Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.
If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?
I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.
If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.
So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?
I have a table with about 200 million rows of data. I add a couple million rows of data each week to the table in a single load process. The table is used for reporting purposes only and there are never (not intentionally at least) any updates or deletes to the table. The data is always being added to the "end" of the table with the new AsOfDate being the main factor in the clustered index.
My question is this: Since I'm not "inserting" rows that would split pages, should I have my FILLFACTOR for the table set to 100, or am I missing something? I obviously want to save physical hard drive space, but I also don't want to slow down the import process.
I would like to know people's thoughts on any special network considerations to take for mirroring and the logic behind them. Is it best to segregate mirroring traffic from other network traffic? Use a VLAN? Dedicate one NIC for mirroring and the other for general network traffic or just aggregate the two and let both types of traffic share the bandwidth?
I haven't seen much in this area from Microsoft's best practices and wanted to know what those who have implemented it have done and why. There are pros and cons for each method: Letting everything share one massive pipe with load balancing vs. trying to segregate traffic in some way so that general network connections etc. do not impact the mirroring capability.
We have about 150 SQL servers and basically we're considering the pros and cons of installing SSIS on a central SSIS server - that is responsible for all DTS jobs - as opposed to installing SSIS on the local SQL instance.
On the plus side so far:
1./ Central administration, alerting, change management etc
2./ Possible performance gain on the local instance not having SSIS installed?
On the negative side:
1./ Central point of failure
2./ Possibility that it would need to be a clustered...
3./ Compatibility issues may mean having to make the central SSIS server 32-bit?
4./ Possible performance cost of remote SSIS?
5./ With multiple DTS packages running at different times, when would we take the server down for maintenace...?
Can a publisher be mirrored? What are the implications, issues, gotchas? Transactional, Merge or Transactional w/ Updating Subscribers is what I'm considering.
Bottom line is I would like to use mirroring, but only one mirror will not suffice.
Hello, I'm developing application which monitors network packets. The monitoring data are saved into table. Monitoring table maintains the data for fixed quantum time,for example during one 1 hour. So, every minute before or after insert new data, I delete the time-expired data. I doubt that the endless delete operation would results in some problems(increasing index,etc..).
I would like to know how to, if at all possible, to reconstruct the following trigger as to be able to handle multiple row insert when a single insert command is used - because the trigger will only be called once...I'm not familiar and don't know anything about cursors and i've read that its not the best way to go.
TRIGGER ON childtable INSTEAD OF INSERT AS BEGIN DECLARE @customkey char(16); DECLARE @nextchild int; DECLARE @parent int; DECLARE @date datetime;
SET @date = getdate();
SELECT @parent = parenttable FROM inserted;
SELECT @nextchild=count(*)+1 FROM childtable WHERE parenttable = @parent;
Can someone point me to a resource for Table Design Considerations for Merge Replication? I have an ASP.Net/SQL2K5 app that I need to run on disconnected machines, then allow dfor data sync through merge replication. I assume that the first step is getting my tables indexed in a replication friendlt manner?
Many Thanks to anyone who can point me in the right direction!
I may be overthinking this, but I want to make sure this is right. If you have a processor license of SQL Server Standard running both Reporting Service databases and the IIS interface, isn't it true that the underlying licenses of other servers containing your data are irrelivent in the context of serving the reports over the web? Example. Server 1 has SSRS as described above, processor license of Standard. Server 2 has user license of SQL Enterprise and serves data to a couple of reports on Server 1. This does not violate a license, correct? Doesn't Server 1 just take one of the CALs from Server 2?
Can someone point me to some good articles or perhaps directly supply some words of wisdom with regard to wise utilization of variables within a T-SQL script from and standpoint of conserving memory usage and improved execution cost?
For example:
(1) Is it better to use varchars, nvarchars, etc. defined with minimal lengths to support the needs of the script or is it just as efficient to declare all with a length of say 4,000?
(2) I've seen behavior that leads me to believe that when passing a variable as a parameter in a nested procedure call, if the declared types of the parameter and the variable being passed in don't match (i.e. one is numeric(38,10) and the other is int), then implicit type conversions hurt performance. Is this true and how broadly does it apply?
(3) Does the number of variables declared in a script materially impact the performance and / or resource utlization?
(4) Is it more efficient to have a series of variable value assignments in a single SELECT statement versus a series of SET statements? Should I always perfer one to the other? Only within a looping construct?
Hi All I am new to this forum. I want to know if MSDE can be installed twice (two instances) on a single computer? I will be thankful for details provided by you. Radhesham
Any assistance regarding this would be greatly appreciated.
I am trying to intall SP1 on SQL Server 2005 and am experiencing some difficulties with regards to Authentiction modes.
I have tried to use Windows Authetication for All instances and it states that the account information could not be verified for one or more services. I only have one instance which is the default and have Database Services and Reporting services listed in the Products text box. I have also tried using SQL Server Authentication using the password of a SQL Server account but am still experiencing problems.
Any ideas or suggestions would be greatly appreciated.
Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database? I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors. But I have heard a lot of discussion that you can't just simply backup SQL Server data files? I'm wondering how sound the backup I've created is... Any suggestions?
They say that the definition of insanity is doing the same thing again and again while expecting a different outcome. I must be going insane.
To top it off, the first time I tried to enter this message the post failed.
I installed visual studio 2005 and SQL express in the spring and began working with them. Life was good. I got distracted for several months and when I went back in I saw the 2008 beta 2 versions of VS and VWD. I uninstalled everything and installed the new versions, but kept having problems with missing links in the help files. I am a newbie, so the help files were important to me so I decided to go back to the old versions.
I deleted everything again, used the MSI cleanup utility to make sure everything was deleted and tried to reinstall the 2005 versions. At one point I got SQL server installed, but had to use a different (besides SQLexpress) instance name and was unable to link it with VWD. I have repeatedly uninstalled and reinstalled (maybe a dozen times) while trying various means of deleting file contents and under the programs/apdata and Microsoft SQL directories and in various sequences to no avail. The latest message is below along with the end of the log file.
Does anyone know a good hosting vendor for the visual studio authoring environment? (just half kidding)
Thanks for any help ... bill
Error message ....
TITLE: Microsoft SQL Server 2005 Setup ------------------------------
The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29503&EvtType=sqlsetuplib%5cservice.cpp%40Do_sqlShutDownRANUInstance%40sqls%3a%3aService%3a%3aStart%40x3
Log file summary .....
Microsoft SQL Server 2005 9.00.3042.00 ============================== OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600) Time : Tue Sep 18 14:29:26 2007
HOMEWXP : The current system does not meet minimum hardware requirements for this SQL Server release. For detailed hardware requirements, see the readme file or SQL Server Books Online. HOMEWXP : Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online. Machine : HOMEWXP Product : Microsoft SQL Server Setup Support Files (English) Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_SQLSupport_1.log -------------------------------------------------------------------------------- Machine : HOMEWXP Product : Microsoft SQL Server Native Client Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_SQLNCLI_1.log -------------------------------------------------------------------------------- Machine : HOMEWXP Product : Microsoft SQL Server VSS Writer Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_SqlWriter_1.log -------------------------------------------------------------------------------- Machine : HOMEWXP Product : MSXML 6.0 Parser (KB933579) Product Version : 6.10.1200.0 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_MSXML6_1.log -------------------------------------------------------------------------------- Machine : HOMEWXP Product : Microsoft SQL Server Setup Support Files (English) Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_SQLSupport_2.log -------------------------------------------------------------------------------- Machine : HOMEWXP Product : Microsoft SQL Server Native Client Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_SQLNCLI_2.log -------------------------------------------------------------------------------- Machine : HOMEWXP Product : MSXML 6.0 Parser (KB933579) Product Version : 6.10.1200.0 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_MSXML6_2.log -------------------------------------------------------------------------------- Machine : HOMEWXP Product : SQL Server Database Services Error : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually." -------------------------------------------------------------------------------- Machine : HOMEWXP Product : Microsoft SQL Server 2005 Product Version : 9.2.3042.00 Install : Failed Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_SQL.log Last Action : InstallFinalize Error String : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually." The error is (3) The system cannot find the path specified. Error Number : 29503 -------------------------------------------------------------------------------- Machine : HOMEWXP Product : Microsoft SQL Server Management Studio Express Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_HOMEWXP_SSMSEE_1.log -------------------------------------------------------------------------------- SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt.
I am seeing repeated failures installing SP1 database services with crashes in hotfix.exe being reported and the following appearing in log files:
Property(S): SqlUpgradeMessage = SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_resource_semaphores', because it does not exist or you do not have permission.. To continue, correct the problem, and then run SQL Server Setup again. Property(S): UpgradeInstruction = Connect to SQL instance ZENO as sysadmin Launch SQL statement USE master Launch script file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstallsysdbupg.sql Launch script file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstallDbEngine_hotfix_install.sql Launch script file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstall epl_hotfix_install.sql Stop service MSSQLSERVER MSI (s) (38:80) [09:35:35:246]: Product: Microsoft SQL Server 2005 - Update 'Service Pack 1 for SQL Server Database Services 2005 ENU (KB913090)' could not be installed. Error code 1603. Additional information is available in the log file C:WINDOWSHotfixSQL9LogsSQL9_Hotfix_KB913090_sqlrun_sql.msp.log.
I have 2 SQL server installs for 2008 R2 configured as multi instances. I have a product called Esri ArcMap 10.3 that can be used to generate a database. When I run the wizard against one installation, the wizard successfully creates the database. When I then run the same against the other installation it fails with the following error [Microsoft][SQL Server Native Client 10.0]Invalid cursor state
I've attempted to look at the configuration of each using select * from master.sys.configurations
From this I found several differences
Successful Mulit instance Optimize for Ad hoc Workloads β False Max Degree of Parallelism - 0
UnSuccessful Multi instance Optimize for Ad hoc Workloads β True Max Degree of Parallelism - 4
I attempted to co-ordinate the differences running the wizard for each iteration but it always failed with the same error above. The error always seems to occur when a particular store procedure is run. There are quite a number of scripts run prior to this and are technically under the covers and only discovered via tracing, in this case using SQL Profiler. I don't have access to individual scripts that I can run incrementally to replicate the issue. I have to rely only on the Esri Wizard.
Reviewing the error against several forums suggests that this is an ODBC error but the trace I ran using SQL Profiler finds that the driver used is Native.
My question then is "What are the conditions that would cause this error above (Invalid Cursor)?" "Is there other configuration settings that are not captured via the SQL identified above?" "Could this be caused by mapped drives for data, Logs and Temp?"
I have installed SQL server 2005 on Win SERVER 2003, then uninstalled and then re-installed but I have found out that the previous instances are still listed and the the previous collation too (I have changed reg setting and and language option re-installation).
It looks like that removing program does not remove completely every object, probably registry values
Do you know how to perform a good uninstallation removing everything (add/remove program does not work properly as written before) ?
We are attempting to run the SQLScan utility from the SQL Server 2000 security tools (http://www.microsoft.com/downloads/details.aspx?familyid=9552D43B-04EB-4AF9-9E24-6CDE4D933600&displaylang=en). I have identified 6 systems with a vulnerable installation. Each developer has updated to SP4 and when I run the scan again, I can see that the instance is now at SP4 revision but it still reports that the instance is vulnerable. From all the documentation I have read, SP4 should include all previous patches. Is the SQLSCAN tool not compatible with SP4 and if so, how can I centrally determine whether I have vulnerable version on the network or not?
Is there such a tool for SQL Server 7 as SQLSCAN is for 2000?
I should state also that I am not technically proficient with SQL Server but have been tasked to scan the network for vulnerable versions that we may have and am finding SQL Server to be very confusing indeed.
