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

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 桦仔 阅读(...) 评论(...) 编辑 收藏