SQL Server Admin 2014 :: Certain Rows Not Replicated Yet Replication Was Not Broken
Oct 24, 2013
We had an issue recently where a (transactional) replicated table was replicating data as expected.
Then about 30 or so rows in the source table were not in the destination table, but other rows created after those 30 rows were replicated.
We have pretty much confirmed that users did not delete those rows.
Unfortunately we had to resolve the issue quickly and so blew away & recreated the subscription so a lot of evidence is probably gone from the crime scene.
We cant figure out what could cause 30 rows not to be replicated, yet leave replication operational.
We have installed 2014 sql server. We have currently 2008r2. We have to run the real time report. So we need to set up transactional replication b/n those two servers. We need to use 2008r2 as publisher and 2014 as subscriber.
Is it ok to have subscriber higher version than the publisher?
Any experience of success or failure setting up CDC on the subscriber end of transactional replication?
Also for a bonus answer, why are explicit index operations not permitted (I'm assuming this is even on the publisher?) From BOL:
• Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.
I have created a publication on SQL 2014 SP1/CU1 it is going to a distribution server also a SQL 2014 database. The subscriber is also SQL 2014 SP1/CU1. When I set it up there are no errors. I fire up Replication MGR and it looks good after the snapshot and distribution agent have been started. Again, no errors. When I do a tracer token the information gets to the distributer in 1 - 4 seconds but it never gets to the subscriber, the tracer token monitoring screen only shows pending for distributor to subscriber.
I can upload the scripts used to create the publication and/or the subscription but am a little hesitant because of server names in the scripts.
We have push transaction replication from A database to 2 other B and C database. I have configured email to sent upon failure of subscription job of B and D database on Database A. Is this the way that we need to configure email to send when there is replication break up or failure.
Database is MS sql server 2008 R2 Publication Database: A Replication mode : Transaction Replication Replication used : Push Replication from database A Subscription Database: B and C
We have transaction replication configured across multiple SQL instances and could find one of the replication (from publisher to subscriber) net transport sessions is happening via Named pipes rather than TCP. where to troubleshoot this issue and what action to be taken to make it happen via TCP.
I am planning to have AlwaysON Availability Groups setup between Server 1 and Server 2
Server 1 -->Publisher-->2014 SQL Enterprise edition-->Windows Std 2012 --> Always on Primary Replica
Server 2 -->Publisher(when DR happens)-->2014 SQL Enterprise edition-->Windows Std 2012 --> Secondary Primary
Server 4 as Subscriber
Server X as Remote Distributor ..
If i create Publications on Server1 (primary replica) to subscriber 4 servcer, will the publication be created automatically in Secondary Replica Server2 ? or do i have to create manullay using GUI/T Sql on Both Servers?
if for any reason AG fails over to async node, how replication behaves? As data will not be in sync with previous primary replica, how replication will work? I think that we have to reset replication from scratch as there's a high chance subscribers might be more updated than current primary replica as failover to this node causes data loss. How to keep replication in sync without resetting up? Can we achieve this?
As per attachment, i have been created replications but in local subscription it is not populated any thing at the same time, Subscription database has been created but tables is not populated as per publication table.
Is it possible to configure transnational replication between two different domains also non trusted domains.
It's possible means what i need to take care before configure replication and how to configure transnational replication between two different domains.
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.
Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....
If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
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?
"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."
Is it better to run the profiler or performan counter?
What are the filters we have to select in the profiler to monitor the Sql server
I have a SQL server box running 2014 reporting services. I have another server running IIS v8.
I would like to be able to connect to the IIS site and be given the SSRS report browser.
So externally if I browse to [URL], I am presented with the report server interface, the same as if I browse to http://xxx.xxx.xxx.xxx/reports internally.
What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.
The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.
The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.
What would best the approach to accomplish the end result?
I have 10 databases which are configured as principal in mirroring I need to failover all the databases as part of failover , instead of writing query each database as parner failover, is an script which will generate the databases as principal to failover ?
After installing SSMS on some computers - the only way we can get SSMS to run correctly is to run it as the administrator. Is there a way where you don't have to do that? These end users are logging as themselves and have accounts in SQL Server all set up - but SSMS will only launch for them if we right click and select "run as administrator".After doing some digging - it seems that this is a common problem out there.
