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 }
用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 }