SQL Server Admin 2014 :: Rebuild Indexes On Listener

Jul 27, 2015

Do I need to rebuild my indexes on my High Availability listeners?

When I do a full index rebuild on my primary DB's. Does rebuilding also send the rebuild to the listener(s)?

View 1 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: DNS Pointing To A Listener

Jun 25, 2014

In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?

View 1 Replies View Related

SQL Server Admin 2014 :: Monitor AG Listener Remotely?

Jul 28, 2015

I need to check connectivity to AG listener remotely and log that to a table/file. What is the best way to do that? Powershell?

View 4 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Listener On Multi Subnet AG

Feb 16, 2015

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.

View 2 Replies View Related

SQL Server Admin 2014 :: Does Each Node Need To Have Same Instance Name For Listener To Work?

Apr 15, 2015

Say I have 2014 AlwaysOn setup and working:

SQLNode1 ->Instance01
SQLNode2 ->Instance02

When I setup my listener: ListenerA...Do I need to use the instance name in it?

ListenerAInstance01 or ListenerAInstance02 depending on which SQLNode is the "active" availability group?

Am I better off to use the same instance name for both nodes, since my goal is to have all databases on both instances in the same availability group and sync'd? When SQLNode1 migrates over to SQLNode2 I will need to update the instance name in my connection string on the listener from ListenerAInstance01 to Instance02? When I connect with SSMS do I just use: ListenerAInstance01 (or 02)?

View 3 Replies View Related

SQL Server Admin 2014 :: Availability Groups - Adding One More Listener

Sep 1, 2015

We have a server A and a server B setup with SQL2014 High Availability - working fine.

I need to add an additional listener to our new DR site.

What is the best way to do this without impacting production - can I just add the additional replica from nodea as a secondary replica?

View 1 Replies View Related

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 Server Admin 2014 :: Read Only DNS Listener Failover Automation From Primary To Secondary

Mar 23, 2015

I have a 2 node cluster with 2 standalone 2k14 instances having alwayson setup. As per client requirement we have created a client access point with a cname alias in dns to connect to secondary replica. Now, everytime whenerver the roles switch over one has to manually move this resource from the previous secondary node to the new secondary node. This is tedious, and should not be done manually either, so I am looking for a way to automate it so that as soon as the role switches over, the resource group after some time should also switch over to the current secondary.

Env details
xa (current primary)
y2a (secondary)

client access point (resource group): aglreadtest

View 2 Replies View Related

SQL Server Admin 2014 :: Disconnected From Listener In Availability Group (When Primary Failed)

Jun 27, 2015

I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22

I also define an listener . IP .. 23

1- In First step I connected To Listener (23) And in a while I inserted A record to a table .

While 1=1
insert into Tbl_T1(f1,f2) Values (1,2)

2- in second, I Stop the primary .

- I expected this while whitout disconnect, continue.

3- The while code stopped whit this message :

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

4- I execute again the script, And it worked in new primary.

My questions :

1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?

2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :

Msg 9002, Level 17, State 2, Line 27

The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.

Is this (Fill All space) a reason to switch primary? Or not ?

View 2 Replies View Related

SQL Server Admin 2014 :: Rebuild CMS Tables From XML File?

Jul 23, 2014

I had to reinstall my local copy of SQL a few weeks ago, which naturally overwrote the

msdb.dbo.sysmanagement_shared_server_groups_internal and
msdb.dbo.sysmanagement_shared_registered_servers_internal tables.

However I still have the local XML file that SSMS reads so I can still access the groups, I just get weird errors when trying to re-register my install as the new CMS. How to rebuilt those tables from the XML file or know of a way to repopulate?

View 3 Replies View Related

SQL Server Admin 2014 :: Update Stats After Rebuild And Reorganize Index

Jun 26, 2015

We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions

1 do we need to update stats after we rebuild an reorganize index.
2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?

View 2 Replies View Related

SQL Server Admin 2014 :: Rebuild Index Disk Space Requirement?

Sep 15, 2015

I'm trying to determine how much space I would need for my data drive and log file drive to do index rebuild. I have a database which is 100gb, it is in simple recovery mode. let me know what to have a look at to determine how much space.

