We are running SQL 7 on a server, and are moving to a new server and will be
upgrading to SQL 2005 at the same time. Currently, both the old and the new
servers have two drives, one for programs and one for data.
With the current configuration, SQL 7 and the data are both installed on the
data drive, in the MSSQL7 directory. Our sys admin wants to install SQL 2005
on the program drive of the new server, while putting our databases on the
data drive. I argue that if SQL itself is on the program drive, then the
system databases will be in one place, while our databases will be in
another. So I'd prefer to have SQL 2005 installed on the data drive, as it
is now.
I'm interested in any feedback regarding what you guys think is the better
configuration, and also if there's any performance hit from having the
program and the database on two drives of the same machine.
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
USE [ETLConfiguration] GO /****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SSIS Configurations]( [ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL, [PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]
It seems to me, that the best way is to have one Environment Varible containing the name of the SQL Server, so that you can look up the configuration in the SSIS Configuration Table when you run the package.
Is this the preferable way of doing it ? I would like to hear some positive/negative comment of why chosing a configuration type instead of another.
It seems to me that putting all of the configuration in the Environment variable is harder work but most secure (server breakdown vs table corruption/database error...)
I am creating a windows application to manage my ssis packages.
My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.
However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.
The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.
Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.
I have a Sql7.0 box inside a firewall and Sql6.5 outside the firewall. I would like to use a DTS to transfer data. I followed the KB artice on configuring SQL to listen on a specfic port, but still have problems. I added entries in Client Config on both machines. Still no luck, looks like the 7.0 box not talking on new port...
I have a question about SQL Server configurations, especially those related to memory.
We are running SQL 6.5 SP5a on a 400 MHz machine with 1GB of RAM.
Our memory setting is 409600 (800 MB), which according to all the documentation that I have read is a conservative figure. The problem I believe is in our settings for Locks and Open Objects. The settings that are in place are Locks=3000000 and Open Objects=500000.
I am not a DBA, I am a developer, but have somehow become part of the DBA team. These settings seem out of proportion with the memory we have. The reason given for 3 million locks, by other members of the team, is that our biggest unit of work is a query that performs a select into of approx. 3 million records. To further compound things, we are getting an out of memory error on the server after only a day of running.
The other members of the team have implemented a start and start of the service to occur at 4 AM every morning, but I am convinced that this is not the appropriate action, but they will not action any configuration changes unless I can prove that it will help the memory problem and not effect the queries.
If anyone has any information or links to information regarding Locks and how they relate to query performance, or at least a sample of what your settings are it would go a long way towards convincing either myself or the rest of the team what direction to go.
I have SQL Reporting Services 2005 installed. When I go into the SQL Reporting Services Configuration Manager I immediately get an error that says that "No report servers were found on the specified machine" then Details: Invalid Namespace.
The machine that it is running on is our report server. The name in the Machine Name says the name of the report server not the name of the specific machine so maybe this is the problem? I think the name of the report server should go in instance? Problem is that it won't let me change anything and it just gives me that error and everything else on the screen is disabled.
Hey guys, got a question regarding hardware configuration for a new SQL server we are installing here. We are migrating from a Windows 2000 standard, SQL 2000 standard box with 4GB of RAM to a Windows 2000 datacenter, SQL 2000 enterprise box with 32GB of RAM.
What would be the best way to set this up in Enterprise Manager? Should I just let SQL Dynamically use the RAM or should I select a fixed amount? Also with the processors, should I allow SQL to use all available processors or just 3 of the processors? Also with such a large amount of RAM, I've read people setting there pagefile to 0 so there is no load on the hard drives.
In my Package Configurations, I have three different configurations. Two are of the type, SQL Server and one of type XML. I want to get the currently used Configuration name, Configuration String and the configuration type in my Script Task code. Can anybody help me with this code. Thank you.
We store our configurations in a SQL Server configuration table. We have SSIS packages that can be used in different senarios. Each of these senarios requires different configuration values.
Is it possible to change the SSIS configuration key used for a package at run time? i.e. we'd like to be able to use a job that says run this SSIS package but use configuration values with that name.
I've installed SBS 2003 with SQL 2005. The MBSA shows that the SQL service and agent should not be run as a Local System or Administrator. I've tried to create a new user and start the service with the new user, but no matter what I do, I cannot start the service with the new user. I've tried to add the user to the SQL* groups and Domain Users group, but no luck. I've tried adding permissions to those groups to all the SQL directories, but again, no luck. I'm getting error 5 - permission denied. Anyone got a good HOWTO?
Hi, I am very new to SSIS and SSRS.I just installed in sql server 2005 professional in my office machine. I am going to work on SSIS and SSRS. My machine itself will be the server.Could you please tell me what are the configurations I should do?.. Thanks in advance. -Selva
In my package I am using some 3 to 4 database connections. Hence I can say 3 to 4 connection managers I have. Other than that I have 2 Flat file connection manager.
What will be the best way to configure the same? and Please let me knowwhy that is the best choice.
Hi All, Using Visual studio 2005 you can create a simple database to enable the use of login using asp.net configuration by clicking on website on the menu bar. By default the database is created and stored in the App_data file. However my service provider has a separate server for SQL (SQL Server), therefor it will not work. How can I use this Asp.Net Configuration to create a database on my sql server? Hope this question makes sense!!
Hi Guys Got this problem, ive setup some new roles and users through asp.net configuration and it creates ASPNETDB.MDF in the App_Data folder when ive got a DB in SQL server 2005 express. Basically how do i change it so the tables created for the users and roles are created in the SQL Server 2005 Db? p.s I'm very new to sql server 2005 Thanks
To get an understanding of the SQL Server technology and the hardware requiremnets. To configure a VLDB system for OLTP what is a typical hardware configuration that will be used.
No of Rows:10 Million No of Tables : 50
Will have to spilt this out into many databases, or will one database be able to handle this. Assume that the database is normalized.
Any suggestion or information will be of great help. Does MS have any information relating to this information on their web site?
Hi, I have to buy a new machine for SQL 7.0/NT. I know the db size it's going to have. How can I get what machine configuration I should suggest. I mean...what should be the RAM/HDD size/etc..? We are expecting around 40hits/day? Anyone have any idea? Please suggest. Thanking you.!
It might be a silly question but really took my head. I have a http server and wanted to install SQL server there, so that my client in US can access it. I just wanna know is there anything specific settings while or after installation I need to do so that they can access it and can configure in their Enterprise Manager. Is it okay if I simply install SQL SErver 2000 in my http server and give the IP address of the server with the port number so that they can go to Client Network Utility and add my server. Later they can come to Enterprise Manager and connect to that?
I am about to rebuild the primary SQL Server 6.5 server at our site. i would like to hear from those experienced in this regarding the optimal configuration settings. I have Microsoft`s recommendations, but want to hear from some real-world experiences. I have 1GB of RAM and a 27 GB hard drive.
We are upgraded from sql 7.0 to sql 2000. We have a brand new server in which we are installing it on. We are trying to decide on the best setup for it. The db are a datawarehouse. So mainly used for input. Gets updated with new data once a day.
We were going to have: Partician 1 : Raid 1 - OS and Applications
Now we are trying to decide on the other particians: We have 6 36 Gig Drives to work with. One of them is going to be a hot swappable So basically we have 5 36 Gig drives to work with. I've read that its better to divide up the data/logs/tempdb. Would the following setup be good for performance or if not what would you recommend: We were thinking of making all of them Raid 5. My network guys say that with Raid 5 I'll end up with about 70 % so that accounts to about 127 Gig. So I was thinking the following:
I have to build NT server for a SQL database which is just over 1.1 Gb. Does anybody have any suggestions for server configuration that I should look into i.e. Splitting the data and trans log across different disks. Amount of memory .... etc.
I would like to determine the configuration for a given database in Microsoft SQL Server 7.0/2000.
If you examine the properties of databases using SQL Server Enterprise Manager, you can see that certain options exist for a database. For example: - ANSI NULL Default - Recursive triggers - Auto Update statistics - Torn page detection - Auto close - Auto shrink - Auto create statistics - Use quoted identifiers
Can this information be obtain via a stored procedure?
What about information regarding the SQL server configuration itself? If I use SQL Server Enterprise Manager, I can get properties for the server. For example: - "Dynamcially configure SQL server memory" vs "Use a fixed memory size"
I need help with a new install of MSDE, which I'm hoping to use with learning ASP.NET.
I downloaded and installed the latest MSDE. The Service Manager is now running...and after a few hours of poking around I figured out my server name by looking in the log files (\computernameNETSDK). LOL So, it's running and the three services are available, with SQL Server as the default.
Now, I am having problems launching OSQL and I think it has something to do with the username. I do recall part of the install process that I was asked to supply a password. I am using that password in this situation. I tried the following, where "administrator" is the local workstation's administrator account. For kicks, I tried "sa" as a username. No luck. Here's the output:
D:Temp>osql -U administrator Password: [Shared Memory]SQL Server does not exist or access denied. [Shared Memory]ConnectionOpen (Connect()).
D:Temp>osql -U sa Password: [Shared Memory]SQL Server does not exist or access denied. [Shared Memory]ConnectionOpen (Connect()).
D:Temp>
Where do I go from here so I can start playing with MSDE? Also, I find it odd that there is no MS SQL Server in my Program Groups (XP). Is there no GUI for administering the database(es)?
Hi. I'm debugging our application, and I need to see the Sql Server 2000 error log files. The problem is that the Sql Server Engine does not log every error that occurs. For example, when I run a query "Select * from kkk", it gives an error saying "Invalid object name 'kkk'", but the error is only returned to the client. Its not logged in the error log files. I think, that this happens because that error has a low level (16), and the log is only for high-level errors. Is there a way to configure which errors are logged in the error log, or to make all errors get logged?? Thanks!
I have 4 servers, the first two are web app and load balanced the second two are to be configured for failover. Let us call them SvrA and SvrB. SvrA will have active SQL (2000EE) and passive Content (large directory of courses offered) SvrB will have passive SQL(2000EE) and active Content. Both SvrA and SvrB will be connected to a redundant SCSI 18 drive Drive bay (cheap SAN) which will be configured with a) mirrored OS drives, b) a RAID 5 partition for SQL and c) a RAID 5 partition for the content folders.
SvrA & SvrB will be quad processor machines. EE license for 4 processors as 2nd svr is failover for SQL.
My concern is the failover set up for the content portion, or is it the failure of the server that causes the failover? This is quite confusing to me.
I am trying to run aspnet_regsql to set up the membership tables on this SQL 2005 server. It’s a new installation of SQL 2005, using "SQL Server and Windows Authentication mode" Security. I followed the directions at http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277 to enable remote connections, enabling the browser service, and opening the firewall. Under SQL Server Configuration manager, I see the protocols for Shared Memory, Named Pipes, and TCP/IP are enabled. Also the Services SQL Server, Server Agent, and Server Browser are automatically running. But when I run aspnet_regsql I get the error: Unable to connect to SQL Server database (because of error 40 – could not open a connection to SQL Server). What else could be needed to allow this connection?
Hi to all, I'm having problem to enable FTS and re-configure FTS when I'm restoring the existing db with FTS in another machine. In this database the FTS is running and FTS catalogs as well, the path for FTS of this database is in drive M. When I'm restoring this database in another server I have problems to re-configure FTS, in this server there's no drive M, how can continue using FTS, please suggest!
