Always on environment is established. There are a lot of online materials, mainly the establishment of windows clusters and the establishment of SQL Server Always on.
It is easy to ignore the SQL server account synchronization problem (Always on can implement internal database synchronization, but cannot implement instance-level synchronization, such as job and account synchronization)
Here are several solutions:
The SQL account uses a domain account to run IIS under a local account, but configures a simulated account in web. config or machine. config.
The SQL account uses a local account, the client connection string uses the user/pwd Method for connection, and adds a common service to the windows Cluster, the purpose is to execute an SQL script after the master SQL server service is migrated to another server. This script re-maps the permission relationship between the local SQL account and the database.
The SQL account adopts the local account + domain account public mode, the domain account permissions are associated with the database, and the local SQL account has the simulated domain account permissions, and the client uses the user/pwd method to connect, you also need to modify the framework so that you can switch the context user: execute as login 'domain \ user1' before each SQL operation'
The SQL account uses a local account, but the account is not directly created. Step: export the script with SID from the source SQL Server, and then import the script with SID in the Always on database.
Using the Always on plug-in is not mature and it is unclear whether automatic synchronization can be performed.
They are all third-party and do not dare to use them.
Using Contained Database, this Database can include login user/pwd at the db level, but the specific Database name must be specified during connection; otherwise, a connection cannot be established.
Performance considerations:
- After Always on is adopted, all the submission behaviors are handled by the Master SQL, so the CPU usage of the Master is very high (considering one master and five slave, and 3 sets are successfully submitted before the overall submission is successful), and the Slave CPU is almost idle. Therefore, it is best to use read/write splitting to connect the listener IP address (write) and read the IP address (Hasse algorithm)
- Main features are HA + medium performance considerations
- It is not applicable to scenarios with high concurrency (you must split the database or write the DAL)
