SQLSERVER数据库管理员的专用连接DAC

DAC：Dedicated Admin Connection

当SQL Server因系统资源不足，或其它异常导致无法建立数据库连接时，可以使用系统预留的DAC连接到数据库，进行一些问题诊断和故障排除。DAC只能使用有限的资源。请勿使用DAC运行需要消耗大量资源的查询，否则可能发生严重的阻塞。

如何启用DAC功能专用管理员连接功能以及注意事项

1、只有系統管理員(sysadmin)角色相關成員可以使用DAC連接存取SQL Server(Local)本地连接

2、一個執行個體只能存在一個DAC。

3、使用DAC通常是讓DBA查詢和排解SQL Server問題(當無法正常連接執行個體)，

4、好比執行sp_who2、Kill SPID、DBCC SQLPERF、DBCC DROPCLEANBUFFERS …等,使用DAC連接時，切勿執行需耗費大量資源的命令，如DBCC CHECKDB、DBCC SHRINKDATABASE..等

5、使用DAC登录才能修改系统表或者查看系统表，以前SQL2000的时候你可以随便修改系统表，到了SQL2005就开始限制您了

开启DAC的SQL

 1 USE master        
 2 GO        
 3 sp_configure 'show advanced options', 1 
 4 GO    
 5 sp_configure 'remote admin connections', 1 
 6 GO        
 7 RECONFIGURE WITH OVERRIDE    
 8 GO
 9 
10 
11 SELECT *  FROM sys.configurations where name = 'remote admin connections'

也可以在外围应用配置器那里开启

命令行下使用DAC登录
sqlcmd加上 /A 选项专用管理连接
sqlcmd /S JOE /E /A
1>DBCC DROPCLEANBUFFERS
2>GO

排错和诊断的SQL

1 SELECT * FROM sys.dm_tran_locks
2 SELECT * FROM sys.dm_os_memory_cache_counters
3 SELECT * FROM sys.dm_exec_requests 
4 SELECT * FROM sys.dm_exec_sessions

例如查询 sys.dm_tran_locks 以了解锁定状态

查询 sys.dm_os_memory_cache_counters ，检查缓存数量

查询sys.dm_exec_requests 和 sys.dm_exec_sessions 以了解活动的会话和请求。
避免使用需要消耗大量资源的 DMV（例如，sys.dm_tran_version_store 需要扫描整个版本存储区，并且会导致大量的 I/O）或使用了复杂联接的 DMV

在sqlserver management studio中使用DAC连接的时候，要选择新建查询或者数据库引擎查询，不能使用一上来默认的那个登录框进行DAC连接登录，那个

是连接数据库引擎的，如果用DAC连数据库引擎，会报不支持DAC连接。

下面说一下DAC侦听的端口号

若要了解 DAC 所侦听的端口号，可以看SQL错误日志
SQL错误日志
消息
Dedicated admin connection support was established for listening remotely on port 1434.

其他有关DAC错误日志的消息：

消息
Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [客户端: 127.0.0.1]

消息
Configuration option 'remote admin connections' changed from 1 to 1. Run the RECONFIGURE statement to install

DAC的本地连接和远程连接的方式：

如果将 SQL Server 配置为接受远程管理连接，则必须使用显式端口号启动 DAC：

sqlcmd –Stcp:<server>,<port>

sqlcmd /Stcp:192.168.1.100,1434 /U sa /P test

SQL Server 错误日志列出了 DAC 的端口号，默认情况下为 1434。

如果将 SQL Server 配置为只接受本地 DAC 连接，请使用以下命令和环回适配器进行连接：

sqlcmd –S127.0.0.1,1434

或者

sqlcmd加上 /A 选项专用管理连接
sqlcmd /S JOE /E /A

或者

总结：经过本人的实验，发现无论你是用sqlcmd或者SSMS，本地连接还是远程连接，都要使用这种方式

sqlcmd –Stcp:<server>,<port>

本地：sqlcmd –S127.0.0.1,1434

远程：sqlcmd /Stcp:192.168.1.100,1434 /U sa /P test

网上有些文章说不用加端口号，启用SQL Browser服务，就可以连接SQLSERVER，实际上不加1434端口号的话，已经不是在使用DAC来

连接SQLSERVER了，不加1434端口号使用的只是普通连接

2013-11-30补充：

反编译了一下DAC的DLL

在下面的公用DLL路径

DAC功能应该就是调用这个路径下的C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Dac.dll

用ILSpy这个工具来反编译，实际上SQLSRVER很多功能组件都是用.NET来编写的

除非一些核心功能组件用C++或者C，你们会发现很多DLL都可以用ILSpy这个.NET反编译工具来反编译

