Creating A Contained Database – SQL 2012/2014

“A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.

  • Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
  • All metadata are defined using the same collation.
  • User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
  • The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.”

MSDN Source reference – click here

T-SQL Code

EXEC sys.sp_configure N’contained database authentication’, N’1′

GO

RECONFIGURE WITH OVERRIDE

GO

USE [master]

GO

ALTER DATABASE [MyDemoDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT

GO

 

SQL Server 2014 Upgrade Advisor

Running the SQL Server 2014 upgrade advisor is a good idea when say planning an in-place upgrade from a previous version of SQL Server.

To do this, run setup from the installation disc, go to planning, run install upgrade advisor.

You might get a pop up such as below, this is resolved by downloading and installing the SQL 2014 feature pack components from this link. Click here.

sql2014_needs

Deprecated Database Features: SQL 2005 – SQL 2014 Links

Below are reference links to Microsoft documentation web pages listing deprecated database features from SQL 2005 to SQL 2014….  

2005   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.90).aspx  

2008   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.100).aspx  

2012  http://technet.microsoft.com/en-us/library/ms143729(v=sql.110).aspx

2014   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.120).aspx  

Resolving SQL Server Agent Job Issue – System.InvalidOperationException: Unable to generate a temporary class (result=1)

Below is the final error message l kept getting when setting up a SQL Server Agent Job which ran an SSIS package. The SSIS package is designed to create a folder in a network share as well as copy some files into that folder.

 

Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started:  11:03:22 AM
Error: 2014-10-07 11:03:23.35
Code: 0x00000001
Source: Create Reports
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.InvalidOperationException: Unable to generate a temporary class (result=1).
error CS2001: Source file ‘C:WindowsTEMPp6e47evu.0.cs’ could not be found
error CS2008: No inputs specified

at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GetSerializersFromCache(XmlMapping[] mappings, Type type)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at ReportExecutionService..ctor()
at ST_960eb2bb9aa341f4a31d1409aa0aadbb.csproj.ScriptMain.Main()
— End of inner exception stack trace —
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  11:03:22 AM
Finished: 11:03:23 AM
Elapsed:  0.546 seconds

 

Resolution:

  • Create a credential on the security level of the sql server instance
  • USE [master]

    GO

    CREATE CREDENTIAL [CredOne] WITH IDENTITY = N’DomainCredOneUser’, SECRET = N’Password’

    GO

  • Create a proxy on the job module of the sql server instance
  • USE [msdb]

    GO

    EXEC msdb.dbo.sp_add_proxy @proxy_name=N’CredOne’,@credential_name=N’CredOne’,

    @enabled=1,

    @description=N’My description’

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=2

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=3

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=10

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=9

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=11

    GO

    EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=12

    GO

  • Ensure the network share folder has full control permission given to the user domainuser
  • Ensure modify permission is given to that same domain account to the folder c:windowstemp on the server box
  • The SQL Job should now run without issues

 

Restore to SQL 2005 from SQL 2008

You cant restore a database backwards from SQL Server 2008 to a SQL 2005 Instance.

One trick you can use is to script the entire database, and ensure you select in the advanced options as shown below, SQL 2005.

Then run the script at the destination SQL 2005 instance.

Remember to change the database file paths in the output SQL script as needed.

e22