View 7 Replies View Related

SQL Server Admin 2014 :: 10gb Size Of Index Rebuild When Space Left Overall 5gb

Mar 2, 2015

I have 10 Gb index and disk space only left 5gb .

How can i rebuild index ?

View 4 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.

In order to do that, I have taken the records based on the fragmentation %.

But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 Replies View Related

SQL Server Admin 2014 :: Backup Of Indexes For A Particular Table?

Aug 9, 2014

how to take backup of indexes for a particular table.

View 1 Replies View Related

SQL Server Admin 2014 :: Backup Of Indexes For A Particular Table

Aug 9, 2014

How to take backup of indexes for a particular table.

View 5 Replies View Related

SQL Server Admin 2014 :: Indexes With More Writes Than Reads

Jul 17, 2015

I have inherited a database that is over-indexed, i.e. there are sometimes 10-20 indexes on a table. The performance is at times not great due to blocking from long running queries. I want to clean up the indexes as a starting point.

Through a query I found some time ago on the SQLCat blog I have discovered a large number of indexes in the database that have a huge disparity between reads and writes. The range of difference is sometimes almost 2 million more writes than reads. Should I just drop the indexes that have say, more than 100,000 more writes than reads and then see what the Missing Index DMVs tell me after a few days of running without those indexes?

In some cases there are a few hundred thousand reads but maybe a million writes on the index. Thus, there are a fair number of reads happening, just not in comparison to the number of writes. In some cases there are almost no reads and a million or more writes. I am obviously dropping those indexes. I just am not sure what to do about the indexes that do have a fair number of reads.

View 9 Replies View Related

SQL Server Admin 2014 :: Column Store Indexes Ignored When Run Test Queries

Aug 25, 2015

I had an existing table with lots of indexes.

As a test (fro speed) - I added a non clustered column-store index.

When I run test queries it always ignores my new column-store index. Why?

Should I remove the old indexes, leaving just the column store?

View 2 Replies View Related

SQL Server Admin 2014 :: How Do Physical Data Files Grow When Rebuilding Indexes

Feb 2, 2015

I've been trying to get a definitive answer to this question but alas I have conflicting and patchy answers so far from other sources. I have an index that, lets say, requires 10GB of data space to rebuild..This index resides on a filegroup that spans 2 files on two seperate drives (i.e. a mdf and ndf)

When I rebuild this index how will each of these datafiles grow as the rebuild proceeds to completion? Lets for the time being remove the caveats of any other activity hitting the example index/database in question.My tests seem to show that only the mdf will grows (or the file with the lowest id in the that filegroup) provided there is enough space available in that particular file to complete the operation. The secondary ndf dat file doesnt grow at all if the mdf has enough space.

Is expected behavior? i.e. the index will be rebuilt in a contiguous manner relative to the files contained with the filegroup i.e. fileid 1 will grow till limit reached then next fileid grows etc?

View 0 Replies View Related

Indexes Need Rebuild After Restart Server???

Oct 4, 2007

Hi All.

Our inhouse app used to run on a SQL2000, but we've recently moved it to 2005.
The move was done by way of backup and restore (it was on a whole new server).

After the move, an odd problem showed up: once in a while, the server seems incapable of finding/using its indexes: everything starts working slowly, until I run a maintenance plan that rebuilds its indexes etc.
In the database/server all relevant options seem to be ok (auto update statictiscs etc.), and my conclusion that it doesn't use its indexes comes from the fact that:
* it gives the results from certain select statements in a totally different order (although the set of rows is the same),
* performance is (all of a sudden) very slow (seconds turning to minutes!!)

This happens:
* at least after a reboot of the server
* sometimes just in the middle of the day

The only way I've found to solve the matter, is by running the maintenance plan to rebuild the indexes, but sometimes this only seems to work the second time.


Does anybody share this experience, or know what to do about it?

Thanks,

Nick.

View 2 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 Replies View Related

SQL Server 2008 :: Update Statistics On Tables Of Database After Rebuild Indexes?

Nov 5, 2015

If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?

Also, How can I see if I Need to update statistics^on the tables of my database?

View 3 Replies View Related