微乳并没有混淆他们的代码，这些如果没有开发文档的话，要理解这些代码的层次结构和意思会比较困难

其中一个类的代码

  1 using Microsoft.SqlServer.Management.Common;
  2 using Microsoft.SqlServer.Management.Smo;
  3 using Microsoft.SqlServer.Management.SmoMetadataProvider;
  4 using Microsoft.SqlServer.Management.SqlParser.Common;
  5 using Microsoft.SqlServer.Management.SqlParser.Metadata;
  6 using Microsoft.SqlServer.Management.SqlParser.MetadataDifferencer;
  7 using Microsoft.SqlServer.Management.SqlParser.MetadataServices;
  8 using System;
  9 using System.Collections;
 10 using System.Collections.Generic;
 11 using System.Globalization;
 12 using System.IO;
 13 using System.Linq;
 14 using System.Runtime.CompilerServices;
 15 using System.Text;
 16 using System.Text.RegularExpressions;
 17 using System.Xml;
 18 namespace Microsoft.SqlServer.Management.Dac.UI
 19 {
 20     internal class InternalUIHooks
 21     {
 22         private static class DifferencerTestUtils
 23         {
 24             private class DacUtils
 25             {
 26                 public class MetadataObjectComparer : IComparer<IMetadataObject>
 27                 {
 28                     public static InternalUIHooks.DifferencerTestUtils.DacUtils.MetadataObjectComparer Instance = new InternalUIHooks.DifferencerTestUtils.DacUtils.MetadataObjectComparer();
 29                     public int Compare(IMetadataObject x, IMetadataObject y)
 30                     {
 31                         if (object.ReferenceEquals(x, y))
 32                         {
 33                             return 0;
 34                         }
 35                         if (x == null)
 36                         {
 37                             return -1;
 38                         }
 39                         if (y == null)
 40                         {
 41                             return 1;
 42                         }
 43                         IList<IMetadataObject> hierarchy = InternalUIHooks.DifferencerTestUtils.DacUtils.MetadataObjectComparer.GetHierarchy(x);
 44                         IList<IMetadataObject> hierarchy2 = InternalUIHooks.DifferencerTestUtils.DacUtils.MetadataObjectComparer.GetHierarchy(y);
 45                         int num = 0;
 46                         while (num < hierarchy.Count || num < hierarchy2.Count)
 47                         {
 48                             if (num >= hierarchy.Count)
 49                             {
 50                                 return -1;
 51                             }
 52                             if (num >= hierarchy2.Count)
 53                             {
 54                                 return 1;
 55                             }
 56                             int num2 = hierarchy[num].TypeInfo().CompareTo(hierarchy2[num].TypeInfo());
 57                             if (num2 != 0)
 58                             {
 59                                 return num2;
 60                             }
 61                             int num3 = StringComparer.Ordinal.Compare(hierarchy[num].Name, hierarchy2[num].Name);
 62                             if (num3 != 0)
 63                             {
 64                                 return num3;
 65                             }
 66                             num++;
 67                         }
 68                         return 0;
 69                     }
 70                     private static IList<IMetadataObject> GetHierarchy(IMetadataObject obj)
 71                     {
 72                         List<IMetadataObject> list = new List<IMetadataObject>();
 73                         for (IMetadataObject metadataObject = obj; metadataObject != null; metadataObject = InternalUIHooks.DifferencerTestUtils.DacUtils.GetParentObjectSafe(metadataObject))
 74                         {
 75                             list.Add(metadataObject);
 76                         }
 77                         list.Reverse();
 78                         return list;
 79                     }
 80                 }
 81                 internal static DacType CreateDacFromSql(string sql)
 82                 {
 83                     return InternalUIHooks.DifferencerTestUtils.DacUtils.CreateDacFromSql(sql, new Version("1.1.1.1"), string.Empty);
 84                 }
 85                 internal static DacType CreateDacFromSql(string sql, Version version, string description)
 86                 {
 87                     DacCompilationUnit dacCompilationUnit = new DacCompilationUnit("Dac", version, "SQL_Latin1_General_CP1_CI_AS");
 88                     dacCompilationUnit.Description = description;
 89                     dacCompilationUnit.AddTSqlSourceFile("input.sql", sql);
 90                     DacCompilationResult dacCompilationResult = dacCompilationUnit.Compile();
 91                     List<DacCompilationError> list = new List<DacCompilationError>(dacCompilationResult.Errors);
 92                     if (list.Count != 0)
 93                     {
 94                         InternalUIHooks.DifferencerTestUtils.DacUtils.PrintCompilationResultErrors(dacCompilationResult);
 95                     }
 96                     return InternalUIHooks.DifferencerTestUtils.DacUtils.NormalizeDacType(dacCompilationResult.DacType);
 97                 }
 98                 private static DacType NormalizeDacType(DacType dacType)
 99                 {
100                     DacType result;
101                     using (MemoryStream memoryStream = new MemoryStream())
102                     {
103                         DacType.Save(dacType, memoryStream);
104                         memoryStream.Seek(0L, SeekOrigin.Begin);
105                         result = DacType.Load(memoryStream);
106                     }
107                     return result;
108                 }
109                 private static void PrintCompilationResultErrors(DacCompilationResult result)
110                 {
111                     List<DacCompilationError> list = new List<DacCompilationError>(result.Errors);
112                     Console.WriteLine("Compilation Result Errors (" + list.Count + ")");
113                     foreach (DacCompilationError current in list)
114                     {
115                         Console.WriteLine("\t- {0}{1}: " + current.ToString(), current.IsWarning ? "Warning" : "Error", (current.SourceInfo != null) ? (" " + InternalUIHooks.DifferencerTestUtils.DacUtils.GetLocationString(current.SourceInfo)) : "");
116                     }
117                     Console.WriteLine();
118                 }
119                 private static string GetLocationString(SourceInfo sourceInfo)
120                 {
121                     return string.Concat(new object[]
122                     {
123                         sourceInfo.Filename, 
124                         ":", 
125                         sourceInfo.Start.LineNumber, 
126                         ",", 
127                         sourceInfo.Start.ColumnNumber
128                     });
129                 }
130                 public static DacType CreateDacFromFile(string fileName)
131                 {
132                     string sql = File.ReadAllText(fileName);
133                     return InternalUIHooks.DifferencerTestUtils.DacUtils.CreateDacFromSql(sql);
134                 }
135                 public static DacType CreateDacFromFile(string fileName, Version version, string description)
136                 {
137                     string sql = File.ReadAllText(fileName);
138                     return InternalUIHooks.DifferencerTestUtils.DacUtils.CreateDacFromSql(sql, version, description);
139                 }
140                 public static string GetMetadataObjectIdString(IMetadataObject obj)
141                 {
142                     List<string> list = new List<string>();
143                     IMetadataObject metadataObject = obj;
144                     while (true)
145                     {
146                         TypeInfo typeInfo = metadataObject.TypeInfo();
147                         string text = typeInfo.Name.Substring(1);
148                         string item = string.Format(CultureInfo.InvariantCulture, "{0}{1}", new object[]
149                         {
150                             text, 
151                             (!(metadataObject is IServer)) ? ("[" + metadataObject.Name.Replace("]", "]]") + "]") : string.Empty
152                         });
153                         list.Add(item);
154                         if (metadataObject is IServer)
155                         {
156                             break;
157                         }
158                         metadataObject = InternalUIHooks.DifferencerTestUtils.DacUtils.GetParentObject(metadataObject);
159                     }
160                     list.Reverse();
161                     return string.Join("/", list.ToArray());
162                 }
163                 public static string GetMetadataObjectTypeString(IMetadataObject obj)
164                 {
165                     return obj.TypeInfo().Name.Substring(1);
166                 }
167                 public static IMetadataObject GetParentObject(IMetadataObject obj)
168                 {
169                     MetadataTypeInfo metadataTypeInfo = obj.TypeInfo();
170                     MetadataPropertyInfo parentProperty = metadataTypeInfo.ParentProperty;
171                     object propertyValue = MetadataUtils.GetPropertyValue(obj, metadataTypeInfo, parentProperty);
172                     return (IMetadataObject)propertyValue;
173                 }
174                 public static IMetadataObject GetParentObjectSafe(IMetadataObject obj)
175                 {
176                     MetadataTypeInfo metadataTypeInfo = obj.TypeInfo();
177                     MetadataPropertyInfo parentProperty = metadataTypeInfo.ParentProperty;
178                     if (parentProperty == null)
179                     {
180                         return null;
181                     }
182                     object propertyValue = MetadataUtils.GetPropertyValue(obj, metadataTypeInfo, parentProperty);
183                     return (IMetadataObject)propertyValue;
184                 }
185                 public static void WriteObject(XmlWriter writer, object value, bool writeDefinition)
186                 {
187                     Type type = (value != null) ? value.GetType() : null;
188                     if (value == null)
189                     {
190                         writer.WriteAttributeString("isNull", true.ToString());
191                         return;
192                     }
193                     if (type.IsPrimitive || type.IsEnum || type == typeof(string))
194                     {
195                         writer.WriteString(value.ToString());
196                         return;
197                     }
198                     if (type == typeof(IdentityColumnInfo))
199                     {
200                         writer.WriteStartElement("Increment");
201                         InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, ((IdentityColumnInfo)value).Increment, true);
202                         writer.WriteEndElement();
203                         writer.WriteStartElement("Seed");
204                         InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, ((IdentityColumnInfo)value).Seed, true);
205                         writer.WriteEndElement();
206                         return;
207                     }
208                     if (type == typeof(ComputedColumnInfo))
209                     {
210                         writer.WriteStartElement("IsPersisted");
211                         InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, ((ComputedColumnInfo)value).IsPersisted, true);
212                         writer.WriteEndElement();
213                         writer.WriteStartElement("Text");
214                         InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, ((ComputedColumnInfo)value).Text, true);
215                         writer.WriteEndElement();
216                         return;
217                     }
218                     if (type == typeof(DataTypeSpec))
219                     {
220                         writer.WriteString(((DataTypeSpec)value).SqlDataType.ToString());
221                         return;
222                     }
223                     if (type == typeof(CollationInfo))
224                     {
225                         writer.WriteStartElement("CollationInfo");
226                         InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, ((CollationInfo)value).Name, true);
227                         writer.WriteEndElement();
228                         return;
229                     }
230                     if (value is ISystemClrDataType)
231                     {
232                         writer.WriteStartElement("ISystemClrDataType");
233                         InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, ((ISystemClrDataType)value).Name, true);
234                         writer.WriteEndElement();
235                         return;
236                     }
237                     if (value is IMetadataObject)
238                     {
239                         IMetadataObject obj = (IMetadataObject)value;
240                         MetadataTypeInfo metadataTypeInfo = obj.TypeInfo();
241                         if (metadataTypeInfo.IsValue || (metadataTypeInfo.IsReference && writeDefinition))
242                         {
243                             writer.WriteStartElement(InternalUIHooks.DifferencerTestUtils.DacUtils.GetMetadataObjectTypeString(obj));
244                             foreach (MetadataPropertyInfo current in metadataTypeInfo.InstanceProperties)
245                             {
246                                 object propertyValue = MetadataUtils.GetPropertyValue(obj, metadataTypeInfo, current);
247                                 writer.WriteStartElement(current.Name);
248                                 InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, propertyValue, writeDefinition && current.PropertyType == PropertyType.Child);
249                                 writer.WriteEndElement();
250                             }
251                             writer.WriteEndElement();
252                             return;
253                         }
254                         if (metadataTypeInfo.IsReference)
255                         {
256                             writer.WriteAttributeString("id", InternalUIHooks.DifferencerTestUtils.DacUtils.GetMetadataObjectIdString(obj));
257                             return;
258                         }
259                     }
260                     else
261                     {
262                         if (value is IEnumerable)
263                         {
264                             foreach (object current2 in (IEnumerable)value)
265                             {
266                                 string localName = (current2 is IMetadataObject) ? InternalUIHooks.DifferencerTestUtils.DacUtils.GetMetadataObjectTypeString((IMetadataObject)current2) : "Item";
267                                 writer.WriteStartElement(localName);
268                                 InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, current2, writeDefinition);
269                                 writer.WriteEndElement();
270                             }
271                         }
272                     }
273                 }
274             }
275             public static void WriteChangeResult(XmlWriter writer, ChangeResult changeResult)
276             {
277                 writer.WriteStartElement("ChangeResult");
278                 writer.WriteStartElement("CreatedObjects");
279                 InternalUIHooks.DifferencerTestUtils.WriteObjectRefs(writer, changeResult.CreatedObjects);
280                 writer.WriteEndElement();
281                 writer.WriteStartElement("DeletedObjects");
282                 InternalUIHooks.DifferencerTestUtils.WriteObjectRefs(writer, changeResult.DeletedObjects);
283                 writer.WriteEndElement();
284                 writer.WriteStartElement("ModifiedObjects");
285                 InternalUIHooks.DifferencerTestUtils.WriteModifiedObjects(writer, changeResult.SourceModifiedObjects.Values);
286                 writer.WriteEndElement();
287                 writer.WriteEndElement();
288             }
289             public static void WriteModifiedObjects(XmlWriter writer, IEnumerable<ObjectDifference> objectDifferenceCollection)
290             {
291                 List<ObjectDifference> list = objectDifferenceCollection.ToList<ObjectDifference>();
292                 list.Sort((ObjectDifference x, ObjectDifference y) => InternalUIHooks.DifferencerTestUtils.DacUtils.MetadataObjectComparer.Instance.Compare(x.ChangedObjectSource, y.ChangedObjectSource) * 2 + InternalUIHooks.DifferencerTestUtils.DacUtils.MetadataObjectComparer.Instance.Compare(x.ChangedObjectTarget, y.ChangedObjectTarget));
293                 list.ForEach(delegate(ObjectDifference diff)
294                 {
295                     InternalUIHooks.DifferencerTestUtils.WriteObjectDifference(writer, diff);
296                 }
297                 );
298             }
299             public static void WriteObjectDifference(XmlWriter writer, ObjectDifference objectDifference)
300             {
301                 writer.WriteStartElement(InternalUIHooks.DifferencerTestUtils.DacUtils.GetMetadataObjectTypeString(objectDifference.ChangedObjectSource));
302                 writer.WriteStartElement("ChangedObjectSource");
303                 writer.WriteAttributeString("Id", InternalUIHooks.DifferencerTestUtils.DacUtils.GetMetadataObjectIdString(objectDifference.ChangedObjectSource));
304                 writer.WriteEndElement();
305                 writer.WriteStartElement("ChangedObjectTarget");
306                 writer.WriteAttributeString("Id", InternalUIHooks.DifferencerTestUtils.DacUtils.GetMetadataObjectIdString(objectDifference.ChangedObjectTarget));
307                 writer.WriteEndElement();
308                 writer.WriteStartElement("Properties");
309                 List<PropertyDifference> list = (
310                     from p in objectDifference.PropertyDifferences.Values
311                     orderby p.Name
312                     select p).ToList<PropertyDifference>();
313                 list.ForEach(delegate(PropertyDifference p)
314                 {
315                     InternalUIHooks.DifferencerTestUtils.WritePropertyDifference(writer, p);
316                 }
317                 );
318                 writer.WriteEndElement();
319                 writer.WriteEndElement();
320             }
321             public static void WritePropertyDifference(XmlWriter writer, PropertyDifference propertyDifference)
322             {
323                 writer.WriteStartElement(propertyDifference.Name);
324                 if (propertyDifference is OrderedCollectionDifference)
325                 {
326                     OrderedCollectionDifference orderedCollectionDifference = propertyDifference as OrderedCollectionDifference;
327                     using (IEnumerator<OrderedScalarDifference> enumerator = orderedCollectionDifference.OrderDifferences.GetEnumerator())
328                     {
329                         while (enumerator.MoveNext())
330                         {
331                             OrderedScalarDifference current = enumerator.Current;
332                             writer.WriteStartElement(InternalUIHooks.DifferencerTestUtils.DacUtils.GetMetadataObjectTypeString((IMetadataObject)current.SourceValue));
333                             writer.WriteAttributeString("sourceIndex", current.SourceIndex.ToString(CultureInfo.InvariantCulture));
334                             writer.WriteAttributeString("targetIndex", current.TargetIndex.ToString(CultureInfo.InvariantCulture));
335                             writer.WriteStartElement("SourceValue");
336                             InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, current.SourceValue, false);
337                             writer.WriteEndElement();
338                             writer.WriteStartElement("TargetValue");
339                             InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, current.TargetValue, false);
340                             writer.WriteEndElement();
341                             writer.WriteEndElement();
342                         }
343                         goto IL_12E;
344                     }
345                 }
346                 if (propertyDifference is ScalarDifference)
347                 {
348                     ScalarDifference scalarDifference = propertyDifference as ScalarDifference;
349                     writer.WriteStartElement("SourceValue");
350                     InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, scalarDifference.SourceValue, false);
351                     writer.WriteEndElement();
352                     writer.WriteStartElement("TargetValue");
353                     InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, scalarDifference.TargetValue, false);
354                     writer.WriteEndElement();
355                 }
356                 IL_12E:
357                 writer.WriteEndElement();
358             }
359             public static void WriteObjectRefs(XmlWriter writer, IEnumerable<IMetadataObject> objectCollection)
360             {
361                 List<IMetadataObject> list = objectCollection.ToList<IMetadataObject>();
362                 list.Sort(InternalUIHooks.DifferencerTestUtils.DacUtils.MetadataObjectComparer.Instance);
363                 foreach (IMetadataObject current in list)
364                 {
365                     writer.WriteStartElement(InternalUIHooks.DifferencerTestUtils.DacUtils.GetMetadataObjectTypeString(current));
366                     InternalUIHooks.DifferencerTestUtils.DacUtils.WriteObject(writer, current, false);
367                     writer.WriteEndElement();
368                 }
369             }
370             public static bool IsChangeResultDrift(ChangeResult changeResult)
371             {
372                 bool flag = changeResult.CreatedObjects.Count<IMetadataObject>() != 0 || changeResult.DeletedObjects.Count<IMetadataObject>() != 0;
373                 foreach (ObjectDifference current in changeResult.SourceModifiedObjects.Values)
374                 {
375                     flag = (!(current.ChangedObjectSource is IConstraint) || (flag | InternalUIHooks.DifferencerTestUtils.IsConstraintDifferenceDrift(current)));
376                 }
377                 return flag;
378             }
379             private static bool IsConstraintDifferenceDrift(ObjectDifference objectDifference)
380             {
381                 bool result = false;
382                 foreach (PropertyDifference current in objectDifference.PropertyDifferences.Values)
383                 {
384                     if (current.Name == "IsChecked")
385                     {
386                         if (!InternalUIHooks.DifferencerTestUtils.IsNotForReplicationConstraint((IConstraint)objectDifference.ChangedObjectSource) && !InternalUIHooks.DifferencerTestUtils.IsNotForReplicationConstraint((IConstraint)objectDifference.ChangedObjectTarget))
387                         {
388                             result = true;
389                         }
390                     }
391                     else
392                     {
393                         result = true;
394                     }
395                 }
396                 return result;
397             }
398             private static bool IsNotForReplicationConstraint(IConstraint constraint)
399             {
400                 bool result;
401                 switch (constraint.Type)
402                 {
403                     case ConstraintType.Check:
404                     {
405                         result = ((ICheckConstraint)constraint).NotForReplication;
406                         break;
407                     }
408                     case ConstraintType.ForeignKey:
409                     {
410                         result = ((IForeignKeyConstraint)constraint).NotForReplication;
411                         break;
412                     }
413                     case ConstraintType.PrimaryKey:
414                     case ConstraintType.Unique:
415                     {
416                         result = false;
417                         break;
418                     }
419                     default:
420                     {
421                         result = false;
422                         break;
423                     }
424                 }
425                 return result;
426             }
427         }
428         public const string DacTypeName = "Dac";
429         public const string DefaultCollation = "SQL_Latin1_General_CP1_CI_AS";
430         public event TextUpdateHandler ScriptUpdate
431         {
432             [MethodImpl(MethodImplOptions.Synchronized)]
433             add
434             {
435                 this.ScriptUpdate = (TextUpdateHandler)Delegate.Combine(this.ScriptUpdate, value);
436             }
437             [MethodImpl(MethodImplOptions.Synchronized)]
438             remove
439             {
440                 this.ScriptUpdate = (TextUpdateHandler)Delegate.Remove(this.ScriptUpdate, value);
441             }
442         }
443         public event TextUpdateHandler ModelUpdate
444         {
445             [MethodImpl(MethodImplOptions.Synchronized)]
446             add
447             {
448                 this.ModelUpdate = (TextUpdateHandler)Delegate.Combine(this.ModelUpdate, value);
449             }
450             [MethodImpl(MethodImplOptions.Synchronized)]
451             remove
452             {
453                 this.ModelUpdate = (TextUpdateHandler)Delegate.Remove(this.ModelUpdate, value);
454             }
455         }
456         public event TextUpdateHandler ActionUpdate
457         {
458             [MethodImpl(MethodImplOptions.Synchronized)]
459             add
460             {
461                 this.ActionUpdate = (TextUpdateHandler)Delegate.Combine(this.ActionUpdate, value);
462             }
463             [MethodImpl(MethodImplOptions.Synchronized)]
464             remove
465             {
466                 this.ActionUpdate = (TextUpdateHandler)Delegate.Remove(this.ActionUpdate, value);
467             }
468         }
469         public DacType CreateDacFromSql(string sql)
470         {
471             return this.CreateDacFromSql(sql, new Version("1.1.1.1"), string.Empty);
472         }
473         public DacType CreateDacFromSql(string sql, Version version, string description)
474         {
475             DacCompilationUnit dacCompilationUnit = new DacCompilationUnit("Dac", version, "SQL_Latin1_General_CP1_CI_AS");
476             dacCompilationUnit.Description = description;
477             dacCompilationUnit.AddTSqlSourceFile("input.sql", sql);
478             DacCompilationResult dacCompilationResult = dacCompilationUnit.Compile();
479             List<DacCompilationError> list = new List<DacCompilationError>(dacCompilationResult.Errors);
480             if (list.Count != 0)
481             {
482                 StringBuilder stringBuilder = new StringBuilder();
483                 stringBuilder.Append("***COMPILE ERROR***");
484                 stringBuilder.Append(Environment.NewLine);
485                 foreach (DacCompilationError current in list)
486                 {
487                     stringBuilder.Append(current.Message);
488                     stringBuilder.Append(Environment.NewLine);
489                 }
490                 this.OnScriptUpdate(stringBuilder.ToString());
491                 return null;
492             }
493             return this.NormalizeDacType(dacCompilationResult.DacType);
494         }
495         public void CompareSQLScripts(string sourceSQL, string targetSQL, bool isAzure)
496         {
497             this.OnActionUpdate("Building Source SQL DAC");
498             DacType dacType = this.CreateDacFromSql(sourceSQL, new Version("1.1.1.1"), "V1");
499             this.OnActionUpdate("Building Target SQL DAC");
500             DacType dacType2 = this.CreateDacFromSql(targetSQL, new Version("2.2.2.2"), "V2");
501             this.OnActionUpdate("Preparing Incremental Upgrade Script");
502             PrepareIncrementalUpgradeScriptStep prepareIncrementalUpgradeScriptStep = new PrepareIncrementalUpgradeScriptStep(dacType.Definition, "Dac", dacType2.Definition, "Dac", this.GetDefaultDifferencerFilter(), isAzure ? ScriptTarget.SqlAzure : ScriptTarget.Sql100, false);
503             prepareIncrementalUpgradeScriptStep.Execute();
504             IEnumerable<ActionGroup> incrementalUpgradeActionGroups = prepareIncrementalUpgradeScriptStep.GetIncrementalUpgradeActionGroups();
505             foreach (ActionGroup current in incrementalUpgradeActionGroups)
506             {
507                 ActionGroupScripter actionGroupScripter = current.GetActionGroupScripter(ScriptTarget.Sql100);
508                 this.OnActionUpdate(actionGroupScripter.Description);
509             }
510             this.OnScriptUpdate(prepareIncrementalUpgradeScriptStep.CompleteScript);
511             this.OnModelUpdate(this.GetChangeResultOutput(dacType.Definition.Databases["Dac"], dacType2.Definition.Databases["Dac"]));
512         }
513         public string GetDatabaseScript(ServerConnection serverConnection, string databaseName)
514         {
515             string input = string.Empty;
516             try
517             {
518                 IServer offlineDatabase = InternalUIHooks.GetOfflineDatabase(serverConnection, databaseName);
519                 PrepareDeploymentScriptStep prepareDeploymentScriptStep = new PrepareDeploymentScriptStep(offlineDatabase, databaseName, new TargetEngineInfo(serverConnection), true);
520                 prepareDeploymentScriptStep.Execute();
521                 input = prepareDeploymentScriptStep.CompleteDatabaseObjectsScript;
522             }
523             catch (Exception exception)
524             {
525                 InternalUIHooks.ThrowExceptionMessage(exception);
526             }
527             Regex regex = new Regex("^RAISERROR.*\\nGO.*\\n", RegexOptions.Multiline);
528             return regex.Replace(input, Environment.NewLine);
529         }
530         public void Apply(ServerConnection serverConnection, string sql, string databaseName)
531         {
532             Database database = new Server(serverConnection).Databases[databaseName];
533             try
534             {
535                 string text = "Dac";
536                 DacStore dacStore = new DacStore(serverConnection);
537                 DacDefinition dacDefinition = new DacDefinition(dacStore, text, databaseName);
538                 if (!dacDefinition.IsRegistered)
539                 {
540                     text = "Dac";
541                     dacDefinition.Register();
542                 }
543                 else
544                 {
545                     text = dacStore.DacInstances[databaseName].Type.Name;
546                 }
547                 DacCompilationUnit dacCompilationUnit = new DacCompilationUnit(text, new Version(1, 0), database.Collation);
548                 dacCompilationUnit.AddTSqlSourceFile("input.sql", sql);
549                 DacCompilationResult dacCompilationResult = dacCompilationUnit.Compile();
550                 if (dacCompilationResult.Errors.Count<DacCompilationError>() > 0)
551                 {
552                     StringBuilder stringBuilder = new StringBuilder();
553                     foreach (DacCompilationError current in dacCompilationResult.Errors)
554                     {
555                         stringBuilder.Append(current.Message);
556                     }
557                     throw new Exception(stringBuilder.ToString());
558                 }
559                 DacType dacType = dacCompilationResult.DacType;
560                 IServer offlineDatabase = InternalUIHooks.GetOfflineDatabase(serverConnection, databaseName);
561                 this.OnModelUpdate(this.GetChangeResultOutput(offlineDatabase.Databases[databaseName], dacType.Definition.Databases[text]));
562                 this.OnScriptUpdate(dacStore.GetIncrementalUpgradeScript(databaseName, dacType));
563                 dacStore.IncrementalUpgrade(databaseName, dacType, new DacUpgradeOptions());
564             }
565             catch (Exception exception)
566             {
567                 InternalUIHooks.ThrowExceptionMessage(exception);
568             }
569         }
570         private string GetChangeResultOutput(IDatabase sourceDatabase, IDatabase targetDatabase)
571         {
572             ChangeResult changeResult = Differencer.Compare(sourceDatabase, targetDatabase, this.GetDefaultDifferencerFilter());
573             StringBuilder stringBuilder = new StringBuilder();
574             using (XmlWriter xmlWriter = XmlWriter.Create(stringBuilder, new XmlWriterSettings
575             {
576                 Indent = true, 
577                 IndentChars = "   ", 
578                 NewLineChars = Environment.NewLine, 
579                 CloseOutput = false
580             }))
581             {
582                 InternalUIHooks.DifferencerTestUtils.WriteChangeResult(xmlWriter, changeResult);
583             }
584             return stringBuilder.ToString();
585         }
586         private static void ThrowExceptionMessage(Exception exception)
587         {
588             StringBuilder stringBuilder = new StringBuilder();
589             while (exception != null)
590             {
591                 stringBuilder.AppendLine("->" + exception.Message);
592                 exception = exception.InnerException;
593             }
594             throw new Exception(stringBuilder.ToString());
595         }
596         private static IServer GetOfflineDatabase(ServerConnection serverConnection, string databaseName)
597         {
598             DacDatabaseObjectExtractor dacDatabaseObjectExtractor = new DacDatabaseObjectExtractor(ConnectionManager.Create(serverConnection), databaseName);
599             DacExtractValidationResult dacExtractValidationResult = dacDatabaseObjectExtractor.Validate();
600             if (dacExtractValidationResult.ErrorObjects.Count<ValidatedObject>() > 0)
601             {
602                 StringBuilder stringBuilder = new StringBuilder();
603                 stringBuilder.AppendLine("Database cannot be extracted due to the unsupported objects --");
604                 foreach (ValidatedObject current in dacExtractValidationResult.ErrorObjects)
605                 {
606                     stringBuilder.AppendLine(current.Name + " : " + current.Description);
607                 }
608                 throw new Exception(stringBuilder.ToString());
609             }
610             return SmoMetadataProvider.CreateDisconnectedProvider(dacDatabaseObjectExtractor.Discover()).Server;
611         }
612         private DacDifferencerFilter GetDefaultDifferencerFilter()
613         {
614             return DacDifferencerFilter.Create(new TargetEngineInfo(DatabaseEngineType.Standalone, new ServerVersion(10, 0)), 26, true, DatabaseCompatibilityLevel.Version100, "SQL_Latin1_General_CP1_CI_AS");
615         }
616         private DacType NormalizeDacType(DacType dacType)
617         {
618             DacType result;
619             using (MemoryStream memoryStream = new MemoryStream())
620             {
621                 DacType.Save(dacType, memoryStream);
622                 memoryStream.Seek(0L, SeekOrigin.Begin);
623                 result = DacType.Load(memoryStream);
624             }
625             return result;
626         }
627         protected void OnActionUpdate(string value)
628         {
629             if (this.ActionUpdate != null)
630             {
631                 this.ActionUpdate(this, new UpdateUiArgs(value));
632             }
633         }
634         protected void OnScriptUpdate(string value)
635         {
636             if (this.ScriptUpdate != null)
637             {
638                 this.ScriptUpdate(this, new UpdateUiArgs(value));
639             }
640         }
641         protected void OnModelUpdate(string value)
642         {
643             if (this.ModelUpdate != null)
644             {
645                 this.ModelUpdate(this, new UpdateUiArgs(value));
646             }
647         }
648     }
649 }

