Hello,
We have a server running SQL Server 2000 Standard Edition on a Windows 2003 Standard Edition Server. Can we install SQL Server 2005 Standard Edition on the same box? I'm assuming that so long as the instance names are different that it should not be a problem.
Has anyone already done this? Any problems?
Thanks a bunch.
Re: Best Practices (security): Should SQL Server (2005) *not* be installed on the same physical HD as the Windows OS (Server 2003 R2) ?
Hi,
We're setting up some new servers, and today I'm looking into best practices for the SQL Server Setup portion of it.
The servers have include 2 x 250G HD, and from what I've read, where IIS is concerned, it should not be installed on the drive that has the OS on it, for security reasons. I was wondering if the installation of SQL Server should be on the non-OS drive as well ?
Thanx,
Barry O'Neill
I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.
Here is the breakdown of the servers:
1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit
2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard
3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)
**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?
Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server? I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit.
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that
we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions:
1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server?
2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected
problems when running both 2000 and 2005 SQL Server at the same database server?
3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
TIA,
Jeffrey
Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
Hi All,
Is it possbile to define connection string for web and database server which is running on different machine. Note( Not same LAN).
E.g (My Web Server is in London and Database server in Sydney).
Please can any one help me.??
Thanks in advance...
Dj
Hello!
What is the best way to make a copy of an existing sql server database to another (physical) server?
Plan to make a full backup of another sql server database to another server. I've read about detach and attach
and copying the datafiles and log files but some say it is prone to data loss? Is this true?
And another thing, what if the existing sql server can't affor downtime for me to copy db files etc.?
Anybody here who have done the same?
Any help is very much appreciated.
Thanks!
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success)
- Add task for transferring database objects (Success)
- Create package (Success)
- Start SQL Server Agent Job (Success)
- Execute SQL Server Agent Job (Error)
Messages
* The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005,
this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
How do I solve this problem?
Hi,
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
Thanks.
Hi,
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
Thanks.
Hi Guys,
I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well. (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!)
Everything went fine except that nothing seemed to be installed. I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing.
A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005.
So after a number of retries in which the install program kept saying that there was nothing to install, I selected every option under Advanced in the intall process. This resulted in stuff being installed and I now have SQL Server 2005 running on my machine, but no SQL Server Manager icon.
My questions are:
Where can I find the SQL Server Manager executeable?
Has anyone installed SQL Server Express, SQL Server Mangement Studio Express, SQL Server 2005 and SQL Server Management Studio on a single machine successfully? If so, what order did you install them in? (I'm planning on uninstalling everything SXQL Server related and starting again.)
Thanks very much for your help.
Regards
Gary
Hi
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'abc'.
------ Error Message Ends
Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help.
Regards,
Salman Shehbaz.
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
We have a project already developed iusing VS 2003 Enterprise architect edition using sql server 2000(have lots of table and stored procs etc.)
Now planning to switch to Sql server 2005 are there any issues associated with the switch.
I mean front end programming wise asp.net and vb.net front end codes do they work straight away or need to make any changes to all the front end codes, we are using datasets, data readers calling stored procs and also using lot of hash tables and XML object to make bulk loads to sql server via sqlxml3.0 sp3.
Please help thank you very much for the information.
Hi all,I just asked some people to help me out and phone microsoft with thefollowing information, kindly they refused unless we setup a supportcontract with them first, for pre-sales information. (That really doesnot sound like good business sense to me - anyway here is our problem,if anyone could help thanks)."To tell and ask microsoft:We will be setting up a microsoft sql server 2000 instance running on awindows 2003 server.1) We need to check this can run alongside a microsoft 2003 sql server(either workgroup or standard edition), on the same machine. Are thereany .dll clashes if we do this? If there are can we run SQL Server2000, in a virtual machine running windows 2000 professional. (I have alicenced copy we can use for this).2) If we run one instance of 2000, and one of 2003 of the sql servers,can one use the processor licence model, and one use the CAL licencemodel."Thanks for any help, and any idea why they actually force you to usenews groups for pre-sales information?David
View 4 Replies View RelatedHi,
I have an interesting situation here. I have a SQL Server 2000 database which is attached to SQL Server 2005. The database (SQL 2000) however only has service pack 3 applied. I need to apply SP4 before I can move forward with SQL 2000->2005 upgrade. The question: can I somehow apply this support package with my current configuration or should I install full SQL 2000 front-end anew (which I don't have at the moment)? Whenever I try to run SQL 2000 SP4 it complains "SQL Server 2000 is not installed on this machine."
The server I try to install the update is Windows Server 2003 R2 Standard Edition x64. SQL Server 2000 database itself is x86 (obviously).
Thanks.
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
I don't know why my application on the internet try to access the SQL Server 2005, when the host still having the 2000 edition. I put the following connection string:
Data Source=##DBSERVER##;Initial Catalog=##DBNAME##;User ID=##DBUSER##;Password=##DBPASSWORD##;
Isn’t everything fine with that? I created the table by host but dosen’t work. I’m wondering if can I conect straight with Data Base, without register in the server, like with mdb?
This is the error that I’m getting.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables.
How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.
Hi All
I have Computer is setuped SQL Server 2000 and SQL server 2005 when I restore SQL Server 2005 it's OK but when it's not OK when I restore in SQL Server 2000. The Error as follows:
Please help me to solve this problem. Thanks
I'm working towards MCAD and as elective I want to do the SQL Server course. The exam is on SQL Server 2000. I was just wondering whether the online course that Microsoft currently have a available for free at https://www.microsoftelearning.com/sqlserver2005/default.aspx would be useful and applicable to SQL Server 2000, or whether it is sufficiently different product to make it confusing to use this as a learning resource.
Also if anyone can point me toward any webcasts for SQL Server that would be great. I listened to Fritz Onion's 15 part series when I studied for my ASP.NET exam and would like to do something similar for SQL Server 2000 if any are available.
Hi Friends,I have installed SQL server 2005 Express Edition and SQL Server managementI have a SQL server 2000 db backup file. I try create a new database in my SQL server 2005 express Edition and try restore that backup file from device, it only searching for file with *.bak and *.tm extension! I tried generate backup file with .bak extension and tried restore into SQl server 2005 express edition but still it is not allowing to do so! I also tried copy my database's data file and log file and paste it under SQL server 2005 express edition Data folder and still not able to read the tables.Is that any ways to do restoring for this SQL server 2000 backup file into SQL server 2005 express edition! Anybody can help me on this please...:eek:
View 2 Replies View RelatedWe have experienced an issue with back backup / restore of a database originating from SQL Server 2000 to SQL Server 2003.
We have the following setup:
SQL Server 2000
- DatabaseA
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseA
- DatabaseB
- asdfUser (SQL User)
- asdfUser is (dbowner) of DatabaseB
SQL Server 2005 Standard
-asdfUser is NOT Setup as a user yet.
-We restore DatabaseA and DatabaseB to the SQL Server 2005 Standard. The databases are restored with the security permissions of asdfUser being the DB Owner of DatabaseA and DatabaseB.
-We create a new SQL user named asdfUser on the SQL Server 2005 box. We then try to add the UserMapping of DBOWNER for the DatabaseA and DatabaseB. We receive an error message stating that the asdfUser already have permissions to the databases. We proceed with the user creation without those permissions.
-We proceed to the login properties of the asdfuser and view their UserMappings. The asdfUser does not have access to DatabaseA or DatabaseB. We then add the UserMapping of DBOWNER to both DatabaseA and DatabaseB. We Try to select OK and we receive an error message that states that the user already has those permissions.
-When we query the UserID's of the asdfUser that is in the database and the UserID of the asdfUser that is created, they are two different values.
I assume this is a bug... any word on a fix?
Scenario
Recently moved a SQL2000 database to SQL2005.
Collation on SQL 2000 database server was SQL_Latin1_General_CP1_CI_AS
Colaltion on SQL 2005 database server is Latin1_General_CI_AS
After restoring the SQL 2000 database on SQL 2005 we observed that the database collation was SQL_Latin1_General_CP1_CI_AS. We have altered the database collation to Latin1_General_CI_AS. We understand this will work for all new objects created on the database but existing objects (prior to the collation change) will continue to have the 'SQL_Latin1_General_CP1_CI_AS' collation.
What is the best method to change the collation of the older columns to 'Latin1_General_CI_AS' -
1) Run ALTER TABLE ALTER COLUMN <colname> <datatype> COLLATE Latin1_General_CI_AS <nullability>
on each varchar/char column
or
2) For each table
BCP out data
DROP TABLE
RECREATE TABLE (taking the new collation Latin1_General_CI_AS)
BCP in data
3) Other methods?
Thanks in advance
David.
Hi
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
3) Any thing that I need to look out for.
Thanks for any inputs on this.
Regards
Our software vendors upgraded their software and our environment - and applications do not interface as they ought. The vendors do not appear to have any further ideas (to those listed below) and at this stage neither do I. It is now a week since the *upgrade* went live. Looking for new suggestions....
Full story (epic) below. Object names have been changed to protect the *innocent*.
Our organisation had 2 applications:-
appBank
appHouse
both running on the respective platforms:
Server name svrCAT:
Windows Server 2000 sp 4 / SQL Server 2000
Server name svrDOG:
Windows Server 2000 sp 4 / SQL Server 2000
Transactions were "posted" from svrDOG to svrCAT via a linked SQL Server, using login dbL1nkLogin.
dbL1nkLogin was made a user of (with relevant table permissions) of the respective databases:
dbForBank
dbForHouse
*** At this point all was working ***
Upgrading appBank to appBigBank required SQL Server 2005 (and more diskspace).
A new server was purchased:
Windows Server 2003 R2 sp 2 / SQL Server 2005 (Server name svrHORSE)
A linked server was created from the SQL instance on svrHORSE to svrDOG using login name dbL1nkLogin.
dbL1nkLogin was made a user (with relevant table permissions) on database:
dbForBigBank
Attempts to "post" transactions were "posted" from svrDOG to svrHORSE via the linked SQL Server fail with
the appBank application generating the following errors:
On first attempt:
************* FILE ERROR *************
Error Code...... 3902-
Error Message...The COMMIT TRANSACTION request has no co
.rresponding BEGIN TRANSACTION.
Table Name......** NOT APPLICABLE **
Description.....** NOT APPLICABLE **
Function....... COMMIT
sys991: Program already in use
COBOL error at 000444 in sys500
Called from 00076A in sys990ssv
Called from 000457 in sys991
Called from 01177D in gls489
Called from 000DFA in gls396
Called from 003961 in gls394
Called from 000258 in glp900
Called from 003DC1 in syp250
On subsequent attempts:
************* FILE ERROR *************
Error Code...... 1206-
Error Message...The Microsoft Distributed Transaction Co
.ordinator (MS DTC) has cancell
Table Name......tblHouseFinancials
Description.....DYNAMIC SQL CALL INTERLUDE
Function....... UPDATE
*FAILED* Processing Job 'WIN/'
* Parameters and controls within the appBank application were verified.
* Password on dbL1nkLogin was reset in the SQL instances on svrHORSE and svrDOG
* Compared the link server settings on svrHORSE to svrCAT:
Security tab: Be made using security context - Remote Logging: dbL1nkLogin / password
Server Options: Collation Compatible checked (on svrHORSE only)
Data Access checked
RPC checked
RPC Out checked
* Made dbL1nkLogin dbo on databases dbForBigBank and dbForHouse.
* Changed default database from master to dbForBigBank on svrHORSE
* Rebooted svrHORSE and svrDOG (multiple times)
* Verified that svrHORSE could ping svrDOG, and vice versa
* Using DTCping.exe, Verified that the SQL instance on svrHORSE could ping svrDOG, and vice versa
* On svrHORSE, Administrative Tools -> Component Services -> Computers has the following settings for "My Computer" Network DTC Access:
Network DTC Access - checked
Allow Remote Clients - checked
Allow Remote Administration - checked
Allow Inbound - checked
Allow Outbound - checked
No Authentication required - checked
Enable XA Transactions - checked
DTC Logon Account - NT AuthorityNetworkService
These settings were already set. My understanding of KB329332 suggest they are correct. As I could not establish the configuration date of these settings rebooted the server to ensure they'd taken effect.
* Loaded SQL Server 2000 sp4 on svrDOG (Rebooted afterwards in case services not stopped and started correctly)
srvDOG now has MDAC 2.8 sp1 (2.81.1117.6) while srvHORSE has a later version (2.82.3959)- I assume sp2. While they are not the same version, MDAC 2.8 sp2 KB 231943 advises that 2.8 sp2 is not available via the web - so I think it's the best I can do).
* Stopped and manually started MSDTC and SQL Server (via Administrative Tools - Services) in case MSDTC was not being started first.
* KB873160 suggests opening port 135 and adding msdtc.exe as an exception in the Windows Firewall. At present there is no firewall on svrHORSE - which I would assume negates this necessity (at present). In Administration Tools - Services there was a service for the firewall running. Stopping this service has no effect on this issue.
* ( The svrHORSE machine was not created from an ghosted image ).
** None of these things have resolved the issue. **
.... to be continued...
KD
Not sure if this was posted but having a hard time finding an answer.
I'm currently running sql server 2000 enterprise edition on my machine and was wondering what would happen if i installed 2005 express edition and vice versa? I've found many blogs and post about compatibility issues with the 2005 Express Beta version but couldn't find any news with full version. It would be much appreciated if someone can help me out with this problem.
Thanks!
Hi,
I'm trying to set up a transactional replication from SQl Server 2005 to SQl server 2000. The Distributor and the publisher are on our server and the subscription is supposed to be on a SQL 2000 server on a different location. Before upgrading to 2005 I didn't have problems - the replication from 2000 to 2000 was working perfectly.
After I succesfully created the distributor and the publication the first problem that I encountered was that when I tried to create the subscription it was giving me an error that I cannot use an IP to acces the server. I realized to fix that issue by creating an Alias in the SQL Server Configuration Manager for the server where I wanted to create the subscription (a push subscription).
Now when I try to add the subscriber it gives me another message "Execute permission denied on object 'sp_MS_replication_installed', database 'master', owner 'dbo'" - so I cannot create the subscriber.
The user that I use to create the subscriber on the 2000 server is dbo for the subscriber database but it doesn't have rights on the master database. Also I realized that on the 2000 server I still have the old subscription but I cannot delete it - for the same reason - no access on the master database. Before upgrading to 2005 I had the exact same rights on the 2000 server.
Any help would be appreciated.
Thanks.
Hello:
I have an old database created in SQL Server 2000,
now I can open, access it in SQL Server 2005 Management Studio, but I can't create Data Diagrams.
Please advise on how to make it work....
Thank you
Hi,
We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.
However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors
1)
Error messages:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403)
Get help: http://help/MSSQL_REPL-2147199403
Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
2)
Command attempted:
{call sp_MSensure_single_instance (N'Merge Agent Name', 4)}
Error messages:
The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036
looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1
this is the stored procedure executed by sp_MSensure_single_instance and @retcode = -1 and thus cannot obtain exclusive lock on resource
EXEC @retcode = sys.sp_getapplock @Resource = 'Merge Agent Name',
@LockMode = 'Exclusive',
@LockOwner = 'Session',
@LockTimeout = 0,
@DbPrincipal = 'MSmerge_PAL_role'
so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources
i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work
can anyone help?
Thanks