Rebuild Index In Server 2014 Log File Increased

May 13, 2015

i have add maintenance plan rebuild index in sql server 2014  log file  increased very tremendous space.

View 6 Replies View Related

Rebuild Indexes

Oct 8, 2001

Hi All,
SQL 7 REQUIRES THE DBA TO MANAGE THE INDEXES AND IN PARTICULAR THE FILL FACTOR AVAILABILITY. DOES SQL SERVER 2000 AUTOMATICALLY ADDRESS THIS OR DO WE STILL HAVE TO PERIODICALLY RUN,

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

TO REBUILD ALL INDEXES IN THE DATABASE?
Thanks in advance,
-Praveena

View 1 Replies View Related

When To Rebuild Indexes

Apr 1, 2008

Hi!

I'm not a dba at all, just a simple developer but I have got the assignment to help out with performance problems on a db.

I noticed that almost all indexes are fragmented 75-99%. Can I rebuild them when the database is in use? Can I reorganize indexes when the db is used?

/Magnus

Jesus saves. But Gretzky slaps in the rebound.

View 8 Replies View Related

Rebuild Indexes

Sep 11, 2007

In the maintance plans there is a Rebuild Index choice.
If u choose tables and views the plan executes
ALTER INDEX <index> ON <table> ;REBUILD
for all indexes in the datebase.
I am currently using this plan on our production DB, scheduled for every Saturday night.
I wonder if there is a downside of using maintance plans. Because it seems to be doing the job. Any comments?

View 3 Replies View Related

Rebuild All Indexes

Dec 17, 2007

I am upgrading from SQL2000 to SQL2005.
I have restored my 2000 db to 2005.
I have changed the Compatiblilty level to 90.
Now I need to reindex.
How do I reindex all the tables at once?
Thanks for ALL your help
r/p

View 20 Replies View Related

Hot Rebuild All Indexes

Nov 12, 2006



how can run a command to rebuild all indexes in a database?



View 4 Replies View Related

A Question About Clustered Indexes Forcing Rebuild Of Non-clustered Indexes.

Sep 18, 2007

So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?

View 4 Replies View Related

Error While Rebuild Indexes

Sep 21, 2015

We have a script running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Attached script for your consideration. There is no database name with amoperations. There is table called DatabaseObjectAudit but it exist on master db.

Message:

-->Start Index Maint
-> Gathering fragmentation information (can take a while!)
-> Gathering COMPLETE : Total of 43 databases were found.
-> Gathering COMPLETE : Total of 1622 indexes were found.

[code]...

View 9 Replies View Related

Rebuild Replicated Indexes

Jun 14, 2006

I have a number of databases that are being transactionally replicated from SQL 2000 Enterprise edition publisher to SQL 2005 Enterprise edition subscriber. I have included indexes in the replication. The subscriber database is then accessed and the data de-normalised and aggregated for reporting purposes.

My question is this: I want to periodically re-build the indexes on the publisher and subscriber via an automated task. If I rebuild the indexes on the publisher, will that automatically replicate to the subscriber? Will there be a problem with the "snapshot being out of date", and therefore replication stopping? I run a new snapshot once a day in the small hours of the morning. If there is likely to be a problem with the rebuild throwing the replication out, would it be wise to have the rebuild job running just before the new snapshot is taken?

Any help/advice appreciated.

View 3 Replies View Related

SQL Tools :: Update Stats And Rebuild Indexes

Sep 28, 2015

We are planning to standardize our newly deployed sql server, As a part of it we have configured 2 maintenance plans 1) Update Statistics which runs daily and 2) Index Reorganize which runs on weekly.

Apart from above, any other things to be in place for better maintenance of the sql server.

Also, how to Index Rebuild activity for clustered indexes requires any downtime.

View 10 Replies View Related

SQL Server 2014 :: Inline Syntax For Indexes?

Aug 11, 2014

I wonder whether there's documentation about inline syntax for indexes for SQL Server 2014?

CREATE TABLE Consumer
(
Account nvarchar(20) null,
Consumption float null,
INDEX IX_Consumer_Account NONCLUSTURED (Account)
);

View 9 Replies View Related







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