{"id":2933,"date":"2013-02-13T15:43:15","date_gmt":"2013-02-13T15:43:15","guid":{"rendered":"http:\/\/mehmetsalihdeveci.net\/?p=2933"},"modified":"2019-02-28T08:53:02","modified_gmt":"2019-02-28T08:53:02","slug":"sql-server-2008-database-mirroring-3","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-2008-database-mirroring-3\/","title":{"rendered":"SQL Server 2008 Database Mirroring -3"},"content":{"rendered":"

Merhaba,<\/p>\n

Bu makalede \u00f6nceki 2 makalede anlatt\u0131\u011f\u0131m ve ger\u00e7ekledi\u011fim SQL Server 2008 de Database Mirroring olay\u0131n\u0131n\u0131n testlerini, monitoring olay\u0131n\u0131 ve Database Failovering yap\u0131s\u0131n\u0131 da anlat\u0131p Database Mirroring konusunu bitirece\u011fim.<\/div>\n
<\/div>\n

4.Mirroring testi ve Monitoring<\/strong><\/p>\n

Mirroring yap\u0131ld\u0131\u011f\u0131 zaman Principal database de bulunan datalar\u0131n ayn\u0131s\u0131 anl\u0131k olarak Mirror database de de bulundu\u011funu a\u015fa\u011f\u0131daki gibi \u00f6rnekle g\u00f6sterece\u011fim. Mirror database e Restore modda oldu\u011fu i\u00e7in read modda de\u011fildir. Database girmeye \u00e7al\u0131\u015ft\u0131\u011f\u0131m zaman a\u015fa\u011f\u0131daki hatay\u0131 al\u0131yorum.<\/p>\n

\"28\"<\/a><\/p>\n

<\/p>\n\n

Mirror database e eri\u015fim i\u00e7in bu database in snapshot \u0131n\u0131 al\u0131p snapshot \u00fczerinden read yapabiliriz. Snapshot i\u015flemini management studiodan yapam\u0131yoruz bunun i\u00e7in gerekli TSQL kodu a\u015fa\u011f\u0131daki gibidir. Bu kodu mirror database i\u00e7in \u00e7al\u0131\u015ft\u0131rd\u0131m.<\/p>\n

CREATE DATABASE TestDatabaseSnapshot\u00a0 ON\r\n( NAME = TestDatabase, FILENAME = 'D:\\Mirroring\\TestDatabase_Snapshot.ss' )\r\nAS SNAPSHOT OF TestDatabase;\r\nGO<\/pre>\n
\u00c7al\u0131\u015ft\u0131rd\u0131ktan sonra Databases sekmesi alt\u0131nda Database Snapshot sekmesi alt\u0131nda Snapshot database i a\u015fa\u011f\u0131daki gibi g\u00f6r\u00fcyor olmal\u0131y\u0131z.<\/p>\n
\"29\"<\/a><\/p>\n
Mirroring Test<\/strong><\/p>\n
Principal ve Mirror database de a\u015fa\u011f\u0131daki sorguyu \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131n\u0131z zaman ayn\u0131 datalar\u0131n \u00e7ekildi\u011fini ve kay\u0131t say\u0131lar\u0131n\u0131n da ayn\u0131 oldu\u011funu g\u00f6receksiniz b\u00f6ylece Principal ve Mirror database in sync oldu\u011fu ortaya \u00e7\u0131kacakt\u0131r. Bu arada a\u015fa\u011f\u0131daki sorgu \u00e7al\u0131\u015ft\u0131r\u0131lmadan \u00f6nce yukarda \u00f6rnek data girdi\u011fim T-SQL kodu halen \u00e7al\u0131\u015fmaktayd\u0131. Transaction ak\u0131\u015f\u0131n\u0131 durdurup Mirror da tekrar snapshot ald\u0131m ve a\u015fa\u011f\u0131daki sorguyu \u00f6ylece \u00e7al\u0131\u015ft\u0131rd\u0131m. Aksi takdirde principal da transaction devam ederken mirror da her an ayn\u0131 datay\u0131 g\u00f6steremiyor olacakt\u0131m. Datalar\u0131n ayn\u0131 oldu\u011funu ispatlamam i\u00e7in Principal a gelen transactionlar\u0131 durdurmak gerekiyor.<\/p>\n
Principal:<\/strong><\/p>\n
select * from TestDatabase.dbo.testtable order by col2 desc<\/pre>\n
\"30\"<\/a><\/p>\n
Mirror Database<\/b><\/p>\n
select * from TestDatabaseSnapshot.dbo.testtable order by col2 desc<\/pre>\n
\"31\"<\/a><\/p>\n
Mirroring Monitoring<\/strong><\/p>\n
Di\u011fer yandan Mirroring i monit\u00f6r etmek i\u00e7in Mirroring yap\u0131lan Principal database gelip a\u015fa\u011f\u0131daki gibi Mirroring eylemini monit\u00f6r edebilirsiniz.<\/p>\n
\"32\"<\/a><\/p>\n
Database Mirroring Monitor b\u00f6l\u00fcm\u00fcnde mirroring yap\u0131lan Sunucular\u0131n durumlar\u0131 a\u015fa\u011f\u0131daki gibi belirtiliyor. Burdan Mirroring yap\u0131l\u0131yormu yap\u0131lm\u0131yor mu, Witness \u0131n durumu ve Bu sunucular\u0131n O anki Rolleri Status k\u0131sm\u0131ndan anla\u015f\u0131l\u0131r.<\/p>\n
Ayr\u0131ca Principal log ve Mirror Log ba\u015fl\u0131\u011f\u0131 alt\u0131nda bulunan alanda ise Principal ve Mirror database \u00fczerinde G\u00f6nderilmeyen Transaction log lar, En eski g\u00f6nderilmeyen Transaction lar ve G\u00f6nderilen Transaction log lar\u0131n Ortalama b\u00fcy\u00fckl\u00fc\u011f\u00fc gibi bilgiler burdan incelenebilir.<\/p>\n
\"33\"<\/a><\/p>\n
A\u015fa\u011f\u0131daki Warnings sekmesi alt\u0131nda ise Uyar\u0131lar\u0131n Threshold de\u011ferleri ayarlan\u0131p ona g\u00f6re uyar\u0131lar\u0131n gelmesi ayarlan\u0131r.<\/p>\n
\"34\"<\/a><\/p>\n
5.Database Failovering<\/strong><\/p>\n
Makalenin ba\u015f\u0131nda Mirroring olay\u0131n\u0131n en \u00e7ok disaster durumlar\u0131nda high availability sa\u011flamas\u0131 i\u00e7in kullan\u0131ld\u0131\u011f\u0131n\u0131 s\u00f6ylemi\u015ftim. Disaster durumu sadece Sunucu Down oldu\u011fu zaman de\u011fil SQL Server Service sinin beklenmedik bir durumda durdu\u011fu an i\u00e7inde kullan\u0131l\u0131r. K\u0131saca Veritaban\u0131 herhangi bir nedenden dolay\u0131 hizmet veremiyorsa e\u011fer o Veritaban\u0131 Disaster durumundad\u0131r.\u015eimdi \u00f6rnek bir disaster senaryosu \u00fczerinden bu durumu a\u00e7\u0131klayaca\u011f\u0131m. Principal ve Mirror database lerin 2 si de Ayn\u0131 bilgisayarda bulundu\u011fu i\u00e7in \u00f6rnek senaryo i\u00e7in Sunucu Down olma durumunu kullanamayaca\u011f\u0131m. Bu durumda Principal database in bulundu\u011fu SQL Server Instance s\u0131na ait Servisleri durduraca\u011f\u0131m. SQL Server servislerini durdurmak demek o database in hizmet vermemesi anlam\u0131na gelir.<\/p>\n
\u015eimdi Principal database servislerini durdurmadan \u00f6nce Son kez Mirroring durumuna bakal\u0131m. A\u015fa\u011f\u0131daki Mirroring Monitoring ekran\u0131nda g\u00f6r\u00fcnd\u00fc\u011f\u00fc gibi iki database de Synchorized durumda. Failover olmadan \u00f6nceki Rol durumlar\u0131 da g\u00f6r\u00fcnd\u00fc\u011f\u00fc gibi Principal olarak MSSQLSERVER default instance s\u0131 yani D1010984, Mirroring rol\u00fc olarak ta MYTESTINSTANCE instance s\u0131 g\u00f6z\u00fckmektedir. Mirroring State kolonundan da Mirroring in \u015fuan yap\u0131ld\u0131\u011f\u0131n\u0131 g\u00f6r\u00fcyoruz.<\/p>\n
\"35\"<\/a><\/p>\n
Principal role de bulunan SQL Server Servisini Configuration Manager dan a\u015fa\u011f\u0131daki gibi Stop ediyorum. Ba\u015flang\u0131\u00e7ta Witness durumunu se\u00e7ti\u011fim i\u00e7in DEVINSTANCE instance s\u0131 otomatik olarak rol de\u011fi\u015fimi yapacak ve Mirror olan database i Principal rol\u00fcne atayacakt\u0131r.<\/p>\n
\"36\"<\/a><\/p>\n
Bu durumda Failover olay\u0131n\u0131n ger\u00e7ekle\u015fip Principal Role \u00fcn MYTESTINSTANCE a ge\u00e7mesi gerekiyor. A\u015fa\u011f\u0131daki gibi Mirroring Monitor de de bunun aynen ger\u00e7ekle\u015fti\u011fini g\u00f6rebilirsiniz. Bu durumda MYTESTINSTANCE \u00f6nceki durumdan farkl\u0131 olarak Okunabilir ve yaz\u0131labilir durumdad\u0131r. Yani Principal database ile ayn\u0131 \u00f6zelliktedir. Ayr\u0131ca Mirroring State kolonundan da Mirroring in durdu\u011funu g\u00f6r\u00fcyoruz.<\/p>\n
\"37\"<\/a><\/p>\n
A\u015fa\u011f\u0131dada Management Studio dan da ilk durumda Mirror Rol\u00fcnde bulunan MYTESTINSTANCE s\u0131na ba\u011fland\u0131\u011f\u0131mda a\u015fa\u011f\u0131daki gibi bu database in Principal Rol\u00fcne d\u00f6n\u00fc\u015ft\u00fc\u011f\u00fcn\u00fc ve Mirroring in durdu\u011funu g\u00f6r\u00fcyoruz. Veritaban\u0131 art\u0131k bu instance \u00fczerinden hizmet vermeye devam etmektedir.<\/p>\n
\"38\"<\/a><\/p>\n
\u015eimdi daha \u00f6nce Principal Role de olan MSSQLSERVER Instance Servisini tekrardan Start etti\u011fim zaman a\u015fa\u011f\u0131daki gibi Mirroring devam edecek fakat MSSQLSERVER Instance s\u0131 Mirror rol\u00fcnde MYTESTINSTANCE instance s\u0131 ise Principal modda olacakt\u0131r.<\/p>\n
\"39\"<\/a><\/p>\n
Rollerin de\u011fi\u015fti\u011fini ve Mirroring in devam etti\u011fini a\u015fa\u011f\u0131da g\u00f6rebilirsiniz.<\/p>\n
\"40\"<\/a><\/p>\n
Failover olay\u0131n\u0131 b\u00f6ylece tamamlam\u0131\u015f olduk. \u015eimdi her iki instance sa ba\u011fland\u0131\u011f\u0131m zaman ger\u00e7ektende rollerin de\u011fi\u015fti\u011fini a\u015fa\u011f\u0131daki gibi g\u00f6rm\u00fc\u015f olaca\u011f\u0131z.<\/p>\n
\"41\"<\/a><\/p>\n
Failover durumu ger\u00e7ekle\u015fti ve Database ler Rollerini de\u011fi\u015fti. Ayn\u0131 sunucuda \u00e7al\u0131\u015ft\u0131klar\u0131 i\u00e7in bu durum bir s\u0131k\u0131nt\u0131 olu\u015fturmamaktad\u0131r. Ancak e\u011fer farkl\u0131 sunucularda olsalard\u0131 kullan\u0131c\u0131lar i\u00e7in tekrar rol de\u011fi\u015fikli\u011fine gidilebilirdi. B\u00fcy\u00fck \u015firketlerde bu mant\u0131k genelde kullan\u0131lmaktad\u0131r. Principal rol\u00fcnde bir production veritaban\u0131 olan \u015firketler bir de ayn\u0131 veritaban\u0131n\u0131n disaster veritabanlar\u0131n\u0131 olu\u015fturmakta ve anl\u0131k olarak her iki veritabanlar\u0131n\u0131 da senkron tutmaktad\u0131rlar. B\u00f6ylece Production veritaban\u0131 herhangi bir durumdan dolay\u0131 hizmet veremez ise t\u00fcm talepler Disaster veritaban\u0131na y\u00f6nlendirilirler. Buda \u015firketler i\u00e7in high availability sunma a\u00e7\u0131s\u0131ndan g\u00fczel bir \u00e7\u00f6z\u00fcm te\u015fkil etmektedir.<\/p>\n
6.Sonu\u00e7<\/strong><\/p>\n
Mirroring, disaster durumlar\u0131nda high availability i sa\u011flamak, data kayb\u0131n\u0131 \u00f6nlemek ve raporlama yapan ki\u015filerin production ortam\u0131na katt\u0131\u011f\u0131 y\u00fck\u00fc azaltmak i\u00e7in anl\u0131k olarak bir database in kopyas\u0131n\u0131n farkl\u0131 bir yerde tutulmas\u0131n\u0131 ve gerekti\u011finde burdan hizmet vermesini sa\u011flayan g\u00fczel bir \u00e7\u00f6z\u00fcmd\u00fcr. Mirroring e benzeyen ve ayn\u0131 amaca hizmet eden Log shipping gibi teknolojilerde high availability i\u00e7in tercih edilebilir.<\/p>\n
\u00d6te yandan Microsoft un Nisan 2012 de Release etti\u011fi SQL Server 2012 ile beraber gelen AlwaysOn teknolojisinin getirmi\u015f oldu\u011fu Availability Groups, High Availability a\u00e7\u0131s\u0131ndan yine m\u00fckemmel bir \u00e7\u00f6z\u00fcmd\u00fcr. Bu teknolojide b\u00fcy\u00fck \u00e7apl\u0131 Veritaban\u0131 bar\u0131nd\u0131ran \u015firketler taraf\u0131ndan incelenmeli ve kullan\u0131lmal\u0131d\u0131r. Mirroring \u00e7\u00f6z\u00fcm\u00fc SQL Server 2012 de ile beraber deprecated olarak ilan edildi yani SQL Server 2012 de Mirroring kullan\u0131labilinecek ancak bir sonraki maj\u00f6r release ile beraber mirroring tamamen kalkacak ve sadece AlwaysOn kullan\u0131lacakt\u0131r. Buda \u015fu anlama geliyor Mirroring daha uzun y\u0131llar boyunca SQL Server high availability \u00e7\u00f6z\u00fcm\u00fc i\u00e7in kullan\u0131lacakt\u0131r. En az\u0131ndan Microsoft \u015fuanda SQL Server 2000 den deste\u011fini \u00e7ekti\u011fi gibi SQL Server 2012 den de deste\u011fini \u00e7ekene kadar,\u00a0 ki bu s\u00fcre benim tahminimce 10 y\u0131ldan fazla olacakt\u0131r, Mirroring \u00e7\u00f6z\u00fcm\u00fc kullan\u0131lmaya devam edecektir.<\/p>\n\n
 <\/p>\n
Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda a\u015fa\u011f\u0131daki konularda 7×24 Uzman Dan\u0131\u015fmanlara yada E\u011fitimlere mi\u00a0<\/span>\u0130htiyac\u0131n\u0131z var mehmet.deveci@gridgroup.com.tr adresine mail atarak Bizimle ileti\u015fime ge\u00e7ebilirsiniz.<\/span><\/p>\n
– Oracle Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle Veritaban\u0131 Bak\u0131m ve Destek<\/span>
\n– Exadata Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Exadata Bak\u0131m ve Destek<\/span>
\n– SQL Server Veritaban\u0131 Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– SQL Server Veritaban\u0131 Bak\u0131m ve Destek<\/span>
\n– Goldengate Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Goldengate Bak\u0131m ve Destek<\/span>
\n– Linux Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Linux Bak\u0131m ve Destek<\/span>
\n– Oracle EBS Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle EBS Bak\u0131m ve Destek<\/span>
\n– Weblogic Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Weblogic Bak\u0131m ve Destek<\/span>
\n– Oracle Veritaban\u0131 E\u011fitimleri<\/span>
\n– Oracle VM Server Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle VM Server Bak\u0131m ve Destek<\/span>
\n– Oracle EPPM Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle EPPM Bak\u0131m ve Destek<\/span>
\n– Oracle Primavera Dan\u0131\u015fmanl\u0131\u011f\u0131<\/span>
\n– Oracle Primavera Bak\u0131m ve Destek<\/span>
\n– Oracle E\u011fitimleri<\/span>
\n– SQL Server E\u011fitimleri<\/span>
\n– Goldengate E\u011fitimleri<\/span>
\n– Exadata E\u011fitimleri<\/span>
\n– Linux E\u011fitimleri<\/span>
\n– Oracle EBS E\u011fitimleri<\/span>
\n– Oracle VM Server E\u011fitimleri<\/span>
\n– Weblogic E\u011fitimleri<\/span>
\n– Oracle EPPM E\u011fitimleri<\/span>
\n– Oracle Primavera E\u011fitimleri<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Merhaba, Bu makalede \u00f6nceki 2 makalede anlatt\u0131\u011f\u0131m ve ger\u00e7ekledi\u011fim SQL Server 2008 de Database Mirroring olay\u0131n\u0131n\u0131n testlerini, monitoring olay\u0131n\u0131 ve Database Failovering yap\u0131s\u0131n\u0131 da anlat\u0131p Database Mirroring konusunu bitirece\u011fim. 4.Mirroring testi ve Monitoring Mirroring yap\u0131ld\u0131\u011f\u0131 zaman Principal database de bulunan datalar\u0131n ayn\u0131s\u0131 anl\u0131k olarak Mirror database de de bulundu\u011funu a\u015fa\u011f\u0131daki gibi \u00f6rnekle g\u00f6sterece\u011fim. Mirror database …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[19,23],"tags":[460,466,508,717,1031,1049,1515,1577,1583,1595,1630,1647,1658],"class_list":["post-2933","post","type-post","status-publish","format-standard","","category-sql-server","category-veritabani","tag-database-high-availability","tag-database-mirroring","tag-deveci","tag-high-availability","tag-mehmet-salih-deveci","tag-mirroring","tag-salih-deveci","tag-sql-server","tag-sql-server-2008","tag-sql-server-2008-mirroring","tag-sql-server-database-mirroring","tag-sql-server-high-availability","tag-sql-server-mirroring"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/2933","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=2933"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/2933\/revisions"}],"predecessor-version":[{"id":5897,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/2933\/revisions\/5897"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=2933"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=2933"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=2933"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}