Can someone tell me how to make a backup in a sql clustered environment (MSSQL 2000) and how to restore that backup in clustered environment (MSSQL2005)? A step by step backup and restore procedure is appreciated.
View 5 RepliesI have clustered SQL server machines(SSserver1 and SSserver2). I need to restore a database from another standalone server to this clustered environment. In this case do I need to restore the DB on both the nodes that are part of the clustering(SSserver1 and SSserver2) or Just can I do it on one of them?
View 5 Replies View RelatedI am embarking on a project to upgrade our SQL server environment. I'm curious for some opinions on the relative benefits of Log Shipping versus a Clustered environment and you have any experience that has helped form your opinion on the issue?
Thanks,
Marc
I have a two node failover cluster(Active/Passive). I am planning on installing the Report Server database on the failover instance.
Do i need to install RS on both the nodes?
thanks?
I get the following error when trying to execute a call to a procedure located on a linked server. My source server is within a clustered environment.
"Could not relay results of procedure % from remote server %."
Straight select T-SQL calls work fine.
Could anybody help?
I am looking at combining / consolidating some of our servers and was wondering if I can install SQL 2000 and SQL 2005 in a clustered Environment on a san.
Cheers
Angie
We are running 2 SQL Server and both run in failover clustered Environment.
The Problem is now we need to Replicate a Database from one Virtual SQL Sever to the other.
The Second one (clusterd environment)is stroing their database localy while the First One (clustered environment)is storing database in a shared storage.
Note that Both Server are used for sperate purpose , but we now need to set replication on the Other Clustered Setup for Reporting Purpose.
Will it work if we configure replication from One SQL Server Clustered Setup to the Other Clstered Setup.
If yes, then please let me know how it can be done ?
Thanks
Hi can anyone help with a step by step instructions on how to set up log shipping in a clustered envionment?
I aware that your need to set up a clutered share but am looking for as much details as possible.
Thank your for your help in advance.
Hello,
Yesterday, our server 'failed over' to the passive node in our clustered environment. We are running Windows NT, with MSSQL 7.0. What triggered the fail over, according to the logs, was an error in tempdb. Basically, the error said that tempdb could not be expanded, (could not create any more space for tempdb).
The drive that tempdb database is located on has more than enough room. The tempdb is set at 300M, and can grow to over 700M. The drive that tempdb is on
has over 19Gig of space on it.
One other important piece of information is that the drive where our production database is on and the tempdb is quite full. The 19 gig that is left over represents only about 5 percent of the disk.
Can anyone shed some light on why the sql server would have failed over when trying to expand tempdb, when there was sufficient resources to handle that growth? Am I barking up the wrong tree? Could this be another error? When we restarted the server (both nodes and the disk array) the production database came back as suspect. Most of the stuff in BOL points to the space issue. The production database is around 37 gig.
Any help would be appreaciated
thanks,
jim
Is it possible to use registered servers feature in clustered environment and with different versions of Sql server like 2012 & 2014.
View 0 Replies View RelatedI am running into an issue trying to uninstall SQL Server 2005 64-bit. It hangs at the same point: Removing backup files. I then get a message that, "The setup has encountered an unexpected error while Completing Commit. The error is: The cluster resource cannot be moved to another group because other resources are dependent on it." The instance that I am trying to uninstall is in a cluster hosting two other SQL Server installations. I have tried a few combinations when attempting to uninstall: running the cluster group containing the server I am uninstalling on a different cluster node than all the other cluster groups; running them all on the same node. The only one I haven't tried is moving the Cluster Group containing the IP address of the cluster, cluster name, etc to another node while running the other groups on the other node. Has anyone run into this issue? Each of the nodes has been rebooted several times during these attempts and we are using Windows Server 2003 Clustering. Again, the hang happens at the same point every time. The exact error(s) from the even log are as follows:
Product: Microsoft SQL Server 2005 (64-bit) -- Error 29528. The setup has encountered an unexpected error while Completing Commit. The error is: The cluster resource cannot be moved to another group because other resources are dependent on it.
Product: Microsoft SQL Server 2005 (64-bit) -- Error 29530. Failed to set registry settings for server network libraries. The action is SetShilohRoot. The error is 2 (The system cannot find the file specified.
)
Thanks,
Todd
I am able to send databasemail on my staging servers which are non clustered but databasemail fails on my clustered production servers.
Both production and staging servers sit on the same domain(although the domain controller is sitting on one of the staging servers)
I have set up the account and profile and have double checked that it is pointing to the correct SMTP server and to the same one as the staging servers.
I have also enabled database mail in surface area configuration.
The message gets queued, because i see the 'mail is queued' message.
The databaseMail Process also gets started, but during the actual send process I see
The following is the error in the databasemail log.
Any ideas what could be my problem?
Databasemail Log
Date 18/07/2007 18:49:17
Log Database Mail (Database Mail Log)
Log ID 14
Process ID 964
Last Modified 18/07/2007 18:49:17
Last Modified By TT-IXTOOLSixtools
Message
1) Exception Information
===================
Exception Type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
btw, I ran this command to send mail
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='mytestemail@email.com',
@subject = 'My Mail Test',
@body = @body1,
@body_format = 'HTML' ;
We will be implementing our first SQL cluster in December. Our current plan calls for a shared development/test database server with one physical server, but two SQL Server instances. Our production environment will be a SQL cluster. Is it necessary to create a clustered test environment for testing patches, hot-fixes, etc...?
Thanks, Dave
Would it be possible to disjoin the SQL Server Clustered environment to a new domain without having to reinstall the cluster?
disjoin
e.g 2 node activeactive cluster with 4 named instances. SQLserver1.dn.za; SQLserver2.dn.za; SQLserver3.dn.za;SQLserver4.dn.za
servernode1.dn.za; servernode2
re-join them as SQLserver1.dn.ra; SQLserver2.dn.ra; SQLserver3.dn.ra;SQLserver4.dn.ra
servernode1.dn.ra; servernode2.dn.ra
What would be the impact on the servers, will they be able to resolve the new dns.?
How can i add additional SQL server instance to existing clustered environment on SQL 2005.
View 7 Replies View RelatedI have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
View 3 Replies View RelatedI am trying to set up log shipping in a clustered server environment. I am pretty confused about the location of the shared folder to be created to put backup created by log shipping job. Which drive should I use either local or clustered shared drive to store the backups in primary server and to copy the same in secondary Server?
View 2 Replies View RelatedI've seen other posts on this topic, but not with error 0x32.
I was attempting to change a CLUSTERED instance of SQL 2005 from dynamic port to static port and in the process have somehow messed it up to the point it will no longer start. Version is 9.00.2047.00
The ERRORLOG has the following
2007-01-31 15:02:05.77 spid9s Starting up database 'model'.
2007-01-31 15:02:05.77 Server Error: 17182, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server TDSSNIClient initialization failed with error 0x32, status code 0x1c.
2007-01-31 15:02:05.77 Server Error: 17182, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server TDSSNIClient initialization failed with error 0x32, status code 0x1.
2007-01-31 15:02:05.77 Server Error: 17826, Severity: 18, State: 3.
2007-01-31 15:02:05.77 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-01-31 15:02:05.77 Server Error: 17120, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
The values have only been changed using SQL Server Configuration Manager (no direct registry changes have been made).
Thanks for you help.
Hello,
How do I restore a sql database that is on a clustered server from a sql database backup file that is on a non_clustered server?
Thanks,
Serey
I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)
Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.
Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.
Any advice on how to get this thing finished out?
Hello, everyone:
What is difference between testing environment and product environments for MS SQL Server? Thanks a lot.
YTZ
Hi,
I have been experiencing difficulties achieving the above.
We have a secure network environment and the connection can only be established from within, not from outside, so basically, there is a rule that allows me to go outbound on port 5022 to our other network.
There is no domain trust relationship, so I have had to create Certificates and use these as the method of authentication.
I have managed to get this working on two other environments where there is two way communication enabled on port 5022.
I thought I might be able to fool it into forcing the mirroring without two-way communication by setting up mirroring in the environment and using the hosts file to change the actual IP address of the server, this didn't work. The mirroring monitor thought that mirroring was still active, but on the other server, the state never changed and was just waiting for something to happen.
I also thought I might be able to turn the transactional safety off, but only to find out that this can only be enabled in the Enterprise version of SQL Server.
The reason why I have been trying to get mirroring working this way, is due to a requirement for an encrypted connection, I know that with SQL Server mirroring you can choose the encrpytion method and the plan was to create snapshots of the database in the other environment, just so we could access the database.
I think we're at a stage where we will be willing to explore other possibilities, so if anyone could point me in the right direction, it would be greatly appreciated.
Please bear in mind, that we're not concerned with redundancy, this is purely to get the data from one point to another.
Thanks,
Akeel.
I developed an asp.net application in visual web developer 2005 express edition and SQL sever 2005 express with Advanced services. The application has been deployed and iam wondering what tools are availabel to for backing up my data. Are there any tools i can use to back-up my database. Iam not talking of third party tools but tools a vailable in sql sever 2005 express with advanced services or visual web developer express.
OR can write a vb.net Sub procedure that i run and have my database backed up. If so where can i start or what other options may i explorer.
Hi Everbody:
I would like to know your experience about how to make regular database restore and point-in-time restore in SQL Cluster environment.
(1). My first question is about database backup. We use SQL Server backup in our shop. We hope that we can back up the database to a one network shared drive. The SQL Server can not do it. It only allows us to back up database to the local drive. Since there is no too much spaces in the local drives (C, D and E), I would like to know how we can back up to the network shared drive.
(2). A few database are critical databases which require point-in-time restore. How we can back up database (complete backup or differential backup), transaction log?
(3). If database and transaction log crash at the same time, can we still do point-in-time restore?
(4). How we do Master DB and MSDB back up? Weekly or monthly?
Thank you very much for your advice.
Joan
I've read lots about why you shouldn't normally shrink databases in posts such as this:
[URL]
But we have a situation where we are required to copy the live db to various non production environments for testing. Part of this process involves truncating a number of tables with masses of blob data. So we're freeing up quite a lot of space. The question is how to reclaim this? The database is peculiar in that it's got no clustered indexes so I can't rebuild indexes on another filegroup with drop to move and rebuild.
I've tried dbcc shrinkfile specifying a size. I've tried to shrink the file in increments. The problem is I'm just not getting much space released. I get maybe 2-3%. I suspect this is because we're dealing with heaps with some tables that have sparsely populated blob / image data.
Is there an alternative to shrinking? Should I recreate all the db objects in a new database? It doesn't matter if the process takes a while or if it has to be done manually.
How can I create a new database on a cluster sql environment? Do I need to create the database on both nodes?
Thanks in advance!
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies View RelatedI desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr](
[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
[ContactKey] [int] NOT NULL,
[EventTypeEnum] [tinyint] NOT NULL,
[code]....
I have created two tables. table one has the following fields,
Id -> unique clustered index.
table two has the following fields,
Tid -> unique clustered index
Id -> foreign key of table one(id).
Now I have created primary key for the table one column 'id'. It's created as "nonclustered, unique, primary key located on PRIMARY". Primary key create clustered index default. since unique clustered index existed in table one, it has created "Nonclustered primary key".
My Question is, What is the difference between "clustered, unique, primary key" and "nonclustered, unique, primary key"? Is there any performance impact between these?
I have large table with 10million records. I would like to create clustered or non-clustered index.
What is the quick way to create? I have tried once and it took more than 10 min.
please help.
I am new to database.
I have a website in a hosting environment and it uses sql server 2005 databases. I want to have a complete backup of my database in addition to the hosting company's routine backup. I want to write a program to automatically backup everything every night. And of couse have the ability to restored the whole database if necessary.
How should I do it? Are there any tools exist that I can use? What is the best way to deal with backup/restore in hosting environment?
Thanks!
hi,I was redirect to this URL* based on Deploying a SQL Database to a Remote Hosting Environment,pertaining to my question on how do I convert MDF file to .SQL.I had downloaded and installed the program recommended that is Database Publishing Wizard.but the problem is now, there isn't any context menu "Publish to Provider" * when i right click on thedatabase aspnetdb.mdf..please advice me. thanks.* Reference : http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
View 4 Replies View RelatedI have been looking for some documentation that would support or rejectmy opinion on Production -vs- Development naming conventions. Ibelieve that each environment should be housed on separate servers withidentical names, access, users, stored procs....... If you eitheragree or disagree with this methodology, I would appreciate your input.TIA,Bill
View 5 Replies View Related