View Code

用C#调用DAC的例子

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using Microsoft.SqlServer.Management.Dac;
 6 using Microsoft.SqlServer.Management.Smo;
 7 using Microsoft.SqlServer.Management.Common;
 8 using System.Data.SqlClient;
 9 
10 namespace ConsoleApplication15
11 {
12     class Program
13     {
14         static void Main(string[] args)
15         {
16             try
17             {
18                 String connectionString = "Data Source=XXXXX,1433;Initial Catalog=master;Integrated Security=False;User ID=sa;Password=sa;";
19                 SqlConnection sqlConnection = new SqlConnection(connectionString);
20                 ServerConnection conn = new ServerConnection(sqlConnection);
21                 Server destServer = new Server(conn);
22                 Console.WriteLine(destServer.Information.Version);
23 
24 
25                 DacExtractionUnit dacUnit = new DacExtractionUnit(destServer, "DB2", "DB2", new Version("1.0.0.1"));
26 
27                 dacUnit.Extract(@"e:\DB2.dacpac");
28                 Console.WriteLine("finish");
29             }
30             catch(Exception ex)
31             {
32                 Console.WriteLine(ex);
33 
34             }
35             Console.WriteLine("press any key to close");
36             Console.ReadLine();
37                 
38         }
39     }
40 }

View Code

posted @ 2012-09-23 09:28 桦仔阅读(11693) 评论(3) 编辑收藏举报

刷新页面返回顶部

SQLSERVER数据库管理员的专用连接DAC

SQLSERVER数据库管理员的专用连接DAC

公告