i have a client that wants to migrate his data to sql server 2016 . currently they have 2008r and the databases are huge(total of 4 tb) on the instance. what is the best way to performe this with the least amount of down time? i thought about mirroring but im not sure it can be done  between the two versions. if so what are the steps to do so ? if im missing information please let me know. thanks
102523
Problem definition  Our database server needs to be transferred to an other datacenter. It runs on Microsoft SQL Server 2012 Enterprise (64-bit) and contains two databases of about 2TB and 1TB.  Having little to no downtime for this would be ideal.  Workload  Those databases are used for a .NET website and are constantly getting updated.   Having it not available over the weekend would be acceptable though. The currently in use DB would remain the only one in use until the switch over to the new one.  That switch would ideally be made just by changing DNS entries to point to the new DB server while making sure the DB is not being updated.  Also, time taken by this operation does not really matter as long as the switch from one server to the other (downtime) is kept low.  Approaches considered   Backup and restore  This has been done in the past but involved a high downtime even though it was done through an internal network, so more efficiently than through Internet Log shipping  As far as I understand, this approach would minimize downtime by configuring a master/slave and transferring  an exact copy of the master DB to its slave being read only. As mentioned above, no access to the slave would be necessary and we just need a way to have a replica of the master DB without data corruption.   It also seems to be quite efficient in terms of resources utilization and wouldn't impact to much the master performance.   I might be wrong about this approach so feel free to correct me. Database mirroring  I'm not too aware of that approach but it seems like a valid option. No need to have real time sync and performance of the master is quite important so asynchronous would be the way to go if this approach were to be chosen. Other options?  That server runs directly on bare metal hardware so lower level solutions are unfortunately not an option. Maybe there is a better way to get this done?   Constraints  As described, those databases are quite big to the point they are hard to maintain but that's an other problem.  The versions of SQL Server will be the same (Microsoft SQL Server 2012 Enterprise 64-bit).  It will have to be transferred over network between two datacenters so most probably over Internet. Having disks sent from one site to the other for an initial sync is unfortunately not an option. Having some sort of security for the transfer would be ideal but we will do the best of this situation.  That should give a quite good overview of our needs for this task and hopefully some of you had to face that situation before.
94856
I'm not DBA at all but I need to do something to synchronize four SQL server databases. I just discover there is a Merge Replication system in SQL Server 2008. At this moment I'm reading the specification and the documentation but it's not clear if this tool is able to do real synchronization or is just merging data from my master database to my slave database. By real synchronization I mean the data is synchronized from my master to my slaves but also from my slaves to my master.
86788
Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers? The biggest database is 0.5 GB. any suggestions are welcome. Thank you.
25857
My aim is to audit all queries on an instance, who's running them and from where, how many reads, in order to consolidate/migrate on another instance.   Figured out the best way is via Extended Events to .xel files, over 2 weeks, copy those and analyse on my well-specd desktop. Set up the capture with the minimal number of details (fields), so really can't make it smaller than this - aprox 12GB in 12 files. Used "Merge Extended Event Files" in SSMS to load the .xel files and process as detailed in   Is this really the best way to do it ? Is there a better tool to load the files ? It's really slow with SSMS 17.7 - example: with only 4 filters so far (before getting into grouping and aggregates) it took over 12h to get to crunch through 50% of the logs.
118242
The current system is installed on the traditional disk, now I want to migrate it to LVM managed disk, how can I migrate it without any data loss? I'm working with vSphere, so I can add and remove disk easily, change system to another virtual disk and rebooting are acceptable. Is there someone can help me? Thank you!
121315
Is there a way to copy maintenance plans from one SQL Server 2012 to another? Both servers are not accessible via the same copy of Management Studio at the same time (on different networks).
46649
We have a small branch app that stores some data in a SQL Express Database.  We want to have an almost real time copy of this table in our headquarters, were we have SQL Enterprise 2014. I say almost, because it doesn't matter if there is a 5 minute delay or something like that.   The main thing is that it's able to synchronise after a network disconnection between branch and headquarter.  Any solution that does not requiere licensing costs for the branch office?
28203
Hi I've stumbled into the administration of a Legacy SQL 2000 server that has had next to no maintenance for the past 8 years (and is very slow).  As I have only been doing DBA / PT for the past 2 years (I'm primarily a Developer) I don't have allot of experience on 2000.  Can anyone point me in the direction of some maintenance, tuning scripts.  Just figured I'd ask first before I reinvent the wheel for something I will rarely use.  Thanks Guys / Girls !
96831
I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a differential backup is added and every hour a transaction log backup is added to the backup device.  When restoring this onto a new server using SSMS I first create a new database, then add the backup device and then restore from that backup device. I then have to manually select the applicable files:   The Full backup (always the first one in the list) The latest differential backup All transaction log backups that came after the last differential backup.     This works fine for the occassional restore, but I need to automate this in T-SQL.   There is some logic on which files to pick, but I don't even know how to get to the content of the backup device yet.  I would like to have a stored procedure that takes the name of the database and the backup device and restores the latest version. Anybody knows of such a thing?  Or do I miss something here and there is a simple command for this?
28720
I have two SQL server 2012 instances and I want to configure mirroring between the 2 servers with database.  My questions are:  How can it automatically change host of one if the other one was dropped ?  Is the performance of mirroring is acceptable and is there another way to make one as host and other is backup then if the main server was dropped the other works automatically better than mirroring?
94661
I am migrating SQL Server DBs to a new instance.  I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog.  Does anyone know of a sample script to do this?  Thanks!
38778
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:  Restore failed for Server '...\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended) System.Data.SqlClient.SqlError: The database was backed up on a server running version 10.50.1600.  That version is incompatible with this server, which is running version 10.00.2531.  Either restore the database on a server that supports the backup, or use a backup  that is compatible with this server. (Microsoft.SqlServer.Smo)   Is there a way to get a backup which is compatible with the older (in my case 10.00.2531) version from the newer (in my case 10.50.1600) version of SQL Server Express?
89109
I read that it's possible to restore a database in SQL Server as long as you're restoring from an older version to a newer version, for backwards compatibility reasons.  Does anyone know off hand if you can restore a database from a *.bak file for different editions of SQL Server?  We are moving a very large database via FTP that will take a couple days, so we'd rather only do this once. If nobody responds by the time we transfer the database via FTP, we will obviously try this out and see if it works by testing, and answer our own question.  Below is a query to get version details of SQL Server.  The productversion is in the format {major revision}.{minor revision}.{release revision}.{build number}.  In my case, the {release revision} has a value of 5500 for the source and 5512 for the target.  So that looks okay. However, the edition is different.  Query:  SELECT    SERVERPROPERTY('productversion'),    SERVERPROPERTY('productlevel'),    SERVERPROPERTY('edition')   Source database:  10.0.5500.0 SP3 Developer Edition (64-bit)   Target database:  10.0.5512.0 SP3 Enterprise Edition (64-bit)
64057
What is the recommended process of upgrading Availability Groups with one Sync Replica from SQL 2014 to 2016?  My understanding is that in-place upgrades are not really liked among database professionals. Is there a way to avoid in-place upgrades in Availability Groups? Is there a possibility to join a 2016 server into an existing 2014 availability group and to failover there, then "kill" one of the other instances and upgrade it? (e.g. by removing/re-installing SQL side-by-side on the same host)  Are there any other upgrade scenarios that we should consider?  The ideal solution would be that we always have 2 online replicas, and that we don't need to do any in-place upgrades.
123419
I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.  I would like to establish a reporting server (may be 2008 or 2012), that consolidates the rows  from selected tables across the 8 source servers into a single instance of those tables on the reporting server. This is one-way replication (no changes will be made to the reporting server). I will need to replicate changes from the source databases with relatively low latency (say 20-30 seconds).  Furthermore, I'd like to find methods to achieve this with as little impact to the source servers as is practical.  3rd-party agents, triggers, or schema mods to those servers are difficult in my environment.  My questions:   What are promising architectures and technologies for  achieving this goal?  I've looked at SQL Server Merge Replication, but I am concerned about latency. Is this an appropriate technology for this goal? Are there many-to-one architectures for transactional replication?   Should I be looking at 1-to-1 replication into 8 databases on my reporting server, followed by some custom merge function (a 2-step replication)?   Thanks, John
60275
What are best practices/important steps when the Windows Server machine hosting SQL Server 2008 R2 is moved to another domain?   What steps definitely/might need to be done for SQL Server before and after the new domain is joined?
81584
We're working on migrating some SQL Server VMs to Azure SQL Managed Instances.  We did an initial backup/restore without a problem:  RESTORE DATABASE [xxx]      FROM URL = 'https://&lt;account-name&gt;.blob.core.windows.net/&lt;container-name&gt;/&lt;filename&gt;.bak'   After doing testing, we now need to do a fresh restore from our SQL VMs.  We started doing the same process we initially did but when restoring, we got an error:  Msg 1801, Level 16, State 1, Line 1 Database 'xxx' already exists. Choose a different database name. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.   So I tried the WITH REPLACE option.  Unfortunately:  Msg 41901, Level 16, State 2, Line 1 One or more of the options (replace) are not supported for this statement in SQL Database Managed Instance. Review the documentation for supported options.   Reviewing the documentation just tells me that WITH REPLACE isn't available on a Managed Instance.  I can't find any documentation on doing an in-place restore/overwrite of an existing managed instance database.  Am I forced to drop and re-create the database?