SQL 2012 :: Removing IP Address From Listener Pool When Demoting One Of Remote Sites

Aug 15, 2014

We have a multi-site AG and are demoting one of the remote sites out of the AG. In doing so, we've discovered that no logic exists to remove an IP address from the listener.

It seems that the ALTER AVAILABILITY GROUP MODIFY LISTENER logic lacks functionality other than ADD. [URL] ....

I'm afraid to just remove the IP address from the cluster object as that IP is also stored in the HADR systables.

select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners

View 1 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Application Connecting To AG Listener Static IP Address - Not Name

Jul 22, 2015

We have a bought in application written in Visual Basic 6 using ADO 2.7 to connect to SQL Server. The application has two connections strings and uses internal logic to determine which of the two connection strings connects to a read-write database. It does something as simple as trying an update that makes no changes to a very small table somewhere.

Previously this application connected to a Mirrored Database hosted on two Windows Server Failover Clusters and SQL Server 2005.For the future the App will be connecting this application to a SQL Server 2014 Availability Group.The Availability Group is made up of four servers, in a cluster, two at one site on Subnet A and the other two at a second site on Subnet B. The Availability Group has been given a Listener with a DNS name of AGLISTEST on port 1433 with two Static IP addresses, one for each subnet.

Our Contractor has configured the Application’s connection strings to connect to the two Static IP addresses assigned to the AG. He is of the opinion that although ADO has zero understanding of an Availability Group, at least one of the Static IP Address will translate to the Availability Group primary server because it is a Cluster Resource and so act like a Cluster IP Address on Windows Server Failover Cluster; i.e. sort of mimicking the previous configuration. This does work and he has demonstrated it, however I can’t find any article that proves to my satisfaction this is intended behavior not something that accidentally works. If it is accidental behavior then that would be something that might stop working at an unexpected point in the future.

All the research I have found relates to connecting to the AG Listener name, and all the multi-subnet issues and Active Directory issues that go with connecting using what is a legacy client.So; the question is, is this translation / redirection of static IP Addresses assigned to an Availability Group listener documented and safe?

View 3 Replies View Related

SQL 2012 :: Replication Between Different Sites And Domains

Jun 10, 2015

Can we setup a replication ( publisher and subscriber are Sql server) between two different domains, basically different company databases.

View 1 Replies View Related

Recovery :: Unable To Connect To Availability Group Listener From Remote Server Without Using FQDN

Jul 21, 2015

Environment: SQL Server 2014 on Windows Server 2012 R2.

We have our availability group configured and working.  However, when we try to connect to the AG listener from a remote server, we have to use the fully qualified domain name (FQDN) to connect.  We'd like to be able to connect just using the host name.  Interestingly, ping actually resolves the IP correctly for either.

View 3 Replies View Related

SQL 2012 :: Enable / Disable AG Listener

Jul 3, 2014

How can one enabledisable Availability Group listener? I know AG listener can be deleted and added but didnt find anything to disable or enable that.

View 1 Replies View Related

SQL 2012 :: Cannot Connect With Read Only Intent When Using Listener Name

May 6, 2014

I have an issue when connecting with application Intent=readonly when using Listener name. I can connect to the read only secondary with application Intent=readonly if I use the server name. It gives a host does not exist error when using listener. This happens only if I am using the application Intent keyword. I can connect for normal readwrite operation using listener name.

View 3 Replies View Related

SQL 2012 :: Using Availability Group Listener For UNC Connection?

Sep 10, 2014

I have set up a couple of servers in a SQL 2012 AlwaysOn Availability Group (non FCI). I have also configured a Listener which enables SQL clients to connect to the server currently servicing the database, as expected.

I would also like non SQL clients to be able to connect to the server currently hosting the database so that they can run scripts sitting in a share. I don't have a shared disk so just have a directory share on each server with the same scripts in each directory.

I am able to ping and RDP to the listener IP address/name and end up on the correct server but am unable to connect to the share ListenerNameShare. Is that actually supported? If it is, any thoughts on what I need to do to get it going. If it isn't what other options do I have?

View 0 Replies View Related

