1. Her zaman SQL ile uygulama sunucusunu birbirinden ayır
  2. Registered Server özelliğini kullan
  3. Template leri kullan
  4. SQL'in 3 farklı lisanslama metodu var
    1. Developer
      1. Enterprise seviyesinde özellikleri olup sadece geliştirme makinalarında kullanılabilir ve lisans ücreti düşüktür. 
    2. Express
      1. Çok düşük kaynak kullanımına olanacak sağlar, ücretsizdir
    3. Standart
      1. En önemlisi, herşeyi yapar candır
    4. Enterprise 
      1. Linsans ücreti çok yüksek, bu sebeple iyi düşünülmeli 
      2. Always ON
      3. 7/24 Aynı performans
      4. Data işlemleri
  5. Scale Out
    1. MDF-NDF ile LDF dosyaalrını ayır ve farklı disklere taşı. 
    2. İndex ve Tabloları birbirinden ayır
  6. AutoClose should be off
    1. Uygulamaların sisteme tekrar tekrar session açmasına yol açabilir
  7. AutoShring should be off
    1. Çünkü indexlerde ciddi fragmantasyoa yol açıyor
  8. Statistics
    1. SQL server tarafından hanögi index in kullanılması gerektiğini belirler
  9. sp_configure blocked process treshold. Start using Sql profiler to catch the Blocked Process Report 

 

Asenkron olması - query background için daha uygun

select  is_auto_update_stats_on, is_auto_update_stats_async_on, * from sys.databases

Nasıl Asenkron Stats eklemes yapılır

ALTER DATABASE DB
SET auto_update_statistics_async on

Kullanışlı DMV fonksiyonları

select * from sys.dm_db_index_physical_stats(null,null,null,null,null)

select DB_NAME(database_İd),object_name(object_id),avg_fragmentation_in_percent,* from sys.dm_db_index_physical_stats(DB_ID('EBIFlowDB'),null,null,null,null)
where
avg_fragmentation_in_percent > 20

select object_name(object_id),* from sys.dm_db_index_usage_stats 

select * from sys.dm_os_windows_info

select * from sys.dm_os_sys_info

SoruCevap
four-node failover cluster için quorum configuration seçimiNode and Disk Majority doğrucevapçünkü çift sayılıkonfigurasyoneğerteksayıyanifive-nodegibibudurumdasadeceNode Majorityyeterliolacaktı.
Create a script that writes audit events into application log whenever data in the ProductPrices table is updated. Schema Name:Sales, Table name:ProductPrices

USE Master

CREATE SERVER AUDIT C_Audit

TO APPLICATION LOG

ALTER SERVER AUDIT C_Audit

WITH(STATE=ON)

USE Contoso

CREATE DATABASE AUDIT SPECIFICATION C_AuditSpec

FOR SERVER AUDIT C_Audit

ADD (UPDATE ON Sales.ProductPrices BYdbo)

ALTER DATABASE AUDIT SPECIFICATION C_AuditSpec WITH (STATE=ON)

MSSQLservisiçalıştırılırkenyenidenduruyor ve hatayıneredenyakalarız.MSSQL11.MESSQLSERVER\MESSQL\Log\ErrorLog
ServerA → ServerB Mirroring

1-Backup full backup

2-Restore full backup

3-Log restore

Design a database on SAN and high availability on plus database must remain online if SQL Server FailureYou should include two servers configured as a failover cluster

SQL1 - Principal server

SQL2 - Mirror server

SQL3 - Witness server

Upgrade 1 and 2

Remove SQL3

Upgrade SQL2

Manually failover from SQL1 to SQL2

Upgrade SQL1

Manually failover from SQL2 to SQL1

Add SQL3 back

You suspect that SQL Server is assigning stronger locks to queriesStart a SQL Profiler Trace: Lock: Timeout
Migrate Database to Microsoft Azure ServerUse SQL Server Management Studio to deploy the database to Windows Azure SQL Database
query_processing error - failover occurs set server configurationHealthCheckTimeout
Migrate SQL2008 to SQL2012side-by-side migration for login:backup master and restore it to SQL2012
Power Vıew Components

