Issue with Default language of a Login

Recently we have faced one issue with converting the datetime to varchar.
When we are trying to execute the query it is throwing the error like conversion failed. out of range etc...

The database was migrated from SQL Server 2008 to 2014.

In 2008 it was executing fine without any issues. But it is not working in SQL Server 2014.

We tried to modify the query but not worked. We tried all the possible ways.

In last we have compared the properties of the application login in 2008 and 2014. In 2008 default language for that login is British English. In 2014, when migrated we have did not changed it to British English. Once we changed that to British English problem solved and every thing working as expected.  :)

So now on wards we need to check the Login default language also while migrating.

Replication Issues

https://technet.microsoft.com/en-us/library/ms152532(v=sql.105).aspx

Data Is Not Being Delivered to Subscribers

·         The data is not being applied due to filtering, an agent issue, or another replication error.

·         The data is being deleted at the Subscriber after it has been applied.

Explanation

There are a number of possible causes for data not being delivered to Subscribers:

·         The table is filtered, and there are no changes to deliver to a given Subscriber.

·         One or more agents are not running or are failing with an error.

·         A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.

·         Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.

·         The INSERT stored procedure used by a transactional article includes a condition that is not met.

·         Data is deleted by a user, a replication script, or another application.

·         Data is deleted by a trigger, or a trigger includes a ROLLBACK statement.

https://technet.microsoft.com/en-us/library/ms151272(v=sql.105).aspx

Data at the Publisher and Subscriber Do Not Match

Data at the Publisher and Subscriber is considered non-convergent (in other words the data does not match) if:

·         There are a different number of rows at the Subscriber than at the Publisher, and the publication is not filtered. If the publication is filtered, it might be expected that the number of rows differs.

·         The data in one or more rows is different in content at the Publisher and Subscriber.

Explanation

Data at the Publisher and Subscriber can be non-convergent for a number of reasons:

·         Data is updated at a Subscriber that should be treated as read-only. The subscription database should be treated as read-only unless you are using merge replication, transactional replication with updatable subscriptions, or peer-to-peer transactional replication.

·         Triggers are used at the Subscriber. Triggers can modify data at the Subscriber and also prevent the data from being updated if the trigger issues a ROLLBACK.

·         Scripts are executed by replication at the Subscriber but not at the Publisher.

·         Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.

·         Constraint violations or other issues prevent rows from being inserted, updated, or deleted at the Subscriber.

https://technet.microsoft.com/en-us/library/ms152522(v=sql.105).aspx

The System Has a Performance Issue

Replication performance can be measured along the following five dimensions:

·         Latency: the amount of time it takes for a data change to be propagated between nodes in a replication topology.

·         Throughput: the amount of replication activity (measured in commands delivered over a period of time) a system can sustain over time.

·         Concurrency: the number of replication processes that can operate on a system simultaneously.

·         Duration of synchronization: how long it takes a given synchronization to complete.

·         Resource consumption: hardware and network resources used as a result of replication processing.

Replication performance is affected by the following factors:

·         Server and network hardware

·         Database design

·         Distributor configuration

·         Publication design and options

·         Filter design and use

·         Subscription options

·         Snapshot options

·         Agent parameters

·         Maintenance

A Slow Network Is Causing Problems

 

Security Issues Are Preventing Data from Being Replicated

https://technet.microsoft.com/en-us/library/ms152519(v=sql.105).aspx

 

The password has changed for an account used by one or more replication agents.

An account used by a replication agent is no longer valid and must be changed.

A replication agent does not have sufficient permissions to connect to a computer in the topology or perform operations in a database.

The Snapshot Agent cannot write to the snapshot folder; the Distribution Agent or Merge Agent cannot read from the snapshot folder.

https://technet.microsoft.com/en-us/library/ms152544(v=sql.105).aspx

Running a Large Number of Agents Is Causing Memory Problems

In some cases, you might need to run a large number of replication agents simultaneously on a single computer. For example, if you are generating snapshots for a large number of publications or you are synchronizing a large number of push subscriptions at the Distributor. In these situations, the non-interactive desktop heap (a memory structure in Microsoft Windows) can run out of memory.

Question to new customer from SQL side

1. What is the environment.  Are there any Architecture diagrams available ?
2. No.Of   Preprod /UAT/Testing/PROD servers. Versions of SQL Server
3. In these what are the High critical, medium and low servers and databases. Also is there any DR structure available or planning to set up now.
4. Are service accounts configured independetly to each server/instance or one for domain. Password reset policy.
5. What is load on the servers, average requests on the database. Avg growth of the databases.
6. standard maintenance jobs scheduled on each SQL Server.
7. Important list of jobs running on the servers
8. How many SQL instances installed per Server.
9. Any specific standards for installing SQL Server(Pre and Post).  -- Client level
10. What is the storage structure.
11. Uptime of database High critical, medium and low servers.
12. Backup startegery for disaster recovery.
13. What is the prefered SQL Server edition.
14. Database monitoring mechanism. Is there any tool to monitor and how it works.
15. What is the Windows version, CPU and RAM configurations.
16. What are the high availability techniques are in use in current environment.
17. What level of permissions required for database users.
18.   Any Troubleshooting guide they follow?
19. IS there any inventory of the Database/Instances/Servers. Which database relates to which application
20.   What are the issues /tickets we get usually.
21. SSIS,RS,AS
22. Any SQL Server/OS up-gradation plans.

23. Is there any incoming data from other interfaces

24.  About Monitoring Tools and ticketing system.