SQL 2012 :: Availability Group Listener Port

Oct 26, 2014

So I have Availability groups configured as well as the Availability Group Listener, what If I want to change the port that the listener is listening on, do I need to reboot the server or is it dynamic across the board ?

View 5 Replies View Related

SQL 2012 :: Error At Adding Listener For AlwaysOn

Jun 11, 2015

I am trying to setup an AG on a cluster, I was able to define where I want my replica, but I need to setup the ip for the listener of that one

When I am adding the Ip at the listener I am getting the following message:

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. For information about this error code, see "System Error Codes" in the Windows Development documentation.

The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)

Browsing around I found this guide: [URL] ......

And I make sure that the cluster has permissions to "Read all properties", "Create Computer Objects", even to "Create all child" permissions

I also added those permissions to the account that starts sql at that cluster and I am logged as that and I am still getting the error....

View 4 Replies View Related

Cannot Allocate More Connection.connect Pool Is At Maximum Increase Max Pool Size

Feb 21, 2007

hi i'm having this error on my application"cannot allocate more connection.connect pool is at maximum increase max pool size" the proble is when i do testing this error does not apply it only Appears when the application is been used by many people
How can I resolve this?
Thanks

View 1 Replies View Related

SQL 2012 :: Backups And Buffer Pool

Jan 23, 2015

I am sure I have seen in the past in a monitoring tool that PLE drops off to 0 whenever we do a backup. I was doing some reading around this however and found something that said backups use a different portion of memory external to the buffer pool (minmax settings).

Is this correct and how can I tell how much memory will be required for a backup?

View 2 Replies View Related

SQL 2012 :: How To Find Max Pool Size

Aug 10, 2015

How to find max pool size in sql server 2012?

View 2 Replies View Related

SQL 2012 :: AlwaysON Multi Subnet Cluster / AG Listener

May 3, 2013

When 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 Related

SQL 2012 :: Find When Database Was Last Removed From AlwaysOn Listener?

Feb 25, 2014

How to find when (date/time) a database was last removed from AlwaysOn Listener?

Is there any query (DMV) to find the details easily?

View 1 Replies View Related

SQL 2012 :: AlwaysOn Availability Group Listener DNS Resolving To DR Node

Jan 19, 2015

We are rolling out the use of Availability Group listeners to our SQL Server 2012 Environment which has a 2 node multi-subnet cluster. The Primary is R/W and the Secondary is a non-readable node that would be manually failed over to in a DR scenario

I have set up the AGL and asked the sysadmins to create a DNS record in both subnets with fixed IP's.

The issue I have having is that when I ask the app developers to connect to the databases using the AGL it is totally random whether the AGL resolves to the Primary or DR node - as a result that are having problems getting their apps to connect.

I was thinking of asking the sys admins to remove the DNS record in the DR subnet and then add it back in should we need to fail over - but I was thinking there must be a better way.

View 3 Replies View Related

Specifying A Address ( IP Addres + Path ) To A Database On A Remote Computer In A Connectionstring

Jun 22, 2007

All of the books I have about Visual Basic 2005 Database Programming, gives examples of connecting to a database on a localhost computer. But what about the connection to a database on a sql server 2005 on a computer wich can only be reached via the internet ? Is that possible without using ASP.NET ?



My problem:



I have a desktop with a sqlserver 2005 database . This desktop is connected with the internet . The IP address is a dynamic IP address.

On my laptop is a ADO.NET 2.0 program that needs to be connected with the database on my desktop.

What kind of address do I have to specify in my connectionstring in order to get connected to the database on my desktop , which can only be reached via the internet ?

I'm I wright to suppose that in the case of a static IP address, all I have to do is put the IP address in the connectionstring in order to get connected with my desktop via the internet? Are there some changings to be made in the settings of the SQL SERVER 2005 on my desktop ? What about the security ?

On the other hand , because the address of my desktop is a dynamic IP address, how do I solve this problem.?

I've heard about a program DynDns that can solve the problem of the dynamic address. Can i use this program together with my ADO.NET program to solve the problem , and how do I specify in this case the address to the database on my desktop in the connectionstring?

