We have two SQL servers, one in NY, one in Illinois, and we
want to sync them. Suppose we have a T1 line as backbone,
which one is a better solution for us, Clustered or SQL
replication ? Your help will be greatly appreciated!
Xiao
Hi All,
Just want to know if we can replicate data (Transactional Replication) from a clustered servers (server A and Server B are active-passive clustered running SqlServer 7) to another server C running SqlServer 7.
If yes, how to go about doing this. Any white papers, KB articles or books out there which will walk through the steps to do it.
Appriciate any help.
Thanks,
Sri
Using SQL 2005 SP2. I have a publication that contains indexed views, and some other objects that query the indexed view using WITH (NOEXPAND). Currently replication fails because the CLUSTERED INDEX on the view is NOT replicated. I've experimented with various schema options but nothing changes. The view is replicated but not the clustered index on that view. I've seen some discussion on replicating indexed views to a table, but I would like to replicate indexed view schema fully. (Including the clustered index on that view). Is there a way to make this work?
View 4 Replies View RelatedWe 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
I set up replication on our servers at work to streamline some procedures we run daily/weekly on them. This copies around 15 articles from two databases on the "Master" server to another server used for execution purposes. For the most part it was a pretty straight forward task and it seemed to work nicely; but I realised after some investigation that the non-clustered indexes weren't copying over to the child server.
I set the non-clustered indexes property in the properties of the publishing articles to "True" and generated a new snapshot, this seemed to work, but I've come into work this morning to find the property has reset to "False" and I have no indexes on the table again. Why is this happening and is there any way I can resolve the matter so the indexes are copied over concurrently?
Group,
I am getting the following error during replication of Database to a client:
The schema script 'Statutes_6.dri' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed. (Source: MSSQLServer, Error number: 7696)
Get help: http://help/7696
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. (Source: MSSQLServer, Error number: 319)
The database is relatively small, only about 5 tables but there is a clustered Full-text Index.
Any ideas?
Thanks,
Frank
My ambition is to run ASPStateInMemory with durability on, supplementing the in-memory database with an on-disk version to give session state persistence when and if the clustered instance of SQL runs into, say a network card failure and needs to fail to a partner node. I understand that DAG's are usually run between standalone instances of SQL running on different machines. But, can I combine the DAG with the FCI? Instead of using a remote standalone SQL server, can I just another instance in the same cluster?
View 7 Replies View RelatedHello,Being a bit of a SQL Server novice, need some advice with the followingsituation.Server A and Server B have SQLServer 2000 based databases. The vendorof the application/system has implemented their own replication processto ensure the 2 databases are in sync. However, there is no clusteringwith virtual IP addresses implemented. So to an external client/db, itis 2 identical databases with the same name on 2 distinct servers.We need to develop an application that will reside on a networkedserver C and with SQLServer 2000 as well. While most of the tables inthis database are self contained, around 10 tables will have to bemirror copies of the same tables from either Server A or Server B.Question, how do we implement subscription based replication on top ofa redundant database, when no clustering is implemented? So, inessence, when Server A is alive, the database on Server C willperiodically (or on change) replicate the 10 tables from Server A. WhenServer A is not alive, it needs to do same from server B. (When bothserver A and B are alive, it is acceptable to get data from either,since they are synchronized internally).Any alternate suggestions on achieving this functionality are welcometoo. If SQL Server 2005 has some capabilities that may address thisproblem, that is a consideration as well.Thanks
View 1 Replies View RelatedCan I enable the Non - Clustered Index option set to 'TRUE' at publisher does it affect anything?
Does the Non clustered Indexes are automatically transfer to the subscriber database?
Does it affects database performance when I enable this option!
Hello,
Not sure if this question belongs here in the Setup & Upgrade section but here is the problem.
When installing replication in a non clustered environment, the Sql replication jobs run fine with the windows login provided (this login has access to the snapshot folder and has the proper roles. All the log reader, distributer and subscriber agents work fine)
When installing replication in a windows (MS) clustered environment, the agents running under the same login and same privilages dont work. It seems like there is something wrong with the proxy account and the ability of sql to access disk resources using this account. The only workaround is to go to the agent jobs, change the login under which they work from the proxy account to an actual windows account.
Has anyone come across this issue? I am forced to use the sql agent account and running the replication agents under way more privilages than I would like to.
TIA
I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:
Cannot alter the table '' because it is being published for replication.
How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?
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?
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 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.
Hi there, I have a table that has an IDENTITY column and it is the PK of this table. By default SQL Server creates a unique clustered index on the PK, but this isn't what I wanted. I want to make a regular unique index on the column so I can make a clustered index on a different column.
If I try to uncheck the Clustered index option in EM I get a dialog that says "Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.". If I simply try to delete the index I get the following "An explicit DROP INDEX is not allowed on index 'index name'. It is being used for PRIMARY KEY constraint enforcement.
So do I have to drop the PK constraint now? How does that affect all the tables that have FK relationships to this table?
Thanks
What is the easiest way to remember the definitions of clustered and non clustered indexes.
View 9 Replies View RelatedI have a really super slow stored proc that does something simple. it updates a table if certain values are received.
In looking at this the matching is done on the Primary Key, which is set as a Clustered index, looking further I have another constraint, that sets the same column to a Unique, Non-Clustered.
I am not sure why this was done, but it seems to be counter productive. I have read only references to Which one is better on a primary key, but not can their be both and if it is "Smart".
Hello,
I've a table with primary key defined as non-clusterd, now without dropping it can I modify the existing index to clustered through tsql as I had to write some migration script and in that script I wanna do this.
Thanks in Advance,
Rohit
I would like to find information on Clustered and Non-clustered indexes and how B-trees are used. I know a clustered index is placed into a b-tree which makes sense for fast ordered searching. What data structure does a non-clustered index use and how? I tried to find info. on the web but couldn't get much detail...
View 3 Replies View RelatedI have a table<table1> with 804668 records primary on table1(col1,col2,col3,col4)
Have created non-clustered index on <table1>(col2,col3,col4),to solve a performance issue.(which is a join involving another table with 1.2 million records).Seems to be working great.
I want to know whether this will slow down,insert and update on the <table1>?
the query:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WHERE a.AssociationGuid IN (
'0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4',
'52C616C0-C4C5-45F4-B691-7FA83462CA34',
'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so...
Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets.
AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid
FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE
a.AssociationGuid IN (
SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada
WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
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?
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies View RelatedI 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] ....
Does creating both a Clustered and Non-Clustered on the same field increase performace or decrease performance?
Or having either one is enough?
What does an index add to the performance?
Why do we use Clustered Index and Non-clustered Index?
thanks
Dear All,
i've observed one particular table, one column is having clustered and non clustered index. is it ok? or i need to drop the non clustered column?
the table has 16 columns and at present 8 million records are there. per day approxmately 60000 rows will be getting into the table. it has another 3 non clustered indexes.
please suggest me.
Arnav
Even you learn 1%, Learn it with 100% confidence.
hi,
how clustered indexes and non-clustered indexes been saved in memory?
non-clustered is a table of a references to the actual table?
and what about clustered indexes?
thanks.
Hi everybody!
I just ran the Database Engine Tuning Advisor on a relative complex query to find out if a new index might help, and in fact it found a combination that should give a performance gain of 94%. Fair enough to try that.
What I wonder about: The index I should create contains 4 columns, the last of them being the Primary Key column of the table, which is also my clustered index for the table. It is an identity integer btw.
I think I remember that ANY index does include the clustered one as lookup into the data, so having it listed to the list of columns will not help. It might at worst add another duplicate 4 bytes to each index entry.
Right? Wrong? Keep the column in the index, or remove it since it is included implicit anyway?
Thanks for suggestions!
Ralf
After applying SP3 to to cluster, cluster still displays that it has SP2, is there a way to check for sure which version it has. And what's the appropriate way to install SP3 on cluster.
View 1 Replies View RelatedMany times we see tables with customerID as the PK and clustered but with customerName as null.
So, I try to inforce it by alter table add constraint of AK1_customerName unique for it. However, though the uniqueness is enforced, the display sequence is also 'altered' to be sorted by customerName?
This does not look like right...
-D