Need to know proper procedure for moving our NT/SQL 6.5 server to new IP subnet. Re-IP'd the server, moved and fired it up SQL Server gets:
DBLibrary - Server is unavailable or does not exist...general network error.
What needs to be done at the SQL level to complete the move?
Thanks.....
We have a SQL 2014 AlwaysOn availability group running on two Windows 2012 R2 servers that are in the same subnet. We created a new server in a second subnet, installed SQL, joined the server to the Windows cluster, added a new IP resource for the new cluster, and performed the other remaining steps to add a new AG replica to the SQL instance on this new server. When we try to move the core cluster resources to the new node to test failover, we get an error. Here's the command we've been using:
Move-ClusterGroup "Cluster Group" -Node node3
and it returns the error: The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group...I've checked the ownership of the cluster groups and the cluster resources and it looks like they are set appropriately:
>Get-ClusterGroup | Get-ClusterOwnerNode
Cluster Object Owner Nodes
---------------- ---------------
Available Storage {}
Cluster Group {node1,node2,node3}
SQLAG {node1,node2,node3}
[code]....
We've double-checked that all IP resources are in the right subnets and that the dependencies for the Cluster Name resource and the Listener Name resource are set appropriately. I'm not sure what else to check since the PowerShell commands seem to indicate that node3 is an owner of the appropriate resources. What other things need to be checked or if the ownership being checked isn't the same as what PowerShell is checking?
I am working on a app that stores IPs in a sqlsvr 2k db.. all is well and good but when doing some reports on the data, I need to include/exclude certain ip ranges/subnets. Is there an easy way to do this, is there an IP between function that I might not know about? Some like where IP in "127.144.4.0/24" or where ip between x and y? Otherwise I will have to break apart the ip into maybe its class c/d and convert it to a number and do some boolean >< logic on it. Thanks,BD
View 1 Replies View RelatedHi,
I need to develop an application where my web server is in different subnet (XXX.XXX.35.XXX) and SQL server 2005 on other subnet(XXX.XXX.46.XXX). But i could not connect to it. When I connect to remote server on same subnet the exception is not thrown. But an arror specifying(could not connect to remote server) if i connect to different subnet.Can any one help me please?
hi :
I have a SQL-Server on different subnet and I want to access it, I have all rights on that computer , but cant access on my local computer (SQL SErver) which is insided company's network... Is there a way i can link the server? or ?
thanks for the help
aakash
According to Microsoft, if you need to change the subnet for the clustered servers, you will need to either edit the registry or reinstall virtual SQL Server.
Does any one know which registry key(s) store the subnet information? I was reviewing the clustered key but was able to find the key for the IP address only.
Thanks ...byyu :D
-- Prepare sample data
DECLARE@Sample TABLE (ID INT, NetworkIP VARCHAR(15), SubnetMask VARCHAR(15))
INSERT@Sample
SELECT1, '192.168.1.0', '255.255.255.128' UNION ALL
SELECT2, '10.1.1.64', '255.255.255.240' UNION ALL
SELECT3, '172.16.11.0', '255.255.255.252'
-- Show the expected output
SELECTID,
NetworkIP,
SubnetMask,
dbo.fnIsInRangeIP('192.168.1.5', NetworkIP, SubnetMask) AS IsInRange
FROM@SampleAnd here is the function codeCREATE FUNCTION dbo.fnIsInRangeIP
(
@IP VARCHAR(15),
@NetIP VARCHAR(15),
@MaskIP VARCHAR(15)
)
RETURNS BIT
AS
BEGIN
RETURNCASE
WHENCAST(PARSENAME(@IP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT) = CAST(PARSENAME(@NetIP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT)
AND CAST(PARSENAME(@IP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT) = CAST(PARSENAME(@NetIP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT)
AND CAST(PARSENAME(@IP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT) = CAST(PARSENAME(@NetIP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT)
AND CAST(PARSENAME(@IP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT) = CAST(PARSENAME(@NetIP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT)
THEN1
ELSE0
END
ENDE 12°55'05.25"
N 56°04'39.16"
Hi Folks,I'm very new to SQL Server. I have a home network. My primary connectionto the internet is using "Gateway 1" a linksys router, of which, my serveris a LAN node on that router. I have another LAN node connecting to the WANnode of the "Gateway 2" a wireless USR router to use with my laptop. Usingthe wireless laptop, I am on a different subnet than the Server. I'd liketo make a connection to the server through Visual Studio.net but I can'tseem to do it. I'm using TCP/IP with a port different than the default port(let's call it port 2110, even though it's not). I do have port forwardingon Gateway1 and I've tried everything from just the server internal IPaddress, the external IP address, IP address with port designation, URL withport designation, nothing seems to work. Any ideas?Thanks!Rick
View 1 Replies View RelatedI have setup two node SQL 2012 Always ON cluster with file share witness. I have configured two networks, one for heartbeat and second for public.My question is do I need a third network/ subnet for AG Listener? Which network does the SQL user databases in the AG group use to replicate between the replica servers?When I do the SQL backups, do I need to backup primary and secondary replicas or just backup the databases in the secondary replica?
View 4 Replies View RelatedWe are running SQL Server 2012 Enterprise on Windows 2012 R2. We have set up A WSFC with the primary Node in our East Coast Data Center and the Secondary Node in our West Coast Data Center each node is in a dfferent subnet. Since we have high latency between the sites we run in Asynchronous mode with manual failover only. My quesion concerns Quorum, everything I read would indicate we need a third node (odd number) (e.g. a fileshare Witness) with only the file share and the primary node having a vote...What I don't see is any need for a file share if we can only do manual failover.
View 7 Replies View RelatedWhen I fail an availability group between subnets, I am finding that the DNS entry in DNS is staying. So what happens is the Availablity Group listener has 2 records in DNS, one for each IP. This causes the App to timeout at times, since DNS will return either of the two IP's.
View 9 Replies View RelatedOS - Windows 2012 R2 Standard Edition.
DB - SQL 2012 Enterprise edition
Total 3 nodes participates for AO setup, 2 Nodes for Local HA and 1 Node for another datacenter for DR. All the 3 Nodes are same domain name and member.
1. Local First 2 Nodes are same subnet XXX.XX.44.XX
2. DR Node another subnet XXX.XX.128.XX
Does it require to add two different IP address while creating cluster name? Not using shared disk SAN storage etc.. I am using Node majority quorum witness setting for failover.
I have a web server that is on our DMZ subnet and I have a sql 2014 database server with Always on Availability Groups that is on our internal domain subnet.The failover cluster is on the internal domain subnet as well. I am trying to connect a web application, that is working off the .net 2.0 framework, to an SQL Server 2014 database using the Always on Availability Group Listener.
The connection strings works perfect with the named instance of the sql server but when I try to use the Availability Group listener it timed out. I did some research online and was led to believe that increasing the connection time would resolve it. I did that and got a different error pertaining to a successful connection but an error occurred during the login process (Provider: TCP Provider, error: 0 - The specified network name is no longer available.)
I just want to connect to an Availability Group Listener on the domain subnet. I have rules in the firewall to allow all services for the IP of the Listener. I switch back to the named instance of the same server and it works flawlessly. I have also connected to the Listener while running the application on the same subnet as the sql server without an issue. I have tried the IP instead of the Listeners name and still received the same error on the DMZ subnet. I'm running on Windows Server 2012 R2 on all servers.
The Web Server is using IIS 8.5..All Physical Machines except the replicas and webserver are virtual machines.All Static IPs involved.As mentioned all SQL Servers are 2014.
I am trying to build out an AlwaysOn AG with 2 nodes each in a different subnet (in AWS if that matters), windows 2012r2 / SQL 2014 RTM
I created a AG Listener with 2 ip address, 1 for each subnet (checked that neither ip address are used). But whenever i failover the AG to the secondary, and try and connect via the listener it fails,
I am trying to connect via SSMS from the primary instance. and just time out, If i roll over to the primary i can connect no issues, I've tried playing with the connection settings, upping the time out to 30 secs, adding the MultiSubnetFailover=true. etc but not getting any joy.
I am setting up SQL 2014 always on. I was able to set up the replicas between 2 servers in the same subnet.Their IP addresses are say like this:
100.20.200.200
100.20.200.201
When I am trying to introduce another node into the cluster which has IP address like 100.10.101.102, I am getting an error that the server isn't reachable.
How do you move a database from lets say E drive to G drive on the same server? Any ideas.
Regards,
I would like to know what is the best way to move all the data from a DB to a new database. Some of the tables has different names, and attributes, so is there a way to create a script or something that will do that.
Both databases are in the same server.
I’m working with an msde database on my local machine, but need to take that database and move it to my remote host. This is problematic because I can not just do a back up and restore.
I was wondering if there were any asp.net applications that would generate lots of sql statements to reproduce the database on my remote host.
I had built a program to back up all the tables in that way, but, forgot that the membership stuff uses stored procedures, and I don't not know how to modify my program in order to back those up too.
Any help will be much appreciated, thank you very much for reading my post
Hello,
What is the easiest way to move a database from one server to another with the all table structures, view and stored procedures?
Thanks,
Can someone tell me how to move the log file for a database from one drive
to anoyher. I want to keep the data on one drive and the logs on another
so I need to move the log files to another drive.
Thanx, Kelly
Without restoring the database, has anyone written a slick little piece of code that moves a file existing on on filegroup to another newly created filegroup without doing it from a restore.
The purpose of doing this is to get rid of one data file and placing the data into the data file we are retaining. We have some more physical file space and are moving data into one data file and one transaction log file.
We have already ran the DBCC SHRINKFILE with EMPTYFILE to move the data,
We have already ran the ALTER DATABASE statement but here, because the filegroup is identified as PRIMARY in the sysfilegroup table, we can not REMOVE the data file because of indexes relating to the PRIMARY filegroup.
So if I could ALTER DATABASE and create a bogus filegroup and move the data file which I want to get rid of into the bogus filegroup, I could ALTER DATABASE with REMOVE filegroup and solve the situation.
Does anyone know of an easier way to do it other than BACKUP database and RESTORE database.....please help!!!
Thanks,
Daimon
Using sp_detach and sp_attach we can move different databases to different locations.
how do we do the same for system databases such as master,model,msdb and tempdb.
As off i know we got to be in master database to runthis command, if not this how do we go about this.
Thanks,
sheena
We will move t-log from one disk to another one. I tried Alter Database and found it did not work for log.
Can somebody give me a method about moving log from different disks?
Thanks.
Jean
I read the one of the replies to a question.
"Subject:
From:
Date: SQL 7 Restore (reply)
Manish (manish45@yahoo.com)
11/1/01 9:03:43 AM
If you want to move all the databases to new server. Stop the SQL server service on existing server. Copy mdf and ldf files of all databases to same location iin new server. Also take the backup of master and msdb database from existing server. Then restore master and msdb on new server.
"
My question is how to move my database to a DIFFEREN LOCATION on a different server. My two servers are different file layout (not my choice though.)
Thanks a lot in advance.
Wuchun
P.S. Should I use the DTS at all to perform this task?
Hi:
I need your suggestion or experience about how to move a database from SQL 6.5 to SQL 7.0. The server where SQL 6.5 resides locates in different network than SQL 7.0. DTS does not work for the database move because of two server can not talk directly.
We need to do this task very soon in production environment. I tried to backup database to a disk (backup device in SQL 6.5) but I failed to restore it in SQL 7.0 because the backup file was not detected by restore utility in SQL 7.0.
Please tell me what I should do. Besides BCP, are there any other methods to move the database from SQL 6.5 to SQL 7.0 in diffferent networks.
Thank you very much.
Joan
Does anyone know how to move the transaction log(s) of a LIVE database to a new location. I must move the log of a database to a new mirrored drive without any disruption to users. I cannot take the database offline or use the sp_detach_db stored procedure. Your inputs are much appreciated!
View 1 Replies View RelatedHow do I move all the databases to a new drive in the same machine?What effects/problems will be?
Thanks!
Hi
I am new to sql server 7 and dont know how to 1.make a copy of a database
and 2 to move a database i know there are sys stored procedures but the syntax looks crptic and i dont know where to type them in if any one could give me any really clear examples of how to do both of the above i would be really grateful, thanks!
What's the easiest way to move a 7.0 log file to a new physical location?
View 2 Replies View RelatedHi there,
Does anyone know how to move tempdb (mdf and ldf files) to another physical disk of already instaled SQL Server 2000? (The idea is to move the tempdb to another dedicated disk in order to improve the performance.)
Regards,
Azv
Next month client is moving servers to new location. The servers will get new ip and subnet Our user db's which are on a SAN will be moving to new SAN. My plan is the following, please correct any mistakes.
1. Do full backups of everything
2. Detach user db's
3. Copy these files to usb box (100 gig worth)
4. After server is in new world bring it up
5. Copy db files from usb
6. Attach db files
We have to recut two NT Servers that have SQL 6.5 on them. After recutting them and installing SQL 6.5 I will recreate my devices and then my Databases. If I use the sql dumps to restore the data bases
should I import my logins through the SQL Security manager first and then restore the dumps. If someone has done this or has a better approach it would be great help.
i've got to move 37 SQL Server databases over to new server. My manager tells me that i have to conduct some "check totals" to make sure that the databases contained exactly the same data from what it was when it got moved.
Is there some stored_procedure to do this ? to make sure that all the stored procedures, views, tables got move across ?