Is there someone who has experience with that program and the problem I described here ?

Thanks for the help !!!

View 1 Replies View Related

SQL 2012 :: AlwaysOn Avail Group - Prevent Login Direct To Server Instance Only Through Listener?

Sep 30, 2015

Is it possible to ONLY allow a login to the availability group listener, but block logins to server instance/nodes?

So: MySQLServerA and MySQLServerB are in an avail group "MyAvailGroup".

I want users to login to MyAvailGroup's listener, but I do NOT want them to login to the actual hosts/nodes directly.

Is that possible?

View 0 Replies View Related

CONNECTION STRING TO A REMOTE SERVER WITH SQL EXPRESS 2005, FIXED IP ADDRESS

Jan 28, 2008



I'M HAVING AN ISSUE, UNDERSTANDING, THE CONNECTION STRING.
I WANT TO CONNECT TO AN INSTANCE OF SQLEXPRESS ON A REMOTE SERVER WITH A FIXED IP ADDRESS
THE TCP PORT IS OPEN TO 1433
I OPENED THE PORT ON THE ROUTER AND THE WINDOWS 2003 FIREWALL

MY CODE IS AS FOLLOWS:
S = "Provider=SQLNCLI;"
S = S & "DATA SOURCE=44.66.777.888,1433SQLEXPRESS;"
S = S & "INITIAL CATALOG=TESTDB;"
S = S & "Persist Security Info=false;"
S = S & "UID=TEST999;"
S = S & "Pwd="TEST999888;"
CnMgt.ConnectionString = S
CnMgt.Open S

I'VE FOLLOWED THE STEPS OUTLINED IN ARTICLE 914277

CAN SOMEONE HELP?
THANK YOU


PS. IF THE CLIENT IS LOCAL, I HAVE NO ISSUE OPENING THE DATABASE.
I DO NEED TO OPEN THE DB FROM FROM CLIENTS.

View 5 Replies View Related

Timeout Expired. The Timeout Period Elapsed Prior To Obtaining A Connection From The Pool. This May Have Occurred Because All Pooled Connections Were In Use And Max Pool Size Was Reached.

Feb 11, 2004

What does this error message imply?

View 3 Replies View Related

SQL 2012 :: Replace Statement On IP Address

May 19, 2015

I am having trouble using the replace statement on IP Addresses. I want to replace the ending with ".0" - not matter what the IP Address starts with.

175.139.45.127
175.139.45.12
175.139.45.1
10.10.10.100
10.10.10.10
10.10.10.1

View 9 Replies View Related

SQL 2012 :: Extended Events And Client IP Address

Jan 14, 2015

I want to gather (and filter by) client ip address in an extended events session. So far, I have the sqlserver.login event added, but I can find no way to get client ip address added to the action or predicate.

I can filter by the pred_source field "session_nt_domain" or "nt_domain" but this is not always populated by all logins and also, this domain only lists machine name, such as FRED, as opposed to the full domain FRED.foo.bar.

Is it possible to gather login client ip address in an extended events session? Surely internal SQL Server processes have access to it because the login audit can populate client ip address.

View 1 Replies View Related

SQL Server 2012 :: How To Get Email Address From Varchar Column

Mar 11, 2014

i have a situation where is need to get email address from a varchar column. Here is some sample data for five records; The format can be change.

dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk

Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;

dummy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;

dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;

dummy text;
t: +11 (0) 222 222 2222;
e: myemail@test.com;
w:http://www.yahoo.com/faqs;

View 4 Replies View Related

SQL 2012 :: Removing DB File

Sep 23, 2014

Getting below error while ran EMPTYFILE.

DBCC SHRINKFILE: Page 10:6521 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 1

Cannot move all contents of file "tempdata" to other places to complete the emptyfile operation.

View 3 Replies View Related

SQL 2012 :: Splitting Non-uniform Concatenated Address Column Into 2 Different Columns

Jan 13, 2015

I have a very interesting problem in T-SQL coding for which I can't figure out the solution. Actually there is a Line_1_Address column in our data warehouse address table which is being populated from various sources. Some sources have already concatenated house number + street address fields in the Line_1_Address column whereas one source has separated columns for both data fields.