1-Install Reporting Services

2-Install Database Engine and PowerPivot For SharePoint

3-Configure Microsoft SQL Server Reporting Services Add-in Sharepoint mode

SSL Sertifika hatası alınıyor ancak yükleme normal yapılmış, ayarları düzenlemek gerekiyor.

"A self-generated certificate was successfully loaded for encryption"

  • From the SQL Configuration manager on the SQL Server, open the PROTOCOLS properties for the SQL Instance
  • Choose the server certificate provided by network administrators from the drop-down list on the CERTIFICATES tab
  • Restart the SQL Server
Transaction Log file maksimum parametresi düzenlemeManagement Studio → Database → Properties → Files → Transaction Log Autogrow window - set
OLTP - Uses (SAN) - Any server fails, the database remains - minimize the amount of storage used by the database:
  • Two servers configured in a Windows Failover Cluster in the same data center.
  • SQL Server configured as a clustered instance.

 

A database contains a large product catalog that is updated periodically and you need to be able to send the entire product catalog to all branch offices on monthly basisSQL server that includes an application database configured to perform snapshot replication

Two servers in the same data center that hosts your production database.

  1. catastrophic server failure or disk failure occurs.
  2. transactional consistency

Two server configured on the same subnet

SQL Server Availability Group configured in Synchronous- Commit Availability Mode

SQL 2005 upgrade to SQL 2012
  1. Perform a full database backup on SQL2005
  2. Restore the full database backup on SQL 2012
  3. Change the compatibility level for the database to 110 on SQL2012
  • Multiple databases on the same low-latency subnet must allow applications to write changes locally, and these changes must be replicated to all related databases.
  • An order summary table is repopulated once a week. This table must be replicated to all databases.
  • Field offices using unreliable connections keep a local copy of the product catalog and process orders locally. These orders must be periodically replicated to all other
  • Information order-tracking database must be replicated across a low-latency connection as changes occur to multiple reporting databases

 

  • Peer-to-Peer Replication
  • Snapshot Replication
  • Merge- Replication
  • Transactional Replication
SSIS packages
  1. Create domain user account and grant permission to domain user account to access the network share.
  2. Create a credential that references the domain user
  3. Add a proxy that references the credential
  4. Assign the proxy to the SSIS package execution subsystem
You create a stored procedure that return database level information from DMV
  1. Modify the sp to include the EXECUTE AS OWNER statement Grant VIEW SERVER STATE permission to the owner of the stored procedure
  2. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the sp to include the EXECUTE as {newlogin} statement.
sp_ ile başlayan prosedür yazdırmama kuralı

Create a condition named StoredProceedureNamingConvention by using the Stored Procedure facet that has a single expression

Set the Field to @Name, Operator to LIKE, and VALUE to 'sp[_]%'

Create a Policy named StoredProceedureNamingPolicy

SET the check condition to StoredProceedureNamingConvention and Evaluation Mode On Change, Prevent

Enable StoredProceedureNamingPolicy

Development Team wants to refresh test db wednesdat at 10am

Transaction backup

Backup size çok düşük

Import data from text file to the database

  1. Data import using sp
  2. data is loaded and minimally logged

 

Data Import Command: BULK INSERT

Recovery Model: Bulk-Logged

The firewall only allows inbound connections on port 1433. The server only hosts single instance. How to allow remote connection?
  • Enable Inbound connections 1434 in the Windows Firewall on the server
  • sp_configure 'remote admin connections ' reconfigure

You plan a bulk insert

  • transaction log minimally affected
  • database online and all user transactions are recoverable
  • All transactions are fully recoverable prior to import
  1. Perform a LOG backup
  2. Configure the database to use the BULK-LOGGED recovery model
  3. Execute the BCP tool
  
  
  
  • No labels

EBIFlow 2024© Tüm Hakları Saklıdır