SQL Server 2012 File Stream / Table °ü·Ã DMV
SQL Server 2012 File Stream / Table °ü·Ã DMV - Version : SQLServer 2012 DMV´Â SQL ServerÀÇ ´Ù¾çÇÑ Á¤º¸¸¦ È®ÀÎ ÇÒ ¼ö ÀÖ´Â µ¿Àû°ü¸® ºä ÀÌ´Ù. ÆÄÀϽºÆ®¸²Àº SQL Server 2012¿¡ µµÀÔµÈ »õ·Î¿î ±â´ÉÀ¸·Î SQLServer ±â¹ÝÀÇ ÀÀ¿ë ÇÁ·Î±×·¥¿¡¼ ¹®¼ ¹× À̹ÌÁö¿Í °°Àº ±¸Á¶È µÇÁö ¾ÊÀº µ¥ÀÌÅÍÆÄÀÏÀ» ½Ã½ºÅÛ¿¡ ÀúÀåÇÒ ¼ö ÀÖ´Â ±â´ÉÀÌ´Ù. ÀÀ¿ë ÇÁ·Î±×·¥Àº dzºÎÇÑ ½ºÆ®¸®¹Ö API¹× ÆÄÀÏ ½Ã½ºÅÛÀÇ ¼º´ÉÀ» È°¿ëÇÒ ¼ö ÀÖ°í µ¿½Ã¿¡ ±¸Á¶ÈµÇÁö ¾ÊÀº µ¥ÀÌÅÍ¿Í ÇØ´çµÇ´Â ±¸Á¶ÈµÈ µ¥ÀÌÅÍ °£¿¡ Æ®·£Àè¼Ç ÀÏ°ü¼ºÀ» À¯ÁöÇÒ ¼öÀÖ´Ù. ÆÄÀϽºÆ®¸²¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº ´ÙÀ½ ¸µÅ©¸¦ Âü°í ÇÑ´Ù ÆÄÀϽºÆ®¸² : http://msdn.microsoft.com/ko-kr/library/gg471497.aspx À̹ø½Ã°£¿¡´Â SQL Server 2012¿¡¼ ÆÄÀϽºÆ®¸²°ú ÆÄÀÏÅ×ÀÌºí¿¡ ´ëÇÑ Á¤º¸¸¦ È®ÀÎÇÏ´Â DMV¸¦ ¾Ë¾Æ º¸ÀÚ. [sys.database_filestream_options] FileTableÀÇ FILESTREAM µ¥ÀÌÅÍ¿¡ ´ëÇÑ ºñÆ®·£Àè¼Ç ¾×¼¼½º ¼öÁØ°ú °ü·ÃµÈ Á¤º¸¸¦ Ç¥½ÃÇÑ´Ù. SQL Server ÀνºÅϽºÀÇ °¢ µ¥ÀÌÅͺ£À̽º¸¶´Ù Çϳª¾¿ÀÇ ÇàÀ» Æ÷ÇÔÇÑ´Ù. Ä÷³¤² À¯Çü ¼³¸í database_id int µ¥ÀÌÅͺ£À̽ºÀÇ ID.ÀÌ °ªÀº SQL Server ÀνºÅϽº ³»¿¡¼ °íÀ¯ÇÏ´Ù directory_name Nvarchar(255) ¸ðµç FileTable ³×ÀÓ½ºÆäÀ̽º¿¡ ´ëÇÑ µ¥ÀÌÅͺ£À̽º ¼öÁØ µð·ºÅ͸® non_transacted_access tinyint »ç¿ëÇϵµ·Ï ¼³Á¤µÈ FILESTREAM µ¥ÀÌÅÍ¿¡ ´ëÇÑ ºñÆ®·£Àè¼Ç ¾×¼¼½º ¼öÁØÀÔ´Ï´Ù.¾×¼¼½º ¼öÁØÀº CREATE DATABASE ¶Ç´Â ALTER DATABASE ¹®ÀÇNON_TRANSACTED_ACCESS ¿É¼ÇÀ» »ç¿ëÇÏ¿© ¼³Á¤. 0 - »ç¿ë ¾È ÇÔ.(±âº»°ª) ¼öÁØÀ» ¼³Á¤ÇÏ·Á¸é NON_TRANSACTED_ACCESS ¿É¼Ç¿¡OFF °ªÀ» ÁöÁ¤. 1 - Àбâ Àü¿ë (NON_TRANSACTED_ACCESS ¿É¼Ç¿¡ READ_ONLY °ªÀ» ÁöÁ¤) 3 - ¸ðµç ¾×¼¼½º(NON_TRANSACTED_ACCESS ¿É¼Ç¿¡ FULL °ªÀ» ÁöÁ¤) 5 - READONLY·Î Àüȯ Áß 6 - OFF·Î Àüȯ Áß non_transacted_access_desc Nvarchar(60) non_transacted_access¿¡¼ ½Äº°µÈ ºñÆ®·£Àè¼Ç ¾×¼¼½º ¼öÁØ¿¡ ´ëÇÑ ¼³¸í. NONE – ±âº»°ªÀÔ´Ï´Ù READ_ONLY FULL IN_TRANSITION_TO_READ_ONLY IN_TRANSITION_TO_OFF SELECT database_id AS DatabaseId, DB_NAME(database_id) AS DatabaseName, non_transacted_access AS NonTransactedAccessEnum, non_transacted_access_desc AS NonTransactedAccessDescription FROM sys.database_filestream_options WHERE database_id <> 32767; [sys.filetable_system_defined_objects] FileTable°ú°ü·ÃµÈ ½Ã½ºÅÛ Á¤ÀÇ °³Ã¼ÀÇ ¸ñ·ÏÀ» Ç¥½Ã. ½Ã½ºÅÛ Á¤ÀÇ °³Ã¼¸¶´Ù ÇϳªÀÇ ÇàÀ» Æ÷ÇÔ. FileTableÀ» ¸¸µé¸é Á¦¾à Á¶°Ç ¹× À妽º¿Í °°Àº °ü·Ã °³Ã¼°¡ µ¿½Ã¿¡ ¸¸µé¾î Áø´Ù. ÀÌ·¯ÇÑ °³Ã¼´Â º¯°æÇϰųª »èÁ¦ÇÒ ¼ö ¾øÀ¸¸ç, FileTable ÀÚü°¡»èÁ¦µÈ °æ¿ì¿¡¸¸ »ç¶óÁø´Ù. Ä÷³ µ¥ÀÌÅÍ Çü½Ä ¼³¸í object_id int FileTable°ú °ü·ÃµÈ ½Ã½ºÅÛ Á¤ÀÇ °³Ã¼ÀÇ °³Ã¼ ID sys.objectsÀÇ °³Ã¼¸¦ ÂüÁ¶. parent_object_id int ºÎ¸ð FileTableÀÇ °³Ã¼ ID. sys.objectsÀÇ °³Ã¼¸¦ ÂüÁ¶ SELECT object_id, is_enabled, directory_name, filename_collation_id, filename_collation_name FROM sys.filetables; [Âü°íÀÚ·á] l http://msdn.microsoft.com/ko-kr/library/gg492092.aspx