Now I'm trying to extract data from this data warehouse table and I need to split the house number from street address and load it into separate columns in my destination table. In case there is no data for house number then I should load it as NULL.

The issue is that data in this Line_1_Address column is very inconsistent so I don't know which functions to use. Here is some sample data for your consideration:

Line_1_Address
101 E Commerce ST
120 E Commerce ST
2 Po Box
301 W. Bel Air Ave
West Main Street, PO Box 1388

[Code] .....

View 6 Replies View Related

SQL 2012 :: Removing Columns That Are Filtered?

Aug 5, 2015

I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?

SELECT DISTINCT
RG.ResNumber,
ResWithSupp.SupplierID,
ResWithSupp.ServiceType,
RG.CustomerID,

[code]......

View 4 Replies View Related

SQL 2012 :: Join Removing NULLS That Need To Be Displayed?

Apr 22, 2014

I have a table named item that looks like this:

PartNumberDescriptionI.DateCreated
1A1XXX ALPHA1 1/1/2014
2A2XXX ALPHA2 1/5/2014
3A3XXX ALPHA3 1/12/2014
1B1XXX BRAVO1 1/22/2014
2B2XXX BRAVO2 1/28/2014
3B3XXX BRAVO3 2/2/2014

And a second table named orders that looks like this:

OrderNoPartNumberQtyO.DateCreated
1CHXX1 1A1XXX 2 1/8/2014
1CHXX2 2A2XXX 3 1/8/2014
1CHXX3 2A2XXX 1 1/20/2014
2CHXX1 1B1XXX 4 2/10/2014
2CHXX2 2B2XXX 2 2/10/2014
2CHXX3 2B2XXX 1 2/22/2014

I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:

SELECT I.PartNumber, I.Description, I.DateCreated
FROM item I
CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated
FROM Orders O
WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O
PartNumberOrderNoO.DateCreated
1A1XXX 1CHXX1 1/8/2014
2A2XXX 1CHXX3 1/20/2014
3A3XXX NULL NULL
1B1XXX 2CHXX1 2/10/2014
2B2XXX 2CHXX3 2/22/2014
3B3XXX NULL NULL

View 4 Replies View Related

SQL 2012 :: Removing Joins And Execution Time?

Jun 13, 2014

I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?

I am getting same result set.

View 6 Replies View Related

SQL 2012 :: Removing Additional Unwanted Log File?

Jul 6, 2015

I wanted to remove an extra transaction log file that was no longer required, and ran the following against the database...

DBCC Shrinkfile (DB_Name_log2, Emptyfile);
go
alter database [Db_Name]
remove file DB_Name_log2;
go

I got a successful removal message. But if I go into the properties of the database, and click on files, it still shows up. Why is this and how can I get rid of it?

It shows up in sys.master_files as offline.

View 4 Replies View Related

SQL Server 2012 :: Removing Database Name Prefix From Tables

Aug 21, 2014

When I create a table in sql server database name appears in prefix of table.

My database name: Digitall

My table name: Digitall.News

How can I remove Digitall prefix from my tables?

View 1 Replies View Related

SQL Server 2012 :: Removing Character (Letters Only) From A String

Dec 19, 2014

I need to build a query, or use a function to removing alfabetic Character from a string.

My string is
WI14000323-0003

The value i want to keep is : 14000323-0003.

I try using this SELECT:

SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable

but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.

View 2 Replies View Related

SQL Server 2012 :: Removing / Deleting Data From 2 Tables

Mar 11, 2015

I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -

SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml

[code]...

View 2 Replies View Related

SQL 2012 :: Removing Consecutive Characters From Middle Of A String

Apr 14, 2015

I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?

Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.

I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.

View 9 Replies View Related

SQL Server 2012 :: Removing Greek Characters From A String

Sep 17, 2015

I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.

DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters

In this case, the "α" is being replaced, but so are "a" and "A".

I have tried changing the datatype from varchar to nvarchar and also changing the collation.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved